Step 2 of 17 · 1 min

Why replicate

A replica is a copy of a database on another machine, kept up to date as the original changes. Teams set them up for four main reasons:

• Availability. If the main database fails, a replica can take over in seconds or minutes, instead of hours of restoring backups.
• Read capacity. Reads can be spread across several replicas, so more reads can be served in total.
• Distance. A replica in another region lets nearby users read with lower latency. Lesson 5.5 covers this.
• Keeping heavy work away from the main database. Analytics queries, reports and backups can run on a replica without slowing down the app.

Step 3 of 17 · 1 min

How leader-follower replication works

The most common setup, and the one Chirp used, is leader-follower replication (also called primary-replica, and in older documentation master-slave):

• One database is the leader. All writes go to the leader, and only to the leader.
• The others are followers. They receive every change from the leader and apply it to their own copy. They can serve reads, but they don't accept writes from the app.

The changes travel through the write-ahead log from lesson 1.4. Every change the leader makes is first recorded in its log. In Postgres's built-in streaming replication, each follower keeps a connection open to the leader and receives log records as they're written, then replays them, applying the same changes to its own data in the same order. Since every follower applies exactly the same changes in exactly the same order, they all end up with exactly the same data.

One phrase in that paragraph hides all the interesting problems: "as they're written." The leader and its followers are separate machines, connected by a network. The log records take time to arrive and time to replay. So at any moment, each follower is a little behind the leader. That gap is called replication lag.

Step 4 of 17 · 1 min

When does a write count as done?

This is the most important decision in setting up replication, and it comes down to one question: when the leader tells the app "your write is committed," which machines have the change?

Asynchronous replication. The leader commits the change, tells the app it's done, and sends it to followers afterwards. Writes are as fast as they'd be with no replicas at all, and a slow or broken follower doesn't affect the app. But if the leader fails before a change has reached any follower, that change exists nowhere else. This is Postgres's default, and it's what Chirp started with.

Synchronous replication. The leader waits until at least one follower confirms it has received the change before telling the app the write is done. If the leader then fails, that follower definitely has every committed change. The cost:

• Every write takes longer, by at least one round trip to the follower. In the same availability zone, that's well under a millisecond. In another zone, a millisecond or two. In another region, 60 ms or more (lesson 1.2).
• Writes depend on the follower. If the synchronous follower is down or slow, writes stall.

A middle ground is to have several followers and wait for any one of them. In Postgres, synchronous_standby_names = 'ANY 1 (replica_a, replica_b)' means "wait for whichever of the two confirms first." One follower being slow or down no longer stalls writes, and every committed change is still on at least two machines.

Here's what each choice means when the leader fails:

Step 5 of 17 · 1 min

Replication lag

With asynchronous replication, how far behind are followers? Normally, not far: Chirp's replicas usually lagged by a few milliseconds. But lag isn't steady, and it can grow quickly:

• Big writes. A single transaction that updates two million rows produces a huge amount of log in one go, and followers have to replay all of it.
• Followers replay mostly in one stream. The leader can process many writes in parallel from many connections, while a follower applies the log largely in order. A leader under heavy write load can produce changes faster than a follower can apply them.
• Long queries on the follower. In Postgres, a long-running query on a replica can conflict with changes that need to be applied (for example, rows the query is still reading that the leader has since cleaned up). The follower either pauses replay until the query finishes, letting lag grow, or cancels the query. The settings that control this (like max_standby_streaming_delay) are a trade-off between lag and cancelled queries.
• An overloaded follower. If the follower's CPU or disk is busy serving lots of reads, it replays more slowly.

You can measure lag in two ways: in time (how long ago, roughly, was the newest change it has applied?) or in bytes of log it still has to apply. Postgres shows both, in the pg_stat_replication view on the leader. Chirp alerts when any replica is more than five seconds behind.

Step 6 of 17 · 4 min

The bugs lag creates

Here's the part that took Chirp two months to work through. Sending reads to replicas means some reads see an older version of the database. Each of the following bugs is a different way that shows up for users.

"My post disappeared"

Priya posted a photo. The app sent the new post to the leader, got "201 Created" (lesson 1.5), and immediately reloaded her profile to show it. That reload was a read, so it went to a replica. The replica was 300 ms behind and hadn't applied the new post yet. Priya's profile showed no new post.

She assumed it had failed, and posted it again. Now she had two.

This is a violation of read-your-writes consistency, the guarantee that after you write something, your own reads will see it. Other people seeing your post a moment late is usually fine. You not seeing it is confusing and looks like a bug.

There are a few ways to provide it, with different trade-offs:

