Storing data · Chapter 7

Replication

Keep copies of your data on several machines: leader-follower, multi-leader and leaderless replication, sync vs async, replication lag bugs, failover and split brain.

IntermediateInterview priority: High50 min
On this page
  1. The problem: one database is one bad day away from an outage
  2. The idea: one leader takes the writes, followers copy it
  3. How many followers do you need? A worked example
  4. Synchronous or asynchronous: when does the leader say "done"?
  5. Replication lag and the bugs it causes
  6. Failover: when the leader dies
  7. Other topologies: multi-leader and leaderless
  8. When replication is the wrong tool
  9. In the interview
  10. Quiz

The problem: one database is one bad day away from an outage

A shop runs on a single Postgres server. It handles 20,000 reads and 1,000 writes a second, and for a while that is fine. Then three things happen in the same quarter:

  • The machine dies. A power supply fails at 3 a.m. Nothing can read or write until someone rebuilds the server, and every order placed since the last backup is at risk.
  • Reads outgrow it. A marketing campaign doubles traffic. Almost all of it is people browsing (reads), but there is only one machine to answer them.
  • Users are far away. Customers in Sydney talk to a database in Virginia. Every query pays a 150–200 ms round trip before any work starts.

One server: too busy, and a single point of failure

  1. One database server has two problems. It can only answer so many queries, and if it dies, your data is unreachable. Replication keeps copies of the same data on several servers.

Replication means keeping a copy of the same data on several machines, called replicas. It solves all three problems at once: if one copy dies another takes over (availability and durability), reads spread across copies (read scaling), and a copy can sit near distant users (latency). The price is that copies are never perfectly in step, and most of this lesson is about what that costs you.

The idea: one leader takes the writes, followers copy it

The most common setup is leader-follower replication (also called primary-replica or primary-standby). One replica, the leader, accepts every write. It records each change in a log and streams that log to the other replicas, the followers, which apply the same changes in the same order.

Writes go to the leader; reads can go to any follower

  1. One copy is the leader, sometimes called the primary. Every write goes to the leader, which then sends each change on to its followers, also called replicas.
  2. Reads can go to any follower. Most apps read far more than they write, so adding followers multiplies how many reads you can handle. That is read scaling.

Because every follower applies the same changes in the same order, they all end up with the same data. Because only one node accepts writes, two writes can never conflict: the leader decides their order. That is why this design is the default in Postgres, MySQL, SQL Server, MongoDB replica sets and Redis.

What actually gets shipped

The leader does not send "the database". It sends a stream of changes, in one of three forms:

MethodWhat is sentCatch
Statement-basedThe SQL text, e.g. UPDATE ... SET seen_at = NOW()Non-deterministic functions (NOW(), RAND()) give different results on each replica. Mostly abandoned for this reason.
Physical (WAL shipping)The leader's write-ahead log: low-level "these bytes changed on this disk page" recordsExact and fast, but every replica must run the same database version. Postgres streaming replication works this way.
Logical (row-based)"Row 42 in users changed: name = 'Asha K.'"Works across versions, and other tools can read it. MySQL's row-based binlog and Postgres logical replication. This same stream feeds change data capture (CDC) into caches, search indexes and Kafka.

A write-ahead log (WAL) is the file a database appends every change to before it changes the data itself, so it can recover after a crash. Replication reuses that log: each follower tracks its position in it (a log sequence number, LSN, in Postgres; a GTID in MySQL), so "how far behind is this follower?" is a precise question with a precise answer.

How many followers do you need? A worked example

Followers multiply read capacity, but not write capacity: every follower has to apply every write, so writes cost each follower the same as they cost the leader.

Say the app does 40,000 reads and 2,000 writes per second. One node can handle about 10,000 queries a second, and you want to run each one at no more than 70% busy, so each node's budget is 7,000 per second. Treat applying a replicated write as roughly as expensive as running a query (a simplification, but a useful one).

