Data replication

Data replication means keeping a copy of the same data on several machines, so that losing one machine loses nothing and many machines can serve reads. Copying is the easy part. Data keeps changing, so every design must answer three questions: who accepts writes, how long a write waits for its copies, and what readers see while the copies disagree.

Picture it first: one notice office, many boards

Your college has one notice office, and only the office writes notices. A runner carries photocopies of each new notice to the boards in every hostel and department, and students read whichever board is nearest.

• Thousands of students can read at once, and nobody crosses the campus to check a notice.
• If the office’s files are ruined, every board still holds a copy.
• The runner is sometimes slow. A student who corrects her exam slot at the office and walks back to her hostel board still sees the old slot.
• If the office burns down, a board-keeper becomes the new office. Any notice the runner had not delivered yet is gone.

Now change the rules. If every building gets its own office, students post notices nearby, but two offices may publish different dates for the same exam. With no office at all, whoever posts a notice pins it on two of the three boards, and readers check two boards and trust the newer notice.

Map it back: the office is the leader (also called the primary or source), the boards are followers (replicas, standbys), the runner is the replication stream, and each photocopy is a record in the replication log. The three rule sets are single-leader, multi-leader and leaderless replication.

Why replicate at all

Replication pays for four different things. Know which one you are buying, because they pull the design in different directions.

Availability. If your only database server dies, every write fails until it is repaired or restored. A follower that already holds the data can be promoted in seconds; restoring multi-terabyte backups took GitHub hours in its October 2018 incident.

Read scaling. Say a food-delivery app’s order-tracking service sees 40,000 reads/s and 2,000 writes/s at dinner time, and one server comfortably handles about 10,000 queries/s (illustrative numbers). Reads alone need 40,000 ÷ 10,000 = 4 servers’ worth of capacity, so you run one leader and four read replicas. But every replica still applies all 2,000 writes/s: replication multiplies read capacity, never write capacity. For writes you need sharding.

Latency and locality. Light in fibre costs about 10 ms of round trip per 1,000 km. Mumbai to Frankfurt is about 6,500 km in a straight line, so a European user reading from Mumbai pays at least 65 ms per round trip. A replica in Frankfurt removes that for reads.

Durability. Copies in different availability zones mean a committed write survives the loss of a disk, a machine or a whole zone, provided the commit waited for a copy.

Replication copies the whole dataset to every replica; sharding splits it so each node holds a part. Real systems combine them, with each shard running as a small replicated group (see database scaling).

Three ways to arrange replicas

Single leader

One node, the leader, accepts every write. It records each change in its log and streams the log to followers, which apply the changes in the same order. Clients read from the leader or any follower.

Because one node orders all writes, replicas never disagree about what happened, only about how far they have got. Concurrent writes to one row are settled on the leader by ordinary locking or MVCC, as on a single machine. This is the default in PostgreSQL, MySQL and MongoDB replica sets, and the right starting point for almost every transactional design.

You pay with a single write bottleneck and a failover whenever the leader dies.

Multi-leader

Several nodes accept writes, typically one per region (or per device in an offline-first app), and each forwards its changes to the others asynchronously. Mumbai users write to the Mumbai leader at local latency, and a cut-off region keeps taking writes.

The price is conflicts. If a customer edits her delivery address in Mumbai while a support agent edits the same order in Frankfurt, both leaders accept the change and only discover the clash when they exchange logs.

Leaderless

No node orders the writes. The client, or a coordinator acting for it, sends each write to several replicas and counts it successful once enough acknowledge; reads ask several replicas and keep the newest version. Amazon’s Dynamo popularised the design; Cassandra, ScyllaDB and Riak follow it. There is never a failover, which is great for write availability, and you pay with versioning, conflict handling and background repair.

Synchronous, asynchronous and semi-synchronous

The leader has committed a write locally. When does it tell the client “OK”? That one decision sets your write latency and how much data a failover can lose.

• Asynchronous: reply once the local log is flushed; ship to followers afterwards. Fastest, and a slow follower never slows the leader, but if the leader dies before shipping, acknowledged writes are gone.
• Synchronous: wait until followers confirm. Nothing acknowledged is lost with the leader, but every commit pays a round trip, and an unreachable follower stalls every commit.
• Semi-synchronous: wait for some followers, usually one, to store the change; the rest are asynchronous. Most production “synchronous” setups are really this.

