Your routing layer sends writes to the primary and SELECTs to read replicas. What correctness problems does that split introduce, and how do you deal with them?
answer
- Lag breaks read-your-writes and monotonic reads
- In a transaction → always primary
- SELECT ... FOR UPDATE is a write
- Sticky-to-primary window after a write
- LSN/GTID-aware routing = real freshness guarantee
basics
~20 sReplicas lag, so a read right after a write may not see it, and successive reads can go backwards in time. Fixes: keep reads inside a transaction on the primary, pin a session to the primary for a short window after it writes, or route only when the replica has caught up to the write's log position.
solid answer
~60 sReplicas are behind the primary by anything from microseconds to minutes, which breaks two expectations applications silently rely on: **read-your-writes** (a user saves a profile, is redirected, and sees the old value) and **monotonic reads** (two reads hitting differently-lagged replicas appear to move backwards). The split also breaks non-obvious cases: anything inside a read-write transaction must stay on the primary, a `SELECT ... FOR UPDATE` is a write in disguise, and session state — temporary tables, session variables, advisory locks, prepared statements — does not exist on the node you were not routed to. Remedies, roughly in order of robustness: route all statements in an open transaction to the primary; after a write, pin that session or user to the primary for a lag-sized window; make routing lag-aware by recording the write's log position (LSN/GTID) and only using a replica that has applied it; eject replicas whose lag exceeds a threshold. Best of all, let the application declare per query whether a stale read is acceptable, rather than having a proxy guess from SQL text.
go deeper
Know that replicas lag, that a read straight after a write may miss it, and that reads inside a transaction should go to the primary.
Name read-your-writes and monotonic reads as the broken guarantees, and describe sticky-to-primary and lag thresholds as fixes.
Add position-aware routing with LSN/GTID, session-state and locking-read traps, read-only enforcement on replicas, and lag as a correctness signal to monitor.
Argue that freshness is an application-level contract: have callers declare it per query, size the primary for the total-replica-loss case, and treat replica ejection cascades as a designed-for failure mode.
## Why splitting reads is tempting and dangerous Moving SELECTs to replicas adds read capacity without sharding. The catch is that replication is asynchronous in the common case, so a replica is a *past* version of the database. Every guarantee an application unconsciously assumed on a single node must now be re-established. ## The two anomalies interviewers want named **Read-your-writes violation.** A user updates a record, the response redirects to a page that reads it, the read lands on a replica that has not applied the change yet, and the user sees their edit vanish. They retry, doubling the writes. This is the single most common production bug of read/write splitting. **Non-monotonic reads.** Two consecutive reads go to replicas with different lag. The second returns older data than the first, so lists lose rows, counters decrease, and pagination skips or duplicates. Sticking a session to one replica for its lifetime fixes this cheaply. ## The routing traps - **Transactions.** Once a transaction is open and has written, every subsequent statement in it — including SELECTs — must go to the same primary connection. A proxy routing statement-by-statement will otherwise tear a transaction across nodes. - **Locking reads.** `SELECT ... FOR UPDATE` / `FOR SHARE` take row locks and are only meaningful on the primary; text-based routing that sees "SELECT" and picks a replica will either error (read-only node) or, worse, take a meaningless lock. - **Session state.** Temporary tables, session variables, advisory locks, `SET` statements, and server-side prepared statements exist on one connection to one node. Splitting statements across nodes loses them, and a pooling proxy that multiplexes connections can lose them even without a split. - **Functions with side effects.** A SELECT calling a function that writes is a write. - **Replica read-only enforcement.** Replicas should reject writes outright (`read_only`/`super_read_only`, or physical standby semantics). A misroute then fails loudly instead of diverging data — an error is much better than a silent split-brain write. ## Techniques, weakest to strongest 1. **Transaction-scoped routing.** Anything inside an explicit transaction, or after the first write in it, goes to the primary. Cheap, and eliminates a whole class of bugs. 2. **Primary stickiness after write.** Once a session (or a user) performs a write, route its reads to the primary for a window at least as long as observed lag. Simple, effective, and it costs primary capacity exactly when correctness demands it. 3. **Lag thresholds.** The router measures each replica's lag and removes any replica beyond a bound from the pool. This bounds staleness but does not guarantee read-your-writes for any specific write. 4. **Position-aware routing (the strong version).** After a write, the client keeps the resulting log position — a PostgreSQL LSN, a MySQL GTID set. A read that must be fresh is sent only to a replica that has already applied that position, or waits briefly for one to catch up. This gives genuine read-your-writes without pinning everything to the primary. It requires client cooperation or a proxy that can track it, and a fallback to the primary when nobody catches up in time. 5. **Application-declared freshness.** The application marks each read as "must be current" or "stale is fine", by API, hint or annotation. This is the most correct approach because only the application knows the semantics; parsing SQL to guess is inherently fragile. ## Operational notes Monitor lag as a first-class signal, because it is now a correctness input and not just a health metric. Watch for the feedback loop where a slow replica is ejected, its traffic lands on the remaining replicas, and they fall behind too — a cascade that ends with everything on the primary. Size the primary so it can absorb all read traffic during such an event, or shed load deliberately instead. And note that read splitting changes failure behaviour: a replica outage now degrades a subset of reads rather than nothing at all.
- A proxy decides routing by inspecting SQL text and sending anything starting with SELECT to a replica. What goes wrong?Text inspection cannot see semantics. `SELECT ... FOR UPDATE` takes locks and belongs on the primary; a SELECT calling a function that writes will fail on a read-only replica; a SELECT inside an already-writing transaction must stay on the primary connection; and CTEs containing data-modifying statements start with WITH, not SELECT. Routing should follow transaction state and explicit application intent, with SQL text as at most a hint.
- How does storing the log position of a write give read-your-writes without sending all reads to the primary?The write returns a monotonic position — an LSN in PostgreSQL, a GTID set in MySQL — that identifies the point in the replication stream containing that change. The client carries it (in a session, a cookie, or a request header), and a subsequent read is only routed to a replica whose applied position is at or beyond it, otherwise it waits briefly or falls back to the primary. Freshness is then guaranteed for the specific write that matters instead of being approximated by a time window.
saying these in an interview costs you the question
- Assuming replicas are 'basically instant' so lag can be ignored
- Routing statements inside an open transaction independently of each other
- Classifying SELECT ... FOR UPDATE as a read
- Fixing stale reads with a sleep before the read
- Leaving replicas writable so a misrouted write silently diverges