WAIT FOR LSN: read-your-writes on async replicas

A common scaling pattern sends writes to the primary and reads to asynchronous replicas. Its weakest point is the read immediately after a write: the client committed on the primary, but the replica has not replayed that WAL yet, so the follow-up read returns the old value. This is the stale read problem, and until PostgreSQL 19 there was no server-side primitive to close the window — only application workarounds.

The stale read problem

With asynchronous streaming replication, COMMIT on the primary does not wait for any standby. The WAL record is shipped, written, flushed, and replayed on the replica some milliseconds — or under load, seconds — later. A client that writes and then reads through a replica connection pool can observe its own commit as missing:

primary:  UPDATE profile SET display_name = 'new' WHERE id = 42;  -- COMMIT
replica:  SELECT display_name FROM profile WHERE id = 42;
          --> 'old'   (replay has not reached the commit record yet)

The window is unbounded: replication lag spikes with write bursts, long transactions, and recovery conflicts, so any fixed assumption about "the replica is probably caught up" eventually breaks.

Workarounds before PostgreSQL 19

Four patterns are widely deployed, and each pays for correctness somewhere else:

• Fixed sleep after writes. Delay the next read by N milliseconds. Lag is not constant, so the sleep is either too short (still stale under load) or too long (idle latency on every request when the replica is caught up).
• Sticky reads. Pin a session's reads to the primary for a while after its writes. Correct, but it pushes read traffic back to the primary for exactly your most active users — the ones you offloaded reads for.
• synchronous_commit = remote_apply. Every COMMIT waits until a synchronous standby has replayed the change, making it visible everywhere. This works, but it taxes all writes with replica round-trip latency and couples write availability to standby health. It is a cluster-wide durability decision, not a per-request consistency hint.
• Polling pg_last_wal_replay_lsn(). After the write, record pg_current_wal_insert_lsn() on the primary, then loop on the replica comparing replay position until it passes your LSN. Semantically correct, but every check is a network round trip, the loop burns connections, and timeout/promotion handling is entirely your code.

PostgreSQL 19 replaces the last pattern with a server-side blocking wait.

WAIT FOR LSN syntax

WAIT FOR blocks the session until the server reaches a target LSN, then returns a one-row status:

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

Possible return values are success, timeout, and not in recovery. Without TIMEOUT (or with 0) the command waits indefinitely. On timeout — or on running a standby mode against a server that is not in recovery — it raises an error unless NO_THROW is given, in which case the status column tells you what happened.

The typical read-your-writes flow:

-- on the primary, immediately after the write
SELECT pg_current_wal_insert_lsn();
--  0/306EE20  -> hand this to the application / pooler

-- on the replica, before the dependent read
WAIT FOR LSN '0/306EE20' WITH (TIMEOUT '200ms', NO_THROW);
--  status = success -> the write is now visible on this replica
SELECT display_name FROM profile WHERE id = 42;

Use pg_current_wal_insert_lsn() (insert position), not the flush position, so the sequence stays correct even when the writing session runs with synchronous_commit = off. The documentation notes that the LSN of the last modification should be tracked on the client application or connection pooler side — WAIT FOR itself does not remember anything between statements.

The four MODE values

MODE selects which stage of WAL processing to wait for. The default is standby_replay.

For read-your-writes, standby_replay is the mode that matters: replay is what makes the row visible to queries. standby_write and standby_flush are also satisfied by WAL the standby already holds from a base backup or archive restore, not only freshly streamed WAL. Using a standby mode on a primary — or primary_flush on a standby — is an error.

Limitations

The restrictions in the command reference shape how you can call it:

• Top-level statement only. WAIT FOR cannot run inside a function, procedure, or DO block. Your driver or pool middleware must issue it as its own statement.
• No held snapshot. The command requires that no active or registered snapshot is held, so it cannot be used in contexts where a snapshot must stay active — including transactions at isolation levels stricter than READ COMMITTED. In practice: send it as a standalone autocommit statement, before the read that needs the guarantee.
• Promotion changes the answer. If the standby is promoted while you wait with a standby mode, the command returns not in recovery (or errors without NO_THROW). Promotion creates a new timeline, and the LSN you were waiting on may belong to the old one — the application must re-evaluate whether the target is still meaningful.
• Numeric comparison only. WAIT FOR compares LSN values, not timelines. A cascading standby whose upstream was promoted can report success once its replay position passes the number, even if that position is on a different timeline. If that distinction matters, validate the timeline yourself.
• Recovery conflicts still apply. On a standby, the waiting session can be interrupted by recovery conflict resolution — some conflicts (a tablespace drop is the documented example) terminate all backends unconditionally. Callers need retry and fallback paths.

Degradation after timeout

Treat WAIT FOR as a best-effort consistency upgrade with a strict budget, not as a correctness primitive. Always pair TIMEOUT with NO_THROW and branch on the status:

• success — read from the replica as planned.
• timeout — fall back: re-issue the read against the primary, or serve the stale read and refresh asynchronously. Record the replay lag at the moment of timeout; a rising timeout rate is an early warning of replica capacity problems.
• not in recovery — the topology changed. Re-resolve the primary, re-fetch a fresh LSN there if the write path moved, and do not reuse the old LSN across the timeline switch.

Size the timeout from your read latency budget (tens to a few hundred milliseconds), not from average lag — the wait exists precisely for the tail. A WAIT FOR that frequently hits its timeout is telling you to fix replication lag first; see Streaming replication upgrades and lag control.

Prompt: design the routing layer

Design a read-your-writes layer on PostgreSQL 19 for my stack.

Context:
- Language/framework: <e.g. Go + pgx / Python + SQLAlchemy / Node + pg>
- Topology: 1 primary, <N> async streaming replicas behind <pooler/proxy>
- Typical replica lag: p50 <ms>, p99 <ms>; write QPS <n>
- Latency budget for post-write reads: <ms>

Output:
1. Where to capture pg_current_wal_insert_lsn() after writes (driver hook, middleware, or pooler), and how to propagate the LSN to read requests.
2. The exact WAIT FOR LSN call with MODE, TIMEOUT, and NO_THROW, including pseudocode for branching on success / timeout / not in recovery.
3. A fallback policy per endpoint type (user-facing read vs background job).
4. Metrics and alerts: timeout rate, wait duration histogram, replay lag at timeout.
Constraints: WAIT FOR must be a top-level statement, cannot run in functions or DO blocks, and cannot be used while a snapshot is held (no REPEATABLE READ transactions).

Command reference: PostgreSQL 19 WAIT FOR. For the broader PostgreSQL 19 feature set, see the PostgreSQL 19 overview. Field reports with measured behavior: rednafi's walkthrough and digoal's analysis (Chinese).