PostgreSQL: synchronous_commit and synchronous_standby_names

PostgreSQL streaming replication is asynchronous by default, with lag typically under one second when the standby keeps up. Name standbys in synchronous_standby_names on the primary to make it synchronous; synchronous_commit then decides how far each commit waits:

Without synchronous_standby_names, only on and off differ; the remote levels behave like on and just wait for the local flush.

The standby list takes a count and a method:

# postgresql.conf on the primary
synchronous_standby_names = 'ANY 1 (replica_b, replica_c)'     # quorum: any 1 of the 2
# synchronous_standby_names = 'FIRST 1 (replica_b, replica_c)' # priority: b, else c
synchronous_commit = on

FIRST waits for the highest-priority connected standbys and slides to the next name if one disconnects. ANY waits for any k of the listed standbys, so the fastest set the pace. List more standbys than you wait for: if a required synchronous standby crashes, commits waiting on it may never complete.

BEGIN;
SET LOCAL synchronous_commit = off;   -- this transaction only
INSERT INTO chat_messages (room_id, sender_id, body) VALUES (42, 7, 'on my way');
COMMIT;

MySQL: semi-synchronous replication

MySQL replication is also asynchronous by default. Semi-synchronous replication makes the source wait until rpl_semi_sync_source_wait_for_replica_count replicas (default 1) acknowledge that they have received and logged the transaction. Where it waits matters:

• AFTER_SYNC (the default): sync the binary log, wait for a replica’s acknowledgement, then commit to the storage engine. No client sees the transaction before a replica has it, so failing over to that replica loses nothing.
• AFTER_COMMIT: commit first, wait afterwards. Other clients can read the row before any replica holds it; if the source dies then, they saw data the new source never had.

What actually gets shipped

“Ship the log” hides a real choice: which log, at what level of detail?

Statement-based. The leader ships the SQL text and each follower re-executes it. The log is tiny, but anything nondeterministic breaks it: MySQL lists UUID(), RAND(), SYSDATE() and USER() among the functions that cannot be replicated safely this way, and it logs a warning when it meets an unsafe statement. MySQL’s STATEMENT binlog format works like this.

Physical (WAL shipping). PostgreSQL streaming replication sends the write-ahead log itself: low-level records saying which bytes changed in which page. The standby becomes a byte-exact copy, indexes included, with no SQL re-executed. In exchange, both sides must run the same major version on the same hardware architecture, so it cannot carry you across a major-version upgrade.

Logical, or row-based. The leader ships one record per changed row, identified by its key: “orders row 17: token became b7c2…”. MySQL’s ROW format (the default) and PostgreSQL’s logical replication work this way. Row images are exact, so nondeterminism stops mattering, and other software can read them: they are what change data capture tools consume. The cost is volume: an UPDATE touching a million rows logs a million row images.

Two details catch teams out. MySQL’s MIXED format logs statements but switches to rows for unsafe ones, and changing binlog_format at all is deprecated: rows are expected to become the only format. PostgreSQL’s logical replication does not carry DDL or sequence values, so apply schema changes to subscribers yourself and bump sequences before failing over to one.

Adding a new follower

You cannot copy a busy database with a plain file copy, because it changes underneath you. Every engine uses the same two-step recipe:

1. Take a consistent snapshot of the leader, tagged with the exact log position it corresponds to.
2. Restore it on the new node and replay the log from that position until the follower has caught up, then keep streaming.

In PostgreSQL the snapshot is a base backup, and the standby then streams WAL from the position recorded in it. If the primary recycles WAL before a standby has fetched it, that standby must start again from a fresh backup. A replication slot makes the primary keep every segment the standby still needs; the catch is that a standby that stays down for days makes the primary hoard WAL until its disk fills, so cap it with max_slot_wal_keep_size.

# On the new standby: copy the primary, write standby settings (-R), use a slot
pg_basebackup -h primary.internal -U replicator -D /var/lib/postgresql/data \
  -R --slot=replica_c --create-slot

MySQL can track positions as a binlog file name plus offset, but global transaction identifiers (GTIDs) are far easier. Every transaction gets an ID of the form source_uuid:sequence_number, such as 3E11FA47-…:23. With SOURCE_AUTO_POSITION = 1, a connecting replica sends the set of GTIDs it already has and the source sends everything missing, which also makes re-pointing replicas during a failover painless.

