Postgres Weekly

#585 — January 23, 2025

Together with

Who Contributed to Postgres Development in 2024? — Each year, Robert compiles a list of those who contributed most to Postgres’ development over the past year. 229 folks were the principal authors of at least one commit, and 66% of new lines of code came from the top 18 contributors.

Robert Haas

Anatomy of Table-Level Locks: Reducing Locking Impact — It’s easy to lock an entire table when performing an operation, but the performance and uptime impacts should quickly drive you to using less restrictive approaches. Gulcin explains some options.

Gulcin Yildirim Jelinek

VeilStream – Simple Data Access Management for Postgres — VeilStream is a secure, self-hosted, Postgres filter that sits in front of your production database to provide a powerful and simple GUI and API for setting up data redaction and anonymization rules. Try it for free today.

VeilStream sponsor

GROUP BY and Fixing Optimizer Estimates — Did you know the more columns a GROUP BY contains, the more likely the optimizer overestimates the row count? Postgres maintains separate statistics for each column and creates group estimates by multiplying them together. Creating extended statistics can help get things in order.

Hans-Jürgen Schönig

QUICK BITS:

Take a look at every byte that goes over the network when making a real Postgres query made to Neon over TLS (above). Everything is annotated to help you understand the complexity.
Clickbench is a benchmark for real-time analytics databases and thanks to pg_mooncake, Postgres is now in the top 10 databases in the benchmark.
🗓️ The schedules for both Nordic PGDay 2025 and pgDay Paris 2025 have now been published.
🇧🇪 If you're going to the FOSDEM PGDay next month or want to, Andreas Scherbaum has lots of useful advice and info here.

Implementing Thread-Safe Scanners and Parsers in Postgres — Unless you’re a Postgres implementer (like top 5 contributor Peter Eisentraut here) this probably won’t be directly useful to you, but it’s fascinating to see what’s involved in potentially moving Postgres’ scanners and parsers to be thread safe. Spoiler: It’s a lot.

Peter Eisentraut

Don't Forget About the Parallel Leader Participation Setting — Kaarel has his own TLDR: “You should actually look into disabling parallel_leader_participation if you’re working with larger out-of-cache datasets, have plenty of cores and especially when the tables are partitioned.”

Kaarel Moppel

📄 Unpivoting Data using JSONB – Very handy tip. Leo Hsu and Regina Obe

📄 Whose Optimization is Better? – Thoughts on comparing the quality of SQL query plans. Andrei Lepikhov

📄 Postgres UUIDv7 + Per-Backend Monotonicity Brandur Leach

📄 Logical Replication in Postgres: The Basics Phil Eaton

📰 Classifieds

🐘 POSETTE: An Event for Postgres is June 10-12. The call for proposals closes Feb. 9. New and experienced speakers are encouraged to submit!

Achieve peak Postgres performance. Take the pganalyze tour.

🛠 Code and Tools

DBOS Transact v2: Lightweight Durable Execution in TypeScript — An opensource library for lightweight durable execution built on Postgres. Durable execution means persisting the execution state of a program while it runs, so if it's interrupted or crashes, it resumes from where it left off, ideal for long-running or business-critical workflows. Documentation.

DBOS, Inc.

HeidiSQL 12.10: A Native Windows App for Database Work — A handy, open source database management tool. Supports Postgres, MySQL, SQL Server, and SQLite. Written in Delphi and is open source.

Ansgar Becker

PostgresML 2.10 – Machine learning from SQL. Now supporting Postgres 17.
pgai 0.7 – Timescale's tools to work with RAG and LLMs in Postgres.
pg-diff 3.0 – PostgreSQL schema and data comparing tool.
tbls 1.80 – Auto document a database in Markdown format.
Bob 0.30 – SQL query builder and ORM/Factory generator for Go.
PostgreSQL JDBC Driver 42.7.5
PostGIS 3.5.2