StepNumber
Budget per node (70% of 10,000/s)7,000 / s
Spent by each follower applying the leader's writes2,000 / s
Left for reads on each follower5,000 / s
Followers needed for 40,000 reads/s40,000 ÷ 5,000 = 8
Plus one spare, so losing or upgrading a node doesn't overload the rest9

Now double the writes to 4,000 per second. Each follower has only 3,000 a second left for reads, so you need 14 followers, and the leader itself is at 57% just on writes. Replication buys you reads; when writes approach what one leader can take, the answer is sharding, not more followers.

Synchronous or asynchronous: when does the leader say "done"?

The key setting is when the leader confirms a write to the client.

Sync waits for followers; async answers at once

  1. You can choose synchronous replication, where a write waits until followers confirm: no lag, but slower writes. Or asynchronous, the usual default: fast writes, but followers can trail a little behind.
  • Asynchronous: the leader writes locally, replies "done", and ships the change afterwards. Writes are fast and a slow follower cannot slow anyone down. But if the leader dies before shipping, the writes it already confirmed are lost.
  • Synchronous: the leader replies only after followers confirm they have the change. A confirmed write survives the leader's death. But every write waits for the slowest follower, and if a follower is down, writes stop.

Nobody runs fully synchronous to every follower in practice, because one sick follower would freeze all writes. The real choices are:

ModeLeader replies afterData lost if the leader diesWrite latencyUsed by
AsyncIts own disk writeThe last few ms to seconds of confirmed writesLowestDefault in MySQL, Postgres, Redis
Semi-syncOne follower (of several) confirmsNone, unless that follower dies too+ one round trip to the fastest followerMySQL semi-sync; Postgres synchronous_standby_names = 'ANY 1 (...)'
Majority (consensus)A majority of replicas confirmNone while a majority survives+ one round trip to the median replicaRaft/Paxos systems: etcd, CockroachDB, Spanner, MongoDB with w: "majority"
Sync to allEvery follower confirmsNoneSlowest follower, and stalls if one is downRare

In the same data centre a round trip is about 0.5 ms, so semi-sync costs little. Across regions it is 50–150 ms per write, which is why cross-region replicas are almost always async.

Postgres: what "synchronous" actually means

Postgres lets you choose how far the standby must get before the commit returns, with synchronous_commit: remote_write (the standby received the change and handed it to its operating system), on (the standby flushed it to disk) and remote_apply (the standby has replayed it, so a read on that standby will see it). Only remote_apply removes replication lag for reads on that standby; the others protect durability but a read there can still be stale for a moment. If the synchronous standby disappears, commits wait (they do not fall back to async on their own), which is the "sync stalls writes" risk in practice. MySQL semi-sync, by contrast, falls back to async after a timeout, which keeps writes flowing but quietly reopens the loss window.

Replication lag and the bugs it causes

With async replication, a follower is always a little behind the leader. That gap is replication lag. It is usually a few milliseconds, but it grows to seconds or minutes when the leader is under heavy write load, when a follower runs a long query, or after a big batch update, and it is unbounded if a follower stalls. Systems that let lag exist are eventually consistent: stop writing and the copies converge, but at any moment a read may be stale.

Lag, and the bug every team ships once

  1. Copying takes a moment. For a split second, a follower may still have old data. That delay is called replication lag.
  2. Lag causes a classic bug: you change your profile, the page reloads from a follower that hasn’t caught up, and your change seems lost. The fix is called read-your-writes: for a little while after you write, read your own data from the leader.

Lag causes three classic anomalies. Each has a name worth knowing for interviews.

AnomalyWhat the user seesFix
Read-your-writes violation"I changed my name, the page reloaded, and the old name is back."Read the user's own data from the leader for a while after they write, or wait until the follower has caught up to their write (below).
Monotonic reads violation"My new comment appeared, I refreshed, and it vanished." Two reads hit two followers with different lag, so time seems to go backwards.Pin each user's reads to one follower (for example by hashing the user ID), or track the newest position the user has seen.
Consistent prefix violationAn observer sees an answer before the question it replies to.Keep causally related writes in the same log (same leader or same partition), or carry version information with reads.

Read-your-writes, done properly

