Replication

Replication means keeping the same data on more than one machine. You do it for four reasons: to keep serving when a machine dies (availability), to survive a disk or data-centre loss (durability), to spread reads over more machines (read scaling), and to put data close to users (latency). The difficulty is that copies must be kept in step over a network that delays and drops messages. Every replication design is a choice about how in-step the copies are, and what happens when they aren't.

#Leader–follower replication

The most common design, used by PostgreSQL, MySQL, MongoDB and most managed databases. One node, the leader (or primary), accepts all writes. It records each change in its log — for PostgreSQL, the write-ahead log — and streams that log to followers (replicas), which apply the same changes in the same order. Followers serve reads; they never accept writes from clients.

Because every change flows through one node in one order, followers can never disagree about the order of writes. They can only be behind. That property makes leader–follower replication easy to reason about, and it's why you should prefer it unless you have a specific reason not to. See leader–follower replication.

#Synchronous or asynchronous

The key decision is when the leader tells the client "committed".

Fully synchronous replication to every follower would make every write wait for the slowest replica, and one dead replica would stop all writes. So real systems wait for one (or a quorum) of the followers. In PostgreSQL:

# postgresql.conf on the primary
synchronous_standby_names = 'ANY 1 (replica_a, replica_b)'   # wait for either one
synchronous_commit = on                                       # flush on the standby before acknowledging

With this, a commit is durable on two machines before the client hears "OK", and either replica can be down without blocking writes.

#Replication lag and what it breaks

Asynchronous followers trail the leader, usually by milliseconds, but by seconds or minutes under heavy write load, during a long-running query on the replica, or after a network hiccup. Reading from a lagging follower produces three classic anomalies. See replication lag anomalies.

Read-your-writes violations. You post a comment, the page reloads from a replica, and your comment isn't there.

Non-monotonic reads. You refresh twice. The first read hits an up-to-date replica, the second a lagging one, and data appears to go back in time.

Broken causality. You see an answer before the question it replies to, because they were written by different users and replicated to your replica out of order (more of a risk in partitioned systems, where different partitions replicate independently).

Fixes, matched to each problem:

• Read-your-writes: route a user's reads to the leader for a few seconds after they write. More precisely, remember the leader's log position at the time of the write and only read from a replica that has applied at least that much.
• Monotonic reads: pin each user to one replica (for example by hashing the user ID), so they never jump to a replica further behind.
• Causality: keep causally related data in the same partition, or read it from the leader.

Here is the log-position approach in PostgreSQL. After a write, record the leader's position:

-- On the primary, right after the user's write commits:
SELECT pg_current_wal_lsn();          -- e.g. 3/A1C2B8F0, store it in the user's session

Before reading from a replica, check that it has caught up to that position, and fall back to the primary if not:

-- On the replica:
SELECT pg_last_wal_replay_lsn() >= '3/A1C2B8F0'::pg_lsn AS caught_up;
-- And the lag in time, for dashboards and alerts:
SELECT now() - pg_last_xact_replay_timestamp() AS replica_lag;

#Failover

When the leader dies, a follower must be promoted. Each step hides a hard problem:

1. Detect the failure. There's no way to tell a dead leader from a slow one or a broken network link, so systems use timeouts. Too short and you fail over during a GC pause; too long and you're down for that long. Values of 10–30 seconds are common.
2. Choose a new leader. Pick the follower with the most of the log. With asynchronous replication, whatever the old leader hadn't shipped is lost — and the application already told those users their writes succeeded.
3. Redirect clients to the new leader, through DNS, a virtual IP, a proxy, or a service discovery update.
4. Stop the old leader. If it was only partitioned, not dead, it may come back still believing it's the leader. Two leaders accepting writes — split brain — corrupts data. Systems prevent it by having the new leader's term or epoch number reject writes from the old one (see leases and fencing tokens) or by forcibly shutting the old node down ("shoot the other node in the head").

This is why you shouldn't script failover yourself. Use a tool built for it — Patroni for PostgreSQL, InnoDB Cluster (Group Replication) for MySQL — or a managed database's automatic failover. These agree on who the leader is through a consensus system (etcd, Consul, ZooKeeper, or a built-in Paxos or Raft group) or a single trusted control plane, not a script's guess. See leader election.

#Multi-leader replication

Sometimes you want writes accepted in more than one place: a database in each region so users write locally, or offline clients (a notes app on a phone) that sync later. Then several leaders each accept writes and replicate to each other.

The cost is write conflicts: two leaders accept different changes to the same row at the same time, and neither knows until replication catches up. Resolution strategies all have sharp edges:

• Last write wins by timestamp: simple, and silently discards one of the writes. Clock skew can even make the earlier write win.
• Merge the values with application logic, or with data types designed to merge automatically (CRDTs: counters, sets, collaborative text).
• Avoid conflicts by routing all writes for a given record to the same leader (each user's data has a home region).

Multi-leader setups are worth their complexity for collaborative and offline-first apps. For an ordinary web application, a single leader with read replicas in other regions is usually the better trade.

#Leaderless replication and quorums

Dynamo-style databases — Cassandra, ScyllaDB, Riak — have no leader. A client (or a coordinator node acting for it) sends each write to all N replicas of a key and treats it as successful once W acknowledge. A read asks replicas and waits for R answers, keeping the value with the highest version.

If R + W > N, every set of R replicas overlaps every set of W replicas in at least one node, so at least one replica in every read has the latest successful write. With N = 3, W = 2, R = 2, you can lose one replica and still read and write. With W = 1, R = 1, writes and reads are fast, but a read can easily miss the latest write. See quorum reads and writes.

Replicas that missed a write catch up in two ways. Read repair: when a read sees a stale replica, it writes the newer value back to it. Anti-entropy: a background process compares replicas and copies missing data, using Merkle trees so it only compares the parts that differ.

Two caveats keep quorums from being a guarantee of strong consistency. Under sloppy quorums, writes may be accepted by stand-in nodes when the real replicas are unreachable ("hinted handoff"), so R + W > N no longer guarantees overlap. And concurrent writes to the same key still need a conflict rule, usually last write wins. Quorum systems trade these edge cases for high availability and no failover step.

#Practice

You run a project-management SaaS on PostgreSQL in one region: one primary, two asynchronous read replicas. Users report: (a) after creating a task, it's missing from the list for a second or two; (b) during last month's primary failure, about 30 seconds of task updates disappeared after failover; (c) a weekly analytics query on a replica sometimes makes that replica fall minutes behind, and users routed to it see old data.

For each, explain the cause and propose a fix with its cost.

#Key takeaways

• Replicate for availability, durability, read scaling and latency; leader–follower is the default because it never reorders writes.
• Synchronous replication to at least one follower means no acknowledged write is lost, at the cost of a round trip per commit.
• Asynchronous lag causes read-your-writes, monotonic-read and causality anomalies; route reads deliberately to avoid them.
• Failover hides hard problems — detection, data loss and split brain — so use a proven tool built on consensus.
• Multi-leader replication buys local writes at the cost of conflicts; avoid it unless the product needs it.
• Leaderless quorums with R + W > N make reads see the latest write, with caveats around sloppy quorums and concurrent writes.