Replication: leader and followers

One database server can do only so much work in a second. When an app grows, the database is often the first part that is too busy. So interviewers ask: "The database is too busy. What do you do?"

A weak answer says "add more database servers" and stops. A strong answer says: "I keep extra copies of the data. Reads go to the copies. Writes still go to one server." It also says when the user hears "saved": before or after the copies have the change. Then it says what that choice costs.

At SDE-1 level you should explain what the copies are for. At SDE-2 level you should work out how many copies you need, and say that writes do not get faster. At SDE-3 level you should also choose how to copy by what the data is worth.

Think of a school with one master register in the principal's office. Every change, such as a new student, is written there first. The school also keeps a copy of the register in each classroom. A clerk carries every new line from the master register to the copies. Teachers look at the copy in their classroom, so the principal's office is not crowded. But to change a line, a teacher must go to the principal.

The principal has a choice. She can say "done" as soon as she writes the line, and let the clerk carry it later. That is fast, but for a short time the copies do not show the new line. Or she can wait until the clerk confirms that a copy has the line. That is slower, but the copy is not behind.

A database works in the same way. Remember two words from lesson 14. A read looks at data. A write changes data.

Replication means keeping copies of the same data on more than one server. Each copy is a replica. The copies have two roles:

• The leader is the one server that accepts writes.
• A follower is a replica that copies the leader. It answers reads. Your app does not write to it.

Tools use other names. Postgres says "primary" and "standby". They mean the same two roles. This course says leader and follower.

How a follower copies the leader

In lesson 21 you saw that a database writes each change to a log on disk before it says "done". Postgres calls this log the write-ahead log. The leader sends this log of changes to every follower. A follower applies the changes one after another, in the same order. The same changes in the same order give the same data. A new follower first takes a full copy of the data, and then follows the log.

Why does only the leader accept writes? Two servers could change the same row in two different ways at the same time, and their data would stop matching. With one leader there is one order of changes, and every follower follows it.

What followers are for

1. More reads. Each follower answers reads, so the reads are shared. If one follower can do 1,000 reads per second, three followers can do about 3,000.
2. Another copy of the data. If the leader's disk breaks, the data is still on the followers.

Followers are not for two things:

• Faster writes. Every write still goes to the leader. The leader also has to send the log to each follower, so each follower adds a little work.
• Less data on each server. Every follower holds all the data. If the data is 2 TB, the leader and each follower hold 2 TB each.

Asynchronous or synchronous copying

When the leader gets a write, it must decide when to tell the app "saved".

• Asynchronous copying: the leader saves the change on its own disk and tells the app "saved". It sends the change to the followers later. The app does not wait for them.
• Synchronous copying: the leader sends the change to a follower and waits until the follower confirms that it saved the change. Only then does it tell the app "saved".

A middle way is to wait for one follower and let the others copy later. Another is to wait for whichever of two followers answers first, so one slow follower does not stop writes.

In Postgres, copying to followers is asynchronous by default. The delay is typically under one second, if the follower can keep up. The setting synchronous_commit decides how long a commit waits. A commit is the moment the database keeps a transaction's changes (lesson 21). These are the settings that matter here:

The last three wait for a follower only if you have named the followers to wait for. If not, they act like local.

SwadKart, a food-delivery app, has one database server. How many followers does it need, and what does each way of copying cost?

Assumptions (made up to teach with, not real traffic):

• The server has 8 CPU cores. One core gives 1,000 ms of work per second (lesson 15), so 8 cores give 8,000 ms per second.
• We plan to use at most 70% of a server at peak, which is 5,600 ms per second (the target from lesson 15).
• At peak there are 1,500 reads per second and 300 writes per second.
• A read costs 4 ms of CPU. A write costs 8 ms on the leader.
• A follower spends 0.5 ms of CPU to apply one change. The leader spends 0.25 ms of CPU to send one change to one follower.
• Reads go to followers only. The leader takes only writes.

Step 1: one server.

• Reads = 1,500 x 4 ms = 6,000 ms per second
• Writes = 300 x 8 ms = 2,400 ms per second
• Total = 8,400 ms. That is 8,400 / 8,000 = 105% of the server. It cannot keep up.

