Replication

Why copy data at all, and why a copy is not a backup

Replication means keeping the same data on more than one machine, and it does three separate jobs that are easy to blur together. It gives availability: when one machine dies another already holds the data. It gives read capacity: copies can serve queries so the original does not have to. And it gives locality: a copy in Frankfurt answers a European user in a few milliseconds instead of a 70 to 90 ms round trip to US East. Each job wants a different trade-off, which is why "we'll add a replica" is the start of a design conversation and not the end of one.

A replica is not a backup. Replication copies mistakes as faithfully as it copies data: a bad DELETE or a bug that corrupts rows reaches every follower within moments. Backups and point-in-time recovery protect against your own mistakes; replicas protect against hardware. This chapter takes the three families in order: one leader with followers, several leaders, and no leader at all. It spends its time on the places each one loses data, because that is what interviewers and incidents probe. How stale a follower's reads can be is covered in the consistency models chapter, and the quorum arithmetic behind leaderless stores is covered there too; both are linked here, not repeated.

Single leader: one place decides the order

In the most common arrangement one node, the leader (also called primary or source), accepts every write. It appends the change to a log and ships that log to followers (replicas, standbys), which apply the entries in the same order. Because there is one writer and one order, followers can never disagree about what happened, only about how far along they are. That single property is why this design dominates relational databases.

What exactly is in the log

The log style decides what else replication can do. A physical standby is the simplest and fastest way to get a failover copy. A logical stream can feed a different database, a search index or a cache invalidator, which is why change-data-capture tools read the logical log.

Synchronous or asynchronous: you are choosing what a crash may cost

The leader can tell the client "committed" at three moments: after its own disk flush, after a follower has the entry, or after a follower has applied it. The earlier it answers, the faster the write and the larger the window in which a leader crash destroys an acknowledged write. PostgreSQL exposes the whole range through synchronous_commit, and its manual states the baseline plainly: with asynchronous streaming, transactions that committed on the primary may not have reached the standby when it crashes, causing data loss.

Notice the last row. With plain on, the standby has the record on disk but may not yet have replayed it, so a read served from that "synchronous" standby can still be stale. Synchronous replication buys durability; only remote_apply also buys read-your-writes from the follower, at the cost of waiting for replay too. MySQL's version is semi-synchronous replication: the source waits until at least one replica has received the events and written them to its relay log on disk, and by default it waits after the binary log is synced but before the transaction commits in the storage engine. If no replica answers within the timeout, the source quietly reverts to asynchronous replication and returns to semi-synchronous when a replica catches up. Read that twice: your durability promise silently weakens in exactly the moment a replica is unhealthy.

The price of waiting

Waiting for a follower adds at least one network round trip to every commit. Using the planning round trips from the capacity chapter: a follower in the same availability zone adds about 0.5 ms, one in another zone 1 to 2 ms, and one across the US or to Western Europe 60 to 90 ms. A single connection that commits one transaction after another can then do at most 1 ÷ 0.08 = 12.5 commits a second against a distant synchronous standby, where the same connection could do hundreds locally. Concurrency and group commit recover throughput across many connections, but each user-visible write still pays the latency. That is why synchronous replication across regions is rare and synchronous replication across zones is common.

The second price is availability. If the single synchronous standby is down and the primary insists on its acknowledgement, writes stop. PostgreSQL's manual warns that such commits may never complete if a synchronous standby crashes. The standard fix is a quorum: ANY 1 (s1, s2) commits when either standby answers, while FIRST 1 (s1, s2) keeps a priority order and promotes the next standby into the synchronous role when the current one disconnects.

The independence assumption is generous, since standbys in one zone share fate, but the shape is right: one mandatory synchronous follower costs availability, and a quorum of followers gives the durability back without the cost.

Failover: promoting a follower is easy, doing it safely is the problem

When the leader fails, a follower must take over: something detects the failure, picks the follower that is furthest along, promotes it, and points clients and the remaining followers at it. PostgreSQL exposes promotion (pg_ctl promote or pg_promote()) and leaves the decision about when to external tooling or a managed service. In MySQL Group Replication a single-primary group elects a new primary automatically, preferring members on the lowest server version, then by member weight (default 50), then by lowest server UUID, and clients still have to be re-routed, by your proxy or by MySQL Router in an InnoDB Cluster.

Hazard 1: lost writes

