Welcome to the 7th blog post of PG19 Hacktober!!

In PostgreSQL 18 and earlier, asynchronous streaming replication introduced an unavoidable window of replication lag between a primary instance and its read standbys. An application could commit a write on the primary and immediately query a standby, only to see stale data, because the WAL records for that commit had not yet been replayed. This is the Read-Your-Writes (RYW) consistency problem in distributed read-scaling architectures.

Three approaches existed to address this:

Approach	Trade-off
Route reads back to primary	Eliminates read scaling benefits
Enable synchronous replication	Slows every COMMIT on the primary
Poll pg_last_wal_replay_lsn()	CPU/network churn; snapshot deadlock risk

None of these were satisfying. PostgreSQL 19 changes that by shipping WAIT FOR LSN ,a native utility command that lets standby readers block efficiently until a target LSN is replayed, without holding any transaction snapshot.

The Problem in Depth

How Replication Lag Creates Stale Reads

When a transaction commits on the primary, PostgreSQL assigns it a Log Sequence Number (LSN) , a monotonically increasing byte offset in the WAL stream. PostgreSQL ships these WAL records asynchronously to standbys, which replay them in the background.

The standby pg_last_wal_replay_lsn() may lag behind the primary pg_current_wal_lsn() by milliseconds or even seconds under load. Any read issued to the standby before replay catches up returns stale data.

Why Polling Loops Were Painful (PostgreSQL 18)

Wrapping the loop in a function or procedure introduces a worse problem. Code that runs inside a transaction typically holds a snapshot. On a standby, a snapshot held while waiting can prevent replay of WAL records, which creates a self-deadlock:

1. The wait is for replay to pass the target LSN.
2. Replay can be held up by the snapshot the waiter holds.
3. Neither side can make progress.

Under REPEATABLE READ, a snapshot is taken at the first statement, so waiting inside such a transaction has the same problem.

A pg_wal_replay_wait() procedure was committed during PostgreSQL 17 development, but it was reverted before release. No native wait mechanism shipped in 17 or 18.

Hands-On Demo

Let’s compare the behavior between PostgreSQL 18 and PostgreSQL 19.

Test Case

PostgreSQL 18 Primary

We create a simple table:

CREATE TABLE customer_orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT,
    total_amount NUMERIC(10,2),
    created_at TIMESTAMPTZ DEFAULT clock_timestamp()
);

Insert a row:

INSERT INTO customer_orders (customer_id, total_amount)
VALUES (101, 249.99);

Now capture the current WAL position:

SELECT pg_current_wal_lsn() AS target_lsn;

Example:

 target_lsn
------------
 B/930B0630

This LSN becomes our synchronization target.

PostgreSQL 18 Standby

On the standby, we need to wait until:

pg_last_wal_replay_lsn() >= B/930B0630

A polling loop can be used:

DO $$
DECLARE
    v_target_lsn    pg_lsn := 'B/930B0630';
    v_current_lsn   pg_lsn;
    v_start_time    timestamptz := clock_timestamp();
    v_timeout       interval := interval '5 seconds';
BEGIN
    LOOP
        v_current_lsn := pg_last_wal_replay_lsn();
        IF v_current_lsn >= v_target_lsn THEN
            RAISE NOTICE
                'Target LSN % reached in % ms',
                v_target_lsn,
                EXTRACT(
                    MILLISECONDS FROM
                    clock_timestamp() - v_start_time
                );
            EXIT;
        END IF;
        IF clock_timestamp() - v_start_time >= v_timeout THEN
            RAISE EXCEPTION
                'Timeout waiting for LSN %. Current replay LSN is %',
                v_target_lsn,
                v_current_lsn;
        END IF;
        PERFORM pg_sleep(0.01);
    END LOOP;
END $$;

In our test:

NOTICE: Target LSN B/930B0630 reached in 0.462 ms
DO

Once the target LSN has been replayed, the standby can safely read the newly committed row:

SELECT *
FROM customer_orders
WHERE customer_id = 101;

Result:

 order_id | customer_id | total_amount |            created_at
----------+-------------+--------------+-------------------------------
        1 |         101 |       249.99 | 2026-10-07 14:47:09.886487+05:30

This works, but it has serious problems:

• CPU churn: Each pg_sleep cycle burns a round-trip query + context switch.
• Network pressure: Every iteration emits a query to pg_last_wal_replay_lsn().
• No clean error semantics: Timeouts surface as raw exceptions that are hard to distinguish from real errors.

Why Stored Procedures Made Things Worse

A natural evolution was to wrap the polling loop in a stored procedure so applications could call it cleanly. This introduced a far more dangerous problem.

The Snapshot Retention Deadlock:

Any SQL function or stored procedure executed inside a PostgreSQL transaction acquires a transaction snapshot. On a standby node, holding an active snapshot prevents the WAL startup process from applying conflicting WAL records — specifically, operations that conflict with the snapshot’s visibility horizon, such as VACUUM heap cleanup, page pin releases, or relation extension locks.

The result is a dependency cycle:

• The procedure waits for WAL replay to advance past the target LSN.
• WAL replay is blocked because the procedure holds an active snapshot.
• Neither can make progress.

Additionally, when default_transaction_isolation = ‘repeatable read’, even an implicit single-statement transaction acquires a snapshot. This meant the stored procedure approach was broken by default for a large class of production applications.

