Database Replication

One-liner: Copy data across nodes for read scaling, availability, and locality; the choices are who accepts writes and whether acks wait for replicas.

Say this#

SAY THIS

Leader-follower (primary-replica, master-slave) — one node takes writes, followers replay its log.
Multi-leader (master-master) — several nodes accept writes (often one per region); conflicts must be resolved.
Leaderless (Dynamo-style) — any replica accepts writes; quorums + read repair (Cassandra, Riak, DynamoDB).
Synchronous replication — leader waits for follower ack before committing; zero data loss, higher latency.
Asynchronous replication — leader acks immediately; low latency, possible loss on failover.
Semi-synchronous — wait for at least one follower (MySQL semi-sync, Postgres synchronous_commit with one standby).
Replication lag — follower staleness; causes stale reads.
Read replicas — followers serving reads; scale the read path.
WAL / binlog shipping — Postgres streaming replication, MySQL binlog; logical vs physical replication.
Failover / promotion — follower becomes leader; automatic via Patroni, Orchestrator, RDS Multi-AZ.
Split brain — two leaders after a bad failover; prevent with fencing (STONITH), quorum, leases.
Conflict resolution — last-write-wins (LWW), per-record merge, CRDTs, application-defined.

How it works#

TEXT

writes -> [Leader] --WAL stream--> [Follower 1] (sync)   -> commit ack                   --WAL stream--> [Follower 2] (async)  -> reads                   --WAL stream--> [Follower 3] (async)  -> reads / DR in another regionwrites -> [Leader] --WAL stream--> [Follower 1] (sync)   -> commit ack
                   --WAL stream--> [Follower 2] (async)  -> reads
                   --WAL stream--> [Follower 3] (async)  -> reads / DR in another region

Client writes to the leader; leader appends to WAL.
Followers stream and apply the WAL (physical) or decoded changes (logical).
Sync follower acks before leader commits; async followers trail by the lag.
Reads routed to replicas; sensitive reads (post-write) pinned to the leader or wait for LSN.
On leader failure: health check → consensus (etcd/Zookeeper-backed Patroni) elects a new leader → fence the old one → update DNS/VIP/proxy (PgBouncer, HAProxy).

Multi-leader: each region has a leader; cross-region async replication; conflicts detected by version vectors, resolved LWW or by app. Good for offline-first and geo-distribution; hard to reason about.

Trade-offs#

Choice	Pro	Con
Leader-follower	Simple, no write conflicts	Write scalability limited to one node; failover complexity
Multi-leader	Local writes in every region, survives region loss	Conflicts, hard invariants (uniqueness)
Leaderless	No failover needed, always writable	Weaker consistency, read repair, app-level conflict handling
Synchronous	RPO = 0	Latency = slowest replica; leader stalls if replica dies
Asynchronous	Fast writes, replicas can be far away	RPO > 0; stale reads; lost writes on failover
Many read replicas	Read scale	Each adds lag risk and leader bandwidth

Numbers#

In-region async lag: typically milliseconds; cross-region: tens to hundreds of ms, seconds under load.
Sync replication adds roughly one RTT per commit: ~1 ms same AZ, ~1-2 ms cross-AZ, 50-150 ms cross-region.
Automated failover (Patroni/RDS Multi-AZ): ~30-120 s typical.
Postgres can comfortably run ~5-15 streaming replicas; beyond that use cascading replication.

Say this#

How it works#

Trade-offs#

Numbers#

In the interview#