Step 2: one follower. It takes all 1,500 reads and applies all 300 changes.

• Follower = 1,500 x 4 + 300 x 0.5 = 6,000 + 150 = 6,150 ms. That is 6,150 / 8,000 = 77%. This is above our 70% target.
• Leader = 300 x (8 + 0.25) = 2,475 ms, which is 31%.

Step 3: two followers. Each takes 750 reads and applies all 300 changes.

• Each follower = 750 x 4 + 300 x 0.5 = 3,000 + 150 = 3,150 ms, which is 3,150 / 8,000 = 39%.
• Leader = 300 x (8 + 2 x 0.25) = 300 x 8.5 = 2,550 ms, which is 32%.

Two followers are enough. At our target, one follower can serve (5,600 - 150) / 4 = 1,362 reads per second (rounded down). Two can serve 2,725, which is more than 1,500.

Step 4: the answer to the predict box. How many writes can the leader take at our 70% target?

• With no follower: 5,600 / 8 = 700 writes per second.
• With three followers: 5,600 / (8 + 3 x 0.25) = 5,600 / 8.75 = 640 writes per second.

So the answer is no. Followers did not raise the write limit. They lowered it a little, because the leader sends the log to each one.

Step 5: the cost of waiting. Assumption: a disk write takes 1 ms on any server. A round trip inside one data centre takes 0.5 ms (lesson 14). A round trip is the time for a message to go and the answer to come back.

• Asynchronous: the app waits for the leader's disk only, so 1 ms.
• Synchronous with one follower: 1 ms for the leader + 0.5 ms for the trip + 1 ms for the follower's disk = 2.5 ms. This is the worst case: the steps run one after another.

Writes are also less available. Assumption: every server works 99.9% of the time (lesson 4), and one server failing does not make another fail. A synchronous write needs the leader and the follower, so it works 0.999 x 0.999 = 0.998001 of the time. In a 30-day month (43,200 minutes), that is (1 - 0.998001) x 43,200 = 86 minutes of downtime (rounded from 86.4). The leader alone gives 43.2 minutes. Suppose the leader waits for whichever of two followers answers first. Writes then stop only when the leader is down, or when both followers are down at once. So the downtime is about the same as the leader alone.

Step 6: the cost of not waiting. Assumption: a follower is 0.4 seconds behind the leader. At 300 writes per second, 300 x 0.4 = 120 changes are only on the leader at any moment. If the leader's disk were lost then, those 120 changes would be gone.

• Followers solve a database that is too busy with reads, and they give a second copy of the data. In the example, two followers took the load from 105% of one server to 39% each.
• Followers cost more servers. They do not raise the write limit, and every follower holds all the data. A read from a follower can show old data.
• Asynchronous solves fast writes that go on when a follower is down. It costs the newest changes if the leader's disk is lost.
• Synchronous solves losing changes. It costs slower writes, and writes that wait when the follower is down.
• I would change it when the data is money or orders that must never be lost: then I wait for one follower. I would also change it when writes, not reads, reach the limit: followers cannot help then.

"More followers means more writes per second." It does not. All writes go to the leader. In the example the limit went from 700 to 640 writes per second after three followers were added.

"Synchronous copying is always the safer choice." It is safer for the data, but it costs speed. With one follower to wait for, a down follower stops every write. The example shows 86 minutes of write downtime a month against 43.

"A follower always shows the same data as the leader." Not with asynchronous copying. A follower applies the log a little later. In the example it was 0.4 seconds behind.

1. What is a leader, and what is a follower? What is the difference between asynchronous and synchronous copying?
2. A database has 4 CPU cores (4,000 ms per second) and a 70% target (2,800 ms). At peak there are 800 reads per second at 3 ms each, and 100 writes per second at 6 ms each. A follower spends 0.5 ms to apply one change. The leader spends 0.25 ms to send one change to one follower. (a) What share of one server does the work use? (b) Is one follower enough, if it takes all the reads? (c) How many writes per second can the leader take with that follower?
3. A teammate says: "Our writes will soon reach 3,000 per second, and the leader takes 700. Let us add 5 followers." What do you say?