⚠️ This guide is AI-generated and may contain inaccuracies. Always verify against authoritative sources and real-world documentation.
Home → System Design → Database Replication

📋 Database Replication

Maintain copies of data across multiple database instances for availability, fault tolerance, and read scalability. The fundamental tradeoff: consistency vs performance.

Medium High Frequency

Architecture Diagram

Client WRITES Client READS INSERT/UPDATE LEADER Primary / Master Handles all writes SYNC ASYNC FOLLOWER 1 Replica / Slave ● In sync (0ms lag) FOLLOWER 2 Replica / Slave ⏱ Lag: ~100ms read-your-writes ProxySQL routes reads to followers ⚠️ Async follower may serve stale data Read-after-write: route to leader!

How It Works

Replication maintains copies of data across multiple database instances. The leader handles all writes and propagates changes to followers. Clients read from followers to distribute the read load.

Replication Models

Leader-Follower (Most Common)

One leader handles writes, followers replicate and serve reads. Simple, well-understood. The default choice for most applications. PostgreSQL streaming replication, MySQL replication.

Multi-Leader

Multiple nodes accept writes, replicate to each other. Used for multi-datacenter setups (each DC has a leader). Must handle write conflicts — last-write-wins, merge, or app-level resolution.

Leaderless (Dynamo-style)

Any node accepts reads/writes. Uses quorum: W+R > N for consistency. Cassandra, DynamoDB. More available but harder to reason about consistency.

Sync vs Async Replication

Synchronous: write waits for follower ACK — strong consistency, slower. Asynchronous: write returns immediately — fast but followers may lag. Semi-sync: wait for 1 replica (best of both).

The Replication Lag Problem

With async replication, followers may be seconds behind. A user updates their profile and immediately refreshes — but reads from a lagging follower and sees old data. Fix: route the user's own reads to the leader for 5 seconds after a write (read-your-writes consistency).

Key Design Decisions

⚡

Synchronous vs Asynchronous replication: Sync gives zero lag (strong consistency) but every write waits for replica ACK — doubles write latency. If a replica is slow/down, the leader is stuck. Async is fast but risks data loss on failover (leader had writes replicas didn't receive). Answer: semi-synchronous — wait for at least 1 replica, not all.

🔄

Leader failover: When the leader dies, a follower must be promoted. With async replication, the promoted follower may lack recent writes (data loss). Split-brain: two nodes both think they're leader → data divergence. Use consensus-based failover (Orchestrator, Patroni) with fencing.

📖

Read-your-writes consistency: After a write, route that user's reads to the leader (or a sync replica) for a brief period. ProxySQL can check SHOW SLAVE STATUS — if replica lag > 1s, route to leader. This is the #1 operational headache of replication.

🌍

Multi-region replication: For global availability, replicate across data centers. Leader-follower works with a single leader (writes all go to one region). Multi-leader gives local writes everywhere but you must solve conflict resolution.

When to Use

  • "How do you handle database failover?" — Replication with automated leader election (Patroni, Orchestrator).
  • "How do you scale reads?" — Add read replicas behind a connection pooler (ProxySQL, PgBouncer).
  • "How do you set up multi-region?" — Cross-region replication with leader in primary region.
  • "Availability requirements 99.99%+" — Replication + automated failover is the baseline.

Interview signal: When you mention read replicas, immediately address replication lag and read-after-write consistency. This single concern shows you understand the #1 operational headache.

Real-World Examples

  • Shopify — MySQL leader-follower with ProxySQL routing. 1 leader + 5 read replicas per cluster. Semi-sync replication. Failover via Orchestrator in ~10–30 seconds.
  • Aurora (AWS) — Leader-follower with shared storage layer. Up to 15 read replicas. Replication lag typically <10ms because replicas share the storage volume.
  • Cassandra — Leaderless with configurable consistency. Write to 2/3 nodes (W=2), read from 2/3 (R=2). W+R > N ensures strong consistency when needed.
  • PostgreSQL — Streaming replication with synchronous_commit options. Patroni for automated failover with etcd-based leader election.

Back-of-Envelope Numbers

Metric Value
Shopify read/write ratio~50:1 (5 replicas handle it)
Replication lag (async) p50 / p9910ms / 200ms
Max acceptable lag1 second (route to leader beyond this)
Leader write capacity (MySQL, 64-core)~10K writes/sec
Read replica capacity~40K QPS each
Failover time (automated)10–30 seconds
Semi-sync write latency overhead+2–5ms per write