Replication lag and the three anomalies

With asynchronous followers, every replica is a slightly old copy of the leader. Usually the gap is milliseconds. It grows when a follower is overloaded, the network is slow, the leader runs one huge transaction, or a long query on a PostgreSQL hot standby conflicts with incoming changes: by default the standby pauses replay for up to 30 seconds (max_standby_streaming_delay) to let that query finish. Measure lag in seconds per replica (pg_stat_replication has lag columns) and alert on it.

Lag is harmless for most reads and baffling for some. Douglas Terry and colleagues named the guarantees users expect in their 1994 paper on session guarantees for Xerox PARC’s Bayou system. Three anomalies come up again and again.

Read-your-writes

A customer changes her delivery address from Flat 2A to Flat 4B and taps Save. The write goes to the leader; the page reloads and reads from a follower that is two seconds behind. She sees Flat 2A, assumes the save failed and types it all again, or the order goes to the old flat.

Read-your-writes (read-after-write consistency) promises that a user always sees their own earlier writes; it promises nothing about other users’ writes. Fixes, from blunt to precise:

• Read from the leader for anything the user can edit: their own profile, their own cart. Other people’s profiles can still come from a replica.
• Read from the leader for a short time after a write. Store the time of the user’s last write and send their reads to the leader for, say, 10 seconds, or longer than your p99 lag.
• Carry a log position. After the commit, record the leader’s WAL position (an LSN) or the transaction’s GTID. Before serving that user’s read, the replica checks it has replayed at least that far; if not, wait briefly or fall back to the leader. You only pay when the replica really is behind.
• Make the write wait for replay with synchronous_commit = remote_apply. Exact, but slow, and it only covers the synchronous standbys.

-- PostgreSQL. On the primary, right after the user's write commits:
SELECT pg_current_wal_lsn();          -- e.g. 0/5A1C2F8; keep it in the user's session
-- On a replica, before serving that user's read:
SELECT pg_last_wal_replay_lsn() >= '0/5A1C2F8'::pg_lsn AS caught_up;

-- MySQL. With session_track_gtids = OWN_GTID the client receives its commit's GTID.
-- On a replica: returns 0 once applied, 1 if the 1-second timeout expires first.
SELECT WAIT_FOR_EXECUTED_GTID_SET('3E11FA47-71CA-11E1-9E33-C80AA9429562:23', 1);

For users on several devices, keep the token server-side, keyed by user, not in one browser’s cookie.

Monotonic reads

A user watches an order. One refresh hits a fresh replica and shows “Out for delivery”; the next hits a lagging replica and shows “Preparing”. Time runs backwards.

Monotonic reads promises that once a user has seen newer data, they never see older data. The usual fix is a sticky replica per user: hash the user ID to pick a replica, and accept one possible jump if it dies and you reroute. Or remember the highest log position the user has seen and only read from replicas at or beyond it.

Consistent prefix reads

In a cricket app, ball-by-ball commentary is spread across shards that replicate independently. One shard stores “Bumrah to Smith, edged, caught at slip!” and another stores “Smith walks back for 41.” A reader whose replicas lag by different amounts sees Smith walking off before the edge.

Consistent prefix promises that if writes happen in a certain order, readers see them in that order: some prefix of the history, never a jumble. A single-leader database gives you this within one database, because followers apply the log in commit order. The anomaly appears when related writes travel through different logs: different shards or different leaders. Keep related writes in one partition (all commentary for a match on one shard, ordered by ball number), or attach causal metadata and hold a write back until what it depends on has arrived.

Failover and its traps

When the leader dies, a follower must take over. Every failover, manual or automatic, has three steps:

1. Decide the leader is dead. Nothing can tell a dead node from a slow one, so this is a timeout on missed heartbeats.
2. Choose a new leader, ideally the follower with the most recent data, agreed by a majority of a coordination group (see consensus and leader election).
3. Reconfigure. Clients send writes to the new leader, the other followers follow it, and the old leader must never act as leader again if it comes back.

Each step has a trap.

Lost asynchronous writes

With asynchronous replication, the new leader has only what reached it. Writes the old leader acknowledged but had not yet shipped are lost, even though clients were told they succeeded.

