Replication Lag, Read-Your-Writes & Failover — the Consistency Window Inside Your Database

A read replica is stale for exactly the same reason a data page can lag its log: replication is WAL shipping over a network. The primary streams its write-ahead log — every committed change, in strict LSN order — to each replica, which replays those records to rebuild a byte-identical copy of the data files. Under the default asynchronous mode the primary returns “committed” to the client the moment its own WAL is durable; it does not wait for any replica. So at every instant a replica's applied LSN trails the primary's commit LSN by some amount. That amount is the replication lag, and a read served by a replica whose applied LSN is behind the LSN of your write returns the pre-write row. The gap — measured both in LSN bytes and in seconds — is a consistency window; the very same gap, viewed from a crash, is your RPO window: everything committed on the primary but not yet applied on the replica is invisible to reads there and unrecoverable from there if the primary dies.

Which topology are we in?

One clarification before the mechanics. Everything on this page is the single-writer topology: one primary accepts every write and streams WAL one way to read-only replicas. Its costs are exactly the ones dissected below — lag, stale reads, and (at failover) an RTO gap plus a lost tail. The alternative, multi-master — several nodes accepting writes and replicating to each other — removes the failover gap, because a surviving master just keeps taking writes — though under async replication the dead master's un-replicated tail is still lost, exactly like the single-writer's RPO (the lost tail is a property of async replication, not of the topology); what it buys instead is a problem the single-writer design makes impossible: two masters can accept conflicting writes to the same row in the same window, and something must decide which wins — last-write-wins (silently drops one update), an application-level merge, or CRDTs. Note that multi-master does not solve read-your-writes either; it makes it harder, because your write may have landed on a different master than the one serving your next read, so the token-style fixes below get more complicated, not unnecessary.

Why it bites: read-your-writes

The canonical symptom: a user edits their profile, the UPDATE commits on the primary, the app immediately issues the follow-up SELECT, the load balancer routes that read to a replica — the whole point of having replicas is to offload reads — and the user sees their old bio. Nothing is broken. The replica is simply a few hundred milliseconds to a few seconds behind. What the user expects is read-your-writes (a.k.a. read-after-write): a session must always observe its own prior writes. It is a deliberately weak, per-session guarantee — other users may still briefly see the old value, and that is fine — but it is the one whose violation a user notices instantly, because they just typed the new value themselves. A close cousin is monotonic reads: once a session has seen a value at LSN L, it must never later see a value older than L (which happens when successive reads land on different replicas at different lag). Read-your-writes is about seeing your writes; monotonic reads is about time never appearing to run backward across your reads. Both break for the same underlying reason, and the same remediations address both.

The mechanism, precisely

