---
title: "This week's Postgres news"
publication: "Postgres Weekly"
issue: 330
published_at: "2019-11-06"
canonical_url: "https://postgresweekly.com/issues/330"
---

Initial Postgres 12 query performance impressions, a Postgres health check tool, installing Postgres on FreeBSD, and more.       

#330 — November 6, 2019 

[Read on the Web](https://postgresweekly.com/issues/330)   

Postgres Weekly  

[Postgres 12 Initial Query Performance Impressions](https://www.cybertec-postgresql.com/en/postgresql-v12-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](https://blog.timescale.com/blog/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   [![\](https://copm.s3.amazonaws.com/b264b6dd.png)](http://www.2qpgconf.com) 

[Hands-On PostgreSQL Training with Experts](http://www.2qpgconf.com) — 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](https://gitlab.com/postgres-ai/postgres-checkup/) — 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](https://gitlab.com/postgres-ai/postgres-checkup/-/tags/1.3.0) has just been released. 

Postgres.ai   

[Application Connection Failover using HAProxy with Xinetd](https://www.percona.com/blog/2019/10/31/postgresql-application-connection-failover-using-haproxy-with-xinetd/) — I’m a _huge_ fan of [haproxy](http://www.haproxy.org/), 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](https://www.2ndquadrant.com/en/blog/postgresql-12-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](https://fluca1978.github.io/2019/11/04/PostgreSQL12FreeBSD.html) — 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](https://fluca1978.github.io/2019/10/30/PostgreSQL_FreeBSD_Ansible.html) 

Luca Ferrari    

📂**Code and Projects**  

[PostgREST 6.0: Serve a RESTful API from Your Postgres Database](https://postgrest.org/en/v6.0/) — 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](https://www.pgmustard.com?referrer=postgresweekly) — 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](https://www.honeybadger.io/blog/pg-partition-manager/) — `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](https://www.postgresql.org/about/news/1989/) — Adds connection pooling and load balancing to Postgres. 4.1 introduces statement level load balancing and auto failback. 

Pgpool Global Development Group      supported by [![Digital Ocean\](https://res.cloudinary.com/cpress/image/upload/v1570809425/nlqv0ueejagpj7gkft55.png)](https://blog.digitalocean.com/how-we-launched-our-marketplace-using-digitalocean-kubernetes-part-1/?medium=display&utm_source=cooperpress&utm_campaign=MDB_Dev_Signup_Cold_Newsletter11-6-2019)  

💡**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](https://blog.digitalocean.com/how-we-launched-our-marketplace-using-digitalocean-kubernetes-part-1/?medium=display&utm_source=cooperpress&utm_campaign=MDB_Dev_Signup_Cold_Newsletter11-6-2019). Find out [how engineers at DigitalOcean built a scalable marketplace for developers](https://blog.digitalocean.com/how-we-launched-our-marketplace-using-digitalocean-kubernetes-part-1/?medium=display&utm_source=cooperpress&utm_campaign=MDB_Dev_Signup_Cold_Newsletter11-6-2019) on top of their managed Kubernetes service.      

🗓**Upcoming Events** 

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

Curated by [Cooperpress](https://cooperpress.com/), [Peter Cooper](https://twitter.com/peterc), and [Craig Kerstiens](https://twitter.com/craigkerstiens). 

[Change your email address](https://postgresweekly.com/edit_subscription/*|UID|*) or[Stop getting this newsletter](https://postgresweekly.com/leave/*|UID|*) 

© Cooper Press Ltd · Fairfield Enterprise Centre, Louth, LN11 0LS, United Kingdom