The window is easy to estimate: writes per second × lag. At 2,000 writes/s and half a second of lag, a crash discards about 1,000 acknowledged writes. Semi-sync shrinks that to zero for the replica that acknowledged, provided you promote that one.

If the old leader comes back holding those writes, do not simply merge them: its history has forked. PostgreSQL marks each promotion with a new timeline for this reason, and MySQL’s semi-sync documentation says a failed source should be discarded, not reused. Rebuild it as a follower and reconcile orphaned writes by hand if they matter. Watch for side effects too: if a cache or an email already used lost rows, the new leader may hand out the same auto-increment IDs again.

Split brain and fencing

If the old leader was cut off rather than dead, it may keep accepting writes while the new leader does too: two leaders, two diverging histories. The defences:

• Majority decisions. Promote only with agreement from a majority of a coordination group, so a node on the minority side of a partition can never be elected.
• Fencing. Stop the old leader from acting: cut its network, revoke its storage, or power it off (“STONITH”, shoot the other node in the head). Better still, give each promotion a higher epoch number and have storage and downstream services reject older epochs, like fencing tokens in distributed locking.
• Routing that follows the epoch, so connection pools drop the old leader the moment a new one exists.

Choosing the timeout

Too short, and a garbage-collection pause or load spike triggers a needless failover that throws away the async window, drops every connection and leaves the new leader with cold caches, possibly to stall in turn. Too long, and nobody can write while you wait. A common shape is a heartbeat every few seconds and failover after about three misses: tens of seconds without writes. Measure your real pauses before tightening it.

A real one: GitHub, October 2018

Maintenance cut the link between GitHub’s US East Coast network hub and its primary East Coast data centre for 43 seconds. In that window the failover tool (Orchestrator, which runs on Raft) formed a quorum without the East Coast site and promoted MySQL primaries on the West Coast. The East Coast primaries held a few seconds of writes that had never reached the West, and the West soon took writes the East never saw. Neither side could simply fail back. Choosing data integrity over uptime, GitHub ran degraded for 24 hours and 11 minutes while it restored from backups and rebuilt replication.

The lesson: automatic cross-region failover over asynchronous replication forks history, and the failover tool’s idea of a valid topology must match what the application tolerates. GitHub’s East Coast apps could not live with cross-country latency on every write.

Multi-leader and leaderless: every node takes writes

Handling multi-leader conflicts

Multi-leader setups pay off when users must write in several regions at local latency, or devices must work offline. Each needs a conflict plan, from best to worst:

• Avoid conflicts. Give each record a home region and send all its writes there; a Mumbai user’s orders are written in Mumbai. Conflicts can then only arise while a region fails over. This is the most practical design for multi-region active-active.
• Merge. Keep both versions and let the application combine them, so a cart becomes the union of both carts. CouchDB keeps every conflicting revision, shows one deterministic winner and expects the app to merge.
• Use mergeable data types (CRDTs): counters, sets and maps built so any two replicas merge to the same answer.
• Last write wins (LWW). Keep the version with the highest timestamp, as DynamoDB global tables do per item in their default mode. Simple and convergent, but it silently drops a write, and with skewed clocks “last” may not be latest.

The mechanics live in clocks, ordering and conflict resolution.

Leaderless in brief

A leaderless store sends every write to all N replicas of a key and calls it done after W acknowledgements; a read asks R replicas and keeps the newest version. If R + W > N, the replicas answering a read overlap those that took the latest write (the overlap argument is in CAP theorem and PACELC). Replicas that missed a write are fixed by read repair and background anti-entropy. Sloppy quorums, hinted handoff, vector clocks and the cases where quorums still return stale data are covered in quorums and leaderless replication.

Replication is not a backup

Replication copies every change, including the bad ones. Run DROP TABLE orders or an UPDATE without a WHERE on the leader, and every follower applies it within milliseconds; a corrupting bug replicates just as faithfully.

You still need point-in-time backups: periodic base backups plus an archive of the WAL or binlog, so you can restore to the second before the mistake. GitHub backed up MySQL every four hours and tested restores daily, yet a multi-terabyte rebuild still took hours.

