PostgreSQL 19 replication - WAIT, sequences and subscriptions

A read scale-out cluster has always had one uncomfortable property: a write commits on the primary, the client is told it succeeded, and a read routed to a standby a millisecond later cannot see it. remote_apply synchronous commit fixes this at a price the documentation describes bluntly - much larger commit delays. PostgreSQL 19 adds a cheaper alternative: do the write on the primary, then make the standby prove it has caught up before serving the read. That is the WAIT command.

WAIT: read-your-writes on a standby

WAIT is a top-level SQL command that blocks until WAL reaches a target LSN, with a mode deciding what “reached” means:

The pattern from the documentation: write on the primary, capture the WAL position, then make the replica catch up to it before reading:

-- on the primary:
UPDATE movie SET genre = 'Dramatic' WHERE genre = 'Drama';
SELECT pg_current_wal_insert_lsn();   -- returns, for example, 0/0306EE20

-- on the standby:
WAIT FOR LSN '0/0306EE20';            -- default mode standby_replay
-- status: success
SELECT * FROM movie WHERE genre = 'Drama';   -- now guaranteed empty

pg_current_wal_insert_lsn() rather than a commit LSN is deliberate - synchronous_commit may be off, so the insert position is the safe bound. TIMEOUT bounds the wait (milliseconds, or a string with a unit; zero, the default, waits forever) and NO_THROW converts timeout and promotion outcomes into a status return value of timeout or not in recovery instead of an error - the right shape for an application that would rather fall back to the primary than fail:

WAIT FOR LSN '0/0306EE20' WITH (MODE 'standby_write', TIMEOUT '100ms', NO_THROW);

The restrictions are real and worth reading before building on this. WAIT cannot run in a function, procedure, DO block, or once the transaction holds a snapshot - so it must be the first statement of a transaction, or issued outside one entirely. During recovery it is also rejected if the session holds a lock and the target LSN is not yet reached: a held lock can make the startup process wait on this session while this session waits on replay, a deadlock that deadlock detection cannot see. Wait first, then take locks.

Logical replication finally covers sequences

Until 19, logical replication moved table data and left sequences behind - the subscriber’s nextval kept issuing values the publisher had already used, which is an outage waiting to happen after a failover. 19 adds sequence synchronisation:

-- publisher:
CREATE PUBLICATION pub1 FOR ALL SEQUENCES;

-- subscriber:
CREATE SUBSCRIPTION sub1
  CONNECTION 'host=pub.example.com dbname=appdb application_name=sub1'
  PUBLICATION pub1;

With copy_data = true (the default) a sequence synchronisation worker copies the initial values at subscription creation and exits when done; ALTER SUBSCRIPTION ... REFRESH PUBLICATION picks up newly added sequences, and ALTER SUBSCRIPTION ... REFRESH SEQUENCES re-syncs the values of all known sequences. The worker count is bounded by max_sync_workers_per_subscription.

Two diagnostics matter:

• pg_get_sequence_data('s1') on the publisher returns last_value, is_called and page_lsn. Compare page_lsn against pg_subscription_rel.srsublsn on the subscriber to detect drift.
• The warning in the documentation is worth memorising: a sequence caches a block of values (typically 32) before writing a new WAL record, so its page LSN only advances when a whole cached batch is consumed. Drift inside one cached block is invisible to the LSN comparison - run REFRESH SEQUENCES on a schedule or after bulk sequence activity, not just when the LSNs differ.

Sequence synchronisation requires the publisher to run PostgreSQL 19 or later; a 19 subscriber against an older publisher gets nothing.

Publications and subscriptions: the rest of the changes

retain_dead_tuples carries the subtle trap: conflict-detection information cannot be purged while the subscription is disabled or its apply worker is failing, so it accumulates - watch the xmin of the pg_conflict_detection slot in pg_replication_slots, and disable the option if a subscription will be down for a while. Detection of update_deleted is also not guaranteed for changes that originated on a different node, only for local writes.

wal_level = replica can now serve logical replication

Setting wal_level = logical used to require a restart, which is why many clusters ran replica and could not add a publication without a maintenance window. In 19, when wal_level is replica the server can raise the effective level to logical on demand - the release notes describe this as automatic enablement of logical replication when needed. Three places report the effective level: the effective_wal_level setting, pg_controldata, and pg_control_checkpoint().

The trade-off is unchanged: logical decoding writes more WAL than replica, so the bill arrives only once someone actually creates a slot.

New and renamed monitoring surfaces

• pg_stat_recovery - new system view with one row reporting standby recovery state: promote_triggered, pause_state, last_replayed_end_lsn, replay_end_lsn, recovery_last_xact_time (the commit time on the primary), and current_chunk_start_time. It returns rows only in recovery and only to pg_read_all_stats holders.
• pg_stat_subscription_stats.sync_error_count renamed to sync_table_error_count, with sync_seq_error_count added alongside for sequence synchronisation errors, and update_deleted for conflicts detected under retain_dead_tuples.
• pg_stat_replication_slots gains mem_exceeded_count (times logical_decoding_work_mem was exceeded) and slot-sync skip columns (slotsync_skip_count, slotsync_last_skip, slotsync_skip_reason); pg_stat_wal_receiver.status gains a connecting value.

The full runbook of these and the rest of 19’s monitoring additions is on the monitoring page.

What changed, and in which release

Where it breaks

• WAIT inside a transaction that already took a lock - rejected with an error during recovery; the fix is ordering, WAIT first.
• WAIT on timeout without NO_THROW - raises an error that aborts the transaction; use NO_THROW and check the status when timeout is a handleable outcome.
• Sequence sync against a pre-19 publisher - does nothing; there is no fallback.
• retain_dead_tuples on a stalled subscription - retention keeps accumulating because purging needs a running apply worker; watch pg_replication_slots for pg_conflict_detection.
• max_retention_duration set too low - the documentation warns premature removal causes incorrect conflict detection, which is worse than no detection.

What this is not for

• Guaranteeing durability of the write itself - WAIT runs after commit; it makes the standby catch up, not the write safer. That remains synchronous_commit’s job.
• Replacing synchronous replication for correctness-critical reads - standby_write and standby_flush give weaker guarantees than standby_replay; only standby_replay means the row is actually visible.
• Sub-second lag budgets without thought - WAIT turns standby lag into client-visible latency; under a lag spike the choice is a timeout, a fallback to the primary, or a stale read.