With asynchronous replication the new primary is missing whatever the old one had acknowledged but not shipped. The size of that loss is lag times write rate: 0.040 s × 5,000 writes a second (the planning write rate for one relational primary) is 200 writes. Choosing the most advanced follower (highest log position) minimises the loss; a synchronous or quorum standby drives it to zero for the acknowledged writes it covers, provided the failover tool can compare the standbys' log positions and promote one that holds every acknowledged write. With a quorum of 1 out of 2, a promoted standby that happens to be the lagging one would silently drop acknowledged writes. The lost writes are not gone from the world: they sit on the old primary's disk, and someone has to decide whether to replay, discard or hand them back to users.

Hazard 2: two primaries

A false failure is worse than a real one. A network partition, a long garbage-collection pause or an overloaded primary that misses its heartbeat looks identical to a crash from outside. The failover tool promotes the standby, the old primary is still alive and still has clients connected, and now both accept writes. The distributed consensus chapter explains why quorum elections do not stop a stale leader from acting on resources outside the quorum, and why a fencing token is the cure. For databases the practical forms are:

• Shoot the old primary (power it off, cut its network port, revoke its storage lease) before the promotion completes.
• Make the old primary demote itself when it loses its lease, so it stops by its own clock.
• Never let it rejoin as a primary. After promotion the cluster has moved to a new timeline, and the old primary holds writes that exist nowhere else. It must be rebuilt as a follower of the new primary, or its extra writes must be reconciled by hand. PostgreSQL standbys follow the switch automatically when recovery_target_timeline is latest, its default.

Detection is a trade-off too. A short timeout means a load spike triggers a needless failover (which itself causes load); a long one extends the outage. Tuning it down to a few seconds without fencing is how teams manufacture split brain.

Hazard 3: things outside the database remember the old primary

If the old primary handed out identifiers 1001 to 1200 and those were emailed, cached or published before it died, the new primary's counter may start at 1001 again and reuse them. Clients with cached connections keep writing to the old address, and caches that were warmed from the old primary hold values the new primary never saw. Plan the redirect (DNS, a proxy, a service-discovery update) and the cache flush as part of failover, not after it. A planned switchover removes the first two hazards and shrinks the third: stop writes, let the follower catch up to the leader's position, demote the old leader, promote the follower, and nothing is lost. You still re-point clients and flush caches.

Followers, lag and the operational cost of read replicas

Lag, and the session guarantees that hide it, belong to the consistency models chapter. What belongs here is measuring it and knowing what makes it grow. PostgreSQL reports three separate lags per standby in its pg_stat_replication view: write_lag, flush_lag and replay_lag, each the time until the standby has written, flushed or applied recent WAL. They answer different questions: flush lag tells you about durability, replay lag tells you about what a reader sees. Alert on replay lag for read replicas.

Two hot-standby traps recur. A read-only query on a standby can conflict with the stream of changes it is replaying, for example when the primary vacuums away rows the query still needs, so replay is held up or the query is cancelled. Setting hot_standby_feedback tells the primary what the standby still needs and prevents the cancellations, at the cost of letting dead rows pile up on the primary. And a standby that disconnects with a replication slot makes the primary keep WAL for it, which the manual warns can fill the disk allocated for pg_wal. The max_slot_wal_keep_size setting caps how much WAL a slot may hold.

Multi-leader: writes in more than one place, and the bill that arrives

With several leaders, each accepts writes and forwards them to the others. It earns its keep in three situations: users in several regions who need local write latency (a US East to Europe write that waits on a 70 to 90 ms round trip feels slow), clients that must work offline (every phone is a leader that syncs later), and collaborative editing. The price is that two leaders can accept conflicting writes to the same record at the same time, and nothing in the architecture says which one is right.

The cheapest defence is to avoid the conflict: route every record to a home leader (a user's profile is written only in that user's home region) so that concurrent writes to one record cannot happen. That is single-leader per record with multi-leader plumbing, and it breaks only when the home region is down. When you cannot avoid conflicts you must pick a resolution rule. The rules, from cheapest to most careful:

Dry run: the same two concurrent writes under LWW and under a CRDT

Two regions, A and B, both hold a profile with bio = "Hi" and a like counter of 5. Region A's clock is exact. Region B's clock runs 80 ms slow. Two users act at nearly the same time, and each region first applies the write locally.

Two lessons sit in that table. LWW converges, which makes it look safe, but convergence is not the same as keeping what users did. And the counter is exactly the case where you should not use overwrite semantics at all: model it as increments and the problem disappears. (A button that can also be undone needs separate increment and decrement slots, a PN-counter.)

How real systems handle multi-leader writes