"Read from the leader for 10 seconds after any write" is simple and common, but the window is a guess: lag above 10 seconds still breaks it, and a user who writes constantly pins all their reads to the leader. A precise version remembers how far the leader's log had got when the user wrote, and only reads from a follower that has replayed at least that far:

// After a write: remember the leader's log position in the user's session.
String updateName(long userId, String name) throws SQLException {
    try (Connection c = primary.getConnection();
         PreparedStatement ps = c.prepareStatement("UPDATE users SET name = ? WHERE id = ?");
         Statement st = c.createStatement()) {
        ps.setString(1, name);
        ps.setLong(2, userId);
        ps.executeUpdate();                                  // auto-commit: committed here
        try (ResultSet rs = st.executeQuery("SELECT pg_current_wal_lsn()::text")) {
            rs.next();
            return rs.getString(1);                          // store as session.minLsn
        }
    }
}

// On a read: use a follower only if it has replayed past that position.
User readUser(long userId, String minLsn) throws SQLException {
    DataSource source = replicas.pick();
    if (minLsn != null && !caughtUp(source, minLsn)) source = primary;
    try (Connection c = source.getConnection();
         PreparedStatement ps = c.prepareStatement("SELECT id, name FROM users WHERE id = ?")) {
        ps.setLong(1, userId);
        try (ResultSet rs = ps.executeQuery()) {
            return rs.next() ? new User(rs.getLong("id"), rs.getString("name")) : null;
        }
    }
}

boolean caughtUp(DataSource replica, String minLsn) throws SQLException {
    try (Connection c = replica.getConnection();
         PreparedStatement ps = c.prepareStatement("SELECT pg_last_wal_replay_lsn() >= ?::pg_lsn")) {
        ps.setString(1, minLsn);
        try (ResultSet rs = ps.executeQuery()) {
            return rs.next() && rs.getBoolean(1);            // NULL (not a standby) counts as false
        }
    }
}

The extra check costs one quick query; real systems cache each follower's replay position for a few milliseconds instead of asking every time. MongoDB offers the same idea built in as causally consistent sessions, and some proxies (ProxySQL with GTID tracking, for example) do it for MySQL.

Failover: when the leader dies

If a follower dies, little happens: it reconnects, catches up from its last log position, and carries on. If the leader dies, writes stop until a follower is promoted. That is failover.

A follower is promoted to leader

  1. And if the leader crashes? A follower is promoted to become the new leader. That is failover. Done automatically, users barely notice.

Automatic failover has three steps, and each one can go wrong:

  1. Detect that the leader is dead, usually by missed heartbeats for 10–30 seconds. Too short a timeout and a busy leader that pauses for garbage collection or a slow disk gets replaced for nothing (and may flip back and forth, "flapping"). Too long and every second is a write outage.
  2. Choose a new leader: normally the follower with the most recent data. The choice must be agreed by a majority, otherwise two parts of the system may pick different leaders. Tools: Patroni with etcd for Postgres, Orchestrator for MySQL, Redis Sentinel, or the database's own election (MongoDB, Raft-based systems).
  3. Reconfigure clients to write to the new leader (by updating DNS, a virtual IP, a proxy or a service-discovery entry), and make sure the old leader cannot accept writes if it comes back.

End to end, managed services typically fail over in roughly 30–120 seconds (for example, Amazon RDS Multi-AZ is usually one to two minutes, Aurora often under a minute). "Users barely notice" is true only if clients retry well.

Async failover loses data. With asynchronous replication, the promoted follower is missing whatever the old leader confirmed but hadn't shipped. Those writes are gone, and if the old leader comes back its extra writes conflict with the new history. A real example: in a 2012 GitHub incident, an out-of-date MySQL follower was promoted and handed out auto-increment IDs that the old leader had already used. Those IDs were also stored in Redis, so some private data was shown to the wrong users.

Other topologies: multi-leader and leaderless

Single-leader replication has one weak spot: all writes go through one node, often in one region. Two other designs remove it, at a cost.

