System design deep dive
Database Replication: deep dive
Replication keeps copies of the same data on several machines so the system survives hardware failures, serves more reads, and puts data closer to users. The hard parts are replication lag, safe failover, and resolving conflicting writes.
How leader-follower replication works, step by step
- A client sends
UPDATE users SET email = ... WHERE id = 42to the primary. - The primary applies the change and appends it to its replication log (the WAL in Postgres, the binlog in MySQL), each entry tagged with an increasing position.
- Followers keep a connection open and stream log entries from their last known position, applying them in the same order.
- In asynchronous mode the primary acknowledges the client right after its own commit. In synchronous mode it waits until at least one follower confirms it has the entry durably.
- Read traffic is routed to followers, which may be a few milliseconds to several seconds behind depending on load and network.
- If the primary fails, a monitor detects missed heartbeats, the most up-to-date follower is promoted, the others re-point to it, and clients are redirected through DNS, a proxy, or a virtual IP.
Replication topologies compared
| Topology | Writes accepted at | Strength | Main challenge |
|---|---|---|---|
| Single leader | One primary | Simple, no write conflicts | Primary limits write throughput; failover needed |
| Multi-leader | One leader per region or data center | Low-latency local writes, survives region loss | Conflicting concurrent writes must be merged |
| Leaderless (Dynamo style) | Any replica, with quorums | No failover step, highly available | Read repair, sloppy quorums, conflict handling |
| Consensus-based (Raft, Paxos) | Elected leader, majority ack | Strong consistency with automatic failover | Write latency of a majority round trip |
Living with replication lag
Asynchronous followers create user-visible anomalies. A user updates their profile and immediately reloads, but the read hits a lagging follower and shows the old value. Fixes include read-your-writes (route a user's reads to the primary for a few seconds after they write, or wait until a follower has reached the write's log position), monotonic reads (pin each user to one follower so time never appears to go backwards), and consistent prefix reads (keep causally related writes in the same partition so a reply never appears before the question).
When to replicate (and what replication will not do)
Every production database should have at least one replica for high availability, ideally in a different availability zone. Add read replicas when reads dominate and the primary's CPU is the bottleneck; a read-heavy app at a 10:1 read-write ratio can often scale reads 4 or 5x this way. Cross-region replicas cut read latency for distant users and provide disaster recovery. Replication does not increase write capacity in a single-leader setup (that needs sharding), and it is not a backup: an accidental DELETE replicates to every copy within milliseconds, so you still need point-in-time backups.
Common mistakes in interviews
- Adding read replicas without mentioning lag or read-your-writes consistency.
- Treating failover as instant and free. Promoting an async follower can lose the last few seconds of writes.
- Ignoring split brain, where two nodes both believe they are primary. Use fencing tokens, a consensus-based coordinator, or STONITH-style isolation of the old primary.
- Proposing multi-leader replication without a conflict resolution strategy.
- Calling replicas backups.
Interview questions
›What is semi-synchronous replication?
The primary waits for at least one follower to acknowledge receiving each transaction before confirming the commit, while other followers stay asynchronous. It guarantees that every acknowledged write exists on two machines, with less latency than waiting for all followers. MySQL and Postgres both support variants of it.
›How does leaderless replication handle a replica that was down?
With hinted handoff, another node temporarily stores writes meant for the down replica and forwards them when it returns. Read repair fixes stale values when a quorum read notices a mismatch, and periodic anti-entropy uses Merkle trees to find and sync any remaining differences.
›How do multi-leader systems resolve conflicts?
Common options are last-write-wins using timestamps (simple but silently drops data), version vectors that detect concurrency and keep both versions for the application to merge, and CRDTs, data types like counters and sets designed to merge automatically. Avoiding conflicts by routing each record's writes to one home region is often the most practical choice.
›How do you avoid split brain during failover?
Use a consensus system such as etcd or ZooKeeper, or a built-in Raft group, to elect exactly one leader with a majority vote. Each new leader gets a higher epoch or fencing token, and storage or downstream systems reject writes carrying an older token, so a resurrected old primary cannot corrupt data.
›What do RPO and RTO mean for replication design?
RPO (recovery point objective) is how much data you can afford to lose, which drives sync vs async replication. RTO (recovery time objective) is how long recovery may take, which drives automated failover and warm standbys. A bank may need RPO near zero and RTO under a minute; an analytics system might accept an hour for both.