A cheap extra layer is a delayed replica. PostgreSQL’s recovery_min_apply_delay = '1h' makes a standby replay each commit an hour late, so after a bad DROP you have an hour to stop replay and copy the table back. See disaster recovery for RPO and RTO planning.

Cheat sheet

How to say it in the interview

“I’d run the database as a single leader with two followers in other availability zones. Commits wait for any one of the two followers, semi-sync, so losing the leader loses nothing acknowledged and one slow follower can’t stall writes. Read-heavy pages go to followers, which can lag by a second, so a user’s own data comes from the leader for a few seconds after they write, or I pass the commit LSN and let the replica wait. Failover is automatic within the region, decided by a majority, with the old leader fenced. Across regions I keep async replicas and fail over by hand, and I keep point-in-time backups, because replication copies mistakes too.”

Why not make every follower synchronous? Every commit would wait for the slowest follower, and one dead follower would stall all writes. Waiting for any one of two or more gives the durability with far less latency risk.

How do you guarantee a user sees their own update? Route their reads of data they can edit to the leader for a short window after a write, or record the commit’s LSN or GTID and have the replica wait, or fall back to the leader if it is behind.

What happens to acknowledged writes when an async leader dies? Unshipped ones are lost: roughly writes per second × lag. Promote the most up-to-date follower, use semi-sync for writes that matter, and rebuild the old leader as a follower.

Statement-based or row-based replication? Row-based. Statements are smaller, but nondeterministic functions make replicas drift. Rows are exact and feed change data capture; MySQL defaults to ROW.

Do read replicas help with a write bottleneck? No. Every replica applies every write. Replicas scale reads; for writes you shard, batch, or move work off the hot path.

Where to go next

• Quorums and leaderless replication: N, R and W in depth, sloppy quorums, hinted handoff and repair.
• CAP theorem and PACELC: why replication forces a latency-versus-consistency choice.
• Failover: detection, promotion and testing failover before you need it.
• Consensus and leader election: how a majority picks one leader safely.
• Write-ahead logging: the log that physical replication ships.
• Change data capture: reading the row-level log from outside the database.
• Database scaling: where read replicas sit on the scaling ladder.
• Disaster recovery: backups, RPO and RTO, the things replication cannot give you.

References and further reading

1. PostgreSQL documentation: Log-Shipping Standby Servers (streaming and synchronous replication)postgresql.orgStreaming replication, typical sub-second lag, replication slots, FIRST and ANY synchronous standbys, LSN functions for measuring lag, and the warning that commits can hang if a synchronous standby crashes.
2. PostgreSQL documentation: Write Ahead Log settings (synchronous_commit)postgresql.orgWhat each synchronous_commit level waits for (off, local, remote_write, on, remote_apply) and what it protects against.
3. PostgreSQL documentation: Replication settingspostgresql.orgSyntax of synchronous_standby_names, the 30-second max_standby_streaming_delay default, and recovery_min_apply_delay for delayed standbys.
4. PostgreSQL documentation: Logical Replicationpostgresql.orgRow-level publish/subscribe replication, the initial snapshot plus change stream, and its restrictions (no DDL, no sequence values).
5. MySQL 8.4 Reference Manual: Replication Formatsdev.mysql.comStatement-based, row-based and mixed binary logging, ROW as the default, the deprecation of changing binlog_format, and functions unsafe for statement-based replication.
6. MySQL 8.4 Reference Manual: Configuring Semisynchronous Replicationdev.mysql.comAFTER_SYNC versus AFTER_COMMIT, the acknowledgement count, and the 10-second timeout after which the source falls back to asynchronous replication.
7. MySQL 8.4 Reference Manual: Functions Used with Global Transaction Identifiersdev.mysql.comWAIT_FOR_EXECUTED_GTID_SET for making a replica wait until it has applied a given transaction, and its return values.
8. October 21 post-incident analysis (GitHub, 2018)github.blogA 43-second network cut that triggered a cross-country MySQL failover, unreplicated writes on both sides, and 24 hours of degraded service.
9. Session Guarantees for Weakly Consistent Replicated Data (Terry et al., PDIS 1994)cs.cornell.eduThe original definitions of read-your-writes and monotonic reads from the Bayou project.
10. Galera Cluster documentation: Certification-Based Replicationmariadb.comHow a write-anywhere cluster orders write-sets globally and rolls back conflicting transactions at commit with a deadlock error.