Postgres Weekly Issue 438: January 19, 2022

#438 — January 19, 2022

Five Easy to Miss Postgres Query Performance Bottlenecks — Postgres’s query planner usually does a fantastic job, but can sometimes throw up the odd surprise or weird strategy, so the common-sensical or obvious query you wrote might not always go to plan! This post presents a valuable look at optimizing such seemingly obvious queries with the help of EXPLAIN ANALYZE and metadata analysis.

Paweł Urbanek

More SQL, Less Code, with PostgreSQL — There are numerous schools of thought on how much work a database system should be doing in contrast to the apps querying it. This post shows off some practical SQL-based ways you can lean towards having Postgres pick up more of the little jobs that your app may currently be handling.

pgDash

Transparent Data Encryption: Encrypting Your Entire Database — TDE is a CYBERTEC patch to PostgreSQL. It is currently the only implementation that supports transparent and cryptographically safe data (cluster) level encryption, independent of operating system or file system encryption.

CYBERTEC sponsor

On Progress Bars for Postgres Queries.. Let's Dive Deeper — Last week we featured a fun post about monitoring the progress of long running queries, and this post digs somewhat deeper into some of the mechanisms involved and includes some recipes for practical monitoring of lengthy queries in production.

Nikolay Samokhvalov

Bi-Directional Replication using pglogical — While Postgres has native replication features now, the pglogical extension still has a few extra tricks up its sleeve including bi-directional replication.

Yaser Raja and Peter Celentano (AWS)

Why I Enjoy PostgreSQL: An Infrastructure Engineer's Perspective — Mostly it’s down to schema changes being (relatively) pleasant.

Shayon Mukherjee

In brief:

Last week we mentioned the PostGIS 3.2 release but Paul Ramsey has put together a quick look at what's new and improved in 3.2.
Every now and then, Robert Haas of EDB does some analysis of who's contributed the most to Postgres by way of source commits – here are the 2020 and 2021 results. It's been another couple of busy years for Tom Lane - that's for sure!
Postgres 15 looks set to include a JSON logging feature.
As part of an ongoing set of challenges to stretch his Postgres skills, Luca Ferrari demonstrates how to generate 'pentagon numbers' in SQL A pentagon number, by the way, is the number of points to represent a pentagon shape of a certain edge length.
Andreas Scherbaum has diligently continued to interview members of the Postgres community each week. The most recent interviews were with Kuntal Ghosh, Emil Shkolnik and Vibhor Kumar.
A high-level report from the Postgres core team on their main non-dev activities from 2019 to the end of 2021.

🔧 Code and Tools:

temBoard 7.9: A Postgres Remote Control/Dashboard — A Python-built Postgres Web dashboard and monitoring system. You install an agent on the Postgres servers in question and then the temBoard webapp runs wherever you want.

Dalibo

Identify, Track & Fix Problematic Tests with Buildkite’s Test Analytics ✅

Buildkite sponsor

Redix: A Fast Persistent Pure Key/Value Store — A key value store that speaks the Redis protocol but uses Postgres as its storage engine. First unveiled three years ago as a learning experience, this has been frequently updated and turned into a neat project - written in Go.

Mohammed Al Ashaal