Replication Topologies

Serializable retries saved one box, but one box still dies alone

Isolation ended with bounded retries on error 40001 keeping concurrent strangers honest on a single machine. Honesty is worthless when the machine itself disappears. A database holding everything on one machine dies the way machines die: a disk fails, a deploy goes sideways, or an entire cloud zone blinks. The application then has nothing to read and nowhere to write, and every user sees an error page until a restore from last night's backup completes, losing a day of orders along the way.

The naive fix is restoring faster or buying a slightly tougher box, and it fails on both halves of the problem. Faster restores still lose everything written since the last backup, and a tougher box still fields every read from every application server, where reads outnumber writes fifty to one. A second tempting fix is synchronous disk mirroring under the database, such as RAID or a replicated volume, and it fails differently: it mirrors every byte including corruption and bad migrations within seconds, so a dropped table lands on both sides at once, and it never serves a single read to relieve the primary. The box sweats under read load while remaining a single point of failure.

The real fix for both halves is the same move: keep live copies, which is called replication. Think of a post office that photocopies each incoming letter to branch offices as it arrives: one analogy for the whole idea, where the main office takes all new mail, branches serve readers from their copies, and the only question is how quickly each copy catches up.

One writer takes all edits while readers spread across copies

The standard setup gives writes exactly one home, called the primary, which is the single machine allowed to accept edits, and fans copies out to machines called replicas, which apply the primary's change log and serve reads. Writes stay simple because one decider orders them with no arguments. Reads scale because each new replica adds serving capacity, and backups move to a replica so dumping data no longer disturbs users.

Letting every copy accept writes trades one outage for endless arguments

Letting two machines both accept writes sounds like removing the wait: each coast writes locally and stays fast. The failure is concurrent edits to the same record, where one operator sets a city on writer A while another sets it on writer B in the same second. Neither writer is wrong locally, so the system needs conflict rules, merge logic, and explanations to users about which version survived. Single-writer replication has one hard problem, which is surviving the writer's death, while multi-writer replication has an endless one, which is copies disagreeing forever and needing a human notion of correctness for every field.

Waiting for copies buys safety while skipping the wait buys speed

Copies lag behind the primary by physics, meaning the time to ship, apply, and confirm each edit. The only choice is whether the writer waits. Synchronous replication, which means the primary acknowledges a write only after replicas confirm it, survives the primary dying a second later with nothing lost, because the copies already hold the edit. Asynchronous replication, which means the primary acknowledges immediately and replicas catch up in the background, keeps writes fast no matter what, because a sick replica never blocks anyone. Nearly everyone runs asynchronous for user-facing writes and reserves synchronous behavior for money paths.

Fresh edits look lost when the next read lands on a stale copy

The classic asynchronous papercut is updating a profile photo, reloading, and seeing the old one because the read landed on a replica that had not yet applied the edit. The cheapest fix is read-your-writes routing, which means sending a user's own fresh reads to the primary for a short window after each write. The next step is sticky reads, which means pinning each user to one replica so versions only move forward from that user's perspective, though a pinned replica can still lag. The strictest step is a lag limit, which means skipping replicas more than a configured number of seconds behind and waiting for a fresher one, trading occasional slowness for bounded staleness.

Lag budgets turn stale reads from surprises into configured behavior

Healthy asynchronous replicas in the same data center lag tens to hundreds of milliseconds, which is the time to ship, apply, and flush the write-ahead log. A long report or a big backfill can push that to seconds or minutes because the replica applies one heavy transaction while new edits queue behind it, and cross-region replicas add the speed of light plus retries, typically 50 to 200 milliseconds between continents even when happy. Size product promises around those numbers: when a user's own edit must be visible instantly, route that read to the primary for a few seconds rather than hoping the replica caught up. Monitor replica lag in seconds and alert before users notice, with a common starting stance of a warning at 1 second and a page at 5 to 10 seconds for read-heavy applications.

Automatic failover without fencing creates two primaries that diverge

The primary goes dark and the orchestrator, which is the automation that watches health and promotes replicas, promotes a replica. But the old primary is not actually dead, only unreachable from the orchestrator, and keeps accepting writes from clients that can still reach it. Two primaries now diverge with no merge button, which is called split-brain. Prevention is fencing, which means the old primary is forcibly shut down, blocked at the network, or required to win a consensus election, which is a majority vote among coordinators, before serving writes. Teams that automate promotion without fencing automate divergence. The asynchronous caveat caps the story: any edits the old primary accepted but never shipped vanish the moment the replica is promoted, which is the durability price of never waiting.

Two dials decide whether the copies save you: durability and topology

The durability dial picks per write path rather than per database: synchronous commit to at least one replica for money movements, asynchronous elsewhere, quorum writes, which require acknowledgment from a majority rather than one fixed replica, when survival matters without letting a single slow replica stall everything, and bounded-staleness reads that skip lagging replicas. The topology dial picks how copies are arranged: cascading replicas, where replicas copy from other replicas to spare the primary from fan-out load, a dedicated backup replica that serves no traffic so dumps never fight readers, and read-your-writes routing for the seconds after each edit. The one-line summary to carry is that one writer with many readers scales reads and buys survival, while the choice of waiting or not waiting decides per write whether safety or speed wins, and asynchronous replication loses exactly the writes the replicas never received plus anything clients did based on unreplicated reads.

Copies solve death and busyness: one writer with many readers scales reads and survives the writer's death, with fencing deciding which copy is allowed to lead. But every copy still holds the same data with the same buffer pool, the same write-ahead log, and the same tuning, so a weekend catalog surge still evicts login pages from memory and a products outage still shares fate with payments. The data must be divided by purpose so each workload gets its own ceiling and its own tuning, which is the federation split.