Read your writes: WAIT FOR in PostgreSQL 19

Summary

PostgreSQL 19 introduces the top-level SQL command WAIT FOR, addressing stale reads that can occur when an application writes to a primary and immediately reads from an asynchronously replicated standby. After the write, the client or pooler obtains an LSN with pg_current_wal_insert_lsn() and sends it to the reading session, where WAIT FOR blocks until the requested WAL position reaches the selected stage. The default standby_replay mode makes the change visible, while standby_write, standby_flush, primary_flush, TIMEOUT, and NO_THROW provide other durability and error-handling choices. The command waits without holding a snapshot, avoiding a replay self-deadlock, and exposes related wait events in pg_stat_activity. The feature preserves asynchronous writes by making only reads that require read-your-writes consistency wait, though timeline changes after promotion require caution.

Takeaways

1. 1

   The default standby_replay mode waits until WAL has been replayed and the changes are visible on the standby; standby_write and standby_flush wait for earlier or stronger progress stages, while primary_flush targets the primary.

2. 2

   TIMEOUT causes an error by default when the wait expires; adding NO_THROW returns success, timeout, or not in recovery instead. An omitted or zero timeout waits indefinitely.

3. 3

   WAIT FOR compares LSN positions without understanding timelines, so a promotion can make a successful result refer to WAL from a different timeline than the original write.