Database Replication

What is Database Replication?

Database Replication is the practice of keeping copies of data on multiple database servers. When your primary database receives a write, that change is propagated to one or more replica databases.

Why Replicate?

Replication Architectures

1. Leader-Follower (Master-Slave)

One leader accepts writes; followers replicate from leader and serve reads.

Writes  → [Leader] 
                ↓ replication
         [Follower 1] ← Reads
         [Follower 2] ← Reads
         [Follower 3] ← Reads

Pros: One write authority simplifies ordering; consistency and durability still depend on acknowledgement and read policies Cons: Write bottleneck on single leader, failover complexity Used by: MySQL, PostgreSQL, MongoDB, Redis

2. Leader-Leader (Multi-Master)

Multiple nodes accept writes. Changes sync bidirectionally.

Writes → [Leader A] ←→ [Leader B] ← Writes
              ↓              ↓
         [Follower]    [Follower]

Pros: No single point of failure for writes, geographic write distribution Cons: Conflict resolution is hard, eventual consistency Example: MySQL bidirectional replication can accept writes at multiple sources but requires conflict control. CockroachDB instead uses a Raft group per range; accepting a SQL request at any node does not make its storage leaderless or ordinary multi-master replication.

3. Leaderless (Dynamo-style)

No leader. Any node accepts writes. Quorum determines success.

Client writes to N nodes
If W nodes acknowledge → Success
Client reads from N nodes  
If R nodes respond → Return most recent (determined by version)

W + R > N creates read/write quorum overlap on the same replica set.
It alone does not guarantee read-your-writes or linearizability: concurrent
writes, failed writes, version selection, and sloppy quorums also matter.

Pros: Highly available, no leader election needed Cons: Complex conflict resolution, eventual consistency Examples: Cassandra, Riak, and the published Amazon Dynamo design. Do not infer DynamoDB implementation details solely from Dynamo.

Synchronous vs. Asynchronous Replication

Synchronous Replication

Leader waits for followers to confirm before acknowledging write.

Write → Leader → [waits for confirmation] → Follower
                         ↓
                     Client gets OK

Pros: Can improve durability; the exact guarantee depends on whether acknowledgements mean receipt, durable flush, or apply Cons: Slower writes, follower failure blocks writes

Asynchronous Replication

Leader acknowledges immediately, replicates in background.

Write → Leader → Client gets OK immediately
           ↓
    [background] → Follower (may lag)

Pros: Fast writes, follower issues don't block Cons: Replication lag, can lose data on leader failure

Semi-Synchronous

Wait for at least one follower to confirm.

Write → Leader → [wait for 1 follower] → Client OK
                        ↓
              Other followers async

This is a trade-off: a required replica can add latency or block acknowledgement, and a received log is not necessarily visible to reads.

Replication Lag

In async replication, followers may be behind the leader. This is replication lag.

Problems from Replication Lag

Read-your-writes inconsistency

1. User writes profile update to Leader
2. User reads from Follower (hasn't replicated yet)
3. User sees old data! 😱

Solutions:

• Read from leader for recently written data
• Wait for a replica to replay the required database log position before reading
• A sticky replica alone does not guarantee read-your-writes if it has not caught up

Monotonic reads violation

1. User reads from Follower A (has new data)
2. User reads from Follower B (further behind!)
3. User sees data go backward in time! 😱

Solution: Sticky sessions or read from single replica per user session

Handling Failover

When the leader fails, you need to promote a follower.

Manual Failover

Operator promotes a follower after checking replication state and fencing the old primary. Manual action is not inherently safe.

Automatic Failover

System detects leader failure and promotes automatically.

Challenges:

1. Split brain: Two nodes think they're leader
2. Data loss: If async replication, promoted node may be behind
3. Detection accuracy: Is leader actually dead or just slow?

Solutions:

• Consensus algorithms (Raft, Paxos)
• Fencing (prevent old leader from accepting writes)
• Require quorum for failover decision

Conflict Resolution (Multi-Master/Leaderless)

When multiple nodes accept writes simultaneously, conflicts occur.

Common Strategies

Last Write Wins (LWW) Most recent write (by timestamp) wins.

Node A at T1: user.name = "Alice"
Node B at T2: user.name = "Bob"
Result: user.name = "Bob" (T2 > T1)

⚠️ Can lose writes silently

Merge Values For certain data types, merge is possible.

Node A: cart += "apple"
Node B: cart += "banana"  
Result: cart = ["apple", "banana"]

Works for: Sets, counters, CRDTs

Application-Level Resolution Present both versions to user/application to resolve.

Version Vectors Track causality to detect true conflicts vs. sequential edits.

Replication Topologies

Star (Hub and Spoke)

        [Leader]
       /   |   \
    [F1] [F2] [F3]

Simple, but leader is bottleneck.

Circular

[A] → [B] → [C] → [A]

Each node replicates to next. Failure breaks chain.

All-to-All

[A] ↔ [B]
 ↑ ↘ ↗ ↓
   [C]

Most fault-tolerant, but complex.

Real-World Examples

Amazon RDS

Replication and failover behavior varies by database engine and deployment type. Check the selected RDS configuration rather than applying one replica count to every engine.

PostgreSQL

Built-in streaming replication. Supports sync, async, and logical replication.

MongoDB

Replica sets with automatic leader election. Secondaries can serve reads.

Redis

Master-replica replication. Redis Sentinel provides automatic failover. Redis Cluster adds sharding.

Replication + Sharding

In practice, you often combine both:

Shard 1: [Leader] → [Replica 1] → [Replica 2]
Shard 2: [Leader] → [Replica 1] → [Replica 2]
Shard 3: [Leader] → [Replica 1] → [Replica 2]

Each shard handles a portion of data, replicated for availability.

Interview Tips 💡

When discussing replication in system design:

1. Identify the goal: "We need replication for high availability and read scaling..."
2. Choose architecture: "We'd use leader-follower with async replication..."
3. Address lag: "For read-your-writes consistency, we'd route recent writes to the leader..."
4. Failover strategy: "We'd use automatic failover with Raft consensus to avoid split-brain..."
5. Combine with sharding: "For large scale, we'd shard the data and replicate each shard..."

Related Concepts

• CAP Theorem — Trade-offs in replicated systems
• Database Sharding — Horizontal partitioning + replication
• Raft Consensus — Leader election for replication
• CRDTs — Conflict-free replicated data types
• Leader Election — Choosing a new leader programmatically

Sources and a design check

PostgreSQL standby documentation distinguishes replication modes. CockroachDB replication describes per-range Raft. In a failover drill, track the last acknowledged write, promoted replica position, and old-primary fencing. A healthy replica is not a backup against an accidental delete that replicates immediately.