Replication
How do you keep copies of data on several machines, and what goes wrong when they disagree?
Why did the page say the customer had not paid?
The kitchen now cooks for three cities: Pune, Nashik and, since Monday, Mumbai. Every order, payment and menu still lives in one PostgreSQL database in the cloud region near Pune.
At Monday’s lunch peak that database ran at full CPU. Most of its work was reads: order pages, menus, histories. So a second database server went up in the other availability zone, a follower that receives a copy of every change, and all reads moved to it. The primary kept the writes. Its CPU fell to forty percent.
On Tuesday at 12:41 a customer paid for her lunch by UPI. The app said “Payment received”. She refreshed the order page to be sure, and it said “Unpaid. Pay now.” She paid again.
Two payments for one order, a few seconds apart. The bank has both. The database has both. The follower, at lunch, ran about a second behind the primary, and her refresh reached the copy before her payment did. Every row in the follower was correct. It described the world as it had been a second earlier, and for the customer looking at the screen, a second earlier is wrong.
There is a second question behind the first. Suppose the Pune machine dies tonight and the follower takes over as the main database. If the follower is a second behind at that moment, the payments from that last second never reached it. Which payments would be lost?
Keeping copies of the same data on several machines is called replication. It buys more reads and a spare for when a machine dies. It costs exactly what that customer saw: the copies are not always the same.
Pause and predict The follower runs a second behind, and a customer refreshes right after paying. Which reads should go to the primary?
A copy that is always a little behind
A follower is the most common first step in scaling a database, and a good one. One server, the leader (PostgreSQL calls it the primary), accepts every write. One or more followers (replicas, standbys) receive a stream of the leader’s changes and apply them in the same order. Reads can go to any of them.
- Replication
- Keeping a copy of the same data on several machines, so that reads can be spread out and the data survives the loss of one machine.
- For example: The order database runs on a leader in zone A and a follower in zone B. Every committed change on the leader is replayed on the follower.
How does the change travel? In PostgreSQL the leader streams its write-ahead log (lesson 2.2) to the follower, which replays it exactly as it would after a crash. So the follower is always doing what a database does after a crash, replaying a log, except that the log comes from the leader.
Pause and predict The leader confirms the payment as soon as its own disk has it. The follower runs 800 ms behind, and the refresh reaches the follower 300 ms later. What does the page say?
Two problems follow, and both have the same cause: the follower holds the leader’s changes only up to some point in the recent past.
- Stale reads. The follower lags the leader by some replication lag. A read in that window sees the past. The customer saw her order as it was before her payment.
- Lost writes on failover. If the leader dies, a follower is promoted to take its place (failover). Anything the leader had confirmed but not yet sent is gone: the new leader never saw it.
A mental model
A shop with a branch has the same problem, and the same two answers.
Synchronous or asynchronous
The single most important setting is when the leader tells the client “committed”.
- With asynchronous replication, the leader confirms as soon as the change is on its own disk. Commits are fast and do not depend on any follower. The price is lag that readers can see and a window of confirmed writes that a failover loses.
- With synchronous replication, the leader confirms only after a follower has the change too. A confirmed write survives the loss of the leader. The price is a network round trip on every commit, and a commit that cannot complete if the synchronous follower is gone.
PostgreSQL makes this a dial. With synchronous_standby_names set, synchronous_commit = on waits until a standby has flushed the change to durable storage, remote_write until it has written it to its file system, and remote_apply until it has applied it so that queries on the standby can see it. With no synchronous standby named (the default), commits never wait for replication. MySQL offers a middle setting called semi-synchronous replication, in which the source waits for at least one replica to acknowledge receipt.
What one follower buys, and what it costs
Monday’s peak, from the database’s statistics: 3,000 queries a second on the leader, 85% of them reads.
| Quantity | Working | Result |
|---|---|---|
| Reads at peak | 3,000 x 0.85 | 2,550/s |
| Writes at peak | 3,000 x 0.15 | 450/s |
| Leader load with reads moved to two followers | writes only | 450/s |
| Reads per follower | 2,550 / 2 | 1,275/s |
| Writes each follower must replay | all of them | 450/s |
| Confirmed writes lost in a failover, 800 ms lag | 450 x 0.8 | 360 |
| The same, 50 ms lag | 450 x 0.05 | 22.5 |
Read the third and fifth rows together. Replication divides reads across copies. It never divides writes: every follower applies every write. When writes are the problem, you need to split the data, not copy it; that is lesson 3.2.
The last two rows answer the failover question in numbers. With asynchronous replication, a failover at the peak loses roughly the writes of the last lag window. At a normal 50 ms that is a couple of dozen; with the follower 800 ms behind it is hundreds, and payments are among them.
Should a payment wait for the follower?
Confirmed payments should survive anything short of losing both zones. Nothing should be able to stop orders at the lunch rush. Those two goals pull in opposite directions here.
The first path names a real risk: PostgreSQL’s documentation warns that commits waiting on a synchronous standby “may never be completed” if that standby crashes. That is why the setting is “synchronous to any one of two”: synchronous_standby_names = 'ANY 1 (s1, s2)' waits for whichever standby answers first. The second path is right that a lost payment is more than a latency problem. Reconciling it later means a customer who paid and got nothing until someone noticed.
The menu database, where losing the last second of edits costs a cook one retype, stays asynchronous. The decision is per database, and later in this part it becomes per operation (lesson 3.3).
What to remember
- One leader takes writes; followers replay its log in order. Replication spreads reads and survives a dead machine; it never spreads writes.
- Asynchronous replication confirms after the leader’s own disk: fast, but readers see lag and a failover loses the last lag window. Synchronous confirms after a follower has it: safe, and every commit waits.
- Lag causes three well-known problems, each named after the guarantee it breaks: read-your-writes, monotonic reads and consistent prefix. Fix them for the session that would notice, by routing its reads by log position, rather than slowing down every read.
- Wait for any one of several standbys, so one slow follower cannot stop commits.
- Failover takes three decisions: detect that the leader is dead, choose the follower with the most data, and stop the old leader from accepting writes if it comes back.
A follower shows the data as it was a moment ago, so before you tell a customer a payment went through, know which machines already hold it.
In the real world
The simulator has one follower and one number for lag. Real systems have several followers at different lags, lag that spikes when a follower replays a big transaction or waits behind a long query (PostgreSQL delays replay up to max_standby_streaming_delay, 30 seconds by default, before cancelling a conflicting query), and failover handled by tools such as Patroni or a managed service rather than a person. Managed databases often hide synchronous replication behind a “high availability” checkbox; read what it waits for.
The order page now reads a customer’s own payment from a copy that has it. The next problem is the orders table itself, which is growing past what one leader can hold and write.