Read from the leader for a while after a write. When a user writes something, note the time (in their session). For the next few seconds, send that user's reads to the leader. Simple and effective, but the "few seconds" has to be longer than the worst lag you expect. If lag spikes to 40 seconds, it breaks again.

Read your own data from the leader. Some data is only ever edited by its owner, such as your profile and your settings. Always read those from the leader when the viewer is the owner. Everyone else can read from replicas.

Remember how far your write got. Every change in the log has a position, which Postgres calls a log sequence number (LSN). After a write, the app records the leader's current LSN in the user's session. For later reads, it only uses a replica that has replayed at least that far (Postgres reports this with pg_last_wal_replay_lsn()), and otherwise reads from the leader or waits briefly. This is the most precise option, and it keeps working when lag spikes. It's also the most work to build.

Show it locally. The app already knows what Priya just posted. It can add the post to her profile on the phone right away, without waiting for a read. Lots of apps do this anyway because it feels instant. It's a good complement to the fixes above, but not a substitute, since the next real reload still has to show it.

Chirp used the first and second approaches together: owners read their own profile and settings from the leader, and anyone who wrote anything in the last 10 seconds reads from the leader. Later, when lag spikes made 10 seconds unreliable, they switched the second rule to tracking LSNs.

"The comment keeps appearing and vanishing"

With two replicas, a user's consecutive reads could go to different ones. One evening, someone opened a post and saw a new comment (from replica A, which was up to date), refreshed, and the comment was gone (replica B, a few seconds behind), refreshed again, and it was back.

This breaks monotonic reads: once you've seen something, you shouldn't later see an older state of the world. The fix is to make each user's reads go to the same replica, for example by choosing the replica from a hash of the user's ID. If that replica fails, the user moves to another one, and may briefly see an older state once. That's rare enough to accept.

"Someone replied to a post I can't see"

The third kind is subtler, and shows up more with sharded databases (Part 5) than with one leader, but it's worth knowing now. Suppose Meera posts a question and Arjun replies to it. If you see Arjun's reply but not Meera's question, the conversation makes no sense. The guarantee that prevents this is called consistent prefix reads: you see changes in the order they happened. With a single leader and replicas that apply its log in order, each replica already gives you this. It becomes a real problem when data is spread across several independent databases.

When lag gets bad

Chirp's worst lag came from its own maintenance. The team ran a job that added a new column to the posts table and filled it in for all two million rows, in one big transaction. On the leader, it took four minutes. The replicas then had four minutes' worth of changes to replay, mostly in a single stream, while also serving reads. Lag climbed to 40 seconds.

For those 40 seconds, users got notifications ("Arjun replied to your post") for replies they couldn't see, because the notification was triggered from the leader and the reply was read from a replica. People who had just posted saw nothing, even with the 10-second rule.

Two fixes:

• Big data changes go in small batches. Update a few thousand rows per transaction, with short pauses, and watch lag while it runs. It takes longer overall, and nothing falls behind.
• Lagging replicas leave the pool. Treat replication lag like a health check (lesson 2.2). If a replica is more than a few seconds behind, the app stops sending it reads until it catches up. If every replica is behind, reads go to the leader. It's busier, but correct.

Step 7 of 17 · 1 min

Writes don't scale with replicas

One thing replicas never do: help with writes. Every write still goes to the single leader, and every follower has to apply every write too. Adding a fifth replica gives you more read capacity and one more machine replaying the full write load.

For Chirp at 100,000 users, writes are a small fraction of the load (lesson 1.3 estimated single digits per second at the database), so this doesn't matter yet. It starts to matter much later, and spreading writes across machines is what Part 5 is about.

Step 8 of 17 · 3 min

Failover

Back to 3:12 am. The leader's disk failed, and the database stopped responding.

Replacing a failed leader is called failover. Chirp used a managed database service that did it automatically, but the steps are the same whether a tool does them or a person at 3 am does:

1. Decide the leader is dead. Usually, by noticing that it hasn't answered for some time. Too short a timeout, and a brief network hiccup triggers an unnecessary failover, which has its own risks. Too long, and you're down longer than you need to be. Chirp's provider waited about 30 seconds.
2. Pick a new leader. Ideally the follower that's furthest ahead, because it has the fewest missing changes.
3. Promote it. It stops replaying and starts accepting writes.
4. Redirect writes. The app has to start sending writes to the new leader, usually by updating the DNS name or the address behind a connection proxy (lessons 1.1 and 2.2 have the DNS-caching caveats).
5. Deal with the old leader. If it comes back, it must not accept writes again.

Chirp was back in 58 seconds. Nobody was paged until the morning, because nothing appeared broken.

The 37 posts

