Read Replica Lag: Read-Your-Writes Consistency, Sticky Sessions and Log Positions

Adding a read replica is one of the cheapest ways to take load off a database. Writes go to the primary, reads go to the replica, and the primary breathes again. The problem does not show up on day one. A user edits their profile, clicks save, the page reloads and shows the old name. They refresh a second later and the new value is there. A ticket arrives saying "it does not save", and nobody can reproduce it locally because there is no replica on a laptop. This post covers why that lag is unavoidable, the ways to guarantee read-your-writes consistency, and which one fits where.

Why lag is never zero

With streaming replication the primary writes changes to its log (the WAL in PostgreSQL), acknowledges the commit, and only then ships the log to the replica, which replays it. In the default asynchronous setup the commit does not wait for the replica. So after every write there is a window, milliseconds on a good day, during which the replica returns stale data.

That window is invisible at quiet hours and grows under load. A long report query on the replica, a large batch update, a network hiccup or a replica hitting its disk I/O limit pushes the lag into seconds, and on bad days into minutes. "Our lag is always a few milliseconds" is a measurement, not a design guarantee. Design for the worst lag you can expect, not the typical one.

Approach 1: stick to the primary for a while after a write

The most common and simplest fix: if a request or session has written, its reads, and reads for a short period afterwards, go to the primary. Some ORMs ship this out of the box. Laravel's database connection config, for example, has a sticky option that routes reads to the primary for the rest of a request once it has written.

To extend this beyond a single request, store a "last write at" timestamp in the session or a cookie. The routing layer picks the primary if not enough time has passed since then.

The upside is that it is easy to build. The downside is that the window is a guess. Pick five seconds and the bug returns the day lag reaches eight. Pick a long window and heavy writers almost never hit the replica, so you lose part of the gain. It is still the right starting point for most web applications.

Approach 2: carry the log position and wait for the replica to catch up

A precise method looks at log position instead of time. After the write, read the primary's current WAL position:

SELECT pg_current_wal_lsn();

Attach that value to the client or the session. On the next read, ask the replica whether it has replayed up to that point:

SELECT pg_wal_lsn_diff(pg_last_wal_replay_lsn(), '0/3A2B1C8'::pg_lsn) >= 0 AS caught_up;

If it is true, read from the replica safely. If not, wait briefly and ask again, or send that read to the primary. MySQL supports the same idea with GTIDs: capture the executed GTID set after the write and use WAIT_FOR_EXECUTED_GTID_SET with a timeout on the replica.

This is correct no matter how large the lag gets, and it never visits the primary without a reason. The price is complexity: you have to carry the position somewhere, check which replica has caught up when you have several, and put an upper bound on the wait. Without a bound, one lagging replica leaves your request hanging. The sensible behaviour is usually to wait a few hundred milliseconds and then fall back to the primary.

Approach 3: tie the commit to the replica applying it

In PostgreSQL, with synchronous replication and synchronous_commit set to remote_apply, the commit is not acknowledged until the synchronous standby has applied it. Reads from that standby see the new data from that moment on. The setting can be changed per transaction, so you can reserve it for critical writes:

BEGIN;
SET LOCAL synchronous_commit = remote_apply;
UPDATE accounts SET name = 'New Name' WHERE id = 42;
COMMIT;

Two things matter here. First, it only has an effect if a synchronous standby is configured through synchronous_standby_names; otherwise the setting quietly behaves like a local commit and you believe you are protected when you are not. Second, every such write now pays the replica's apply time, and if the synchronous standby goes down those writes block. Treat it as a tool for a handful of writes that must be consistent, not as a general fix.

Which one to pick

For most applications, start with sticky sessions and set the window to several times the worst lag you have actually observed. If the primary is still overloaded, or lag outliers exceed the window, move to log position tracking. Keep synchronous apply for the few flows where a stale read does real damage, such as payment status or permission changes.

One alternative is often better than any of these: if only a small share of your reads is truly heavy, send only those (reports, search, exports) to the replica and keep everything else on the primary. Those queries tolerate a few seconds of staleness, and the read-your-writes problem never appears.

Pitfalls

Measuring lag wrong. The expression now() - pg_last_xact_replay_timestamp() on the replica is popular, but when the primary is idle it keeps growing and pages you all night. The replay_lag column of pg_stat_replication on the primary, or the difference between WAL positions on both sides, is far more reliable.

Filling the cache from the replica. You invalidate a cache entry after a write and refill it on the next read. If that read hits the replica, stale data lands in the cache, and now you are waiting on the cache TTL rather than the replica. Fill caches from the primary or check the log position first.

Background jobs. You insert a row and immediately enqueue a job. A worker picks it up within milliseconds, looks the row up on the replica, does not find it and drops the job as "record not found". Make the worker's first read after its triggering write go to the primary, or include the log position in the job payload.

Cancelled queries on the replica. Long queries on a replica get cancelled with a "conflict with recovery" error when cleanup records from the primary collide with them. Turning on hot_standby_feedback reduces cancellations but can cause table bloat on the primary. Raising max_standby_streaming_delay also reduces them, at the cost of more lag. Choose knowingly.

Poolers and transactions. You cannot split reads and writes inside a single transaction across servers. Once a transaction has started, every statement in it must stay on the primary, or you get strange bugs where a transaction cannot see its own writes.

Failover. When the primary dies and a replica is promoted, an asynchronous setup can lose the last few commits. Logic that waits on log positions must recognise that the new primary may start behind the position it is waiting for, otherwise it keeps waiting for a position that will never arrive.

When not to read from a replica at all

If your primary is not yet struggling with read load, a replica does not buy you scale; it buys you a consistency problem. Fix slow queries, indexes and application caching first. If the replica exists for high availability and you do not need it for reads, do not route traffic to it; keep it as a failover target. Moving reads to a replica is an architectural decision, and it requires every read path to answer one question: is data a few seconds old acceptable here? Routing traffic without asking that question is where the tickets above come from.