Pattern — Read-Replica Lag Handling

The problem it solves

The clean fix for Drill 79 — one unindexed report holding all twenty primary connections while payments time out — is to move the heavy reads off the primary entirely, onto a read replica. But a replica is asynchronous: it applies the primary's writes seconds behind, so every read you offload can now return stale data. The report from the replica can miss the payment that landed two seconds ago; worse, if you also route balance lookups there to scale, a customer who just paid refreshes and sees the old balance — a read-your-writes violation that reads as a bug, a double-payment, or a support ticket. Offloading reads without handling lag trades a pool-exhaustion outage for a silent-correctness outage.

The pattern

Read-replica lag handling makes replica offload safe by treating replication lag as a first-class, measured quantity and routing each read by the freshness it actually needs. Three moves together: measure lag continuously and evict any replica that falls too far behind; classify each read by how stale it may legally be (a report tolerates 30s; a just-written balance tolerates 0); and give the reads that need it read-your-writes consistency — pin them to the primary, or to a replica proven caught up past the write — so the reader never sees older data than it already wrote.

How to apply it

Measure lag as an SLI, in real units, per replica. In PostgreSQL, now() - pg_last_xact_replay_timestamp() gives seconds behind; on Aurora/RDS use the AuroraReplicaLag / ReplicaLag CloudWatch metric (milliseconds). Set thresholds: warn at 1s, page at 5s, and evict the replica from the read pool at 10s so a badly-lagging replica stops serving stale reads instead of quietly poisoning them.
Classify every read by its staleness budget. Write a two-column contract: reporting / analytics → 30s stale OK → replica; list & search → 5s stale OK → replica; balance & post-write reads → 0s → read-your-writes. The budget, not the endpoint's name, decides the route.
Give the zero-budget reads read-your-writes. The cheap version: after any write, set a sticky-primary flag keyed on user_id for a 5s window (a little above measured p99 lag) so that user's next reads go to the primary. The precise version: capture the write's log position — pg_current_wal_lsn() (Postgres) or the returned GTID (MySQL) — as a consistency token, and only let a replica serve the read if its applied position has caught up (pg_last_wal_replay_lsn() >= token, or SELECT WAIT_FOR_EXECUTED_GTID_SET(gtid, 0.2)); otherwise wait up to ~200ms or fall back to the primary.
Route on live lag, not static config. Put a lag-aware proxy in front — Aurora reader endpoint, RDS Proxy, ProxySQL/PgBouncer query rules — that health-checks replica lag every few seconds and pulls a replica out of rotation the moment it crosses the eviction threshold, so routing tracks reality.

flowchart TD
    A["Read request"] --> B{"Staleness budget?"}
    B -->|"0s (read-your-writes)"| C{"Wrote in last 5s<br/>OR token not applied?"}
    C -->|"yes"| D["Primary (fresh)"]
    C -->|"no"| E["Replica"]
    B -->|"5-30s OK"| F{"Replica lag < threshold?"}
    F -->|"lag < 10s"| E
    F -->|"lag >= 10s (evicted)"| D
    E --> G["Scaled reads, primary protected"]
    D --> G

When to use it — and when not

Reach for it whenever you offload reads to async replicas to protect a primary (Drill 79) or to scale read throughput, and some of those reads must not go backward in time. The cost is real: a sticky-primary window and consistency-token checks claw read traffic back onto the primary — the very load you were shedding — so if most reads need read-your-writes, replicas buy you little and you should scale the primary or shard instead. Skip the machinery entirely for reads that are already lag-tolerant (dashboards, analytics, search over yesterday's data) — just route them to the replica with a generous staleness budget. And don't reach for async replicas at all when you need strong, always-fresh reads globally: that calls for synchronous replication or a consensus store (Spanner-style), where you pay latency for freshness rather than managing lag.

Signals it's working

The lag SLI is graphed per replica with the 1s/5s/10s lines drawn on it; eviction events fire and clear on their own, and stale replicas leave the pool automatically.
Read-your-writes violations = 0 on a canary that writes then immediately reads its own value; stale-read fallbacks to primary are counted separately and stay a small, bounded fraction (a rising fraction means lag is creeping and the primary is re-absorbing load).
Primary read-pool utilization drops and stays down after offload — the whole point of Drill 79 — while p99 on the offloaded reads holds its budget.
A game-day that injects 8s of replication lag shows: zero-budget reads correctly fall back to the primary, the lagging replica is evicted at 10s, and no user sees a value older than one they wrote.

Seen in the wild

Amazon Aurora exposes AuroraReplicaLag (ms) and a aurora_replica_read_consistency = SESSION mode; RDS Proxy and the reader endpoint route around lagging replicas.
MySQL GTID-based read-your-writes via WAIT_FOR_EXECUTED_GTID_SET; PostgreSQL exposes pg_last_wal_replay_lsn() / pg_last_xact_replay_timestamp() for LSN-token and time-lag routing.
Vitess, ProxySQL, and PlanetScale offer lag-aware / GTID-consistent read routing; DynamoDB makes the same tradeoff explicit as eventually-consistent vs. strongly-consistent reads.

See it

The ChainScene carries the routing decision — measure lag → classify by staleness budget → read-your-writes reads pin to the primary, lag-tolerant reads hit the replica → stale reads = 0; the CardsScene shows the load-bearing numbers (warn 1s / page 5s / evict 10s, a 5s sticky-primary window, a ~200ms token wait).

Named concepts: read-your-writes consistency, replica lag monitoring, lag-aware routing, bounded staleness