Primary-Replica Replication: Making Databases Highly Available

The GitHub Outage That Changed Everything

Primary-Replica Replication: Making Databases Highly Available is one of the most widely used techniques for ensuring database availability, fault tolerance, and scalability. It allows applications to continue serving users even when a database server fails by maintaining synchronized copies of data across multiple servers. A famous real-world example highlighting both the benefits and challenges of replication is GitHub's October 2018 outage, where a network partition and replica failover led to data divergence, demonstrating why understanding primary-replica replication is essential for designing reliable distributed systems.

How Replication Works

Primary-Replica Architecture:

  Writes (INSERT/UPDATE/DELETE)
     |
     v
  +----------+     WAL Stream      +----------+
  | Primary  | ------------------> | Replica 1| (read traffic)
  | (Leader) | ------------------> | Replica 2| (read traffic)
  +----------+     replication     +----------+
     |
     v
  Write-Ahead Log (WAL)
  - Every change is first written to WAL on disk
  - WAL is streamed to replicas in order
  - Replicas apply WAL entries to stay in sync

  Traffic routing:
  - ALL writes   --> Primary only
  - ALL reads    --> Replicas (distributes load)
  - 90% reads?   --> 1 primary + 3 replicas = 4x read capacity

Synchronous vs Asynchronous Replication

SYNCHRONOUS replication:

  Client       Primary         Replica
    |--write-->|               |
    |          |--WAL stream-->|
    |          |<--ACK---------|  (replica confirms write)
    |<--OK-----|               |  (client gets response)

  Guarantee: if primary dies, replica has ALL data
  Cost: every write waits for replica RTT (~1-5ms same DC)
  Use when: zero data loss is critical (financial systems)


ASYNCHRONOUS replication:

  Client       Primary         Replica
    |--write-->|               |
    |<--OK-----|               |  (client gets response immediately)
    |          |--WAL stream-->|  (replica catches up later)
    |          |               |

  Guarantee: NONE - if primary dies before stream reaches
             replica, those writes are LOST
  Cost: zero write latency overhead
  Use when: speed matters more than perfect durability
  Replication lag: typically 1-100ms


SEMI-SYNCHRONOUS:
  Wait for at least 1 of N replicas to confirm.
  Balance: fast writes + at least one safe copy.

Failover: Promoting a Replica

Failover process when primary dies:

  1. Detection: health checker pings primary every 1-5 sec
     3 consecutive failures = primary declared dead

  2. Election: choose the most up-to-date replica
     (the one with the highest WAL position)

  3. Promotion: chosen replica becomes the new primary
     - Starts accepting writes
     - Other replicas point to new primary

  4. Traffic redirect:
     - DNS update (slow: TTL-dependent, 30-300 sec)
     - Virtual IP / proxy (fast: 1-5 sec)
     - Application-level discovery (e.g., Consul, etcd)

  Danger: SPLIT-BRAIN
  If old primary is not dead (just slow/partitioned),
  TWO primaries accept writes simultaneously!

  Prevention: STONITH (Shoot The Other Node In The Head)
  - Power-fence the old primary before promoting replica
  - Use a quorum (majority vote) to decide who is primary

Read-After-Write Consistency

The most common replication bug: a user updates their profile, refreshes the page, and sees the OLD data. Why? Their write went to the primary, but their read was routed to a replica that has not caught up yet.

Solutions:

  1. Read-your-own-writes:
     After a write, route THAT USER's reads to the primary
     for the next 5 seconds. Then switch back to replicas.

  2. Monotonic reads:
     Pin each user to one specific replica (by user_id hash).
     They always read from the same replica = consistent view.

  3. Causal consistency:
     Pass a 'read-after' token with the write response.
     Replica waits until it has applied that WAL position
     before serving the read.

PostgreSQL Replication Setup

-- Check replication lag on replica:
SELECT
  now() - pg_last_xact_replay_timestamp() AS replication_lag;
-- Result: 00:00:00.023 (23ms behind - healthy)
-- If this shows minutes/hours, the replica is falling behind.

-- Primary: check connected replicas
SELECT client_addr, state, sent_lsn, replay_lsn,
       (sent_lsn - replay_lsn) AS byte_lag
FROM pg_stat_replication;

-- Application connection routing (Python example)
import psycopg2

# Writes always to primary
def write_user(data):
    conn = psycopg2.connect(host='primary.db.internal')
    # ...

# Reads to replica (with fallback to primary)
def read_user(user_id):
    conn = psycopg2.connect(host='replica.db.internal')
    # ...

Interview Tip

When an interviewer mentions database replication, say: 'I would set up asynchronous replication with semi-synchronous for at least one replica, giving us both speed and a guarantee of at least one redundant copy. For read scaling, I would route reads to replicas and writes to the primary. I would handle the read-after-write consistency problem by routing a user's reads to the primary for a few seconds after they write. For failover, I would use automated promotion with STONITH fencing to prevent split-brain.'

Key Takeaway

Primary-replica replication provides high availability (survive primary failure) and read scaling (distribute reads across replicas). Choose between synchronous (safe, slow) and asynchronous (fast, slight lag). Always plan your failover strategy before you need it, and handle read-after-write consistency for user-facing operations.