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.
On this page
- The problem: one database is one bad day away from an outage
- The idea: one leader takes the writes, followers copy it
- How many followers do you need? A worked example
- Synchronous or asynchronous: when does the leader say "done"?
- Replication lag and the bugs it causes
- Failover: when the leader dies
- Other topologies: multi-leader and leaderless
- When replication is the wrong tool
- In the interview
- 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
- 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
- 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.
- 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:
| Method | What is sent | Catch |
|---|---|---|
| Statement-based | The 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" records | Exact 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).
| Step | Number |
|---|---|
| Budget per node (70% of 10,000/s) | 7,000 / s |
| Spent by each follower applying the leader's writes | 2,000 / s |
| Left for reads on each follower | 5,000 / s |
| Followers needed for 40,000 reads/s | 40,000 ÷ 5,000 = 8 |
| Plus one spare, so losing or upgrading a node doesn't overload the rest | 9 |
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
- 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:
| Mode | Leader replies after | Data lost if the leader dies | Write latency | Used by |
|---|---|---|---|---|
| Async | Its own disk write | The last few ms to seconds of confirmed writes | Lowest | Default in MySQL, Postgres, Redis |
| Semi-sync | One follower (of several) confirms | None, unless that follower dies too | + one round trip to the fastest follower | MySQL semi-sync; Postgres synchronous_standby_names = 'ANY 1 (...)' |
| Majority (consensus) | A majority of replicas confirm | None while a majority survives | + one round trip to the median replica | Raft/Paxos systems: etcd, CockroachDB, Spanner, MongoDB with w: "majority" |
| Sync to all | Every follower confirms | None | Slowest follower, and stalls if one is down | Rare |
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
- Copying takes a moment. For a split second, a follower may still have old data. That delay is called replication lag.
- 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.
| Anomaly | What the user sees | Fix |
|---|---|---|
| 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 violation | An 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
}
}
}# After a write: remember the leader's log position in the user's session.
def update_name(user_id, name, session):
with primary.cursor() as cur:
cur.execute("UPDATE users SET name = %s WHERE id = %s", (name, user_id))
primary.commit()
with primary.cursor() as cur:
cur.execute("SELECT pg_current_wal_lsn()::text")
session["min_lsn"] = cur.fetchone()[0]
primary.commit()
# On a read: use a follower only if it has replayed past that position.
def read_user(user_id, session):
conn = pick_replica()
min_lsn = session.get("min_lsn")
if min_lsn and not caught_up(conn, min_lsn):
conn = primary # this follower is behind the user's own write
with conn.cursor() as cur:
cur.execute("SELECT id, name FROM users WHERE id = %s", (user_id,))
return cur.fetchone()
def caught_up(conn, min_lsn):
with conn.cursor() as cur:
cur.execute("SELECT pg_last_wal_replay_lsn() >= %s::pg_lsn", (min_lsn,))
row = cur.fetchone()
return bool(row and row[0]) # NULL (not a standby) counts as falseThe 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
- 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:
- 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.
- 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).
- 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.
| Topology | Good at | Weak at | Use when |
|---|---|---|---|
| Single leader | Simple; no write conflicts; strong reads from the leader | Write throughput capped by one node; failover pause; remote users pay latency on writes | The default for almost everything |
| Multi-leader | Fast local writes in every region; a region can fail and others keep writing | Conflicts on the same record; hard to reason about | Multi-region apps, offline-first apps (every phone is a "leader"), collaborative editing |
| Leaderless | No failover at all; tolerates slow or dead nodes; very high write throughput | Only eventual consistency by default; conflicts need versioning; read repair overhead | Huge write-heavy data where some staleness is fine: events, time series, carts |
| Consensus (Raft/Paxos) | No lost writes on failover; no split brain | Every write waits for a majority; cross-region writes cost 50–150 ms | Metadata, 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 orderswithout aWHEREreaches 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
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.
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.
- 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.
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?
This is a read-your-writes violation, and only the writer needs the fresh value. Routing their own reads to the leader (or to a follower past their write's LSN) fixes it cheaply. Synchronous replication to every follower would slow all writes and stall them when one follower is down; caching from a follower can make the stale value last longer.
2. A database uses asynchronous replication. The leader crashes and the most up-to-date follower is promoted. What can go wrong?
Async means "confirmed" only meant "on the leader's disk". Anything not yet streamed is missing on the promoted follower. Semi-sync or majority commits close that window.
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?
Each follower spends 3,000/s applying writes, leaving 6,000/s for reads: 30,000 ÷ 6,000 = 5. Dividing 30,000 by 9,000 gives about 4, but ignores that replicas repeat every write.
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?
Time appears to move backwards because consecutive reads hit followers at different points in the log: a monotonic-reads violation. Sticking a user to one follower (or tracking the newest position they have seen) 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?
Only one side of a partition can hold a majority, and epoch (fencing) checks stop a stale leader that hasn't noticed it was replaced. A shorter timeout makes needless failovers, and so split brain, more likely.
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?
LWW keeps the version with the latest timestamp and throws the other away without telling anyone. Machine clocks drift, so "latest" may not be the true last write. Home each record to one region, or use mergeable data types, when that matters.
7. An engineer accidentally runs DELETE FROM orders with no WHERE clause on the leader. How do the followers help?
Replication copies mistakes as faithfully as good writes. Point-in-time recovery from backups (or a deliberately delayed replica) is the protection against human error.
8. Why do most teams choose semi-synchronous replication instead of synchronous replication to every follower?
Waiting for all followers ties write availability to the weakest one. Waiting for one confirmation means a confirmed write exists on two machines, at the cost of one fast round trip. Other followers can still lag.
Score 60% or more and the lesson is marked complete.