PostgreSQL 19: The WAIT FOR LSN Command

PostgreSQL 19 ships WAIT FOR LSN as a utility command, not a function, not a stored procedure. This architectural distinction is what makes everything work.

PostgreSQL 19

Now let’s perform the same type of test using PostgreSQL 19.

We create the table:

CREATE TABLE customer_orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT,
    total_amount NUMERIC(10,2),
    created_at TIMESTAMPTZ DEFAULT clock_timestamp()
);

Insert two rows:

INSERT INTO customer_orders (customer_id, total_amount)
VALUES (101, 249.99);

INSERT INTO customer_orders (customer_id, total_amount)
VALUES (102, 499.50);

Capture the target LSN:

SELECT pg_current_wal_lsn() AS target_lsn;

Example:

 target_lsn
------------
 1/F90B4C68

Now PostgreSQL 19 allows us to directly wait for that LSN:

WAIT FOR LSN '1/F90B4C68';

Result:

 status
---------
 success

No custom polling loop.

No pg_sleep().

No repeated calls to:

pg_last_wal_replay_lsn()

Timeout Support

PostgreSQL 19 also provides explicit timeout handling.

For example:

WAIT FOR LSN '1/F90B4C68'
WITH (
    TIMEOUT '2s'
);

Result:

 status
---------
 success

You can also use a shorter timeout:

WAIT FOR LSN '1/F90B4C68'
WITH (
    TIMEOUT '500ms',
    NO_THROW
);

Result:

 status
---------
 success

This provides much cleaner control for applications that need predictable waiting behavior

Replication Synchronization Modes

One of the interesting parts of WAIT FOR LSN is the ability to specify a synchronization mode.

For example:

WAIT FOR LSN '1/F90B4C68'
WITH (
    MODE 'standby_write',
    TIMEOUT '1s'
);

Or:

WAIT FOR LSN '1/F90B4C68'
WITH (
    MODE 'standby_flush',
    TIMEOUT '1s'
);

This allows the application to express how far the target LSN must progress before the command succeeds.

MODE ‘mode‘

Specifies the type of LSN processing to wait for. If not specified, the default is standby_replay. The valid modes are:

• standby_replay: Wait for the LSN to be replayed (applied to the database) on a standby server. After successful completion, pg_last_wal_replay_lsn() will return a value greater than or equal to the target LSN. This mode can only be used during recovery.
• standby_write: Wait for the WAL containing the LSN to be written to disk on a standby server, but not yet necessarily flushed. This is faster than standby_flush but provides weaker durability guarantees since the data may still be in operating system buffers. This is satisfied by WAL already present on the standby from a base backup, archive restore, or prior streaming, as well as WAL newly received from the primary. This mode can only be used during recovery.
• standby_flush: Wait for the WAL containing the LSN to be flushed to disk on a standby server. This provides a durability guarantee without waiting for the WAL to be applied. This is satisfied by WAL already present on the standby from a base backup, archive restore, or prior streaming, as well as WAL newly received from the primary. This mode can only be used during recovery.
• primary_flush: Wait for the WAL containing the LSN to be flushed to disk on a primary server. After successful completion, pg_current_wal_flush_lsn() will return a value greater than or equal to the target LSN. This mode can only be used on a primary server (not during recovery).

Reference from: https://www.postgresql.org/docs/19/sql-wait.html

The Big Difference

The most important difference between our PostgreSQL 18 and PostgreSQL 19 tests can be summarized as:

PostgreSQL 18	PostgreSQL 19
Application polling	Native WAIT FOR LSN
pg_last_wal_replay_lsn()	LSN wait primitive
pg_sleep() required	No polling loop
Custom timeout logic	Native TIMEOUT
Custom error handling	NO_THROW available
More application code	Simple SQL command
Snapshot/replay concerns	Designed as a utility command

Flow in PostgreSQL 18 & 19

PostgreSQL 18                         PostgreSQL 19

INSERT                                INSERT
  │                                     │
  ▼                                     ▼
Get LSN                               Get LSN
  │                                     │
  ▼                                     ▼
Poll standby                          WAIT FOR LSN
  │                                     │
  ▼                                     ▼
Check replay LSN                      Target reached
  ├─ Not reached ─► Sleep ─► Poll       │
  └─ Reached                            ▼
        │                              READ
        ▼
       READ

Why This Matters for Real Applications

This feature becomes particularly interesting in architectures where applications use:

• Primary nodes for writes
• Read replicas for scaling
• Connection pools
• Load balancers
• Geographic replicas
• Reporting workloads
• Microservices
• Event-driven applications

The application can now coordinate reads against a known WAL position instead of blindly assuming the standby is already caught up.

Conclusion

WAIT FOR LSN is a small feature that solves a practical problem. Before PostgreSQL 19, applications had to build their own waiting logic around pg_last_wal_replay_lsn(), sleeps and timeouts. Now there is a native command:

WAIT FOR LSN 'target_lsn' WITH (MODE '...', TIMEOUT '...', NO_THROW);

The key improvement is not just fewer lines of SQL. LSN synchronization becomes a first-class PostgreSQL operation instead of application-managed polling.

Note: PostgreSQL 19 is still in beta (Beta 4 was released on September 24, 2026), so details may change before 19.0 is final.