Multi-leader (active-active): several nodes accept writes, typically one leader per region, and they replicate to each other asynchronously. A user in Frankfurt writes to the Frankfurt leader at local speed. The problem is write conflicts: two people edit the same record in two regions within the same second, and both leaders accept it. Ways to resolve conflicts:

  • Last write wins (LWW): keep the version with the later timestamp. Simple, and used by DynamoDB global tables and Cassandra, but it silently drops the other write, and clock skew between machines can make the older write "win".
  • Merge: data types designed to merge automatically, called CRDTs (conflict-free replicated data types), such as counters that add up both sides' increments, or sets that keep both sides' items. Collaborative editors and Riak use these.
  • Ask the application (or the user) to resolve the conflict, as CouchDB and Git do.
  • Avoid conflicts: give each record a home region, and route every write for that record there. This is the most common choice in practice: multi-leader for the system, single-leader per record.

Leaderless (Dynamo-style): there is no leader at all. The client (or a coordinator node) sends each write to all N replicas and treats it as successful once W of them confirm; a read asks R replicas and takes the newest version. Replicas that missed a write are repaired when a read notices the difference (read repair), by a background comparison (anti-entropy, usually with Merkle trees: hash trees that let two nodes find differing ranges quickly) and by hinted handoff (a neighbour holds writes for a down replica and passes them on when it returns). Cassandra, ScyllaDB and Riak work this way. The N/R/W maths and what it does and does not guarantee are covered in consistency and CAP.

TopologyGood atWeak atUse when
Single leaderSimple; no write conflicts; strong reads from the leaderWrite throughput capped by one node; failover pause; remote users pay latency on writesThe default for almost everything
Multi-leaderFast local writes in every region; a region can fail and others keep writingConflicts on the same record; hard to reason aboutMulti-region apps, offline-first apps (every phone is a "leader"), collaborative editing
LeaderlessNo failover at all; tolerates slow or dead nodes; very high write throughputOnly eventual consistency by default; conflicts need versioning; read repair overheadHuge write-heavy data where some staleness is fine: events, time series, carts
Consensus (Raft/Paxos)No lost writes on failover; no split brainEvery write waits for a majority; cross-region writes cost 50–150 msMetadata, locks, money, and "NewSQL" databases (Spanner, CockroachDB)
How consensus replication differs from "leader plus semi-sync"

In a Raft group, a leader is elected by a majority for a numbered term (the epoch), and a write is committed only when a majority has it in their logs. Any future leader must have every committed entry, because winning an election requires votes from a majority, and every majority overlaps the majority that stored the entry. That overlap is what makes failover lossless and split brain impossible: an old leader in the minority cannot commit anything. The cost is a majority round trip on every write, and losing a majority (2 of 3 nodes) halts writes entirely. CockroachDB, TiDB and YugabyteDB run one Raft group per range of keys, which combines this with sharding.

When replication is the wrong tool

  • Writes are the bottleneck. Every follower repeats every write, so more followers do nothing for write load. Shard instead.
  • You need protection from mistakes. A replica is not a backup. DELETE FROM orders without a WHERE reaches every follower in milliseconds. You still need backups with point-in-time recovery; a delayed replica (one deliberately kept an hour behind) is a useful extra.
  • Every read must be fresh. If the business can't tolerate stale reads anywhere, sending reads to followers creates bugs you must then patch one by one. Scale the leader, cache carefully, or use a consensus database.
  • The app is small. A primary with one standby for failover is enough. Read replicas add lag bugs and operational work; add them when the leader's read load is measured to need it.

In the interview

Your turn: spot the bugs

A team runs a Postgres leader with two async followers. Automatic failover triggers after 3 seconds of missed heartbeats. To reduce load, the app sends every read to a follower, including the order page users are redirected to right after checkout, which sometimes shows "Order not found". Last week a failover happened during a traffic spike; orders placed in the last second before it vanished, and the old leader came back after a reboot and accepted writes for two minutes. List what is wrong and how you would fix it.

Compare with our answer

