skip to content

A user saves their profile and the next page immediately shows the old values. Reads are served by an asynchronous replica. Explain what is happening and the ways to fix it.

level: middleimportance: must knowfreq 58%

answer

  1. commit acked before replay ⇒ stale read
  2. read-your-writes + monotonic reads
  3. sticky-to-primary window after write
  4. LSN / GTID token, wait-for-apply
  5. evict lagging replicas from the read pool

basics

~20 s

The write committed on the primary but the replica hadn't replayed it yet — replica lag breaks read-your-own-writes. Fixes: route a user's reads to the primary for a short window after they write, or capture the write's log position and make the replica wait until it has applied at least that position.

solid answer

~1 min

Asynchronous replication means the primary acknowledges the commit before replicas have applied it. The subsequent read hits a replica that is milliseconds-to-seconds behind, so it returns the pre-update row. This is a **read-your-own-writes** violation; a related one is **monotonic reads**, where two consecutive reads land on replicas with different lag and the data appears to go backwards. Fixes, roughly in order of cost: 1. **Write-then-read stickiness.** After a write, pin that user's session to the primary for a few seconds (or until an estimated lag horizon passes). Simple, effective, and the most common production answer. 2. **Causal tokens / LSN waiting.** Capture the write's log position (PostgreSQL `pg_current_wal_lsn()`, MySQL GTID) and carry it in the session/cookie; before reading, either pick a replica that has applied at least that position or have it wait (`WAIT_FOR_EXECUTED_GTID_SET`). Precise, more machinery. 3. **Route that endpoint to the primary permanently** when it's a small fraction of traffic. 4. **Synchronous replication** for the replicas you read from — bounds staleness at the price of commit latency. 5. **Lag-aware routing**: health-check replicas and pull any replica exceeding a lag threshold out of the read pool. UI-level echoing of the submitted values is a fine complement but not a fix — the next page load can still be stale.

code

sql · 5 lines
sql
-- on the primary, right after the write
SELECT pg_current_wal_lsn();     -- e.g. 0/6A2F1C8

-- on a candidate replica, before serving the read
SELECT pg_last_wal_replay_lsn() >= '0/6A2F1C8'::pg_lsn AS fresh_enough;

go deeper

for a junior

Name the cause — asynchronous replication lag — and the simplest fix, reading from the primary right after a write.

for a middle

Distinguish read-your-own-writes from monotonic reads, and describe both the sticky-window and the token/LSN approaches with their tradeoffs.

for a senior

Add operational detail: measuring the lag distribution to size the window, lag-aware routing and replica eviction, and reducing lag by shortening transactions.

for a principal

Frame it as choosing a consistency contract per use case — which reads need causal consistency, which tolerate bounded staleness — and design the routing layer so that contract is explicit and enforceable rather than incidental.

