Database Replication Strategies

Replication copies data across multiple nodes for high availability and read scalability.

Single-Leader Replication

One primary node handles all writes. Replicas sync asynchronously.

Writes → Primary
Reads  → Primary or Replicas

Primary → (WAL/binlog) → Replica 1
                       → Replica 2

Used by: PostgreSQL, MySQL, MongoDB

Sync vs Async Replication

Sync:  Write confirmed after replica ACKs (durable, slow)
Async: Write confirmed before replica syncs (fast, potential data loss)
Semi-sync: At least one replica must ACK

Replication Lag

Async replicas can be seconds behind primary. Reading your own writes can return stale data.

Solution: Read-after-write consistency — route reads to primary for 1 minute after write.

Multi-Leader Replication

Multiple leaders accept writes (multi-datacenter).

DC-1 Leader ↔ DC-2 Leader

Challenge: Write conflicts when same data modified concurrently.

Resolution: Last-Write-Wins, CRDTs, or application-level merge.

Leaderless Replication

Any node accepts writes; uses quorum for consistency.

W + R > N (Quorum)
N=3, W=2, R=2: Strong consistency
N=3, W=1, R=1: High availability

Used by: Cassandra, DynamoDB, Riak

Failover

Automatic promotion of replica to primary:

1. Primary stops responding
2. Replicas hold election (Raft/Paxos)
3. New primary elected
4. Old primary rejoins as replica

Conclusion

Single-leader for simplicity, multi-leader for multi-DC writes, leaderless for maximum availability.