SQL vs NoSQL: How to Choose
SQL vs NoSQL explained: data models, consistency, scaling, query flexibility and when to use relational, document, key-value, wide-column or graph databases.
Database Replication: Leader-Follower, Multi-Leader, Leaderless
Database replication explained: leader-follower, multi-leader and leaderless designs, sync vs async, replication lag, failover, quorums and conflict resolution.
Key takeaways
- Replication keeps copies of data on several machines for availability, read scaling and lower latency.
- Leader-follower is the simplest; asynchronous followers lag, which causes read-your-writes surprises.
- Multi-leader and leaderless designs accept writes in more places but must resolve conflicts.
On this page
Database replication means keeping copies of the same data on several machines. It protects against machine failure, lets you spread reads across replicas and puts data closer to users. There are three main designs: leader-follower, where one node takes writes and copies them to followers; multi-leader, where several nodes accept writes and sync with each other; and leaderless, where clients write to several replicas directly and use quorums. Each trades simplicity, consistency and availability differently.
Why replicate?
- Availability: if one node fails, another has the data.
- Read scaling: spread read traffic across several replicas.
- Latency: serve users from a nearby region.
- Backups and analytics: run heavy reporting queries on a replica, away from production writes.
Replication copies the whole dataset to each node; splitting data across nodes is sharding. Large systems usually do both: each shard is replicated.
Leader-follower replication
One node, the leader (or primary), accepts all writes. It records changes in a log and streams them to followers (replicas), which apply them in the same order. Reads can go to the leader or to any follower.
This is the default for PostgreSQL streaming replication, MySQL replication and many managed databases, because it’s simple: there’s one place where writes happen, so there are no write conflicts.
Synchronous vs asynchronous
- Synchronous: the leader waits for a follower to confirm before acknowledging the write. No acknowledged data is lost if the leader dies, but a slow follower slows every write.
- Asynchronous: the leader acknowledges immediately and followers catch up. Writes are fast, but a leader crash can lose the most recent writes.
- Semi-synchronous: one follower is synchronous, the rest asynchronous, a common compromise.
Replication lag and its surprises
Asynchronous followers are usually milliseconds behind, but lag can grow to seconds or more under load. Users then see odd behaviour:
- Read-your-writes: a user saves a profile, refreshes and sees the old version because the read hit a lagging replica. Fix: read a user’s own recent changes from the leader.
- Monotonic reads: successive reads hit different replicas and data appears to go back in time. Fix: pin each user to one replica.
- Consistent prefix: a reply appears before the message it answers. Fix: keep causally related writes in the same partition and order.
Failover
When the leader fails, a follower is promoted. Automatic failover is fast but risky: if the old leader was only unreachable, you can end up with two leaders (split brain). Robust systems use a consensus-based coordinator or fencing to make sure only one leader accepts writes, and accept that recent asynchronous writes may be lost.
Multi-leader replication
Several nodes, often one per region, accept writes and replicate to each other. Users write to their nearest leader, so writes stay fast even across continents, and each region keeps working if links between regions fail.
The price is write conflicts: two users edit the same record in different regions at the same time. Options include:
- Last write wins: keep the version with the latest timestamp. Simple, but it silently discards data.
- Merge rules in application code, for example combining lists.
- CRDTs: data types designed so concurrent updates always merge consistently.
- Avoid conflicts by routing all writes for a given record to one home region.
Leaderless replication
Popularised by Amazon’s Dynamo paper and used by Cassandra-style databases, leaderless systems let clients send each write to several replicas and read from several replicas.
With n replicas, a write must be confirmed by w of them and a read must query r of them. If w + r > n, every read overlaps with at least one replica holding the latest write. A common setting is n = 3, w = 2, r = 2. Background mechanisms repair divergence: read repair fixes stale replicas when a read notices them, and anti-entropy processes compare and sync replicas. Data placement typically uses consistent hashing.
Comparing the designs
| Leader-follower | Multi-leader | Leaderless | |
|---|---|---|---|
| Where writes go | One leader | Several leaders | Several replicas directly |
| Write conflicts | None | Must be resolved | Must be resolved |
| Write availability | Depends on leader and failover | High, per region | High, while a quorum is reachable |
| Consistency | Strong from leader; lag on followers | Eventual between leaders | Tunable via w and r |
| Complexity | Low | High | Medium to high |
| Typical use | Most applications | Multi-region apps, offline clients | Very high write availability |
These trade-offs are the practical face of the CAP theorem and PACELC: during a partition you choose between availability and consistency, and even without one, you trade latency against consistency.
Choosing
Start with leader-follower: it covers most applications, especially with a synchronous or semi-synchronous standby for failover. Consider multi-leader only when you genuinely need writes in several regions, and leaderless when write availability matters more than simple consistency. The database you choose often decides for you; our SQL vs NoSQL guide covers the families.
Frequently asked questions
Is replication the same as backup?
No. Replication copies mistakes too: a bad delete is replicated in milliseconds. Keep separate backups with point-in-time recovery.
How much replication lag is normal?
Usually well under a second on a healthy network, but it can grow under heavy writes or long-running queries on replicas. Monitor it and alert on it.
Can I write to a read replica?
In leader-follower setups, no: replicas are read-only, and writes must go to the leader.
Sources
Every article is edited by a human and checked against our editorial policy. Spotted a mistake? Tell us.
Keep reading
Database Sharding Explained
What database sharding is, when you need it, how to choose a shard key, range vs hash vs directory sharding, and the operational pain points to plan for.
B-Trees vs LSM Trees: How Databases Store Data
How B-tree and LSM-tree storage engines work, why one favours reads and the other writes, write and read amplification, compaction and which databases use each.