All stories
6 min read

PostgreSQL 19: How to read your own writes from a replica

PostgreSQL 19 can wait for replica replay before a read. See a worked SQL example and learn how to handle timeouts, connection routing and failover.

PostgreSQLDevelopmentEngineering
Two blue database stacks linked by an orange replication track, with a small clock by the replica.

Your app says “Saved”. The next screen says otherwise. If the write went to the primary database and the read went to an asynchronous replica, both responses can be technically correct. The user still sees a bug.

PostgreSQL 19, released on 19 November 2026, gives applications a more precise option than sending every follow-up read back to the primary. The new WAIT FOR LSN command lets a replica wait until it has replayed enough of the primary’s transaction log for a specific committed change to be visible. The useful part isn’t just the wait. It’s being able to say which reads need that guarantee, how long they’re allowed to wait, and what to do when the replica can’t deliver.

The difference between receiving and applying a write

PostgreSQL records database changes in its write-ahead log, or WAL. An LSN, short for log sequence number, is a position in that log. A physical standby receives WAL from its upstream, then replays it. Those are separate steps. A replica can have received or durably flushed a commit without having applied it to the database that a query reads. If the goal is visibility, the mode you want is standby_replay, not standby_write or standby_flush. See the WAIT mode definitions.

A useful mental model is a receipt with a minimum position: “Before reading this record, make sure this replica has replayed at least this far.” It is not a promise that the replica has caught up with every change on the primary. Nor does it promise that the row still contains the exact value you wrote. Someone else might have legitimately changed it since. The guarantee you’re designing for is that the read does not miss your write merely because the replica hasn’t replayed it yet.

A worked example: renaming a project

Imagine a project-management app that routes dashboard reads to replicas. A user renames a project, then lands on a dashboard that immediately fetches the project again. Returning the new name in the write response helps the first render, but does not protect the next fetch.

On the writer, commit the update first. Here’s the sequence for an explicit transaction:

BEGIN;
UPDATE projects
SET name = 'Friday release'
WHERE id = 42;
COMMIT;

SELECT pg_current_wal_insert_lsn() AS min_read_lsn;

The ordering matters. If the application captures an LSN before COMMIT, the standby might reach that position and still not be able to see the transaction. Capture it on the writer after a successful commit. This is a conservative lower bound for the read: concurrent activity can move the log position beyond your commit before you capture it, and the replica may have to apply a little more WAL than strictly necessary. The PostgreSQL example also uses the insert position so the pattern does not assume synchronous commit.

For the immediate dashboard fetch, choose a replica and keep that connection until both the wait and the read are done. Using an illustrative position, not a measured one:

WAIT FOR LSN '0/306EE20'
  WITH (MODE 'standby_replay', TIMEOUT '75ms', NO_THROW);

Only issue the following query on that replica if the returned status is success:

SELECT id, name
FROM projects
WHERE id = 42;

The LSN in a real implementation comes from the write, not from the browser or this example. If the API hands a consistency token across requests, keep it server-side or give the browser a signed, short-lived token scoped to the correct session and database routing context. A caller should not be able to choose an arbitrary future position and tie up read connections. Treat 75 ms as an example budget, not a benchmark or a universal default.

The fallback is part of the feature

If the standby reports a timeout, doing the query anyway brings the original bug back. For this dashboard, fall back to the current writer if your request deadline and writer capacity allow. If there isn’t time or there’s a wider outage, keep the confirmed write response visible and return an honest refresh error rather than treating stale data as the latest version.

Failover needs an explicit policy too. PostgreSQL’s wait compares numerical positions, not timelines: after an upstream change, the same number does not necessarily refer to the same history. A standby becoming primary during the wait can also report not in recovery. Invalidate tokens tied to the old topology and rediscover the writer rather than blindly waiting again. With asynchronous replication, a promoted server may not have received a previously acknowledged write. A visibility check isn’t a durability guarantee or a substitute for reconciliation. See the WAIT notes on promotion and recovery conflicts.

There are two quieter failure modes. First, a pool or proxy that sends the wait to one standby and the query to another has thrown away the guarantee. Reserve a specific backend for the pair, or use a routing setup that can demonstrate affinity. Second, WAIT is a top-level command. Run it before any statement that takes a snapshot or a lock in the read transaction, not halfway through an ORM transaction that has already touched a table. A wait before a fresh snapshot is useful. A wait after the application has already locked in a view of the data is not.

When the simpler solution wins

Read from the primary after writes. For a small application, this is often the best first move. Route only the handful of flows that need an immediate follow-up read to the writer. There’s no replica wait, token or replay-affinity work. The cost is extra writer traffic, especially if a single write causes several dashboard queries.

Use a short primary stickiness window. This is simple if your routing infrastructure already supports it, but the timer answers “how long since the write?”, not “has this replica applied the write?”. When the window ends, a badly lagging replica can still return stale data. Short-lived stickiness is a useful load trade-off, not proof of replay.

Require synchronous replay before acknowledging commits. PostgreSQL already has synchronous_commit = remote_apply. It can make acknowledged changes visible on the participating synchronous standby, but shifts the wait onto the write path and only applies to the standbys that satisfy the configured requirement. It’s a separate replication design, not a switch to turn on just to fix a dashboard refresh.

The PostgreSQL 19 approach earns its keep when replica reads matter, only selected reads need a stronger guarantee, and the team can own the routing, timeout and failover logic. If you don’t have those constraints, the writer is a perfectly reasonable place to read a value you just wrote.

What to measure before switching it on

Start with one write-and-redirect flow. In a disposable environment, test a caught-up replica, a deliberately lagging replica, a connection loss and a promotion. Confirm that no replica read runs after a non-success wait and that the fallback cannot blow the original request deadline. Then measure wait time, timeout rate, writer fallback rate, connection-pool occupancy and the total latency of the user’s read, rather than timing the waiting statement in isolation.

Watch the fallback rate under sustained lag. A large group of requests can wait simultaneously and then all fall back to the primary, precisely when you want it to remain healthy. A bounded wait helps one request. A cap on concurrent waits or fallbacks helps the system.

The right first question is not “Can we use replicas everywhere?” It’s “Which screen must see the write the user was just told had succeeded?” Answer that first, then choose the simplest routing policy that keeps that promise.

Adam Spice

Developer. Curious human.

More stories