Replication Lag & Read-Your-Writes.

🧠Start with the analogy

A read replica is a live stream of a football match. The stadium (primary) is the truth; the stream is the same match, faithfully, ~8 seconds late. You add replicas to get more screens without crowding the stadium.

That analogy gets you 80% of the way — and the last 20% is where production breaks.

🕳️Where the analogy breaks (this is the intermediate part)

1. Stream delay is roughly constant. Replication lag is not.

It sits at 5ms for weeks, then someone runs a 5M-row backfill in one transaction and the replica is 4 minutes behind. Lag is bursty and effectively unbounded. Never design as if it has a ceiling.

2. There is no single "the stream". There are N of them, each at a different delay.

Replica A: 20ms. Replica B: 90s. Your load balancer round-robins. So two consecutive reads in the same request can go BACKWARDS in time — a list shows a row, the detail fetch 404s it.

3. Lag is not load. Classic MySQL applies the relay log on limited threads; the replica can be at 10% CPU and still fall further behind every second. "Add CPU" doesn't fix it.

🔍The symptom you've already shipped

User edits their name → PUT returns 200 → UI refetches → old name. Support calls it "the save button doesn't work." It worked. The read went to a replica.

📏Measure it before you argue about it

Postgres:

SELECT now() - pg_last_xact_replay_timestamp() AS lag;

MySQL:

SHOW REPLICA STATUS\G   -- Seconds_Behind_Source

🛠️Fixes, weakest to strongest

• Sticky-to-primary window: after a write, pin that user's reads to the primary for ~5s (session flag / cookie). One line, kills 95% of reports. It's a guess with a timer.
• Token / LSN read: at write time capture the position

  SELECT pg_current_wal_lsn();
stash it in the session, and on read only use a replica whose pg_last_wal_replay_lsn() >= that token, else fall back to primary. Deterministic — no magic number.

• Classify reads, don't route by default. Any read that FEEDS A WRITE DECISION goes to the primary: balance checks, "do I own this row", uniqueness checks, quota counters. A stale read is an inconsistency; a stale read you then write from is corruption.
• Eject lagging replicas. Health check on lag, not just on "does it answer". > 10s, pull it out of the pool.

⚠️The one that bites hardest

"Eventually consistent" is a fine property for a dashboard, a feed, a search page. It is never fine for the read immediately following that same user's write — because that user knows the truth already, and your app is now arguing with them.

🎯Your exercise

Open your app and list every read that can happen within 2 seconds of a write by the same user (post-save refetch, redirect-then-load, optimistic UI reconcile). For each: does it hit a replica? Pick the worst one and fix it with the LSN-token approach rather than a sleep or a timer.

Bonus: what breaks if you set the sticky window to 5s and a replica lags 30s?