[KongchangAI]
ConceptWAIT FOR LSN

WAIT FOR LSN

An SQL command introduced in PostgreSQL 19 that addresses read-after-write consistency in read-write splitting architectures. It allows a standby server to wait until WAL replay reaches a specified LSN before executing a query.

Timeline (last 90 days)

Nov 19

PostgreSQL 19 introduced the WAIT FOR LSN command to solve the "read-after-write" consistency problem in read-write splitting architectures.

Unverified50%
Nov 19

The WAIT FOR LSN command makes a standby block and wait until its replay progress reaches or exceeds the specified LSN.

Unverified50%
Nov 19

In the example, after a write operation completes, the application can call pg_current_wal_insert_lsn() to obtain the LSN (for example 0/0306EE20); subsequent requests run WAIT FOR LSN '0/0306EE20' WITH (TIMEOUT '0.1s') before executing a query on the standby, and perform the read operation only after the standby has replayed WAL up to the specified LSN.

Unverified50%
Nov 19

The TIMEOUT clause of WAIT FOR LSN is optional; if set, the command returns an error when the standby fails to catch up to that LSN within the specified time, and the application can use this to decide whether to degrade the request to the primary or prompt a retry.

Unverified50%
Nov 19

The mechanisms in MySQL equivalent to WAIT FOR LSN are WAIT_FOR_EXECUTED_GTID_SET() and MASTER_POS_WAIT(); although PostgreSQL has no GTID, the WAL LSN provides an equivalent monotonically advancing position identifier, and WAIT FOR LSN is semantically highly similar to MASTER_POS_WAIT().

Unverified50%
Nov 19

For teams not yet using read-write splitting, WAIT FOR LSN offers a low-cost scaling option: route read-only queries to standbys when needed and maintain intra-session consistency through WAIT FOR LSN.

Unverified50%

All Facts (6)

Source Articles