Postgres Weekly Issue 330: November 6, 2019

#330 — November 6, 2019

Postgres 12 Initial Query Performance Impressions — We’ve been getting excited about Postgres 12 for ages here, but how does it really perform? Kaarel set up a stress test with various levels of scale and.. it’s a mixed bag with no obvious conclusions to draw.

Kaarel Moppel

Building Columnar Compression in a Row-Oriented Database — How Timescale has achieved 91%-96% compression in the latest version of their TimescaleDB time-series data extension for Postgres.

Timescale

Hands-On PostgreSQL Training with Experts — Special rate for hands on PostgreSQL training with local 2ndQuadrant experts at 2Q PGConf 2019 in Chicago. Courses include: PostgreSQL Database Security, PostgreSQL Multi-master Replication, Postgres Optimization, PostgreSQL Business Continuity.

2ndQuadrant PostgreSQL Training sponsor

postgres-checkup: A Postgres Health Check Tool — A diagnostics tool that performs ‘deep analysis’ of a Postgres database’s health, detect issues, and produces recommendations for resolving any issues found. v1.3.0 has just been released.

Postgres.ai

Application Connection Failover using HAProxy with Xinetd — I’m a huge fan of haproxy, a powerful but easy to manage TCP and HTTP proxy/load balancer, so I’m looking forward to the rest of this series.

Jobin Augustine

Implementing K-Nearest Neighbor Space Partitioned Generalized Search Tree Indexes — K-nearest neighbor answers the question of “What is the closest match?”. PostgreSQL 12 can answer this question, and use indexes while doing it.

Kirk Roybal

Installing the PostgreSQL 12 Package on FreeBSD — You have to do some work since the final release of Postgres 12 isn’t in the quarterly package update yet.

Luca Ferrari

Installing Postgres on FreeBSD via Ansible

Luca Ferrari

📂 Code and Projects

PostgREST 6.0: Serve a RESTful API from Your Postgres Database — It’s not new, but it’s a mature project that’s been doing the rounds on social media again this week, so let’s shine a spotlight on it again :-)

Joe Nelson et al.

Take the Guesswork Out of Improving Query Performance — Based on the query plan, pgMustard offers you tips to make your query faster. Try it for free.

pgMustard sponsor

Managing PostgreSQL's Partitioned Tables with Ruby — pg_partition_manager is a new gem for maintaining partitioned tables that need to be created and dropped over time as you add and expire time-based data in your app.

Benjamin Curtis

Pgpool-II 4.1.0 Released — Adds connection pooling and load balancing to Postgres. 4.1 introduces statement level load balancing and auto failback.

Pgpool Global Development Group

supported by

💡 Tip of the Week

Putting multiple LIKE patterns into an array

A simple way to perform arbitary searches over the contents of columns is by using the LIKE clause in your queries. For example, in a table of blog posts, this query could find all posts with a title containing the string 'Java':

    SELECT * FROM posts WHERE title LIKE '%Java%';
    

IF you want to create more elaborate queries, things can soon become unwieldy:

    SELECT * FROM posts WHERE title LIKE '%Java%' OR title LIKE '%Perl%' OR title LIKE '%Python%';
    

Postgres supports two SQL operators called ANY (SOME is an alias meaning the same thing) and ALL that can be used to perform a single check across a set of values, and we can use this with LIKE queries.

ANY and ALL are more commonly used with subqueries, but we can put multiple LIKE match patterns into an array and then supply this to ANY or ALL like so:

    SELECT * FROM posts WHERE title LIKE ANY(ARRAY['%Java%', '%Perl%', '%Python%']);
    

There's also a way to write array literals in a shorter style, if you prefer:

    SELECT * FROM posts WHERE title LIKE ANY('{%Java%,%Perl%,%Python%}');
    

Naturally, while these queries will find any rows where title matches against any of the supplied patterns, you could also use ALL to ensure you only get back titles which contain all of the patterns.

This week’s Tip of the Week is sponsored by DigitalOcean. Find out how engineers at DigitalOcean built a scalable marketplace for developers on top of their managed Kubernetes service.

🗓 Upcoming Events

PG Down Under (November 15 in Sydney, Australia) — The second outing for this annual, Australian Postgres conference.
2Q PGCONF 2019 (December 4-5, 2019 in Chicago) — A conference dedicated to exchanging knowledge about the world’s most advanced open source database: PostgreSQL
PgDaySF (January 21, 2020 in San Francisco) — Bringing the PostgreSQL international community to the heart of San Francisco and Silicon Valley.
PgConf.Russia (Febuary 3-5, 2020 in Moscow, Russia) — One day of tutorials and two days of talks in three parallel sessions.
PGConf India (Febuary 26-28, 2020 in Bengaluru, Maharashtra, India) — A dedicated training day and a multi-track two-day conference.
pgDay Paris 2020 (March 26, 2020 in Paris, France) — Learn more about the world’s most advanced open source database among your peers.