## The mechanism With asynchronous replication the commit path is: the primary writes and flushes its log, marks the transaction committed, and returns success to the client. Separately, a replication process ships the log to replicas, which replay it. The client therefore gets its acknowledgement *before* any replica has the change. If the very next request reads from a replica, it may observe the database as it was before the write. The amount of the gap — **replica lag** — is measured either in time (seconds behind the primary) or in log position (bytes/LSN or GTID difference). Under light load it can be sub-millisecond; under a big batch job, a long transaction, or an IO-starved replica, it can be minutes. ## The two consistency guarantees you are missing - **Read-your-own-writes (read-after-write):** a client that made a write must see that write in its own subsequent reads. This is the one users notice immediately, because it looks like the save failed. - **Monotonic reads:** a client must never see time go backwards. If request 1 goes to a replica that is 100 ms behind and request 2 goes to one 5 s behind, data that appeared can vanish. Round-robin routing across replicas with different lag causes this even without a recent write. Neither is provided by asynchronous replication; both must be added by the routing layer. ## Fix 1 — stickiness after a write Record a timestamp (or a flag with TTL) when a session performs a write, and for the next N seconds send that session's reads to the primary. N should exceed your typical p99 lag, e.g. 2–5 seconds. - Pros: trivial, no engine features required, works with any pooler. - Cons: pushes read load back onto the primary exactly when a user is active; a wrong N is either stale reads or unnecessary primary load; needs sticky session state, which is awkward in stateless multi-node apps unless the flag lives in a cookie or shared cache. - It also only fixes *that* user's reads. A second user reading the same record still sees stale data — which is usually acceptable. ## Fix 2 — causal tokens (the precise fix) After the write, ask the primary for its current log position and hand it back to the client (session, cookie, header). On the next read, the routing layer either: - picks a replica whose applied position ≥ the token, or - asks the replica to wait until it has applied that position, then reads. MySQL exposes this directly with GTIDs and `WAIT_FOR_EXECUTED_GTID_SET`; PostgreSQL exposes `pg_current_wal_lsn()` on the primary and `pg_last_wal_replay_lsn()` on standbys, so the router can compare. Some managed engines wrap it (Aurora's session-consistency modes, Vitess's bounded staleness). - Pros: exact — no guessing at a lag window; keeps reads on replicas whenever they are fresh enough. - Cons: real machinery in the data-access layer; the token must survive across services if the read happens elsewhere; waiting can add latency, so you need a timeout that falls back to the primary. ## Fix 3 — classify the endpoint Many read-after-write problems are concentrated in a handful of screens: "my profile", "my order just placed", checkout. Marking those code paths as primary-reads is often the entire fix and costs nothing conceptually. The discipline is to make read/write routing an explicit, reviewed property of each use case rather than an automatic guess by a proxy. ## Fix 4 — reduce lag itself Worth doing regardless: - Break up giant transactions and bulk operations on the primary; replay is applied as a unit and a 10-minute transaction is a 10-minute lag spike. - Give replicas at least the primary's IOPS; replay is often IO-bound. - Use multi-threaded/parallel apply where the engine supports it (MySQL parallel replication workers). - Watch for replay conflicts with long reader queries on the replica (PostgreSQL will cancel queries or, with `hot_standby_feedback` on, hold back vacuum on the primary and cause bloat — a tradeoff to make deliberately). - Alert on lag and evict lagging replicas from the read pool automatically. ## Fix 5 — synchronous replication Make the primary wait for a replica to acknowledge (and, depending on the mode, to apply) before commit returns. `synchronous_commit = remote_apply` in PostgreSQL gives real read-your-writes on that standby. The price is that every commit pays a network round trip, and a slow or dead sync replica stalls writes unless a quorum/fallback is configured. Usually reserved for durability, not for consistency of read scaling. ## What not to say "Just cache the value in the UI." Echoing the submitted form data hides the symptom on one screen while the underlying read path stays wrong. "Add more replicas" makes lag worse, not better. "Use synchronous replication everywhere" trades a UX bug for a latency and availability problem.

  • How do you choose the length of the stick-to-primary window?
    From the measured lag distribution, not a guess: pick a value above the p99 lag under normal load, commonly 2–5 seconds, and alert when actual lag exceeds it. Too short reintroduces stale reads under load spikes; too long pushes avoidable read traffic onto the primary. The more robust variant is to stop guessing and compare the session's captured log position against each replica's applied position.
  • What causes replica lag to spike, and which of those can you control from the application?
    Common causes are long or very large transactions on the primary (replayed as one chunk), limited replay parallelism, replica disk IO or CPU saturation, network throughput, and on PostgreSQL, replay conflicts with long-running queries on the standby. From the application side the controllable ones are transaction size and duration — batching a million-row update into chunks, and keeping transactions short — plus scheduling bulk jobs off-peak.
  • Two consecutive reads show data appearing and then disappearing, with no write in between. What is going on?
    The reads landed on different replicas with different lag, so the second one is further behind than the first — a monotonic-reads violation. The fix is to route a given session consistently to the same replica (session affinity), or to use applied-position comparison so a session never reads from a replica behind the position it has already observed.

saying these in an interview costs you the question

  • Blaming a caching bug or a failed write instead of recognising replication lag.
  • Claiming replicas are 'usually fast enough' and dismissing the guarantee.
  • Proposing to echo the submitted values in the UI as the fix.
  • Suggesting more replicas as a cure for lag.
  • Not knowing that consistent routing per session also matters (monotonic reads), not just after writes.

context

resolving links…