Neues vom PostgreSQL Planet

Many experienced DBAs joke that you can boil down the entire job to a single rule of thumb: Don’t lose your data. It’s simple, memorable, and absolutely true – albeit a little oversimplified.

Mark Porter’s Cultural Hint “The Onion of our Requirements” conveys the same idea with a lot more accuracy:

To warm up, let's talk about lightweight and heavyweight locks (or "regular locks" or just "locks").

I'm using these materials:

Moving a Postgres database isn’t a small task. Typically for Postgres users this is one of the biggest projects you’ll undertake. If you’re migrating for a new Postgres major version or moving to an entirely new platform or host, you have a couple options:

PostgreSQL handles large JSON payloads reasonably well until you start updating or deleting them frequently. Once payloads cross the 8 KB TOAST threshold and churn becomes high, autovacuum can dominate your I/O budget and cause other issues. I have been exploring the idea of moving older JSON data (read: cold data) to Parquet on S3 while keeping recent data hot in PostgreSQL daily partitions, then simply dropping those partitions instead of running expensive DELETE operations and subsequent vacuum cycles.

PostgreSQL is a very versatile database. It has countless ways of bringing different functionalities to an already very sophisticated piece of software. Today, I’m going to show you how to use Rust and pgrx to bring etcd into your PostgreSQL database through a nice little interface called Foreign Data Wrappers.

(an image representing rust, etcd and postgres working together. Drawn by Jeremy Sztavinovszki)

Our platform team has a regular meeting where we often use ops issues as a springboard to dig into Postgres internals. Great meeting today – we ended up talking about the internal architecture of Postgres replication. Sharing a few high-quality links from our discussion:

Alexander Kukushkin’s conference talk earlier this year, which includes a great explanation of how replication works

On 29th of September 2025, Tom Lane committed patch: Add GROUP BY ALL.   GROUP BY ALL is a form of GROUP BY that adds any TargetExpr that does not contain an aggregate or window function into the groupClause of the query, making it exactly equivalent to specifying those same expressions in an explicit GROUP … Continue reading "Waiting for PostgreSQL 19 – Add GROUP BY ALL."

the psql client version 18 comes with pipelining, which can speed up client-server communication. In this post, let's see how it works and how much can be gained in query throughput with a simple example.

 Learning Structured Query Language can be frustrating when double-checking that what you wanted to have done is actually what was done. PostgreSQL 18 has 'OLD and NEW support for RETURNING clauses in INSERT, UPDATE, DELETE, and MERGE commands'. Now you can get instant feedback. 

The addition of the RETURNING clause in the previous version made MERGE much easier to use. Now it makes other commands easier.

To demonstrate, let's create a table with one column that is designated as a unique, primary key integer and insert a value.

© Laurenz Albe 2025

If you are reading this hoping that PostgreSQL finally got UPDATE ... LIMIT like MySQL, I have to disappoint you. The LIMIT clause is not yet supported for DML statements in PostgreSQL. If you want to UPDATE only a limited number of rows, you have to use workarounds. This article will describe how to do this and how to avoid the pitfalls and race condition you may encounter. Note that most of the following also applies to DELETE ... LIMIT!

A comprehensive list of PostgreSQL 18 new features, performance optimizations, operational and observability improvements, and new tools for devs.

This is a follow‑up to the last article: Run Jepsen against CloudNativePG to see sync replication prevent data loss. In that post, we set up a Jepsen lab to make data loss visible when synchronous replication was disabled — and to show that enabling synchronous replication prevents it under crash‑induced failovers.

PostgreSQL 18 is such an exceptional piece of software - it is hard to imagine anything better, right? I remember a similar thought when PostgreSQL 7 was released (“wow, how cool is that - this is it”). Well, let me put it mildly: I was wrong, and things are a lot better than they used to be. So, obviously the idea that PostgreSQL 18 is as far as humanity can go is also wrong. But what might be next?

PostgreSQL 18 was released on schedule this week!

In PostgreSQL 18, the statistics & monitoring subsystem receives a
significant overhaul - extended cumulative statistics, new per-backend I/O
visibility, the ability for extensions to export / import / adjust statistics,
and improvements to GUC controls and snapshot / caching behavior. These changes
open new doors for performance analysis, cross‑environment simulation, and
tighter integration with extensions. In this article I explore what’s new, what

PostgreSQL 19 changes this week encode() / decode() : support for base64url format added extension_control_path : only the first extension name found in the path will be shown by extension availability views logical_replication : conflict-relevant data retention will now be automatically resumed pg_replication_origin_session_setup() : parameter pid added function lookup failures will now have more specific error details/hints provided PostgreSQL 19 articles Waiting for PostgreSQL 19 – Add date and timestamp variants of random(min, max).

 The PostgreSQL 18 Release Notes are like a great noir novel, full of surprises and intrigue. But we know who did it - the amazing PostgreSQL community.

PostgreSQL 18 was released on September 25, 2024, introducing a host of exciting new features across all major areas. In this blog series, we’ll delve into these features and explore how they can benefit database developers and migration engineers transitioning to PostgreSQL.

Part 1 of the series focuses on enhancements to the COPY command, particularly for use cases involving loading external files into the database.

It has been a while since last time I blogged about any real-database-life problems and investigation. Here is one which I am really happy about, because it took a while and it was not obvious, but I found the root cause of a problem!

We get a lot of queries about performance optimization for PostgreSQL from organizations using Odoo. The platform has been built entirely on PostgreSQL, and Odoo’s ORM and database layer have undergone several performance‑oriented changes across releases 17, 18, and 19 to reduce SQL overhead and take better advantage of PostgreSQL.

I have always maintained that SQL optimization is the most significant factor contributing towards database performance improvements, so I am very happy to see these improvements being rolled out.