Home › Database Replication - Primary-Replica, Multi-Primary, Quorum

Database Replication - Complete Deep Dive

Prerequisites: Database Indexing, CAP Theorem Used in: Key-Value Store, Chat System, Digital Wallet

What is Database Replication?

Database replication is the process of maintaining copies of the same data on multiple machines (nodes) to improve availability, fault tolerance, and read performance.

Real-world analogy: Imagine a popular library with one original copy of every book. If that building burns down, all books are lost. Replication is like making copies and storing them in multiple branch libraries. Readers can go to any branch to read, but when a new book arrives, you need a system to decide which branch gets it first and how other branches get their copies.

Why Replicate?

Reason Explanation
High Availability If one node dies, others serve traffic
Read Scalability Distribute read load across replicas
Geo-locality Place data closer to users geographically
Disaster Recovery Survive entire data center failures

How It Works

Primary-Replica (Leader-Follower)

One node accepts writes (primary), others replicate and serve reads.

flowchart LR
    C[Client Write] --> P[Primary Node]
    P -->|Replication Stream| R1[Replica 1]
    P -->|Replication Stream| R2[Replica 2]
    P -->|Replication Stream| R3[Replica 3]
    CR1[Client Read] --> R1
    CR2[Client Read] --> R2
    CR3[Client Read] --> R3

    classDef client fill:#f96,stroke:#333,color:#000
    classDef service fill:#6f6,stroke:#333,color:#000
    classDef data fill:#ff6,stroke:#333,color:#000

    class C,CR1,CR2,CR3 client
    class P service
    class R1,R2,R3 data

How it flows:

  1. Client sends write to the primary node
  2. Primary writes to its local storage (WAL + commit)
  3. Primary streams changes to all replicas
  4. Replicas apply changes in the same order
  5. Clients read from any replica (eventual consistency) or from primary (strong consistency)

Multi-Primary (Leader-Leader)

Multiple nodes accept writes. Each primary replicates to the others.

flowchart LR
    C1[Client US] --> P1[Primary US]
    C2[Client EU] --> P2[Primary EU]
    P1 <-->|Bi-directional Replication| P2
    P1 --> R1[Replica US-1]
    P2 --> R2[Replica EU-1]

    classDef client fill:#f96,stroke:#333,color:#000
    classDef service fill:#6f6,stroke:#333,color:#000
    classDef data fill:#ff6,stroke:#333,color:#000

    class C1,C2 client
    class P1,P2 service
    class R1,R2 data

Conflict resolution strategies:

Synchronous vs Asynchronous Replication

sequenceDiagram
    participant C as Client
    participant P as Primary
    participant R as Replica

    Note over C,R: Synchronous Replication
    C->>P: Write X=5
    P->>R: Replicate X=5
    R-->>P: ACK received
    P-->>C: Write confirmed

    Note over C,R: Asynchronous Replication
    C->>P: Write Y=10
    P-->>C: Write confirmed immediately
    P->>R: Replicate Y=10 (later)
    R-->>P: ACK received (later)
Aspect Synchronous Asynchronous
Durability Guaranteed on N nodes Guaranteed only on primary
Latency Higher (waits for replica ACK) Lower (immediate response)
Availability Lower (replica failure blocks writes) Higher (primary works alone)
Data loss risk None Possible on primary crash
Use case Financial transactions Social media feeds

Semi-synchronous: One replica is synchronous (guarantees at least one backup), others are async. Used by MySQL and PostgreSQL in practice.

Replication Lag

The delay between a write on the primary and that write appearing on a replica.

Problems caused by lag:

  1. Stale reads — user writes, then reads from replica and sees old data
  2. Inconsistent reads — two reads from different replicas return different values
  3. Causal violations — reply appears before the original message

Read-Your-Writes Consistency

