Replication
A user changes their display name, presses Save, sees "Saved!", and the page reloads with the old name. They press Save again. Same thing. They email support saying the settings page is broken. Nothing is broken. The write went to the primary database, the page read came from a replica, and the replica was 600 milliseconds behind.
Replication means keeping copies of the same database on several machines. Every serious production database does it, for two reasons: so a dead machine does not mean lost data or downtime, and so reads can be spread across copies. This chapter is how it works in Postgres and MySQL, and the handful of problems it creates.
Your primary database confirms a write, then crashes one second later, and a replica is promoted. Can that confirmed write be missing on the new primary? What decides it?
Leader and followers#
The standard setup has one primary (also called leader) that accepts every write, and one or more replicas (followers) that copy it.
What gets copied is the log, not the tables. You met the write ahead log in Transactions and ACID: every change is first written to a log. A replica receives that log as a stream and replays it, so it ends up byte for byte the same as the primary. Postgres calls this streaming replication. MySQL ships its binary log to replicas, which is the same idea.
Synchronous or asynchronous#
The one decision that matters most is when the primary tells your app "committed".
Replication lag#
With asynchronous replication, a replica is always slightly behind. Usually that is milliseconds. Under a burst of writes, a long running query on the replica, or a slow network, it can become seconds or minutes. Play with it.
The fixes, from simplest:
Read your own writes. For a short time after a user writes something, send that user's reads to the primary. Everyone else still reads from replicas. The person who would notice the stale data never sees it.
Read from the primary for critical paths. A balance after a payment, a seat after booking. Replicas are for pages where a second of staleness is invisible.
Wait for the replica. Remember the log position after the write and only read from a replica that has reached it. More precise, and more work.
Watch the lag. Alert on it. A replica that falls an hour behind is serving an hour old version of your product, silently.
When the primary dies#
Losing the primary means promoting a replica: failover. Here is what happens, and where it goes wrong.
A replica copies every change, including DELETE FROM orders without a WHERE clause, within
milliseconds. Replication protects you from a dead machine. It does not protect you from a
bad query, a bad migration or a bad deploy. That is what
backups are for.
Beyond one leader#
Everything above is single leader replication, which is what Postgres and MySQL do by default. Two other shapes exist, and system design interviews like to ask about them.
Multi-leader: several nodes accept writes, often one per region, and replicate to each other. Writes are fast everywhere, and two people can edit the same row in two regions at once, so you need a rule for resolving conflicts. Used sparingly, for example in collaborative or offline first apps.
Leaderless: any replica accepts writes, and reads and writes go to several at once, Cassandra and DynamoDB style. Correctness comes from quorums, which have their own chapter: Replication and quorums.
| Choice | What you gain | What you pay | Pick it when |
|---|---|---|---|
| Asynchronous replicas | Fast commits, and replicas never block writes. | A failover can lose recently confirmed writes, and reads can be stale. | The default. Fine for most data, with read your own writes for user facing pages. |
| Semi-synchronous, one of several | Confirmed writes survive losing the primary, without one slow replica stalling everything. | A round trip added to every commit. | Payments, orders, anything where losing a confirmed write is unacceptable. |
| Multi-leader across regions | Local writes in every region. | Conflicting writes, and resolving them correctly is hard. | Rarely, and only with a clear conflict rule. |
Checkpoint#
1. With asynchronous replication, can a write the app was told is committed disappear after failover?
2. What does a replica actually receive from the primary?
3. Why is a replica not a backup?
- Replicas replay the primary's log. Postgres streams WAL, MySQL ships the binlog.
- Async is the default and can lose confirmed writes on failover.
- Read your own writes hides lag from the person who would notice.
- Failover needs fencing, or you get split brain. Replicas are not backups.
Replication keeps copies of a database on several machines. In the usual setup one primary takes every write and streams its write ahead log to replicas, which replay it. The key choice is when a commit returns: asynchronous is fast but can lose confirmed writes if the primary dies, synchronous waits for a replica, and semi-synchronous waits for any one of several, which is what I would use for payments. Async replicas lag, so I route a user's reads to the primary right after they write, keep critical reads on the primary, and alert on lag. Failover promotes the most up to date replica and must fence the old primary to avoid split brain. And replicas copy mistakes too, so they are not backups.