Read Replicas and Replication Lag

Scenario

Example app: a marketplace sends all GET requests to two read replicas to take load off the primary. Replication is asynchronous; lag is usually under 100 ms.

What you see in production

• Sellers edit a listing, click Save, land on the listing page — and see the old price. Refreshing a second later fixes it.
• During a nightly batch import, lag reaches 40 seconds; "my order disappeared" tickets spike.
• Once a replica fell hours behind after a disk issue, and reads silently served day-old data.

The naive code

Route by HTTP method, ignore lag:

class PrimaryReplicaRouter:
    def db_for_read(self, model, **hints):
        return random.choice(["replica1", "replica2"])     # every read, regardless of lag
    def db_for_write(self, model, **hints):
        return "default"

'pgsql' => [
    'read' => ['host' => ['replica1', 'replica2']],
    'write' => ['host' => ['primary']],
    'sticky' => false,                 // reads right after a write may go to a lagging replica
],

// AbstractRoutingDataSource: readOnly transactions → replica, everything else → primary
@Transactional(readOnly = true)
public Listing show(long id) { return listings.findById(id).orElseThrow(); }   // may be stale after an edit

Reproduce it

A primary and a replica that applies changes 300 ms later:

primary, replica, replication_queue = {"price": 100}, {"price": 100}, []
LAG_MS = 300

def write(key, value, now):
    primary[key] = value
    replication_queue.append((now + LAG_MS, key, value))

def replicate(now):
    for item in [i for i in replication_queue if i[0] <= now]:
        replica[item[1]] = item[2]
        replication_queue.remove(item)

write("price", 89, now=0)                  # seller saves
for t in (50, 200, 350):
    replicate(t)
    print(f"t={t:3} ms  primary={primary['price']}  replica={replica['price']}"
          f"{'  <- stale read' if replica['price'] != primary['price'] else ''}")

t= 50 ms  primary=89  replica=100  <- stale read
t=200 ms  primary=89  replica=100  <- stale read
t=350 ms  primary=89  replica=89

Root cause

Asynchronous replication ships changes after commit; replicas are always slightly behind, and lag grows with write bursts, long-running queries on the replica (which can delay replay), network issues and replica restarts. Any read that must reflect a recent write — the user's own, or a business decision — can be wrong on a replica.

A real-world reminder that replication is an operational system, not just a setting: GitLab's January 2017 outage began with a PostgreSQL secondary falling behind under load and needing a manual re-sync; during that work, data was accidentally removed from the primary and roughly six hours of data were lost (GitLab postmortem).

The fix

1. Decide per query whether staleness is acceptable: catalog browsing yes; "my orders", balances, checkout no.
2. Read-your-writes for the writer's own data (next lesson).
3. Watch lag and stop using replicas that are too far behind:

-- on the primary: lag per replica
SELECT application_name, replay_lag, pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS bytes_behind
FROM pg_stat_replication;
-- on a replica: time since the last replayed transaction
SELECT now() - pg_last_xact_replay_timestamp() AS lag;

SHOW REPLICA STATUS\G      -- Seconds_Behind_Source (8.0.22+; SHOW SLAVE STATUS before)

class LagAwareRouter:
    def db_for_read(self, model, **hints):
        healthy = [r for r in ("replica1", "replica2") if lag_seconds(r) < 2]   # cached by a health checker
        return random.choice(healthy) if healthy else "default"                # fall back to primary

'pgsql' => [
    'read' => ['host' => ['replica1', 'replica2']],
    'write' => ['host' => ['primary']],
    'sticky' => true,   // after a write in this request, later reads in the same request use the primary
],

Trade-offs

• Replicas scale reads, not writes; every replica replays every write.
• Falling back to the primary when replicas lag protects correctness but can overload the primary — cap it and shed non-essential reads.
• Synchronous replication removes lag for committed data but adds write latency and availability risk; most systems use it only for a failover standby.
• Long queries on a Postgres replica can be cancelled by replay conflicts (or delay replay with hot_standby_feedback, which bloats the primary) — run analytics on a dedicated replica.

Detect it in production

• Graph replica lag per replica; alert at your staleness budget (e.g. > 5 s) and on replication stopping.
• Track the share of reads served by the primary vs replicas.
• Tickets like "saved but not showing" cluster around lag spikes — correlate them.

Key points

• Async replicas are always behind; lag spikes under write load.
• Classify reads by freshness need; route critical ones to the primary or use read-your-writes.
• Monitor lag and drain lagging replicas automatically.