Database Replication - Single-Leader

Day 7 taught you to distribute traffic across servers. Day 8 taught you to pool database connections. But what if the database itself is the bottleneck? You can’t load balance a database the same way - data has state. Replication is the answer: create copies of the data, distribute reads across them, and keep one leader for writes.

Day 22 taught you partial indexes - index only the rows you query. But what happens when even a perfectly indexed database can’t handle the read load? A single PostgreSQL instance maxes out at its CPU, RAM, and disk I/O. You’ve optimized the queries. You’ve tuned the indexes. The next step is replication: create copies of the data on separate machines, and distribute read traffic across them.

The problem: one database, one bottleneck

Your e-commerce platform has one PostgreSQL instance. It handles 5,000 queries/second at peak. The CPU is at 85%. You’ve done everything from Days 1-22:

• Indexes (Day 1, 6, 20, 22)
• Connection pooling (Day 8)
• Keyset pagination (Day 17)
• Covering indexes for index-only scans (Day 20)

But the reads keep growing. Adding more CPU or RAM (vertical scaling) eventually hits a ceiling: even exceptionally large cloud instances have finite capacity, become expensive, and remain a single failure domain. You need horizontal scaling: multiple database servers, each handling a portion of read traffic.

But there’s a problem: you can’t load-balance a database the way you load-balance stateless web servers (Day 7). Databases have state. If you just round-robin writes to two databases, they’ll diverge - each has a different view of the data.

Single-leader replication: one writer, many readers

The simplest replication model is single-leader (also called primary-secondary, master-slave, or source-replica):

1. One leader (primary): accepts both reads and writes.
2. N followers (replicas): accept reads only. They receive a copy of the leader’s write log and apply it locally.
3. The application routes write queries to the leader and read queries to followers (or a mix).

-- Application connection logic (pseudocode):
if query.is_write():
    conn = pool.get_leader_connection()
else:
    conn = pool.get_follower_connection()  # round-robin across followers
conn.execute(query)

How PostgreSQL does it: streaming replication

PostgreSQL uses streaming replication for single-leader setups:

1. The leader records every change to a WAL (Write-Ahead Log) - the same mechanism that powers crash recovery (Day 10).
2. Followers connect to the leader and receive WAL records in real time over a TCP connection.
3. Each follower replays the WAL locally, applying the same changes in the same order.

# On the leader (postgresql.conf):
wal_level = replica
max_wal_senders = 10        # max follower connections
synchronous_commit = on     # wait for local WAL flush at commit

# Create a replication slot (PostgreSQL 10+):
SELECT pg_create_physical_replication_slot('follower_1');

# On each follower (postgresql.conf):
hot_standby = on             # allow reads during replay
primary_conninfo = 'host=leader_ip port=5432 user=replication password=...'

The key insight: replication is built on the WAL. The same log that ensures crash recovery (Day 10) is reused for replication. WAL senders manage follower connections, and replication slots can retain required WAL; followers then receive and replay that stream.

Asynchronous vs synchronous: the consistency trade-off

By default, PostgreSQL replication is asynchronous. The default synchronous_commit = on makes COMMIT wait for the leader’s local WAL flush; it does not make a standby synchronous unless synchronous_standby_names is configured:

• The leader commits a transaction, writes the WAL locally, and returns to the client.
• The follower receives the WAL later - maybe 5ms, maybe 500ms.
• If the leader crashes before the follower catches up, the last few committed transactions are lost.

Synchronous replication waits for a configured standby acknowledgement before committing — with synchronous_commit = on, that means WAL flush:

# On the leader:
synchronous_standby_names = 'FIRST 1 (follower_1)'
synchronous_commit = on

Now the leader blocks on COMMIT until follower_1 confirms it flushed the WAL (the behavior of synchronous_commit = on). If the leader crashes after COMMIT returns, follower_1 has the WAL - no loss of that acknowledged transaction.

But there’s a cost: every write now has the network RTT to the follower added. If the follower is in the same data center (1ms RTT), the overhead is negligible. If it’s in another region (50ms RTT), every write takes 50ms longer.

Mode	Write Latency	Standby durability at acknowledgement	Use Case
Async	Minimal (local only)	Recent commits may not have reached a standby	Read scaling, analytics replicas
Synchronous	Higher (waits for follower)	Acknowledged WAL reached the selected synchronous standby	HA failover, financial data