flowchart TD
    W[User writes comment] --> P[Primary]
    P --> R[Replica - lagging 2s]
    RD[User reads own profile] -->|Route to Primary| P
    RD2[Other user reads profile] -->|Route to Replica| R

    classDef client fill:#f96,stroke:#333,color:#000
    classDef service fill:#6f6,stroke:#333,color:#000
    classDef data fill:#ff6,stroke:#333,color:#000

    class W,RD,RD2 client
    class P service
    class R data

Strategies:

Quorum Reads and Writes

In leaderless replication (e.g., DynamoDB, Cassandra), writes go to multiple nodes and reads also query multiple nodes.

Formula: R + W > N guarantees overlap between read and write sets.

Variable Meaning
N Total number of replicas
W Nodes that must ACK a write
R Nodes that must respond to a read 
flowchart LR
    C[Client Write] --> N1[Node 1 - ACK]
    C --> N2[Node 2 - ACK]
    C --> N3[Node 3 - timeout]

    CR[Client Read] --> N1
    CR --> N2
    CR --> N3

    subgraph Quorum["W=2 R=2 N=3"]
        N1
        N2
        N3
    end

    classDef client fill:#f96,stroke:#333,color:#000
    classDef service fill:#6f6,stroke:#333,color:#000
    classDef data fill:#ff6,stroke:#333,color:#000

    class C,CR client
    class N1,N2 service
    class N3 data
Configuration Behavior Use Case
W=N, R=1 All nodes must ACK write; any single node read works Write-heavy consistency needs
W=1, R=N Fast writes; read from all nodes to find latest Read-heavy workloads
W=2, R=2, N=3 Balanced; tolerates 1 node failure General distributed stores
W=3, R=3, N=5 Strong consistency; tolerates 2 failures Financial systems

Failover

When the primary dies, a replica must be promoted.

sequenceDiagram
    participant P as Primary
    participant R1 as Replica 1
    participant R2 as Replica 2
    participant M as Monitor

    P->>M: Heartbeat
    Note over P: Primary crashes
    M->>M: Detect missing heartbeats
    M->>R1: Promote to Primary
    R1->>R2: Resume replication as new primary
    Note over R1: Accepts writes now

Failover challenges:

Real-world examples:

When to Use / When NOT to Use

✅ Use replication when:

❌ Don’t use (or be careful) when:

Common Interview Questions

Q1: What’s the difference between replication and sharding?

Replication copies the same data to multiple nodes for availability and read scaling. Sharding splits different data across nodes for write scaling and storage capacity. In production systems you typically use both: shard the data, then replicate each shard.

Q2: How do you handle split-brain in primary-replica setups?

Use fencing tokens (monotonically increasing IDs). When a new primary is elected, it gets a higher token. The storage layer rejects writes from any node presenting a stale (lower) fencing token. Additionally, use consensus protocols (Raft, Paxos) to ensure only one primary is elected at a time.

Q3: When would you choose multi-primary over single-primary?

Multi-primary is useful for geo-distributed writes where latency to a single primary is unacceptable (e.g., a user in Europe writing to a primary in US-East). However, it introduces write conflicts. Use it when conflicts are rare and can be resolved automatically (LWW, CRDTs). Avoid it for data with strict invariants (account balances, inventory).

Q4: How does DynamoDB achieve single-digit millisecond reads with replication?

DynamoDB uses quorum-based leaderless replication. For eventually consistent reads (default), it reads from one node — fast but might be stale. For strongly consistent reads, it reads from a quorum (R+W>N) to guarantee the latest version. DynamoDB also uses anti-entropy (Merkle trees) to keep replicas in sync.

Q5: What’s the tradeoff between W=1 and W=N?

W=1 gives low write latency and high availability (write succeeds even if N-1 nodes are down), but risks data loss if that one node fails before replicating. W=N gives maximum durability (data on all nodes before ACK) but a single slow or failed node blocks all writes. Most systems use W=majority for a balance.

← Back to Fundamentals

All Concepts HLD Designs

💬 Comments