Dev & EngARTICLE

PostgreSQL 19 brings lock-free REPACK and more robust logical replication for production operators

PostgreSQL 19 brings lock-free REPACK and more robust logical replication for production operators
Image: Roberto Diniz
Article · Dev & Eng
Reading
6 min
Tables
1
In short

Released on November 19, 2026, PostgreSQL 19 replaces VACUUM FULL and CLUSTER with a REPACK command that runs without blocking reads and writes, and reduces manual work in logical replication. For those running databases in production, the real gain lies in the operational details.

PostgreSQL 19 was released on November 19, 2026, and the most detailed assessment so far came from Ahsan Hadi, of pgEdge, in a post published on Planet PostgreSQL. Hadi says he tested the release candidates for weeks before launch, and the angle he proposes is what matters to those who actually run Postgres in production: fewer maintenance windows, less manual replication work, and more visibility into what the planner is doing.

REPACK CONCURRENTLY: the end of downtime for reorganizing tables

The REPACK command replaces VACUUM FULL and CLUSTER, which historically required an exclusive lock on the entire table while rebuilding the physical file. With the CONCURRENTLY option, REPACK rebuilds a bloated table without blocking concurrent reads or writes, which eliminates one of the most troublesome maintenance windows in Postgres operations.

There are prerequisites a careful DBA shouldn't ignore: the table needs a primary key or a replica identity index, and the operation isn't MVCC-safe for transactions that were already open before the physical file swap. This means planning the window around long-running transactions, not assuming that CONCURRENTLY solves any bloat scenario without prior analysis.

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.

Ahsan Hadi, pgEdge

The number comes from the author's own test, not an independent benchmark, but it illustrates the feature's proposal well: swapping the table's physical file without stopping the application. Shaun Thomas, also of pgEdge, wrote a dedicated post on the topic with more technical details about REPACK's internal workings.

Logical replication gains less manual work and more reliability

For those who maintain replicated clusters, the changes in logical replication are the most relevant of this release. Sequences can now be published with ALL SEQUENCES and synchronized with REFRESH SEQUENCES, which removes the manual setval step that every DBA who has done a migration or upgrade via logical replication knows well, and which was a common source of silent error when forgotten.

Another relevant operational change: logical replication can be enabled without restarting the instance when wal_level is already set to replica. This reduces a downtime window that was previously needed just to enable a replication feature.

Two additions complete the picture:

  • retain_dead_tuples, to improve conflict detection in topologies with multiple nodes writing to the same table;
  • the EXCEPT clause in publications, which allows excluding specific tables without rewriting the entire list of published tables.

In short: none of these changes is revolutionary on its own, but together they reduce manual steps exactly at the points where human error tends to cause incidents: forgotten sequence migration and unnecessary instance restarts.

Observability and the planner: visibility before trust

On the automatic maintenance side, autovacuum now uses parallel workers on indexes and ranks tables through a scoring system, prioritizing those that need cleanup the most. It's a welcome adjustment for anyone who has seen autovacuum spend cycles on a small table while a critical table accumulated bloat.

The pg_plan_advice and pg_stash_advice extensions allow directing the planner and pinning an execution plan the DBA already trusts. Caution is warranted here: the ability to pin a plan is useful against unexpected regressions after a statistics upgrade, but it's also an easy tool to misuse, locking the instance into a plan that stops being optimal once data volume changes. Before trusting it, it's necessary to understand why that plan was chosen.

The new WAIT command offers read-your-writes consistency on standbys, a recurring problem in architectures that distribute reads across replicas: the application writes to the primary and, when reading right afterward from a replica, risks not seeing its own data that hasn't replicated yet. WAIT solves this without resorting to arbitrary sleeps in application code.

In the monitoring field, three changes deserve attention before the upgrade:

ChangeWhat changes
log_lock_waitsNow enabled by default
pg_stat_lockNew view to diagnose lock contention
Wraparound warningNow triggers at 100 million transactions, previously 40 million

The adjustment to the wraparound threshold reduces noise in large databases, but it also shortens the reaction margin for those who don't closely monitor the transaction counter's advance: it's worth reviewing the corresponding alert, not simply trusting the new default.

Still on data loading: COPY FROM got faster for text and CSV input, COPY TO can now write JSON, and the ON_ERROR SET_NULL option allows a load to continue even when it encounters invalid values, instead of aborting the entire process.

Upgrade checklist: defaults that change and can break things

Before migrating to PostgreSQL 19, there are changed defaults that deserve explicit checking in any production environment:

  1. JIT now comes disabled by default;
  2. max_locks_per_transaction is now 128;
  3. standard_conforming_strings is always enabled, with no option to turn it off;
  4. the sync_error_count column in pg_stat_subscription_stats was renamed to sync_table_error_count.

This last item is the kind of change that doesn't show up in manual testing, but silently breaks any dashboard or alert built on the old column name. Anyone monitoring logical replication through that view needs to update their queries before the cutover, not after.

Upgrade path and the security reminder that can't wait

A major version upgrade still requires one of the three known routes: pg_upgrade, dump and restore, or logical replication. For those using pgEdge Enterprise Postgres, the steps are documented by the vendor; for Spock clusters, Hadi has already written a guide comparing the two approaches using the 17-to-18 upgrade as an example, whose logic applies equally to the jump to 19.

The difference between the routes is operational: the rolling upgrade adds a new node already on the target version and retires the old node, while the in-place upgrade uses pg_upgrade --link on one node at a time. Those who choose the second route need to know that pg_upgrade doesn't carry over Spock's replication origins: they need to be recreated manually after the upgrade, a detail that's easy to forget and expensive to discover in production.

It's worth noting a point that isn't about PostgreSQL 19, but that any DBA should treat with the same priority: the November 12, 2026 updates for PostgreSQL 18.7, 17.12, 16.16, and 15.20 fixed 52 security vulnerabilities and more than 140 bugs, including flaws in incremental backups that could produce corrupted backups. Anyone who has already applied these updates should take a new full backup: an incremental backup taken before the patch isn't reliable. PostgreSQL 14 reached end of life at version 14.25, which makes planning a major upgrade, not just the security patch, mandatory for anyone still running that series.

Finally, a warning from someone who followed the release candidates closely: several fixes still landed late in the cycle for REPACK CONCURRENTLY, pg_plan_advice, parallel autovacuum, and automatic logical replication activation. These are new enough features to deserve testing against real workloads before any production dependency: a careful DBA's bar doesn't change just because the version has been officially released.

Translated from the Brazilian Portuguese original · Read the original