Neues vom PostgreSQL Planet

Every durable database change in PolarDB passes through one place before it is safe: the write-ahead log. A replica is just a machine that stores that log and replays it. But how if a cluster need a pure copy of the WAL?

Every time PostgreSQL decides whether a row is visible to your query, it asks the same question: was the transaction that wrote this row already committed when my snapshot was taken?

Protect your disk from orphaned temporary files after a backend crash. (I consult on PostgreSQL through PGX Inc..)

A first look at what CloudNativePG 1.31 will bring to PostgreSQL credentials: a DatabaseRole can now generate its own password, keep it in a Secret the operator owns, shape it with criteria and rotate it on a schedule, with PostgreSQL enforcing the lifetime through VALID UNTIL. The feature is merged but not yet released, so this recipe anticipates 1.31 and shows how to try it today on the latest development snapshot.

On 12 October, the PostgreSQL User Group Vienna met, organized by * Cornelia Biacsics * Ranjeet Kumar

Speakers:

• Mayur B.
• Radim Marek

On 13 October, the San Francisco Bay Area PostgreSQL Meetup Group met online. Christophe Pettus delivered a talk. The meetup was organized by:

PostgreSQL 15's `recursive_worktable_factor` helps you fix slow recursive CTEs by tuning a single estimate. (I consult on PostgreSQL through PGX Inc..)

PolarDB separates compute from storage. Many machines can reach the same block device. Exactly one of them may write to it. A second read-write mount corrupts data, and there is no recovery from it.

Yesterday, I spoke about pg_acm at PGConf.EU. Speaking at the biggest Postgres conference in the world is an honor in itself, and it’s not that I have a chance to present there every year, so I am incredibly thankful to the CfP committee for choosing this talk.

This talk has a long history of rejection and denial of the fact that something could be wrong with Postgres, which made me appreciate this opportunity even more.

Every major PostgreSQL upgrade I have done on a single primary came with the same conversation: how long can the application be down?  is fast, but it is still an outage, and you still have to rebuild or resync the standbys afterwards.A multi-master cluster gives you a different option. Spock is pgEdge's open-source PostgreSQL extension for multi-master logical replication, and with it every node takes writes, so you never have to upgrade a node in place. You add a brand new node on the new major version, let it join the cluster, move traffic to it, and retire the old one.

Please enjoy the slides and transcript from Tuning Postgres for Analytics at PostgreSQL Conference Europe (

Every Postgres team eventually types CREATE INDEX in a hurry, and most of them type the same one. B-tree is the right default, but it is not always the right answer. On a table with 10 million rows I found an index that was 3,000 times smaller and only 3 times slower, an index that did nothing at all, and a “covering” index that made queries faster until a routine UPDATE took the advantage away.

Restore to the moment before the disaster using eight parameters that answer where replay stops, which timeline to follow, and what to do when you get there. (I consult on PostgreSQL through PGX Inc..)

We’re wrapping up our review of the March CommitFest for PostgreSQL 19. This fourth and final article in the series covers the changes related to SQL and to our DEV1 and DEV2 courses.

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

Part 5.1 (QPT)

In my last post, a friend of mine kept four databases in production. Ask a team like that for a knowledge graph, and you can guess the fifth: a triple store. Search over company data, an ontology of products or skills, facts for an LLM application: a knowledge base often means RDF and SPARQL, and usually a separate server next to the relational database.

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?