Back to System design
Database Replication and Failover
mediumReplication is easy to turn on and hard to reason about. The questions that matter are how much data you are willing to lose, how long you are willing to be down, and what stops two nodes from both believing they are the primary.
ScaleRead scales linearly with replicas; write bounded by primary
StorageN × dataset size for N replicas
Key Concepts
1
Synchronous versus asynchronous is a data-loss decision. With async replication the primary
acknowledges a commit before the replica has it. Fast, and if the primary dies you lose everything in the
replication lag — usually milliseconds, occasionally minutes under load. That window is your RPO
(recovery point objective). With sync replication the primary waits for at least one replica, so RPO is
zero and every commit pays a round trip. Semi-synchronous — wait for one replica, let the rest catch up —
is the common compromise.
Synchronous versus asynchronous is a data-loss decision.RPO
2
Failover time is a budget you should be able to itemise. Detection is the largest and least discussed
part: you cannot declare a primary dead the instant it misses a heartbeat, because a one-second network
blip would trigger a needless failover. A typical budget is 10-30 seconds of detection, 5-10 seconds to
promote a replica, and a few seconds for clients to notice the new endpoint — 20-45 seconds of
unavailability, which is your RTO. If the business needs less, you are buying a different architecture,
not tuning this one.
Failover time is a budget you should be able to itemise.20-45 secondsRTO
3
Split brain is the failure that costs data. A network partition leaves the old primary alive and
serving writes while a replica is promoted on the other side. Both accept writes; neither is wrong; the
merge afterwards is manual and lossy. The defences are quorum-based promotion (a node may only be promoted
with a majority's agreement, so the minority side cannot promote) and fencing — revoke the old primary's
access to shared storage, or have it detect loss of quorum and demote itself.
Split brain is the failure that costs data.
4
Read replicas change what your application must handle. Reading from a replica means a user can write
and then not see their own write, which reads as a bug to them. The fixes, in increasing cost: route reads
to the primary for a few seconds after a write, track the write position per session and require a replica
to have caught up to it, or accept staleness where it does not matter. Say which you would use and where.
Read replicas change what your application must handle.
5
Lag is the metric to alert on, not replica health. A replica that is up and 40 minutes behind is worse
than one that is down, because the load balancer will happily serve stale reads from it.
Lag is the metric to alert on, not replica health.
6
What the interviewer is probing.
What the interviewer is probing.
7
1. "Primary with two replicas. The primary dies. What happens?" Probing: whether failover is
automatic, and what it costs. Stalls: "A replica takes over." Moves up: the detection/promotion/client
budget itemised, and the data lost equal to the replication lag.
1. "Primary with two replicas. The primary dies. What happens?"
8
2. "How much data do you lose?" Probing: RPO understanding. Stalls: "None." Moves up: async loses
the lag window; sync loses nothing and costs a round trip per commit; semi-sync is the usual compromise.
2. "How much data do you lose?"
9
3. "Both nodes think they are primary." Probing: split brain. Stalls: "That shouldn't happen."
Moves up: quorum promotion so a minority cannot promote, plus fencing of the old primary.
3. "Both nodes think they are primary."
10
4. "A user updates their profile and the page still shows the old name." Probing: read-after-write.
Stalls: "Cache issue." Moves up: the read went to a lagging replica; pin reads to the primary briefly,
or track the write position per session.
4. "A user updates their profile and the page still shows the old name."
11
5. "How do you know replication is healthy?" Probing: the right metric. Stalls: "The replica is
up." Moves up: alert on lag in seconds and bytes, because an up-but-far-behind replica silently serves
stale reads.
5. "How do you know replication is healthy?"
Components
- WAL / binlog / oplog — primary's change stream.
- Replication slot / GTID / oplog position to track follower progress.
- Coordinator (etcd, ZooKeeper, Consul) for leader election.
- Failover automation (Patroni, Orchestrator, RDS, Aurora).
- Client proxy (PgBouncer, ProxySQL) to route reads/writes after promotion.
- Fencing mechanism — STONITH, network ACL change, demoted flag in coordinator.
- Lag monitor + replay alerts.
- Backup + PITR independent of replication.
RPO and RTO, worked
Async replication, healthy system
Typical lag 5-50 ms
RPO (data lost on primary failure) = the lag at that
instant
Lag under heavy write load or a slow replica disk
= seconds to minutes
-- which is why lag must be measured, not assumed. Sync replication
RPO 0
Added commit latency = 1 round trip to the
nearest sync replica
same AZ 0.5-1 ms
cross-AZ 1-2 ms
cross-region (Mumbai-Singapore) ~60 ms Failover budget (typical automated setup)
Detection (missed heartbeats x threshold) 10-30 s
Promotion (replica opens for writes) 5-10 s
Client rediscovery (DNS TTL / driver refresh) 2-30 s
------------------------------------------------------
RTO 20-70 s Lowering RTO
- Shorten heartbeat thresholds: faster failover, more false
positives. There is no free setting.
- Keep client redirection out of DNS: a proxy or driver-level
topology refresh cuts the last segment to ~1 s.Split brain and fencing
How it happens
Partition separates primary P from replicas R1, R2 and the
orchestrator. R1 is promoted. P is still reachable by some
clients and still accepting writes.
Result: two primaries, two divergent write sets. Quorum promotion
A node may be promoted only with agreement from a majority of
the cluster (2 of 3, 3 of 5). The minority side mathematically
cannot promote, so at most one primary exists. Fencing the old primary
STONITH power off or isolate the old node
Storage fencing revoke its access to shared volumes
Self-demotion the primary watches its own quorum; on losing
the majority it stops accepting writes within
the heartbeat window Cluster sizes
3 nodes tolerates 1 failure majority = 2
5 nodes tolerates 2 failures majority = 3
Even sizes buy nothing: 4 nodes still tolerate 1, because a
2-2 split has no majority.Read-after-write options
Problem: user writes, then reads from a lagging replica and sees the old value.
1. Pin to primary after write
Route that session's reads to the primary for N seconds
(N > typical lag, e.g. 5 s).
Cost: primary takes some read load. Simplest thing that works. 2. Write position tokens (the precise option)
The write returns a log position (LSN / GTID). The client sends
it with subsequent reads; a replica that has not reached that
position forwards to the primary or waits.
Cost: plumbing through the application; exact correctness. 3. Monotonic reads via sticky routing
Hash the session to one replica so a user never moves backwards
in time, even if they are behind.
Cost: uneven load; does not fix read-after-write, only the
going-backwards symptom. 4. Accept it
For a feed or a search index, seconds of staleness are invisible.
Say so explicitly rather than paying for consistency nobody sees.