Replication & durability

Timeline (happy path, managed failover):

1. 1t=0 Leader crashes or becomes unreachable.
2. 2t=0 to t=5s Coordinator (Patroni, RDS, Aurora controller) detects missed heartbeats. Detection threshold is usually 3-5 seconds.
3. 3t=5s to t=15s Coordinator fences the old leader (prevent it from accepting writes if it comes back — STONITH or lease revocation) and elects a new leader from the sync replicas. The sync replica with the highest LSN (last committed log position) wins.
4. 4t=15s to t=25s Promote the chosen replica: replay any in-flight transactions, open for writes.
5. 5t=25s to t=30s Update the DNS record or connection endpoint. Clients reconnect.

Total RTO: ~20-30 seconds for a well-tuned setup.

What goes wrong:

1. 1Split-brain — old leader comes back before fencing completes, accepts writes. Now two leaders, diverging state. Fix: fencing must be synchronous before promotion, not parallel.

1. 1All sync replicas also down — you can't promote without either accepting data loss or waiting for a sync replica to return. The choice: downtime or RPO > 0. Document which your runbook prefers.

1. 1Promotion fails — the chosen replica can't replay its log (disk corruption, schema mismatch). Fall back to the next candidate. RTO balloons.

1. 1Clients cache old DNS — some clients cache DNS beyond the TTL. A 60-second TTL means a long tail of clients still hitting the dead leader for minutes. Use an endpoint with short TTL (30-60 s) or a connection manager that handles failover.

1. 1Connection pool thundering herd — 1000 app instances reconnect simultaneously on the new leader, overwhelming it. Stagger reconnects with jittered backoff; warm connection pools.

The production rule: rehearse failover monthly in a non-prod environment. Measure your RTO. If it's not < your SLO, tune or change topology.

The bad news: replication did its job perfectly. The damage is propagated to every replica. Neither sync nor async replicas help here — they all faithfully replayed the destructive write.

The recovery, in order of preference:

1. Point-in-time restore (PITR) — the right answer.

• Target: the moment just before the bad command (say, 14:32:17).
• Restore the DB to a side instance at that timestamp (most managed services: ~10-30 minutes for a TB-scale DB).
• Extract the email column for affected users.
• Forward-merge back into the production DB, preserving any legitimate updates that happened between 14:32:17 and now (use updated_at to tie-break).
• Total recovery: 30-60 minutes depending on data size and merge complexity.

2. If PITR window has expired: fall back to the latest daily snapshot (say 24 hours old). Restore, extract, merge. You lose legitimate updates between the snapshot and the bad command; reconcile from audit logs if you have them.

3. If no backups at all: reconstruction from application logs, event streams, or external systems (identity provider, CRM). This is the "we learned why backups matter" scenario.

The harder question — prevention:

• Migration review — no DML on prod without a reviewed plan, tested on staging.
• Logical backups of critical columns — append-only snapshot of email/PII columns daily, retained separately.
• Audit log of DDL/DML — who ran what, when. Required for forensics.
• Slow-down rules — UPDATE without a WHERE clause blocked by default in prod. DB-level safeguards like the pg_dangerous pattern.

The interview takeaway: replication and durability defend against different failures. An answer that leans only on replication misses the most common real-world data loss scenario.

Writes: blocked. W=3 requires 3 ack'd writes; only 2 nodes are reachable. Clients see write timeouts or errors.

Reads: also blocked with strict quorum. R=3 requires 3 read responses; only 2 are reachable.

Is R + W > N still satisfied? Yes (3+3>5), so read and write quorums would still overlap — but only when you have enough nodes to form a quorum at all.

What options the system has:

1. Sloppy quorum (the Dynamo paper, Riak). The coordinator writes to any 3 available nodes (even if they're not the "correct" replicas). Hinted handoff stores the write with a note to replay it to the canonical replicas when they return. Writes continue; you've traded strict quorum for availability.

• Cost: reads may not see recent sloppy-quorum writes until the hints are replayed. Read-your-writes becomes eventual.
• Note on Cassandra: it also stores hints for down replicas, but hints don't count toward QUORUM (only the ANY consistency level accepts them). So at QUORUM, Cassandra writes fail here rather than going sloppy.

2. Strict quorum — writes block, system is unavailable for writes. This is what Spanner-style systems do. Maintains strong consistency even under failure; trades availability.

• When: systems where losing a quorum should halt writes (financial ledgers, consensus-backed state).

3. Quorum adjustment. If you anticipate this failure shape, you can tune: N=5, W=2, R=4 gives you write-availability under 3-node failure, strong reads require 4 (rare during incidents — probably unavailable too).

The key insight the interviewer wants: quorum is a tradeoff between consistency (higher R+W) and availability (lower W or R). No setting survives "most of the cluster is down" gracefully; you must pick which property degrades first.

Cost 1: write latency. Cross-region RTT is typically 60-150 ms. Synchronous replication across regions would turn every write into a cross-continental round trip.

• Implication: traditional leader/follower databases keep synchronous replication inside a region and replicate across regions asynchronously. The exception is consensus-based databases (Spanner, CockroachDB, and similar), which can commit synchronously across regions by paying roughly a cross-region quorum round trip per write.
• Recovery consequence: with async cross-region replication, a total region loss means RPO > 0 — typically seconds to minutes of unreplicated writes are lost.

Cost 2: stale reads cross-region. An async replica in another region lags by the network latency + replication pipeline — typically hundreds of milliseconds to seconds under normal load, and can grow to minutes under replication lag.

• Implication: reads from the DR region may see data that's behind. Read-your-writes across regions is not free.

Cost 3: split-brain risk. If the primary region is partitioned from the DR region, both sides may think the other is dead and start accepting writes. Requires a coordinator (zookeeper, etcd) outside both regions or a human-in-the-loop for failover.

Common topologies:

1. Active-passive (single-writer). One region is the primary; others are async read-only replicas. Simple, predictable. RPO > 0 on failover. Users globally still write to the primary — cross-region write latency is unavoidable.

2. Active-active with partitioned writes. Each region is primary for its own users (e.g. US users write to us-east, EU users write to eu-west). Async replication between regions makes the other region's data queryable but not writable.

3. Active-active multi-leader. All regions accept all writes. Conflict resolution required (CRDTs, LWW, app-level). Cassandra and DynamoDB global tables (in their default, eventually consistent multi-region mode) work this way. Complex.

4. Paxos/Raft across regions. Spanner-style. Each shard's replicas span 3+ regions with one leader; commit requires a majority. Write latency is roughly a cross-region quorum round trip (often tens to 100+ ms). Strong consistency, high latency. Google Spanner and CockroachDB are the usual references; DynamoDB global tables also offer a multi-region strong consistency mode.

The honest answer in an interview: most systems replicate across regions asynchronously and accept RPO > 0 on regional failover. If the prompt requires strongly consistent multi-region writes, use a consensus-based store and cost it at roughly a cross-region round trip per write.

First — is this normal or abnormal?

Normal lag causes (don't panic, but monitor):

• Write burst on the leader temporarily exceeding the replica's apply rate.
• Long-running query on the replica holding locks (for systems where replicas can serve mixed workloads).
• Replica hardware weaker than the leader.

Abnormal causes (investigate immediately):

• Replica falling permanently behind — apply rate < commit rate. Replica will never catch up without intervention.
• Network partition between leader and replica.
• Corrupt WAL entry stopping replication.

Responses in order:

1. Route traffic away from the lagging replica. Either the LB / connection manager detects lag > threshold and stops routing, or you manually remove it from the read pool. Any read-your-writes or staleness-sensitive query going to this replica is wrong.

2. Diagnose the lag cause.

• Check pg_stat_replication / equivalent — is it streaming (good), catchup (recovering from a gap), or stuck?
• Is the replica CPU-bound? Disk I/O bound? Network saturated?
• Is a long-running query holding a lock?

3. Mitigate the symptom.

• Kill long-running read queries that are blocking apply.
• Increase replica resources if it's structurally underpowered.
• Temporarily reduce leader write rate (throttle batch jobs) if the replica is legitimately overwhelmed.

4. Decide whether to wait or rebuild.

• If the replica is behind by minutes and catching up, wait.
• If it's permanently behind (apply rate < commit rate), rebuild: either take a fresh base backup + start replication from there, or spin up a new replica and retire the lagging one.

5. Update alerting.

• Alert on replication lag > threshold (e.g., 5 seconds warning, 30 seconds page).
• Alert on apply-rate vs commit-rate divergence — catches the "falling behind" case before it becomes visible lag.

The broader lesson: async replicas have inherent lag. Design for it: route stale-tolerant reads to replicas, send RYW queries to the leader, and monitor lag as a first-class metric.