Chirp was using asynchronous replication. In the second or so before the disk failed, the leader had committed 37 posts, a few hundred likes, and one user's email address change. Those changes were in the leader's log, but hadn't yet reached either follower. When a follower was promoted, they simply weren't part of the database anymore.

The people who wrote them had seen "posted." The posts never appeared. The user who'd changed her email address got her password reset links sent to the old one for a week, until she contacted support.

This is the core trade-off of asynchronous replication, and there's a name for the amount you can lose: recovery point objective (RPO), how much recent data you can afford to lose in a failure. Alongside it is recovery time objective (RTO): how long you can afford to be down. Asynchronous replication gives an RPO of "whatever hadn't replicated yet": usually under a second, occasionally more. Synchronous replication gives an RPO of zero, at the cost of slower writes. Chirp switched to waiting for any one of its two replicas (the middle ground above), which added about a millisecond to writes.

Two leaders

The most dangerous failover problem doesn't happen to Chirp that night, but it could have. If the old leader hadn't really died (say, it was only cut off from the network for a minute) and then came back still believing it was the leader, Chirp would briefly have had two leaders, both accepting writes. The two copies of the database would split apart, with different posts and likes in each, and no automatic way to merge them. This is called split brain.

Failover systems prevent it by fencing the old leader: making sure it can't accept writes anymore, for example by revoking its access to storage or shutting it down, before the new leader starts. Lesson 6.5 explains how distributed systems agree on a single leader at all, which is harder than it sounds.

A real example of how failover goes wrong is described in Kleppmann's Designing Data-Intensive Applications: a team promoted an out-of-date replica, which then reused ID numbers the old leader had already given out. Another system still had data under those IDs, so some users were shown other users' private data. The lesson is that failover interacts with everything outside the database that remembers IDs, positions or timestamps.

Step 9 of 17 · 1 min

A replica is not a backup

One more lesson, which Chirp was lucky to learn from someone else's incident report rather than its own.

Replicas copy every change, including mistakes. If someone accidentally runs DELETE FROM posts WHERE with the wrong condition, or a bug corrupts a table, the replicas faithfully apply that too, within milliseconds. They protect you from a machine failing. They don't protect you from bad changes.

For that you need real backups:

