Database Replica Lag and Stale Reads

Your API returns a 200, the user sees success, they refresh, and the data is gone. Or worse: they see someone else's stale dashboard for thirty seconds. Replica lag is not a DBA-only problem. It is an application correctness problem that shows up when read traffic hits replicas and write traffic hits the primary.

At Edmingle we run MySQL with read replicas for reporting and read-heavy endpoints. Lag is usually sub-second, but during bulk imports, schema migrations, or replica maintenance it spikes. Applications that assume 'read after write' consistency without routing logic will lie to users quietly.

How Replica Lag Happens

MySQL async replication applies the primary's binlog on replicas with some delay. That delay is normal. It grows when the primary writes faster than the replica can apply, when network blips occur, when a replica catches up after downtime, or when long-running queries block the replication SQL thread.

Primary (writes)                Replica (reads)
──────────────                 ────────────────
INSERT user ──► binlog ──────► relay log ──► apply (lag: 0.5s-30s+)
UPDATE profile                     │
SELECT * FROM users ◄──────────────┘ (may miss the INSERT)

The lag you measure on the replica (Seconds_Behind_Master or replica lag metrics in RDS/CloudWatch) is an average indicator, not a guarantee for every transaction.

Stale Read Failure Modes

• Read-after-write: user creates a record, UI fetches list from replica, record missing.
• Session consistency: user updates profile, next page load shows old avatar URL.
• Aggregate staleness: dashboard counts lag behind real-time primary data.
• Cross-service inconsistency: event published from primary, consumer reads replica, acts on outdated state.

These bugs are intermittent and environment-specific. They reproduce badly in dev where lag is zero and traffic is tiny.

Detecting Lag in Production

Monitor replication lag as a first-class metric, not an afterthought.

-- On MySQL 8+ replica
SHOW REPLICA STATUS\G
-- Watch: Seconds_Behind_Source, Replica_SQL_Running_State

Alert when lag exceeds SLO thresholds (we use p95 lag > 5s for five minutes as a warning, > 30s as critical). Correlate lag spikes with batch jobs, migrations, and deploys.

Add application-level hints: log which DB host served a read when debugging user reports. A trace attribute db.role= replica vs primary saves hours.

Routing Strategies That Work

The pattern we use most: writes go to primary; reads go to replica unless the request is within N seconds of a write for that user, in which case route to primary. Implement via a lightweight 'recently written' flag in Redis keyed by user ID with TTL of 2-5 seconds.

async function getUser(userId, { afterWrite = false } = {}) {
  const pool = afterWrite || await wasRecentlyWritten(userId)
    ? primaryPool
    : replicaPool;
  return pool.query('SELECT * FROM users WHERE id = ?', [userId]);
}

Schema Migrations and Bulk Jobs

The worst lag spikes I have seen came from well-intentioned batch updates on the primary during business hours. Replicas fall behind, every read path that uses them becomes wrong simultaneously.

Run heavy backfills in chunks with throttling. Use gh-ost or pt-online-schema-change for large DDL. Pause or redirect critical reads to primary during known lag events and communicate in status page if user-visible.

For reporting replicas dedicated to analytics, isolate them from user-facing read pools entirely. Let them lag minutes without affecting product UX.

Engineering Takeaways

• Async replication means reads on replicas are eventually consistent by definition-not a bug until you pretend otherwise.
• Read-after-write bugs are correctness bugs; route recent writes to primary or use a short TTL stickiness cache.
• Monitor lag continuously and alert before users notice; correlate spikes with batch jobs and migrations.
• Separate analytics replicas from user-facing read pools so report lag does not become product lag.
• Log and trace which DB role served each request; stale read incidents are impossible to debug without it.
• Design UX for eventual consistency where business allows; do not over-engineer strong consistency on every endpoint.

Trade-offs I Would Consider

Option: Route all reads to primary

• Pros: Strong consistency, simplest mental model, no stale read bugs.
• Cons: Primary becomes bottleneck, defeats purpose of replicas, higher cost at scale.

Option: Replica reads with post-write primary routing

• Pros: Scales read load, fixes 95% of user-visible stale reads with minimal complexity.
• Cons: Edge cases during lag spikes, requires Redis or session hint, slightly more application code.

What I Would Do in Production

Define per-endpoint consistency requirements in your API design doc: 'strong' vs ' eventual'. Code reviews check routing matches.

Implement wasRecentlyWritten(userId) with Redis TTL after every mutating operation affecting that user's reads.

Dashboard replication lag on the ops wall; page on sustained high lag before support tickets arrive.

Never run unbounded UPDATE on primary during peak; batch with LIMIT and sleep between chunks.

Load-test with artificial replica lag in staging (iptables delay or proxy throttle) to verify routing logic.

FAQ

Q: Is semi-sync replication enough to fix this?

Semi-sync reduces data loss risk on primary failure; it does not eliminate read lag on replicas. Readers still see delayed state unless you route carefully.

Q: What lag is acceptable?

Depends on product. Sub-second for user-facing replica reads is a reasonable target. Analytics can tolerate minutes if isolated.

Q: Does RDS Read Replica lag behave differently?

Same fundamentals. Use ReplicaLag CloudWatch metric. Cross-AZ adds small baseline latency.

Q: Can I use GTID to read my own writes?

MySQL does not offer automatic read-your-writes routing by GTID in app drivers. You implement session stickiness or primary routing in application layer.

Q: What about Galera or InnoDB Cluster?

Synchronous or group replication changes failure modes but read scaling still often uses async replicas or read routing with similar concerns.