On the primary, each commit stamps a monotonically increasing LSN (in PostgreSQL a byte offset into the WAL stream, written like 0/5A2C000; in MySQL the analogous position is a binlog file+offset, or a GTID set). A walsender process streams those WAL records to each standby's walreceiver, which writes them to the standby's own WAL and then replays them into its data pages. Two distinct positions therefore exist on a standby: the received/write LSN (bytes that have arrived) and the replayed/applied LSN (bytes that have actually been applied and are now visible to queries). Only the applied LSN governs what a read sees. Replication lag is thus primary_commit_LSN − replica_applied_LSN, and it has two units that you must not confuse: bytes (how much WAL is outstanding) and seconds (how far back in wall-clock time the replica's view is). They diverge badly under bursty write load — a small byte lag can still be several seconds if the standby is replay-bound, and a large byte lag can be sub-second right after a bulk load. The stale read is not a bug in replication; it is replication working exactly as designed, surfaced at the wrong read.

A traced stale read

Take one write — UPDATE profiles SET bio='…' WHERE id=7 — that commits on the primary at LSN 0/5A2C000. At that instant a particular replica is about 66 KB / 5 s of WAL behind, at 0/5A1B800. The user's browser fires the re-read 50 ms later and it lands on that replica. Follow the applied LSN forward:

The read is not wrong for all time; it is wrong for the duration of the window. The remediations below all reduce to the same idea — make the read that immediately follows a write not observe LSNs earlier than the write's commit LSN — and differ only in how, and in what they cost.

Three read-your-writes remediations, and what each costs

1. Route the user's reads to the primary for a bounded window after their write. Simplest to reason about and always correct: the primary is the one node that is by definition current. After a session writes, pin its reads to the primary for, say, a few seconds (or until you estimate the write has propagated). The cost is structural: you re-concentrate load on the very node you bought replicas to offload. If writes are frequent or the window is generous, a large fraction of sessions are reading the primary at any moment and your read-scaling quietly evaporates. Best when writes are rare relative to reads and the post-write read burst per session is short and self-limiting.

2. Sticky / pinned replica per session. Pin a session to one specific replica so at least its reads are internally consistent (this buys monotonic reads for that session). It is cheap and needs no LSN plumbing. But it does not by itself buy read-your-writes — the pinned replica can still be behind the session's own write — and it is fragile: it fails across devices (phone write, laptop read hit a different replica), and it breaks whenever the fleet rebalances, a replica restarts, or autoscaling moves the session. Useful as a monotonic-reads floor, not as a read-your-writes guarantee on its own.

3. Wait-for-LSN (a read-your-writes token). The precise fix. The write returns its commit LSN; the client carries that token; the follow-up read waits until the chosen replica's applied LSN is ≥ the token before executing (see the handshake below). PostgreSQL gives you the primitives — capture the token with pg_current_wal_lsn() on the primary, read the standby's progress with pg_last_wal_replay_lsn(), and compare via pg_wal_lsn_diff(token, replayed) <= 0 — but there is no built-in server-side blocking wait, so the app either polls the standby's replay LSN or routes to a replica already past the token. MySQL has the blocking wait built in: MASTER_POS_WAIT(binlog_file, pos [,timeout]) blocks until the replica's SQL thread reaches that binlog position, and with GTIDs WAIT_FOR_EXECUTED_GTID_SET(gtid_set [,timeout]) blocks until the replica has executed that transaction set. The cost: token plumbing through the app, and a session that just wrote can stall up to the current lag — so you always pair it with a timeout and a fallback (read the primary, or return the client's own optimistic copy). It preserves read-scaling because only sessions that just wrote ever wait, and only until their one replica catches up.

Synchronous vs asynchronous commit: moving the window to zero

Everything above treats lag as a fact to be worked around on the read path. The other lever is to shrink the window on the write path. Asynchronous commit (the default) returns as soon as the primary's own WAL is durable: fast writes, but a non-zero RPO — a crash between ack and replication loses that tail. Synchronous replication makes the primary wait for one or more replicas to confirm before it acks the client. In PostgreSQL this is synchronous_commit plus synchronous_standby_names: on waits for the standby to flush the WAL, while remote_apply waits until the standby has actually replayed it — and remote_apply is the setting that gives you fresh reads on that standby, because “acked” now implies “applied there.” MySQL's equivalent is semisynchronous replication (rpl_semi_sync_source_enabled), where the source waits for at least rpl_semi_sync_source_wait_for_replica_count acknowledgements; AFTER_SYNC vs AFTER_COMMIT controls whether it waits before or after the local commit is externalized.

The trade is stark. Synchronous commit to the acked replica gives zero RPO there and, with remote_apply, correct reads — but every write now pays a cross-node round trip, and if the required sync replica is down the primary blocks, so naive sync replication trades a durability win for an availability loss. Quorum commit is the escape: configure ANY k (s1, s2, s3) so the primary waits for any k of n standbys. With k smaller than the number of healthy standbys you keep zero-RPO durability and tolerate the loss of individual standbys without stalling — you only block when fewer than k can ack.

Failover: what “we lost 3 seconds of writes” means

When the primary dies and a standby is promoted, the new primary can only continue from the WAL it had actually received and applied. Under async replication, every transaction the old primary committed and acked but whose WAL had not reached the promoted standby is simply gone. That is the RPO made concrete: “we lost 3 seconds of writes” means every transaction committed on the old primary in the ~3 s window whose WAL never crossed the wire to the standby you promoted — real orders, real payments the client was told succeeded — do not exist on the new primary. The RPO equals the lag at the instant of failover, which is precisely why you monitor lag as a first-class SLO, not a curiosity. Synchronous/quorum commit is what drives that failover RPO to zero for the acked set.

Two more failover hazards. First, split-brain: if the old primary was only network-partitioned (not truly dead) and comes back still believing it is primary, you now have two nodes accepting writes that diverge. The defense is fencing — positively preventing the old primary from serving, by revoking its VIP/routing, or STONITH (“shoot the other node in the head,” i.e. power/fence it) — performed before promotion completes. Second, promotion is not free: RTO (time to detect, fence, promote, and repoint clients) is a separate budget from RPO, and a slow, cautious failover that guarantees no split-brain necessarily costs RTO. The lossless-failover assumption is the one candidates get wrong: async failover is not lossless, and even sync failover is only lossless for the writes that were acked against the surviving quorum.

Pitfalls

• Pinning to the primary silently kills read-scaling. The route-to-primary fix is so easy it spreads: soon every session with a recent write reads the primary, and under write-heavy load that is most of them. You bought replicas and are back to a single-node read path — visible only as creeping primary CPU, not as an error.
• Measuring lag in bytes when you needed seconds (or vice-versa). A dashboard showing “lag: 2 MB” tells you nothing about how many seconds of user-visible staleness that is; a replay-bound standby can be seconds behind on a trickle of bytes. Alert on both the byte diff and a time-based estimate.
• A long-running query on the replica stalls WAL apply. In PostgreSQL, replaying WAL that removes rows a long read-only query still needs creates a replay-vs-query conflict. max_standby_streaming_delay lets replay pause (which increases lag and can cancel the query when it expires); hot_standby_feedback = on makes the standby tell the primary to hold off vacuuming those rows (which prevents cancellations but causes table bloat on the primary). It is a genuine trade, not a free knob — a heavy analytics query on a replica can silently balloon your read-your-writes window.
• Assuming failover is lossless. Covered above — async failover always has RPO equal to the lag at failover; only synchronous/quorum commit against the surviving set drives it to zero.
• Trusting sticky-replica routing as read-your-writes. A pinned replica gives monotonic reads, not read-after-write; it can still be behind your own write. Do not conflate the two.

Selection: route-to-primary vs wait-for-LSN vs synchronous replication

These are the three named alternatives, and they sit on a curve of “how much read-scaling / write-latency am I willing to spend for freshness.”

Route-to-primary spends read-scaling: it is correct and trivial to implement, costs nothing on the write path, but reloads the primary and does not scale when writes are frequent. Reach for it first when writes are rare, the post-write read burst is short, or you are shipping a fix today.

Wait-for-LSN spends a little latency on just-wrote sessions and some app complexity: it keeps the read fleet doing its job (only recent writers ever wait, and only until one replica catches up), and it is the right default at scale. Its failure mode is a stall when lag spikes, so it lives or dies by its timeout-and-fallback. Choose it over route-to-primary the moment primary read load from pinning becomes real; choose it over synchronous replication when you can tolerate a non-zero RPO but want cheap, correct reads.

Synchronous / quorum replication spends write latency (and, done naively, availability) to buy the strongest guarantee: with remote_apply the acked replica is current, so reads there are fresh and failover RPO is zero for the acked set. It is the only option that fixes the write-loss problem too, not just the stale-read problem. Choose it when correctness and RPO dominate cost — payments, ledgers, anything where “we lost 3 seconds” is unacceptable — and pair it with quorum (ANY k) plus spare standbys so a single replica outage cannot stall the primary. In practice mature systems layer these: quorum-synchronous commit for durability, wait-for-LSN for cheap read-your-writes off the replicas, and route-to-primary only as the fallback when a token wait times out.

Takeaways

• Replication is WAL shipping; a stale replica read is the LSN gap between the primary's commit LSN and the replica's applied LSN — a consistency window on reads and an RPO window on failover, the same gap seen twice.
• Read-your-writes is a per-session guarantee. The precise fix is wait-for-LSN: the write returns its commit LSN as a token, and the read waits until the chosen replica's applied LSN ≥ the token (MySQL MASTER_POS_WAIT / WAIT_FOR_EXECUTED_GTID_SET; Postgres via pg_last_wal_replay_lsn() comparison).
• Route-to-primary is correct but re-loads the primary and kills read-scaling; sticky-replica buys only monotonic reads, not read-after-write. Know which guarantee each actually provides.
• Async failover is not lossless: RPO equals the lag at failover. Synchronous/quorum commit (remote_apply, semisync) drives that to zero at the cost of write latency and, without quorum, availability — and split-brain still requires fencing/STONITH.

Beyond one node: the single-node / distributed seam

Everything on this page — WAL durability, MVCC visibility, read-your-writes, failover RPO — is single-node reasoning (one primary, its replicas). The moment data is sharded across independent primaries, a different class of problem opens that these mechanisms do NOT solve, and a staff interviewer will push you across that seam:

• Cross-shard atomicity: a transaction touching two shards can't rely on one node's WAL; you need two-phase commit (2PC, with its coordinator-failure blocking window) or a consensus-based commit (Spanner/Percolator-style), or you redesign to keep the transaction within one shard.
• Distributed deadlock: cycles now span nodes; local lock managers can't see them, so you need a global detector or timeout-based abort.
• Live resharding and partition-key skew: splitting a hot shard under load is a routing-epoch cutover problem (see System Design → Data Partitioning), not a VACUUM.
• Distributed SQL (Spanner, CockroachDB, Vitess) hides some of this behind a SQL API but pays for it in commit latency (commit-wait) — know what it's buying and what it costs.

The judgment: push scale up (bigger box, read replicas, partitioning within a node) as far as it goes before you shard, because sharding trades every guarantee on this page for the distributed problems above. When the interview crosses this seam, name it explicitly — that boundary awareness is itself a staff signal. (Full treatment lives in the System Design track: partitioning, consensus, quorum.)

Sources: M. Kleppmann, Designing Data-Intensive Applications, ch. 5 (Replication — leaders/followers, replication lag, read-your-writes & monotonic reads, handling node outages/failover); PostgreSQL documentation — Streaming Replication, Synchronous Replication & synchronous_commit, Hot Standby (hot_standby_feedback, max_standby_streaming_delay), and WAL functions (pg_current_wal_lsn, pg_last_wal_replay_lsn, pg_wal_lsn_diff); MySQL Reference Manual — Semisynchronous Replication, Replication with Global Transaction Identifiers, and MASTER_POS_WAIT / WAIT_FOR_EXECUTED_GTID_SET. Authored for this guide.