Ahsan Hadi: PostgreSQL 19 is Here: Worth the Wait
PostgreSQL 19 was released today. pgEdge packages for it are available the same day across all of our supported deployments - including Spock, where PG 19 support is already available. I have been testing the release candidates for a few weeks, so here is what I think matters most to DBAs and teams. replaces and , and with the option it rebuilds a bloated table without blocking reads and writes. In my own test, inserts kept running at about 2,000 a second with no failed transactions while a bloated table was rebuilt in under a second. It needs a primary key or replica identity index, and it is not MVCC safe for transactions that started before the swap. Shaun Thomas wrote a blog on the topic - give it a read for more detail. gets the biggest changes for anyone running replicated clusters. Sequences can now be published with and synced with , which removes the manual step from migrations and upgrades. Logical replication can also be turned on without a restart when is replica. Subscriptions get for better conflict detection, and publications can leave tables out with ., autovacuum can now use parallel workers on indexes and ranks tables with a scoring system, so the ones that need it most go first. The and extensions let you steer the planner and keep a plan you trust. The new command gives read-your-writes consistency on standbys. are worth knowing. is faster on text and CSV input, can now write JSON, and lets a load continue past bad values. For monitoring, is now on by default, there is a new view, and the wraparound warning now starts at 100 million transactions instead of 40 million.
Checklist Before You Upgrade to PG19
, check the changed defaults. JIT is now off, is 128, is always on, and in is now , which will break any monitoring built on the old name. A major version upgrade needs pg_upgrade, dump and restore, or logical replication. For pgEdge Enterprise Postgres, the upgrade steps are in our docs. For a Spock cluster, I have written up both approaches using a 17 to 18 upgrade as the example. The rolling upgrade adds a new node on the new version and retires the old one. The in-place upgrade uses just one node at a time. If you take the in-place route, note that pg_upgrade does not carry over Spock’s replication origins, so you need to recreate them. The post shows how. the November 12 update releases for PostgreSQL 18.7, 17.12, 16.16 and 15.20 fixed 52 security vulnerabilities and over 140 bugs. That includes several fixes for incremental backups that could produce corrupt backups, so take a new full backup after updating. PostgreSQL 14 reached end of life at 14.25, so anyone still on it should plan a major version upgrade. During the PostgreSQL 19 release candidates, several fixes also went into , , parallel autovacuum and the automatic enablement of logical replication, so test these on your own workload before relying on them. The pgEdge repository requires an access certificate. Install the pgEdge repository package on your host, then register the host with . Send the client.csr file it creates to pgEdge support with your organization name and customer ID, and say whether you need a trial or a paid subscription. When the certificate comes back, install it with , which also enables the repository. After that you can install PostgreSQL 19 and the supporting components with dnf or apt. The full steps, with Debian and Ubuntu instructions as well, are in the docs.Further Reading
The PostgreSQL 19 release notes are available for deep insights into what changed and why. However, if you’re looking for a more casual understanding of some of these fundamental changes to the PostgreSQL Project, Shaun Thomas wrote about just about all of these changes already in a “Looking Forward to PostgreSQL 19” series on our blog - browse through and take a look, anytime.原文:https://www.a8q9.cn/en/article/ahsan-hadi-postgresql-19-is-here-worth-the-wait-5957d2.html