Replication lag: the unavoidable consequence

With async replication, healthy followers with sufficient retained WAL and apply capacity are eventually consistent - they’ll catch up, but not instantly. The delay between the leader generating WAL and a follower receiving and replaying it is called replication lag.

-- On a follower, measure WAL received but not replayed:
SELECT
  pg_size_pretty(
    pg_wal_lsn_diff(pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn())
  ) AS received_not_replayed,
  CASE
    WHEN pg_last_wal_receive_lsn() = pg_last_wal_replay_lsn() THEN interval '0'
    ELSE now() - pg_last_xact_replay_timestamp()
  END AS approximate_apply_delay;

now() - pg_last_xact_replay_timestamp() by itself is not a reliable lag clock: on an idle, fully caught-up follower it grows simply because there is no new transaction to replay. The receive-versus-replay LSN difference measures the follower’s local apply backlog. Measuring transport lag as well requires comparing with a recently observed leader WAL position; a follower cannot infer WAL that it has not received.

Workload (teaching)	Lag ~ms (order)	P95 ~ms	P99 ~ms
Idle (no writes)	~0	~0	~0
Light (100 writes/sec)	~2	~5	~6
Moderate (1K writes/sec)	~18	~35	~40
Heavy (10K writes/sec)	~140	~290	~340
Burst (50K writes in 5s)	~900	~1200	~1400

Key insight: lag grows with write throughput and apply backlog. At ~1K writes/sec, lag is often invisible to users. At ~10K writes/sec, read-after-write problems appear. At burst load, users WILL see stale data unless you route around lag.

The shape is what matters for interviews: lag is not a constant; it scales with write rate and follower apply capacity. At moderate load, lag is often negligible. At heavy or burst load, you must route recent writers to the leader (or accept stale reads).

Read-after-write consistency: three patterns

The replication lag problem creates read-after-write inconsistency: a user writes data, then reads it back, but sees the old version because the read went to a follower that hasn’t caught up.

Three patterns solve this:

Pattern 1: Read-your-writes via session stickiness

After a user writes, route their next reads to the leader for a short window (e.g., 5 seconds). This guarantees they see their own write.

# Pseudocode:
def write_then_read(user_id, data):
    # Write goes to leader
    leader.execute("INSERT INTO ... VALUES (?, ?)", user_id, data)

    # Next reads from this user go to leader for 5 seconds
    cache.set(f"sticky:{user_id}", "leader", ttl=5)

def read(user_id):
    # Check if user recently wrote
    if cache.get(f"sticky:{user_id}") == "leader":
        return leader.execute("SELECT ... WHERE user_id = ?", user_id)
    else:
        return follower.execute("SELECT ... WHERE user_id = ?", user_id)

Pro: Simple. User always sees their own writes. Con: Leader handles more reads. Sticky sessions break if the user switches devices.

Pattern 2: Staleness-aware follower routing

Continuously compare the leader’s sampled WAL position with each follower’s replay position. Route ordinary stale-tolerant reads away from followers whose byte/time lag exceeds your threshold.

def read_with_lag_check(query):
    follower = select_follower()
    lag = replica_monitor.estimated_lag(follower)  # samples leader + follower
    if lag < STALENESS_BUDGET:
        return follower.execute(query)
    else:
        return leader.execute(query)  # follower too stale, use leader

Pro: Keeps badly lagging followers out of the read pool without adding a metadata query to every request. Con: A sampled lag threshold does not prove that a particular user’s commit has replayed, so this is not strict read-your-writes.

Pattern 3: Exact read-your-writes via a WAL-position token

After a transaction commits, read the leader’s flushed WAL position and return it as a causal token. On a later read, use a follower only if its replay LSN has reached that token; otherwise try another follower or fall back to the leader. Preserve the greatest token the client has observed to keep later reads monotonic.

def commit_write(data):
    leader.execute_transaction(data)
    return leader.execute("SELECT pg_current_wal_flush_lsn()")

def read_monotonic(user_id, required_lsn):

    for attempt in range(3):
        follower = select_follower()
        replay_lsn = follower.execute("SELECT pg_last_wal_replay_lsn()")
        if replay_lsn >= required_lsn:
            return follower.execute("SELECT ... WHERE user_id = ?", user_id)
        # Follower too stale, try another
    # All followers stale - use leader
    return leader.execute("SELECT ... WHERE user_id = ?", user_id)

