Postgres Weekly Issue 406: May 19, 2021

#406 — May 19, 2021

The State of Postgres 2021 Results — Recently, Timescale, the folks behind the TimescaleDB Postgres extension, ran a survey to get an idea of the 'state' of the Postgres community. As promised, they've shared the results (including the raw, anonymized data, if you want to do your own analysis) and while the number of respondents wasn't particularly high, some interesting results came of it:

The majority of users call it "Postgres" not "PostgreSQL."
Most respondents are self-managing their Postgres installations.
AWS is by far the most popular cloud amongt users with GCP in a distant second place.
pgAdmin 4 is the most heavily used third party tool for working with Postgres.

Timescale

PostgreSQL Database Monitoring Dashboard — Collect metrics about the performance of your PostgreSQL database — like IO wait, CPU, Data Used, Disk Read time, Disk Write time — with this InfluxDB template. Try it for free with an InfluxDB Cloud account.

InfluxData sponsor

Postgres 13.3, 12.7, 11.12, 10.17, and 9.6.22 Released — Yes, it’s the full array of updates for all maintained lines of Postgres. Why? Beyond the usual bug fixes, there are three security vulnerabilities that needed resolving including a buffer overrun in array subscripting calculations, and memory disclosure issues with INSERT .. ON CONFLICT ... DO UPDATE and UPDATE ... RETURNING queries in certain situations.

PostgreSQL News

▶ Postgres Pulse: A 14-Video Series on Postgres Topics — If you’ve got some time on your hands, you could do worse than to check out EDB’s videos covering a variety of Postgres topics from fixing LDAP auth problems or using synchronous replication to a look into EXPLAIN ANALYZE or reducing database bloat.

Various Speakers

PSA: Three years ago we linked to annotated.conf, a handy collection of annotations for all of postgres.conf's hundreds of settings. Now it needs to be updated to Postgres 13 standards, but the original creator is no longer in the space. Pull requests are encouraged if you can help. (The project is still useful as is, of course.)

Debugging Random Slow Writes in Postgres — An interesting look at stepping through a problem and figuring out which settings needed to be tweaked, and how.

Sergios Aftsidis

Blog Post: Running a Safe Database Migration Using Postgres — A recent database migration took us down for 2 minutes. Learn what happened and how to avoid the same mistake.

Retool sponsor

Another Postgres 14 Highlight: CREATE TABLE COMPRESSION — The forthcoming Postgres 14 will have an option to let you optionally specify a different form of TOAST compression for oversized columns.

Michael Paquier

Rainbow-Color Your psql Output — If you want to show a little pride in your psql setup and you have a terminal that can support true color, here’s a fun solution for macOS and Linux.

Alexander Korotkov

supported by

💡 Tip of the Week

Spotting performance problems quickly

Are you looking for performance problems on your server? Consider checking out pg_stat_statements. It offers a fast and efficient way to look for slow queries.

pg_stat_statements is a module that comes with Postgres but needs to be specifically loaded by adding the following line to postgresql.conf:

    shared_preload_libraries = 'pg_stat_statements';
    

Then restart your server and run:

    CREATE EXTENSION pg_stat_statements;
    

The pg_stat_statements system view will provide you with all the relevant information. You can learn more about how to do this and how to spot performance problems in our blog post “PostgreSQL: Detecting slow queries quickly”.

This tip of the week is brought to you by CYBERTEC, your professional partner for PostgreSQL and Data Science since 2000. Read more about PostgreSQL Performance in CYBERTEC’s blog.