• DynamoDB global tables. In the default multi-Region eventual consistency mode, each item change is replicated asynchronously to the other Regions, and when the same item is modified in several Regions at once the modification with the latest internal timestamp wins on a per-item basis. A strongly consistent read returns the latest version only if the item was last updated in that Region, and transactions are atomic only within the Region where they ran, so another Region can briefly see part of one. The alternative multi-Region strong consistency mode replicates synchronously to at least one other Region before the write returns, requires exactly three Regions (three replicas, or two plus a witness), and does not support transactions or TTL. A write that collides with one in flight elsewhere fails with ReplicatedWriteConflictException and can be retried.
• PostgreSQL logical replication. The manual says a conflict that produces an error, for example a duplicate on a unique key, stops replication until a person fixes it, either by changing the data or by skipping the transaction, which it warns can leave the subscriber inconsistent. When a row was last changed by a different origin, the incoming update is applied anyway, so the remote write always wins (the conflict is logged only if track_commit_timestamp is on at the subscriber). There is no general resolution policy. Treat it as a way to move changes between databases that do not write the same rows.
• MySQL Group Replication. Multi-primary mode is optimistic: members execute transactions locally and roll them back if the group cannot certify them. A transaction run under SERIALIZABLE isolation fails to commit in a multi-primary group, and a group is limited to 9 members. Consensus on transaction order is Paxos-based.

Leaderless: any replica takes writes, and repair does the cleaning

Dynamo-style stores such as Cassandra have no leader. A client or coordinator sends each write to all N replicas of the key and counts acknowledgements against W; reads gather R. The arithmetic of R + W > N, the worked example, and the hinted-handoff caveat are in the consistency models chapter and are not repeated. What that chapter leaves out is how replicas that missed a write catch up, which happens in three layers.

The deadline that makes repair mandatory

Deletes are writes too: Cassandra records a delete as a tombstone and purges it after gc_grace_seconds, 10 days by default. If a replica missed the delete and the tombstone is purged everywhere else before repair reaches it, the old value is still there, and the next repair spreads it back across the cluster as if it were new. The data you deleted comes back. The Cassandra documentation therefore says to repair every node at least once within the grace period, advising every 7 days so that 10 − 7 = 3 days of slack absorb delays, with incremental repair (the default) every 1 to 3 days and a full repair every 1 to 3 weeks as a starting point.

Leaderless stores also need a rule for concurrent versions of one key. The Dynamo paper used vector clocks to detect them and hand both versions back to the application; it truncates a clock when it grows past a threshold (the paper's example is 10 node-and-counter pairs) by dropping the oldest, which the paper concedes can blur the ancestry check. Cassandra chose the simpler rule: every mutation carries a timestamp, taken from the client if it supplies one and otherwise from the coordinator's clock in microseconds, and the highest wins. That is last write wins with all the clock hazards from the dry run above, which is why the Cassandra documentation tells you to run NTP. Monotonic and causal clocks are the subject of the time and ordering chapter.

One more Cassandra detail worth quoting in an interview: blocking read repair gives monotonic quorum reads, so two successive QUORUM reads never go backwards even when a failed write reached only a minority of replicas. The table option read_repair='NONE' turns the write-back off and gives up that guarantee in exchange for partition-level write atomicity.

How the four systems you will be asked about really do it

The pattern across all four: a leader-based system decides order in one place and pays at failover; a leaderless system never fails over and pays on every read and in repair; a multi-region system picks between latency and conflicts. Notice that DynamoDB inside a Region looks like a consensus-backed single leader per partition, the shape described in the distributed consensus chapter, and only becomes multi-leader with last writer wins when you ask it to span Regions in its default mode.

Choosing, in one table

Recognizing it in an unseen problem

• Signals: "multi-region", "survive a datacenter loss", "read replicas", "zero data loss", "offline mode", "collaborative editing". Each one is a replication-topology question in disguise.
• The naive design adds a replica and stops. Ask three follow-ups yourself: is it synchronous, what happens to writes the replica lacks when the leader dies, and who stops the old leader from coming back.
• Count the loss window. Lag × write rate gives the number of acknowledged writes at risk in an async failover (40 ms × 5,000 per second = 200). A number beats "a few".
• Multi-leader means conflicts, so say which rule you pick for which field: counters as CRDTs, free text as last-write-wins only if losing an edit is acceptable, uniqueness through a single home or a constraint check, never through eventual convergence.
• Leaderless means repair. If you pick Cassandra or DynamoDB-style storage, volunteer the repair schedule and the delete-resurrection risk. It signals you have operated one.
• The pitfall to avoid: calling a replica a backup, or promising "no data loss" while using asynchronous replication.