Pro: Ties routing to the user’s actual commit position instead of a clock estimate. Con: PostgreSQL-specific and more complex. The client or session must carry its latest LSN token.

What breaks? - Anti-patterns

Replication introduces new failure modes that don’t exist with a single database. Here are two that catch teams off guard:

Anti-pattern 1: Shared auto-increment ranges in writable multi-leader systems

-- A physical PostgreSQL standby is read-only and replays WAL.
-- Sequence changes are WAL-logged and replicated; it does not independently
-- generate IDs while acting as a standby.

-- Collision advice applies when multiple writable leaders (or logical
-- replication peers) allocate IDs independently. Give each writer disjoint
-- ranges, use a coordinated allocator, or use suitably generated UUIDs.

Anti-pattern 2: Counting on followers for real-time analytics

-- This is wrong:
-- "How many orders did we get in the last 5 minutes?"
SELECT COUNT(*) FROM orders WHERE created_at > now() - INTERVAL '5 minutes';

-- If this runs on a follower with 2s lag, the answer is wrong.
-- The last 2 seconds of orders haven't been replicated yet.
-- Fix: run real-time analytics on the leader, or accept staleness
-- and label the dashboard "data may be up to 2s delayed."

Failover: when the leader dies

When the leader crashes, a follower must be promoted to leader. This is called failover:

1. Detect - A health check detects the leader is down (timeout, not responding).
2. Promote - A follower is promoted to leader (pg_ctl promote or pg_promote()).
3. Reconfigure - Application connection pool updates to point writes to the new leader.
4. Recover - The old leader, when it comes back, becomes a follower (or is discarded and rebuilt from a base backup).

Split-brain is the scariest failure mode: two nodes both believe they’re the leader, both accept writes, and their histories diverge. Safe failover therefore needs coordinated leader election plus fencing that prevents the old leader from continuing to accept writes. Many designs use a consensus-backed coordination store; tools such as Patroni and pg_auto_failover provide coordinated PostgreSQL promotion, but their failure assumptions and fencing still need to match the deployment.

Connection to previous days

• Day 1 (Indexes): Indexes make queries fast on ONE database. Replication makes reads fast across MANY databases. You still need indexes on every follower - replication copies data, not performance magic.
• Day 7 (Load Balancing): LB distributes stateless traffic across servers. Replication distributes stateful reads across database copies. The leader is the “primary” - followers are “secondaries” in the same way LB backends are “workers.”
• Day 8 (Connection Pooling): More followers = more connections needed. The pool must now route writes to the leader and reads to followers - a “read/write split pool.”
• Day 10 (Transactions): ACID guarantees apply within ONE database. Replication introduces a new question: is a transaction “committed” when the leader confirms, or when the followers confirm? Sync vs async.
• Day 13 (Normalization): Normalized schemas replicate better - smaller tables, less WAL to ship. Denormalized tables (with redundant JSON blobs) generate more WAL per write.
• Day 17 (Keyset Pagination): Keyset pagination on a follower with replication lag can skip rows - the cursor is based on created_at, but new rows haven’t arrived yet.
• Day 20 (Covering Indexes): Covering indexes reduce heap fetches and can make reads faster on each node. Reads do not create WAL merely by fetching heap or index pages, so they do not add replication traffic.
• Day 22 (Partial Indexes): A physical standby does not independently build a partial index. It replays the primary’s WAL for both heap and index changes, so the primary’s index definition and contents are reproduced on the standby.

Key takeaways

1. Single-leader physical replication: one writer, read-only standbys. The leader streams WAL; standbys replay heap, index, and sequence changes and may serve read-only queries.
2. Async replication has lag. Healthy followers catch up, but not instantly. Measure both transport and replay position; replay timestamps alone misreport idle systems.
3. Read-after-write consistency is your problem. Session stickiness is simple, monitoring enforces only a general staleness budget, and a WAL-position token can prove that a follower replayed a specific write.
4. Failover is where things break. Split-brain is the #1 risk. Use coordinated promotion and fencing tooling such as Patroni or pg_auto_failover - don’t hand-roll failover.
5. Replication scales reads, not writes. Every write still goes to one leader. Sharding partitions data across independent ownership domains; multi-leader replication allows multiple writers and requires conflict handling. They are distinct designs, and either adds substantial complexity.