Instead of pinning a user to the primary for a fixed window after they write, how can an application make a replica read wait until that replica has applied that specific write, using a replication position such as a PostgreSQL LSN or a MySQL GTID?
answer
- commit has an address: LSN / GTID
- capture on write, carry in session or message
- compare replay position, or wait with timeout
- fallback to primary, never unbounded wait
- applied position, not received bytes
basics
~20 sAfter committing, read the write's position in the change stream (an LSN or GTID) and carry it with the session or request. Before serving a read, compare it with the replica's applied position: if the replica is behind, wait briefly for it to catch up, or fall back to the primary.
solid answer
~60 sEvery commit has an address in the primary's change stream: a log sequence number in PostgreSQL, a transaction identifier in a GTID set in MySQL. After the write, capture that position and store it against the session (or pass it in the request, or in the queue message for cross-service work). On the read side you have two shapes. **Verify and route**: ask the replica for its applied position, and if it is at or beyond the token use it, otherwise use the primary. **Wait**: ask the replica to block until it has applied that position, with a timeout, and fall back to the primary if the timeout fires. MySQL exposes a wait primitive for a GTID set; PostgreSQL is usually done by comparing the replica's replay position with the token. Advantages over a sticky window: no guessed duration, traffic returns to replicas as soon as they are caught up, and it works across services and queue hops. Costs: threading the token everywhere, a timeout policy, and a token that must be treated as opaque and expire with the session.
code
sql · 5 lines-- on the primary, right after COMMIT
SELECT pg_current_wal_lsn() AS token;
-- on a candidate replica, before serving the read
SELECT pg_last_wal_replay_lsn() >= '0/16B3748'::pg_lsn AS caught_up;go deeper
Enough to know that a commit has a position and that a read can check whether the replica has reached it; the sticky-primary window is the simpler fix you would reach for first.
Describe capturing the LSN or GTID after commit, comparing it with the replica's replay position, and falling back to the primary.
Own the failure modes: bounded waits, fallback load on the primary during a lag event, applied versus received, and threading the token through queue messages for cross-service causality.
Decide where the mechanism lives (router or application), which read paths opt in, and how it interacts with the choice to keep replication asynchronous rather than paying apply latency on every commit.
## The idea A sticky window (send this user to the primary for N seconds after a write) is a guess about lag. The precise alternative is to make the read wait for the exact write it depends on. This works because a commit has a concrete address in the replication stream, and a replica can report how far it has applied. ## Getting the token PostgreSQL numbers every byte of its write-ahead log; a position is an LSN like 0/16B3748. After committing you can read the primary's current write position, or the position of your own last commit on that connection. MySQL identifies each transaction with a GTID (source UUID plus sequence number); a session can report the GTIDs it just committed. Either way you end up with a small opaque string: the high-water mark my session depends on. Store it where the next read can find it: the session record, a signed cookie, a request header propagated between services, or a field on the queue message for asynchronous consumers. Treat it as opaque, and scope it: a token from an hour ago is useless and only causes needless primary reads, so let it expire. ## Using the token on the read side Verify and route. Ask the candidate replica for its replay position and compare. If the replica is at or past the token, the write is visible there, so read it. Otherwise use the primary, or try another replica. This adds a cheap query, which you can amortise by having a background health checker sample each replica's position every few hundred milliseconds and keeping the values in the router. Wait on the replica. MySQL provides a blocking primitive that returns once the replica has executed a given GTID set, with a timeout. In PostgreSQL the same effect is normally built by polling the replay LSN in the router rather than by blocking inside the database. Either way, the wait must be bounded: if the replica cannot catch up within a few tens of milliseconds, the correct action is to serve the read from the primary, not to hold the user's request hostage to replication. ## Why this beats a window - No arbitrary constant. Under healthy conditions the replica is already past the token, so reads go to replicas immediately after the write instead of being pinned for the whole window. - It degrades meaningfully. Under a lag spike, traffic shifts to the primary exactly for the sessions that need it, instead of everyone silently getting stale reads once the window expires. - It crosses process boundaries. A queue consumer that receives the token can wait for the same position, which fixes the message overtook replication class of bugs that a session-scoped cookie cannot touch. ## Costs and failure modes Plumbing: the token has to be captured on every write path and threaded through to every read path; miss one and the guarantee is silently partial. Fallback pressure: during a real lag event, every session with a fresh token piles onto the primary, so the primary must have headroom, or you need a load-shedding rule. Waiting inside the database consumes a connection while it blocks, which is dangerous with a small pool. Comparing positions is exact, but only if you compare applied (replayed) position, not received position; a replica can hold the bytes and not yet have made them visible. Also beware token leakage between users: a token is a global high-water mark, so sharing one user's token with another simply makes the second user wait for a write they do not care about. Harmless for correctness, wasteful for latency. ## Where it fits This is the strongest per-read fix that keeps replication asynchronous. The alternative, making commits wait for replica apply, gives every reader the guarantee but pushes replica latency into every write. Token waiting keeps the cost proportional to the sessions that actually need freshness. ## How to present it Say the two words that matter (LSN, GTID), describe capture, propagation, and the compare-or-wait step, insist on a bounded timeout with a primary fallback, and mention the applied-versus-received distinction and the plumbing cost.
- What should happen if the replica does not reach the position within the timeout?Serve the read from the primary and record the event. Holding the request until replication catches up converts a lag problem into a latency and thread-pool problem, and an unbounded wait during a real lag spike will hang the application. The timeout should be short, on the order of tens of milliseconds for interactive paths.
- Why must you compare the replica's applied position rather than what it has received?Received means the bytes are on the replica's disk or memory; applied (replayed) means the change is visible to queries there. A replica can have received a change but not yet replayed it, for example while apply is paused behind a conflicting long-running query, so a received-based check would route a read to a node that still shows the old value.
saying these in an interview costs you the question
- Waiting on the replica with no timeout and no fallback to the primary
- Comparing received or flushed position instead of applied/replay position
- Storing the token forever, so sessions read the primary long after it matters
- Assuming the token is a timestamp and doing arithmetic on it instead of treating it as opaque and ordered
- Claiming this removes replication lag rather than making specific reads wait for a specific write