Neues vom PostgreSQL Planet

The PostgreSQL RPM repository has supported Amazon Linux 2023 for a while, but until this week it had no GIS packages there: a few weeks ago I removed the AL2023 build of the GIS stack, as the distribution does not ship several libraries our GDAL was built against (hdf5, netcdf, xerces-c, Qt and friends).

Taking a second look, all of those turned out to be optional GDAL drivers, so I put the whole stack back, and this time PostGIS also comes with SFCGAL. Everything is available for PostgreSQL 14 to 18, on both x86_64 and aarch64.

Postgres gives you no way to take back a single statement. When someone runs an UPDATE without its WHERE clause, recovery is a procedure rather than a command. You restore a copy of the database to the moment before the mistake, pull out the rows that were damaged, and reconcile them by hand against a live table that has kept moving since.This kind of recovery works, and teams do it. It also takes hours, needs your most senior person, and every step is a chance to make things worse while the table is still serving traffic.

A PostgreSQL superuser can read any file on the server, write to it, and run shell commands on the host. That is more than just a powerful database account. It is shell access.

This is also why the role your application logs in with should almost never be a superuser. Most tools ask for it anyway, because it is the easy way to make setup “just work.” The problem is what else comes attached. 

Create replicas with a single command, but master the procedure for replacing a failed primary—that's where things get tricky and installations break. (I consult on PostgreSQL through PGX Inc..)

Standby lagging on network storage? `recovery_prefetch` lets WAL replay ask the kernel to read pages before it needs them, turning seventeen minutes of… (I consult on PostgreSQL through PGX Inc..)

Yesterday, a colleague of mine opened a simple PR. The business needed a region on an existing table, so he added a text column to hold it. Clean, small, ready to review and merge. Database internals? No problem. This was going to be a five-minute approval. I did not approve it. Not because the code was wrong. The code was fine. I challenged the content: why a free-text column? Why not a domain, an enum, a text column with a check constraint, or a reference table?

Read replicas are one tool, but tuning a replica for one job and sending it another is a recipe for conflict. (I consult on PostgreSQL through PGX Inc..)

Another story of something I saw in the field which surprised me. Turns out recovery_prefetch can cause outages.

Something which has been happening quietly in the background all the time, but we didn’t notice until we looked close enough to see it.

This is the second post in a series exploring graph databases and PostgreSQL. The first post covered what graphs are, what can be modeled as a graph, and the landscape of graph database systems. This post moves from the general to the specific: how we query graphs, why SQL can be an awkward fit, and what SQL/PGQ changes. It ends with the PostgreSQL implementation, why it was reverted, and a few notes on the main features still to come.

A friend of mine is an excellent architect but a complete snob. He has four different databases in production: PostgreSQL for OLTP, ClickHouse for OpenTelemetry data and web analytics, Elasticsearch for full-text search in the application, and MongoDB, “for historical reasons.”

In between, there are ETL and CDC pipelines, plus on-call developers hunting for where exactly the results don’t match. To him, it’s all a well-thought-out architecture; to me, it’s needless complexity in maintenance and development.

The PostGIS Team is pleased to release PostGIS 3.7.0rc3! Best Served with PostgreSQL 19 RC 1 , GEOS 3.15.0 , postgis_tiger_geocoder 2025.2 , and address_standardizer.

A read replica is a PostgreSQL server in permanent recovery that accepts queries—and nearly every surprise it causes follows from that fact. (I consult on PostgreSQL through PGX Inc..)

recovery_min_apply_delay makes a standby late on purpose. For the length of the delay there is a running, queryable copy of your database on which the DELETE without a WHERE clause has not happened yet. That is the whole feature, and it is a good one. The problem is that almost everyone who sets … (I consult on PostgreSQL through PGX Inc..)

After a crash, PostgreSQL syncs every file in your data directory before replaying any WAL—and on large clusters, this can consume minutes. (I consult on PostgreSQL through PGX Inc..)

A hook that fires exactly once at promotion or restore completion—but the server is neither ready nor recovering while your command runs. (I consult on PostgreSQL through PGX Inc..)

We’re continuing our review of the March CommitFest for PostgreSQL 19. This third article in the series covers the changes related to our DBA3 and DBS courses.

As a reminder, here’s the plan for our coverage of the final PostgreSQL 19 CommitFest:

Part 5.1 (QPT)

Part 5.2 (DBA1, DBA2)

For years, https://yum.postgresql.org and https://zypp.postgresql.org have had documentation pages for the extensions we package. But the PGDG repositories hold much more than PostgreSQL and its extensions: backup tools, high availability managers, connection poolers, monitoring tools, drivers, migration tools, and the libraries all of these depend on. Unless you already knew a package name, most of that software was hard to find.

With the first release candidate available, PostgreSQL 19 brings practical advances in database maintenance, query planning, and replication.

Four Supabase row-level security policy sets, tested with Supabase's own roles: own rows, public read, teams without recursion, and admins from the JWT.

Why a Supabase row-level security policy returns nothing, fails or leaks, measured on PostgreSQL 17 with Supabase's own roles and auth.uid(): ten causes.