System designCore9 min
Database replication
Keep copies of the data on several machines, so you survive losing one and can serve more reads. The hard part is that the copies are never quite in step, and every design choice is about how far apart you let them drift.
1 · Why copy
One copy of the data is one dead machine away from an outage
A small online bank keeps every account's balance and payments in one database on one machine. When that machine's disk fails, the bank is down until it is replaced and restored from a backup, and every payment since the last backup is gone.
Replication keeps the same data on several machines, called replicas, and keeps them updated as it changes. It buys three different things, and a design usually wants more than one:
Reason | How replicas help | At the bank |
|---|---|---|
Stay up | If one machine dies, another already has the data and takes over | Payments keep working when a disk fails |
Scale reads | Reads are spread over several copies | Statement pages, read far more often than payments are made |
Be near users | A copy in each region answers local reads quickly | Customers abroad read balances from a nearby copy |
Replication is not a backup. A replica copies every change, including a mistaken DELETE, within a second. Backups keep the past; replicas keep the present on more than one machine.
2 · The common setup
One leader takes the writes, and followers replay its log in order
Most databases replicate the same way. One replica, the leader, accepts all writes. It records each change in a log and streams the log to the other replicas, the followers, which apply the changes in exactly the same order. Reads can go to any replica.
Writes go to one place, so there is never a question of which write came first. Reads spread across the followers.
The order is the point. A deposit of $100 followed by a payment of $80 works; the same two changes applied the other way round bounce the payment. With one leader deciding the order, every follower ends up in the same state as the leader, only later.
3 · The key choice
Whether the leader waits for a follower decides if an acknowledged payment can vanish
When a customer pays a bill, the leader can answer done as soon as its own disk has the payment, or wait until a follower has it too. With asynchronous replication, the default in PostgreSQL and MySQL, it answers at once and the followers catch up, usually in well under a second. That gap is where payments go missing:
The customer was told the payment went through. The new leader never heard of it, and the loss grows with how far behind the follower was.
Mode | The leader answers after | What it costs |
|---|---|---|
Asynchronous | Its own disk has the write | A crash can lose writes it already confirmed |
Synchronous | Followers have the write too | Every write waits for a follower; if it is down, writes stop |
Semi-synchronous | At least one follower has it | One extra round trip per write |
Waiting has its own failure. In PostgreSQL, if the synchronous follower a commit must wait for is gone, commits wait with it, until an operator lowers the requirement. That is why a common setup waits for any one of several followers, which PostgreSQL configures as ANY 1 of a list: no write lives on one machine only, and no single slow follower stalls the bank.
MySQL's semi-synchronous mode has the opposite trap. If no follower acknowledges within 10 seconds, its default timeout, the leader falls back to asynchronous replication and carries on, until a follower catches up. During an outage of the followers, writes are quietly back to living on one machine.
4 · The catch
Followers run behind, so a reader can see the past
An asynchronous follower is always a little behind the leader. That delay is replication lag: usually well under a second, but seconds or minutes when a follower is overloaded, a big batch job runs, or the network is slow. A customer who pays a bill and reloads can be served by a follower that hasn't seen the payment yet:
Nothing was lost; the read just went to a copy that was behind. To the customer, the payment looks like it never happened.
Which promise a reader needs decides where its reads can go. These are the session guarantees from Consistency models, and the usual ways to keep them on followers:
Guarantee | How to keep it |
|---|---|
Read your own writes | Send a customer's reads to the leader for a short while after they write |
Read your own writes, precisely | Wait until the follower has applied the log position of the customer's last write |
Never go back in time | Keep each customer's reads on one follower |
Always current, for everything | Read from the leader, giving up the read scaling |
PostgreSQL can also make the leader wait until a follower has applied each commit, not just stored it, so a read sent there right after the write sees it. It is the strongest promise, and the slowest writes.
5 · When the leader dies
Promoting a follower is where replication's shortcuts come due
With one leader, losing it means failover: another replica becomes the leader, and everything that wrote to the old one has to find the new one. Health checks & failover covers detection in depth; for replication, four steps matter:
- Notice
The leader stops answering health checks for some seconds. Too short a wait promotes on a hiccup; too long leaves the bank down.
- Choose
Promote the most up-to-date follower, by consensus among replicas or by an outside orchestrator, to lose as little as possible.
- Redirect
Applications and the other followers switch to the new leader.
- Fence the old leader
If it comes back believing it still leads, two leaders accept writes: a split brain. It must be shut out, for example with a fencing token.
Real failovers show both shortcuts at once. In October 2018, a 43-second network cut separated GitHub's main East Coast data center from its East Coast network hub. Its orchestrator promoted database leaders on the West Coast. The East Coast leaders had accepted several seconds of writes that never reached the West, and the West then took new writes of its own: 954 writes on one cluster alone had to be reconciled, and the site ran degraded for 24 hours and 11 minutes.
6 · Other setups
Several leaders, or none, trade write conflicts for availability
Setup | Writes go to | Good at | Watch out for |
|---|---|---|---|
Single leader | One replica | Simple; no write conflicts | Failover, and one machine's write capacity |
Multi-leader | A leader in each region | Fast local writes, and each region keeps writing if cut off | Two regions changing the same row: conflicts to resolve |
Leaderless | Any N replicas; W of them must confirm | No failover; stays writable when replicas fail | Reads ask R replicas; W + R > N for overlap |
Multi-leader replication runs a leader in each region and replicates between them. Each region can write locally, and keeps writing if the regions are cut off. The price is conflicts: two leaders can accept changes to the same row before either hears of the other's. For money, that is not a merge problem but an overdraft:
Each leader checked the balance it could see and was right to. Merged, the account is $60 overdrawn. Data like balances belongs on a single leader for this reason.
Leaderless replication drops the leader altogether: a client writes to all N replicas and counts the write done once W confirm, and reads from R replicas and takes the newest answer. If W + R > N, every read overlaps at least one replica that has the latest write, with caveats that Quorums works through.
7 · In a real system
Three copies of every short link, read and written by majority
URL Shortener System Design
Leaderless replication with quorums
The URL shortener keeps every link on three Cassandra replicas. A write is acknowledged by a majority in the local data center, and a read waits for the same majority, so a link that was just created never reads as missing, even with one replica down.
Check yourself
5 questions
Takeaways
Remember this
- Replicas buy availability, read capacity and nearness to users; they are not backups.
- One leader orders all writes, and followers replay its log in that order.
- Asynchronous is fast and can lose confirmed writes; waiting for any one of several followers is the common middle.
- Followers lag, so reads that must see a user's own writes go to the leader or wait for the follower to catch up.
- Failover must promote the most current follower and fence the old leader; several leaders trade failover for write conflicts.