Replication

One notebook, many photocopies

Indexes made each question cheaper. Eventually you run out of cheapness: the shop is busy enough that one notebook simply cannot be consulted fast enough, no matter how good the index is.

Notice, though, what the shop actually does all day. It reads far more than it writes. Customers check their order status constantly; they place an order once. Most systems run somewhere near ninety percent reads.

So make photocopies. One notebook stays the original - the only one anybody is allowed to write in - and copies of it sit on other desks for reading. Writes go to the original; the original sends every change out to the copies. That is replication, and the original is the primary while the copies are replicas.

Imagine it like this: One master notebook that only the manager may write in, and photocopies on every desk for anyone who just needs to look something up.

Words you just learned

primary: - the one copy that accepts writes; the source of truth
replica: - a read-only copy kept up to date from the primary
replication: - streaming every change from the primary to the copies

The copies are always slightly behind

Here is the catch, and it is not a bug you can fix - it is physics. Copying takes time. A change written to the primary is not on the replicas yet. Usually the gap is milliseconds. Under load, or across continents, it can be seconds.

That gap is replication lag, and the moment you have replicas you have to design around it. Your system is no longer one truth. It is one truth plus several slightly stale echoes.

The famous failure looks like this. A user updates their profile name. The write goes to the primary. The page reloads, that read is served by a replica that hasn't caught up, and the user sees their old name. They conclude the save button is broken and press it four more times.

the-classic-bug.sql

sql

-- t=0ms   on the PRIMARY
UPDATE users SET name = 'Aisha' WHERE id = 7;

-- t=8ms   the page reloads, this read lands on a REPLICA
SELECT name FROM users WHERE id = 7;

What it prints

t=0ms  UPDATE 1
t=8ms  name
       ------
       Aliya      <- the OLD name: this replica is 30ms behind

t=30ms (the change finally arrives; too late, the page already rendered)

Nothing failed. No error was raised. The write succeeded and the read succeeded - they simply talked to different copies of the truth, 22ms apart.

Read-your-own-writes

The rule that fixes it: after a user writes something, route that user's reads to the primary for a short window - a few seconds is usually plenty. Everyone else keeps reading from replicas. You give up a little read-scaling for the one case where staleness is unacceptable: a person looking at their own action.

Words you just learned

replication lag: - how far behind the primary a replica currently is
stale read: - correct data from an out-of-date copy
read-your-own-writes: - the guarantee that you always see the effect of your own actions

Sync, async, and what you are really choosing

You get one dial. Asynchronous replication means the primary confirms the write immediately and tells the replicas afterwards. It is fast, and if the primary dies in that window, the writes it confirmed but hadn't shipped are gone.

Synchronous replication means the primary waits for a replica to confirm before telling the user the write succeeded. Nothing is lost if the primary dies - but every write now pays the network round trip, and if the replica is slow or unreachable, writes stall.

This is the trade-off in its purest form. Async: fast, can lose data. Sync: safe, slower and more fragile. Most real systems run async, plus one synchronous replica for the case where the building catches fire.

Remember these

Async - primary confirms first, ships changes after (fast, can lose recent writes)
Sync - primary waits for a replica (durable, slower, stalls if the replica is sick)
Replicas scale reads only; writes still funnel into one primary
Every replica adds lag to reason about, not just capacity

Words you just learned

asynchronous replication: - confirm the write now, copy it to replicas afterwards
synchronous replication: - wait for a copy to confirm before calling the write done
failover: - promoting a replica to primary when the primary dies