# DDIA Ch. 6: your own write, gone from the replica ## Concept Write to the leader, then read from a follower — the classic scale-out pattern, since data is read far more than written. But followers replicate *asynchronously*, so for a moment the follower hasn't seen your write yet. Read it in that window and your own submission looks **lost** — the comment you just posted isn't there. You will write to a real Postgres primary, immediately read a real streaming replica, watch your write come back as **count 0**, and see it reappear only after the replica catches up ~5 seconds later. Then you close the anomaly by routing reads-after-writes back to the leader. ## Provenance *Designing Data-Intensive Applications*, 2nd ed. (Kleppmann & Riccomini, 2025), Chapter 6 — *Replication*, §"Reading your own writes": > if the user views the data shortly after making a write, the new data may not > yet have reached the replica. To the user, it looks as though the data they > submitted was lost. The book names the anomaly and the guarantee that fixes it (read-your-writes / read-after-write consistency). Here you reproduce it on real streaming replication and measure the window. ## Prerequisites The surprise is about the gap between a leader's commit and a follower's apply. These help; each line notes where it shows up. 1. **Leader-follower (single-leader) replication** — writes go to one leader; followers copy its change log and serve reads. Reading a follower offloads the leader — the whole point. 2. **Asynchronous replication** — the leader commits and replies to the client *without* waiting for followers, so a follower is always slightly behind. That "behind" is the whole exercise. 3. **Replication lag** — the delay between a leader commit and a given follower reflecting it. Here we *set* it to 5 s (`recovery_min_apply_delay`) so it's large and obvious instead of a sub-millisecond race. 4. **Read-your-writes consistency** — the guarantee that a user always sees their *own* recent writes (others' can lag). The fix in Step 3. ### Where to learn them - **The anomaly and its fixes (#1–#4):** DDIA §"Reading your own writes" and §"Solutions for Replication Lag". - **How to make it real (#3):** the PostgreSQL manual on streaming replication, `pg_stat_replication`, and `recovery_min_apply_delay`. If only one is new, make it #2 — async replication is why the window exists at all. ## Environment The anomaly (immediate replica read misses the write) is 100% reproducible; the catch-up is ~5 s by construction: - macOS 26.5.2 (Darwin 25.5.0), arm64 - Docker 29.6.2, official `postgres:16` (PostgreSQL 16.14): one primary + one async standby streaming over a Docker network, standby lagged 5 s via `recovery_min_apply_delay` ## Mental model `setup_replication.sh` stands up a **primary** (leader) and a **standby** (follower) with real Postgres streaming replication. The standby is deliberately held 5 seconds behind (`recovery_min_apply_delay = 5s`) so the lag is easy to see. You write a comment to the primary and immediately read it back from *both* nodes; the follower hasn't applied it yet. The setup is scripted (it's just plumbing); the writes and reads in the Steps you run by hand. ## Setup ``` cd study/ddia/ch06/code bash setup_replication.sh # ~30 s: primary + a fresh and a 5s-lagged standby # handles for the two nodes: # primary (leader): docker exec ddia-ch6-primary psql -U postgres -d study # standby (5s follower): docker exec ddia-ch6-standby psql -U postgres -d study ``` Do **not** read the output yet — make each prediction first. ## Steps ### Step 1 — write to the leader, read both (the surprise) **Predict.** Insert a comment on the primary, then *immediately* run the same `SELECT count(*) WHERE id = 1` on the primary and on the standby. What does each return? ``` # on the PRIMARY (leader): docker exec ddia-ch6-primary psql -U postgres -d study -c \ "INSERT INTO comments VALUES (1, 'my first comment');" # then immediately, on the PRIMARY and the STANDBY: docker exec ddia-ch6-primary psql -U postgres -d study -c "SELECT count(*) FROM comments WHERE id=1;" docker exec ddia-ch6-standby psql -U postgres -d study -c "SELECT count(*) FROM comments WHERE id=1;" ``` **Observe.** ``` PRIMARY SELECT count(*) WHERE id=1 -> 1 (your write is here) STANDBY SELECT count(*) WHERE id=1 -> 0 (your write is MISSING -- looks lost) ``` **The leader has it; the follower returns 0.** To a user who just posted the comment and gets routed to a follower for the read, it looks as though their submission vanished — the exact "understandably unhappy" moment the book describes. ### Step 2 — measure the window **Predict.** How long until the standby reflects the write? (We set the standby's apply delay to 5 s.) ``` # poll the standby until the row appears until docker exec ddia-ch6-standby psql -U postgres -d study -tAc \ "SELECT count(*) FROM comments WHERE id=1;" | grep -q 1; do sleep 0.2; done docker exec ddia-ch6-standby psql -U postgres -d study -c "SELECT body FROM comments WHERE id=1;" ``` **Observe.** ``` --- after ~5s the standby catches up --- STANDBY SELECT body WHERE id=1 -> my first comment ``` **~5 seconds.** The write was never lost — it just hadn't been *applied* on the follower yet. Real replicas lag by microseconds to seconds under load; we made it 5 s so you can see the window, but the anomaly is the same at any lag > 0. ### Step 3 — the fix: read your own writes from the leader **Predict.** How do you guarantee a user always sees their *own* recent write? **Observe.** Route reads that might depend on a user's own recent write to the **leader** (or to a follower known to be caught up past that write's position): ``` docker exec ddia-ch6-primary psql -U postgres -d study -c "SELECT body FROM comments WHERE id=1;" # -> my first comment (the leader always has it) ``` Read-your-writes consistency is exactly this: the write's author reads from a source guaranteed to include it. Everyone *else* can keep reading the cheap, slightly-stale followers. ## What you should see - Immediately after the write: **primary = 1, standby = 0** (your own write missing from the follower). - The standby reflects it after **~5 s** (the configured apply delay); the write was never lost. - Reading the **leader** after a write always shows it — the read-your-writes fix. ## Why Single-leader replication scales reads by letting many followers serve queries, but it keeps writes correct by funneling them through one leader. To keep the leader fast, replication is *asynchronous*: the leader appends the change to its write-ahead log, commits, and answers the client immediately, then streams the log to followers, which apply it a little later. That "a little later" is replication lag, and during it a follower's state is an *earlier* version of the database. A read routed there sees the past — including, briefly, a past in which your just-submitted write doesn't exist yet. Nothing is broken and nothing is lost; you simply read a replica before it caught up. We forced the gap to 5 s with `recovery_min_apply_delay`, but under normal load it's just small enough to slip through in real traffic and confuse exactly the user who wrote the data. The fix follows from naming the guarantee you actually need. You don't need every follower to be current — that would throw away the whole benefit of async replication. You need the narrow promise that *the author of a write sees their own write*: **read-your-writes consistency**. Implement it by routing reads-after-writes to the leader, or to a follower you've confirmed has replayed past the write's log position (Postgres exposes the position as an LSN), or by having the client remember the timestamp/LSN of its last write and only reading replicas that have reached it. Everyone else keeps hitting the cheap stale followers, which is fine — they never wrote the data, so they can't tell it's a few milliseconds old. **The boundary — lag is the price of async scale-out, not a bug.** Asynchronous followers exist *because* you want cheap reads and a fast leader; the lag is the cost you accepted for that. So the answer is rarely "make it synchronous" (that reintroduces the latency and availability cost you were avoiding) but "pick the consistency you actually need and route accordingly." Read-your-writes is one such targeted guarantee; the next exercise (monotonic reads) is another, for a *different* anomaly the same lag produces when a user's reads bounce between replicas. Synchronous replication is a valid choice for data where any staleness is unacceptable — but then you're back to waiting for a follower on every commit. ### Go deeper 1. **Read your writes by LSN.** After the insert, capture `pg_current_wal_lsn()` on the primary; poll the standby's `pg_last_wal_replay_lsn()` until it passes that LSN, then read the standby. Predict whether you can get a *fresh* read from the *follower* this way (you can) — the without-hitting-the-leader version of the fix. 2. **Sync vs async latency.** Add `synchronous_standby_names` so the primary waits for the standby to confirm each commit. Predict how much a single `INSERT`'s latency grows — the cost you pay to erase the lag window. 3. **Two windows.** Lower `recovery_min_apply_delay` to `500ms` and re-run. The anomaly still happens, just harder to catch by hand — which is exactly why it slips into production unnoticed.