PostgreSQL 19 Released
79 points by bio
79 points by bio
The VACUUM improvements feel like an early Christmas present.
Developers will find many delightful features in this release, from support for "read-your-writes" consistency on standbys to "get or create" behavior that saves a network round trip.
Looks like that's via a new WAIT FOR LSN query you can run on your standby - so you commit data to the leader, track what the log sequence number is, then on subsequent reads you can say to the standby "don't answer this query until you've caught up to LSN X".
I remember reading about Wikipedia using a hand-rolled version of this against MySQL, it's neat to see it as a core feature of PostgreSQL now.
In addition to this, MediaWiki attempts to ensure that the user sees events occurring on the wiki in chronological order. A few seconds of lag can be tolerated, as long as the user sees a consistent picture from subsequent requests. This is done by saving the primary binlog position in the session, and then at the start of each request, waiting for the replica to catch up to that position before doing any reads from it.
GitLab's database load balancer did (and still does IIRC) something similar: after a write it records the WAL pointer from the primary and stores it in your session. The next time it wants to read from a secondary it first compares that value against the WAL pointer of the secondary. Only if that value is newer than the one from the last write does it read from the secondary.
Seeing something like this (finally) included in PG itself is nice and would probably make that entire implementation a lot simpler.