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 the guarantee holds — but only when you have enough nodes to form a quorum at all.

What options the system has:

1. Sloppy quorum (Dynamo, Cassandra). 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.
• When: Cassandra's default behavior. Good for AP workloads.

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: sync replication is region-local. Cross-region replication is always async.
• Recovery consequence: on total region loss, 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, DynamoDB Global Tables, CockroachDB offer this. Complex.

4. Paxos/Raft across regions. Spanner-style. Each shard's leaders are in 3+ regions; commit requires a majority. Write latency ~100 ms (cross-region RTT). Strong consistency, high latency. Google Spanner is the reference.

The honest answer in an interview: cross-region replication is always async; if the prompt requires strongly consistent multi-region writes, cost it at ~100 ms write latency minimum and accept that regional failover loses data.

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.