Replication — DB Notes

Copying the same data to multiple nodes for fault tolerance and read scalability — the central trade-off is durability versus write latency.

Replication copies the same data to multiple nodes, so the system survives a node failure and can spread read traffic across replicas. The most common setup is leader-follower (also primary-replica): all writes go to one leader, which then streams its change log out to followers that apply it in order.

Fig. 5 — Synchronous vs. asynchronous replication, on a timeline

The client waits until the follower confirms it has the write before getting an ack — durable even if the leader crashes right after, but every write is only as fast as the slowest follower.

Why teams turn it on

Survive a node failure. The leader’s disk dies at 3am. A follower is promoted and the service keeps running. Without a replica, you’re restoring from last night’s backup and losing everything since.

Scale reads. A dashboard product runs thousands of heavy analytical SELECTs. Point them at read replicas so they don’t compete with customer-facing writes on the leader.

Keep data close to users. A replica in Frankfurt serves EU reads with 10ms latency instead of 120ms across the Atlantic. Writes still go to the leader, but most traffic is reads.

Offload maintenance. Take backups, run pg_dump, or feed a data-warehouse ETL from a replica so the leader isn’t touched.

Zero-downtime upgrades. Upgrade a follower to the new database version, promote it, then upgrade the old leader. Users see a brief blip instead of a maintenance window.

Synchronous replication

The leader waits for at least one follower to confirm it has persisted the write before telling the client the write succeeded. The write is guaranteed to survive a leader crash — but write latency is now bounded by the slowest synchronous follower, and if that follower is unreachable, writes can stall entirely.

A team set two replicas as synchronous for safety. One replica’s disk got slow. Every write on the leader now waited on that slow disk, p99 write latency went from 5ms to 900ms, and checkout started timing out. Fix: exactly one synchronous follower, with automatic failover to another if it lags.

Use synchronous replication where a lost acknowledged write is unacceptable: payment ledgers, order-of-record systems, anything you’d have to explain to a regulator.

Asynchronous replication

The leader acknowledges the write immediately, then streams it to followers in the background. Writes are fast and never blocked by a follower — but there’s a window where the leader has data no follower has yet. If the leader crashes in that window, that write is lost even though the client was told it succeeded.

This is the default for most read-scaling setups, where losing the last few hundred milliseconds of writes on a rare hard failover is an acceptable trade for never slowing writes down.

-- PostgreSQL: require at least one synchronous standby to confirm before commit
ALTER SYSTEM SET synchronous_standby_names = 'FIRST 1 (replica_a, replica_b)';

Replication lag: the bugs it causes and how to handle them

With async replication a follower is always slightly behind. Under load — a traffic spike, a big batch UPDATE, a slow follower disk — “slightly” can become seconds or minutes. That produces a family of user-visible bugs:

Read-your-writes. A user posts a comment (write → leader), the page reloads and reads from a follower that hasn’t caught up, and their comment is missing. They post it again. Now there are two.

Monotonic reads. A user refreshes a thread twice. The first read hits an up-to-date follower and shows 10 replies; the second hits a lagging follower and shows 8. Content appears to travel backwards in time.

Cross-entity consistency. A “your order shipped” email links to a tracking page that reads from a follower where the shipment row doesn’t exist yet — 404.

Patterns that fix these

Pattern	What it does
Read-from-leader window	For N seconds after a user writes, route that user’s reads to the leader. Simple, effective for read-your-writes.
Sticky routing	Pin a user’s session to one replica so they at least see a consistent (if slightly stale) timeline — fixes monotonic reads.
Read-your-writes token	Client remembers the log position (LSN) of its last write; the router picks a replica that has caught up to at least that position.
Critical-path reads to leader	Payment confirmation, “did my transfer go through” — always read from the leader, accept the cost.

Multi-leader and leaderless

Sometimes there’s more than one node accepting writes:

Multi-leader — a leader per region, each accepting local writes and replicating to the others. Great for write latency and offline tolerance, but two regions can edit the same row concurrently, and now you need conflict resolution (last-write-wins, CRDTs, or app-defined merges). This is the model behind offline-capable apps and some geo-distributed databases.

Leaderless (Dynamo-style, e.g. Cassandra) — the client writes to several replicas directly and reads from several, using quorums (W + R > N) to get consistency. No failover step because there’s no leader to lose.