Postgres Weekly Issue 365: July 22, 2020

#365 — July 22, 2020

Recreating YikYak with PostgreSQL — YikYak was an anonymous social network that used your location to show you posts 5km around you (we can’t see how that could possibly cause problems.. 😂) and you can recreate the underlying principle of its operation using Postgres’s geographical coordinate support.

Adam Fallon

pgwatch2 v1.8.0 Released — pgwatch2 is a popular Postgres monitoring tool and it now has Pgpool-II, Postgres 13 and TimescaleDB metrics storage support.

Kaarel Moppel

Real-Time Postgres Performance Monitoring — Collect out-of-the-box and custom Postgres metrics and correlate them with data from across your distributed infrastructure and applications. Try it free with Datadog for 14 days.

Datadog sponsor

Partitioning a Large Table Without a Long-Running Lock — You’ve got a huge table, you need to partition it, but you need that table to remain available to your app.. what do you do? Andrew Dunstan has a recipe to follow.

Andrew Dunstan

Getting More Performance for LIKE and ILIKE Statements — Our tip of the week (below) covers pattern matching and LIKE, but what if it’s not enough and you want to speed up performance? There are solutions!

Hans-Jürgen Schönig

GROUPING SETS and NULL Values — Our tip in issue 359 was about grouping sets, a way to perform grouping within a query that’s more complex than simple GROUP BY column can do. Bruce looks at the relationship between using GROUPING SETS and null values.

Bruce Momjian

Representing Dates, Times and Intervals in Postgres — Postgres comes with a bunch of built-in date and time related data types. But why should you use them over strings or integers? Here’s an overview looking at the why, plus advice on how to do so effectively.

RapidLoop

💻Live Webinar: 5 Ways to Improve Postgres Insert Performance — Join us on Aug 19 to learn five simple, yet powerful, techniques to supercharge your PostgreSQL ingest performance - plus live demos and pro tips for each tactic.

Timescale sponsor

Examining the Postgres Catalog with Python — Mark Ryan looks at how to get the most out of database metadata - by writing a program to automatically extract the information held within.

Towards Data Science

Generating & Managing Postgres Schema Migrations with Spring Boot JPA

Muhammad Haroon

▶ Using PEM to Improve Performance in Postgres: The Postgres Tuning Wizard and Performance Diagnostics — An hour-long webinar running through how to use EDB’s Postgres Enterprise Manager (PEM) GUI. (Note: the audio is a bit muffled generally.)

EDB

supported by

💡 Tip of the Week

Pattern matching

If you need to filter query results beyond usual comparisons or equality without setting up full text search, what are your options? LIKE is perhaps the best known:

    SELECT * FROM people WHERE name LIKE 'Sam%';

// All 'people' whose names start with 'Sam' here

There's also ILIKE to make such a query case insensitive:

    SELECT * FROM people WHERE name ILIKE 'sam%';

// 'SAM', 'sAMantha', etc. would be picked up

But did you know there are other options?

SIMILAR TO is like LIKE but it uses the SQL's standards definition of a regular expression to do the match (which means you still get % as a sort of .* equivalent):

    SELECT * FROM people WHERE name SIMILAR TO '(Pat|Sam)%';

// Rows where name starts with Pat.. or Sam..

If you prefer POSIX style regular expressions (and I do!) you can also use operators like ~ (case sensitive) and ~* (case insensitive):

    SELECT * FROM people WHERE name ~* '(Pat|Sam).*';
    

This tip is just to whet your appetite, but there's a lot more in the Pattern Matching documentation. You need to be careful with efficiency and only running such queries over subsets of tables if you're operating at scale (otherwise you might need to go with true full text indexes and searching) but for many situations, Postgres's regular expression and pattern matching support is just fine.

Try out this Tip in PopSQL. PopSQL is a SQL editor built for teams. Think “Google Docs meets a SQL editor”. A free Premium trial awaits Postgres Weekly readers (no credit card required).