• Point-in-time recovery. Take a full backup regularly, and keep archiving the write-ahead log continuously in between. To recover, restore the last full backup and replay the log up to the moment just before the mistake. Postgres supports this natively, and managed services usually offer it as a setting.
• Test restores. A backup you've never restored is a hope, not a backup. Chirp started restoring last night's backup to a temporary server every week, automatically, and checking it.
• Optionally, a delayed replica: a follower deliberately kept, say, an hour behind (Postgres's recovery_min_apply_delay). If someone drops a table, you have an hour to notice and copy the data from it before the mistake arrives there too.

Step 10 of 17 · 1 min

Beyond a single leader

Leader-follower replication is the right starting point for nearly everyone. There are two other families you'll hear about, both coming up in Part 5:

• Multi-leader: several databases each accept writes, and send them to each other. Useful when users in different regions all need to write with low latency, at the cost of handling conflicting writes.
• Leaderless: any replica accepts writes, and reads ask several replicas and take the newest answer. This is how databases in the Dynamo family, like Cassandra, work.

Step 11 of 17 · 2 min

From the real world: the night GitLab's backups didn't exist

This lesson's last section said a replica is not a backup. GitLab learned that, and several other lessons, in one night: 31 January 2017.

It began with replication lag. A spike of spam, plus a large background job deleting an account's data, loaded GitLab.com's primary database, db1. Its replica, db2, fell so far behind that the primary had already deleted the write-ahead log segments the replica still needed. GitLab didn't archive WAL anywhere else, so the replica couldn't catch up. It had to be rebuilt from a fresh copy of the primary.

The rebuild kept failing. Late at night, after hours of trying, an engineer decided to wipe the replica's data directory and start the copy again. They ran the command in a terminal they believed was connected to db2. It was connected to db1. They stopped it within a second or two, but about 300 GB of the primary's data was already gone.

Then the team went to restore from backups, and found:

• The nightly pg_dump backups to S3 were empty. The backup job ran pg_dump version 9.2 against a 9.6 database, which fails. It had been failing for a while.
• The failure alerts had been sent by email, and the receiving mail server rejected them, so nobody saw them.
• Cloud disk snapshots were enabled for other servers, but not the database servers.
• The replica was the thing that had just been wiped.

What saved them was luck: about six hours earlier, an engineer had taken a disk snapshot of the database to refresh a staging environment. Copying it back to production took around 18 hours, because the staging disks were slow. GitLab.com came back with about six hours of database changes lost (issues, comments, merge requests, about 700 new user accounts). Git repositories, stored separately, were fine. GitLab live-streamed the recovery and published an unusually honest postmortem.

What it teaches, beyond "check which machine you're on":

• A backup you haven't restored is a hope, not a backup. Restore regularly, automatically, and check that the result contains recent data.
• Alert on the absence of success, not just on failure messages. "No successful backup in 26 hours" would have fired.
• WAL archiving is what lets a lagging replica catch up, and it's also the basis of point-in-time recovery. GitLab had neither.
• Make the dangerous command hard to run on the wrong machine: different shell prompts and colours for primaries, and tools that refuse destructive actions on a node that's currently the leader.

Step 12 of 17 · 2 min

Going deeper: when two machines both have "the" history

Failover has a quiet problem this lesson only hinted at: with asynchronous replication, the old leader and the new leader can end up with different histories, and the database won't merge them for you.

Picture the leader at log position 105, having acknowledged every write up to there. The replica has received up to 103. The leader dies. The replica is promoted and starts accepting new writes at position 104. Now there are two different things called "104": the old leader's (which a user was told had committed) and the new leader's.

When the old leader's machine comes back, it can't simply start following the new leader, because its log has diverged: it contains changes the new leader never saw. In PostgreSQL, a tool called pg_rewind finds the point where the two histories split and rewinds the old leader's data to that point, so it can rejoin as a follower. Anything it had after the split, writes 104 and 105 here, is discarded. (MySQL calls such leftovers "errant transactions," and the same problem applies.)

Those writes aren't lost because of a bug. They were acknowledged under asynchronous replication, which always allowed this. Whether that matters depends on the data. For likes, it's fine. For a payment, it's not, and someone will have to pull the discarded changes out of the old leader's data and reconcile them by hand. GitHub spent most of a day doing exactly that after a failover in 2018 (lesson 5.4).

The ways to avoid it:

• Synchronous replication (synchronous_commit with synchronous_standby_names = 'ANY 1 (...)' in PostgreSQL). A write isn't acknowledged until at least one replica has it, so a promoted replica always has every acknowledged write. It costs latency on every write and availability if no replica is reachable.
• Only promote the most up-to-date replica, and only after confirming the old leader is really gone (fencing, lesson 6.5).
• Keep the old leader's data after failover instead of wiping it, so discarded writes can be recovered if they matter.

Step 13 of 17 · 1 min

What Chirp changed

• Switched from fully asynchronous replication to waiting for any one of two replicas, so a single machine failing loses no committed writes.
• Owners read their own profile and settings from the leader. Users who just wrote something read from the leader until a replica has caught up to their write's LSN.
• Each user's other reads stick to one replica, chosen by user ID.
• Replicas lagging more than 5 seconds are taken out of the read pool automatically.
• Large data changes run in batches of 5,000 rows, with lag monitored throughout.
• Set up point-in-time recovery with continuous log archiving, and a weekly automated restore test.

Step 14 of 17 · 1 min

In the simulation

Stage 1's decision D3 includes read replicas. They're a good choice, and the simulation will show you the "my post disappeared" support tickets if you route reads to them without read-your-writes. Stage 4 brings them back: when Chirp goes multi-region, replicas in other regions lag by much more than a few milliseconds.

Open the simulation →

Step 15 of 17 · 1 min

If you're here for an interview

Replicas come up whenever a design needs more reads or more availability. The follow-ups:

• "What happens right after a user writes?" Name read-your-writes and give one concrete fix (leader reads for recent writers, or LSN tracking).
• "Sync or async?" Explain the trade-off in plain terms: write latency versus losing recent writes on failover. "Wait for any one of N" is a strong answer.
• "What happens when the leader fails?" Walk through detection, promotion, redirecting writes, and fencing the old leader. Mention split brain.
• "Do replicas help with writes?" No, and saying so clearly is better than hoping it doesn't come up.

And "replicas aren't backups" is a short, memorable point that shows you've thought about operations.

Step 16 of 17 · 2 min

Try these yourself

Step 17 of 17 · 1 min

Further reading

• Martin Kleppmann, Designing Data-Intensive Applications, Chapter 5, "Replication." The clearest treatment of lag anomalies and failover problems anywhere.
• PostgreSQL documentation, the chapter "High Availability, Load Balancing, and Replication," especially the sections on streaming replication, synchronous replication and hot standby.
• PostgreSQL documentation on continuous archiving and point-in-time recovery.
• Documentation for an automated failover tool such as Patroni, to see how detection, promotion and fencing are handled in practice.
• GitLab, "Postmortem of database outage of January 31," 2017.
• PostgreSQL documentation, pg_rewind, and "Synchronous Replication" in the High Availability chapter.