PostgreSQL 19 WAIT FOR LSN from PHP: read-your-writes on a replica, and four ways it bites" published: false

You add a read replica. A user saves a form, gets redirected, and the next page shows the old data. The write went to the primary, the read went to the replica, and the replica hadn't caught up yet.

PostgreSQL 19 adds a built-in answer: the WAIT command. You give it the WAL position (LSN) of your write, and the replica blocks until it has replayed that far. I tried it from PHP on PostgreSQL 19 Beta 4, and found a few things worth knowing before you ship it.

Note: PostgreSQL 19 was at Beta 4 when I wrote this. Check the final release notes, as details may still change.

The problem, measured

Setup: one primary and one asynchronous streaming standby on the same machine, PHP 8.5.10 with PDO. After each INSERT on the primary I read the row back from the replica immediately.

5000 of 5000 reads were stale. Not most of them. All of them. The read arrives about 60 µs after the commit, and the row takes roughly 300 µs to get replayed on the replica. Under write load (pgbench with 4 clients on the primary) it was 1496 of 1500.

The fix

WAIT FOR LSN '0/0306EE20' WITH (TIMEOUT '50ms', NO_THROW);
-- returns one row: success | timeout | not in recovery

The flow: write on the primary, grab the current LSN, run WAIT on the replica, then read from the replica.

final class ReadYourWrites
{
    public function __construct(
        private PDO $primary,
        private PDO $replica,
        private int $timeoutMs = 50,
    ) {}

    /** Call right after the write has committed on the primary (synchronous_commit = on). */
    public function commitLsn(): string
    {
        return $this->primary->query('SELECT pg_current_wal_flush_lsn()')->fetchColumn();
    }

    /** Returns the connection that is safe to read from. Never call inside an open transaction. */
    public function readerFor(?string $lsn): PDO
    {
        if ($lsn === null) {
            return $this->replica;
        }
        if (!preg_match('~^[0-9A-F]+/[0-9A-F]+$~', $lsn)) {
            throw new InvalidArgumentException('Not a valid LSN');
        }
        $status = $this->replica
            ->query("WAIT FOR LSN '$lsn' WITH (TIMEOUT '{$this->timeoutMs}ms', NO_THROW)")
            ->fetchColumn();

        return $status === 'success' ? $this->replica : $this->primary;
    }
}

Result on 5000 writes: 0 stale reads, with a median wait of 315 µs on an idle standby and 1.2 ms under write load. If the replica can't catch up in time, the code falls back to the primary, which is always correct.

The LSN has to travel with the user between requests. Keep it in the session, and read from the replica only after WAIT succeeds.

Scenario	Stale reads without WAIT	Stale reads with WAIT	WAIT median
Idle standby	5000 / 5000	0 / 5000	315 µs
Write load	1496 / 1500	0 / 1500	1.2 ms

All numbers come from a single vCPU running everything (primary, standby, PHP, pgbench), so absolute latencies are inflated and a real network adds a round trip. Treat the ratios, not the microseconds, as the point.

Gotcha 1: you can't bind the LSN

WAIT is a utility command, so PostgreSQL doesn't accept parameters in it. With PDO's default native prepared statements:

$stmt = $replica->prepare('WAIT FOR LSN :lsn');
$stmt->execute(['lsn' => $lsn]);
// SQLSTATE[42601]: syntax error at or near "$1"

It only works with ATTR_EMULATE_PREPARES = true. The safe option is to validate the string with a strict regex (as in the class above) and interpolate it. An LSN is just hex, a slash and hex.

Gotcha 2: it must be the first thing in a transaction

The docs say WAIT can't run while you hold a snapshot or locks. In practice:

$replica->beginTransaction();
$replica->query('SELECT count(*) FROM t');
$replica->query("WAIT FOR LSN '$lsn'");
// ERROR: cannot wait for a standby LSN while holding locks

The same applies after the first query in a REPEATABLE READ transaction (WAIT cannot be executed while the current transaction holds a snapshot). A plain SET LOCAL before it is fine.

Two details cost me time:

• An error inside a transaction aborts it (SQLSTATE 25P02), so call WAIT before beginTransaction().
• If the target LSN is already reached, WAIT returns immediately even after a query. The restriction only triggers when it would actually have to wait. So your tests can pass on an idle replica and fail in production.

It also can't run inside a DO block or function (WAIT can only be executed as a top-level statement).

Gotcha 3: the docs' LSN function can hang

The documentation example takes the LSN with pg_current_wal_insert_lsn(). In my idle test, 5 out of 5000 waits timed out. Every one of those LSNs had the same page offset: 24 bytes, which is the size of a WAL page header.

0/0322A018   0/03240018   0/03268018   0/032C8018   0/032DE018

My explanation (an inference, not something I checked in the source): the commit record ended exactly at a WAL page boundary, and the insert pointer was reported after the new page's header. The replica has replayed everything that exists, but the target sits 24 bytes past the last real record, so WAIT blocks until more WAL arrives. On an idle system that can be a long time.

With the default synchronous_commit = on, pg_current_wal_flush_lsn() does not have this problem: 0 timeouts in 5000, and it was also slightly faster. Either way, use TIMEOUT and NO_THROW, check the status, and fall back to the primary. Never ignore the returned status.

Gotcha 4: synchronous_commit = off changes everything

If your app uses asynchronous commit, the flush LSN can be behind your commit record:

Primary session with synchronous_commit = off	Result
flush_lsn, then WAIT	success in 48 µs, but 300 / 300 reads were stale
insert_lsn, then WAIT	correct, but median wait 201 ms

WAIT dutifully reports success for a position the replica really did reach. It just isn't your write. The insert LSN is correct here, but you pay roughly one wal_writer_delay (200 ms by default) per wait, and 8 of 300 still hit my 300 ms timeout.

So: flush LSN if you use synchronous commit, insert LSN plus the fallback if you don't.

Symfony and Doctrine

Nothing here is tied to PDO. With DBAL, run the same WAIT through executeQuery() on the replica connection, before opening a transaction. One natural place to capture the LSN is a listener that runs after a flush on the primary connection and stores the result in the session. I've only tested the plain PDO version above, so treat that as a design sketch.

Takeaways

• Read-after-write against an async replica is broken by default: 5000/5000 stale reads in my test.
• WAIT FOR LSN fixes it cheaply: about 0.3 ms idle, about 1.2 ms under write load on my setup.
• Always pass TIMEOUT and NO_THROW, check the status, and fall back to the primary.
• Validate the LSN yourself, because you can't bind it.
• Call it before opening a transaction.
• Pick the LSN function to match your synchronous_commit setting.

I ran into this while working on search and read-heavy PostgreSQL workloads for PHP, including Fuzzphony. If you have a real multi-machine setup, I'd love to see your numbers in the comments.