A strong answer mentions:

  • Read-your-writes: the redirect reads from a follower that hasn't replayed the insert. Read the new order from the leader (or wait for the follower's LSN to pass the write's).
  • Async loss: confirmed orders can be lost on failover. Use semi-sync (one follower must confirm) for the order database.
  • A 3-second timeout is too aggressive: a spike or GC pause can trigger needless failovers. Use 10–30 s, with a majority-based coordinator such as Patroni plus etcd.
  • Split brain: the old leader rejoined as a writable leader. It must be fenced (STONITH, epoch checks) and rejoin as a follower, after rewinding its divergent writes (pg_rewind).
  • Reconcile the two minutes of divergent writes, using the old leader's log to find them.
Your turn: going global

A social app has users in Europe and North America, 50/50. Profile edits are rare, posts are frequent, and users hate 150 ms delays. The current design is one leader in Virginia with followers in Virginia. Propose a replication design and say what consistency each kind of data gets.

Compare with our answer

One good shape: keep the leader-per-record model but home each user to a region. A European user's writes go to the Frankfurt leader for their shard; Virginia holds async followers of it, and vice versa. Reads are served locally from followers.

A strong answer mentions:

  • Local followers in each region for reads, so browsing is fast everywhere.
  • Writes go to the record's home region (no conflicts), with a cross-region hop only when a user writes to someone else's data, such as a comment on a US post.
  • Read-your-writes for a user's own profile and posts, via the home leader or LSN tracking.
  • Async cross-region replication, accepting a second or so of lag for other people's content.
  • What happens when a region fails: promote the other region's follower and accept losing the last moments of async writes, or use a consensus store for the few things that cannot be lost.
Replication on one page
  • Why: survive machine and zone failures, scale reads, put data near users. Not a backup: mistakes replicate too.
  • Leader-follower is the default: one writer, followers apply its log (WAL, row-based binlog) in order.
  • Followers scale reads, not writes: each one repeats every write. Worked rule: reads ÷ (node budget − write rate) = followers, plus one spare.
  • Async = fast, loses the last confirmed writes on failover. Semi-sync = one follower must confirm. Majority (Raft) = lossless, a quorum round trip per write.
  • Lag is ms normally, seconds under load. Anomalies: read-your-writes, monotonic reads, consistent prefix. Fixes: read own data from the leader, pin users to a replica, LSN/GTID tracking.
  • Failover: detect (10–30 s), elect by majority, repoint clients, fence the old leader. Typically 30–120 s end to end.
  • Split brain: majority election, leases, fencing tokens/epochs, STONITH. Odd number of voters in three zones.
  • Multi-leader: local writes per region, but conflicts: LWW (drops data), CRDTs, or home each record to one region.
  • Leaderless: write W of N, read R of N, read repair, hinted handoff, anti-entropy.
Interactive labPractise it: add followers, fix lag bugs and crash the leaderOpens in the ByteAtlas lab. Your progress is saved in this browser.
Check what stuck

1. A user changes their display name, the page reloads, and the old name appears; a second refresh shows the new one. Reads go to async followers. What is the most targeted fix?

2. A database uses asynchronous replication. The leader crashes and the most up-to-date follower is promoted. What can go wrong?

3. An app does 30,000 reads/s and 3,000 writes/s. Each node can handle 9,000 queries/s within its target, and every follower must also apply all writes. How many followers are needed for the reads, before adding a spare?

4. A user posts a comment, sees it, refreshes and it is gone, then refreshes again and it is back. The load balancer spreads reads across three async followers. What is happening and what fixes it?

5. After a network partition, the old leader and a newly promoted follower both accept writes for the same tables. Which change prevents this?

6. A company runs multi-leader replication across two regions with last-write-wins conflict resolution. Two editors change the same product title in different regions within a second. What is the risk?

7. An engineer accidentally runs DELETE FROM orders with no WHERE clause on the leader. How do the followers help?

8. Why do most teams choose semi-synchronous replication instead of synchronous replication to every follower?

Score 60% or more and the lesson is marked complete.

Passing the quiz also marks it complete. Progress is saved in this browser only.