Database Replication and Failover

medium

Scale: Read scales linearly with replicas; write bounded by primary Storage: N × dataset size for N replicas Stripe, Shopify, GitHub

FundamentalsDatabasesReplication

Replication keeps multiple copies of data on different nodes for high availability, read scaling, and disaster recovery. The choice of topology and sync mode shapes durability, latency, and operational complexity.

ScaleRead scales linearly with replicas; write bounded by primary

StorageN × dataset size for N replicas

Key Concepts

1. Three topologies. Single-leader (Postgres, MySQL, SQL Server default): all writes to one primary; followers stream changes from WAL/binlog and serve reads. Multi-leader: writes accepted at multiple primaries; conflicts resolved by LWW, vector clocks, or CRDTs. Used in multi-region active-active. Leaderless (Dynamo, Cassandra): every node equal; writes go to W of N, reads from R of N. R+W>N gives strong consistency.

1. Three topologies.

2. Sync modes within single-leader. Async: primary commits + acks client + streams to followers later. Lowest write latency; loses last N writes on primary crash. Semi-sync: primary waits for ≥1 follower ack. Bounded loss; small latency increase. Sync: every follower acks. Strongest durability; slow tail (one slow follower kills writes). Quorum-sync (Postgres quorum): M of N must ack — practical compromise.

2. Sync modes within single-leader.quorum

3. Replication lag is the operational pain. Followers fall behind because they apply changes sequentially while the primary writes in parallel. Lag = microseconds to hours under load. User-visible: profile update, next read on follower shows old value. Mitigate with read-your-writes routing (sticky to primary for the affected user), session-level pinning, or short-window primary reads.

3. Replication lag is the operational pain.

4. Failover and split-brain. Failover: detect via heartbeats + quorum, promote most up-to-date follower, fence the old primary, repoint client traffic. Split-brain: two nodes both believe they're primary because of a partition — both accept writes, divergence is permanent. Solve with an external coordinator: etcd, ZooKeeper, Patroni for Postgres, Orchestrator for MySQL.

4. Failover and split-brain.

5. In production. Patroni + etcd is the standard for Postgres HA. RDS and Aurora handle this for you. Stripe and Shopify run sharded MySQL with Orchestrator. GitHub uses Orchestrator and Vitess. Multi-leader is rare outside multi-region active-active; conflict resolution dominates implementation cost.

5. In production.

Approach

Pick the topology. Single-leader is default. Multi-leader for multi-region writes. Leaderless for Dynamo-style scale-out.
Pick sync mode. Async default; semi-sync for stronger durability; sync only on hot critical writes.
Set up replicas. 1-2 same-AZ for HA, 1+ different-AZ for AZ failure, 1+ different-region for DR.
Wire up a proxy that knows the topology: PgBouncer/HAProxy + Patroni for Postgres; ProxySQL + Orchestrator for MySQL.
Use a coordinator (etcd, ZooKeeper) for leader election and consensus on failover.
Implement fencing: ensure the old primary cannot accept writes after demotion.
Monitor lag continuously; alert if it exceeds budget; auto-page on failover.
Practice failover in a staging environment monthly. The first time you do it should not be at 3 AM.

Replication modes

Async: primary commits + acks client + streams to followers later. Lowest write latency. Loses last N writes on primary crash.

Semi-sync: primary waits for >= 1 follower ack before client ack. Bounded data loss; small latency increase.

Sync (chain): every follower must ack. Strongest durability. Slow tail; one slow follower kills writes.

Group commit / quorum-sync: M of N followers must ack. Practical compromise (Postgres quorum mode).

Topologies

Single-leader: writes → primary → followers. Default for OLTP.

Multi-leader: writes accepted at multiple nodes, conflicts resolved later. Lower write latency in multi-region; conflict resolution required (LWW, vector clocks, CRDTs).

Leaderless (Dynamo/Cassandra): write to W of N replicas, read from R of N. R+W>N gives linearizability. No special leader role.

Chained replication: A → B → C. Writes durable when last node acks. Used by some specialized systems (Chain Replication paper).

Components

WAL / binlog / oplog — primary's change stream.
Replication slot / GTID / oplog position to track follower progress.
Coordinator (etcd, ZooKeeper, Consul) for leader election.
Failover automation (Patroni, Orchestrator, RDS, Aurora).
Client proxy (PgBouncer, ProxySQL) to route reads/writes after promotion.
Fencing mechanism — STONITH, network ACL change, demoted flag in coordinator.
Lag monitor + replay alerts.
Backup + PITR independent of replication.

Failover playbook

1. Detect: heartbeat timeout; quorum of observers agrees primary is unreachable.
2. Choose new primary: most up-to-date follower (highest WAL position).
3. Fence old primary: revoke writes (network ACL, demote flag, STONITH).
4. Promote chosen follower: stop replication, accept writes.
5. Reconfigure remaining followers to replicate from new primary.
6. Rewire client traffic: proxy updates routing.
7. Recover: when old primary returns, reset and bring back as a follower.
Test this in staging. Automation tools (Patroni, RDS) handle most of this; understanding the steps lets you debug when they don't.

Trade-offs

Async: low latency, can lose committed writes on failover.

Sync: durable, slow tail, sensitive to slow followers.

Single-leader: simple, but write capacity bounded.

Multi-leader: lower latency in multi-region, conflict resolution complexity.

Leaderless: tunable consistency, operationally complex.

Replica reads can be stale; route critical reads to the primary or use read-your-writes patterns.