Replication

Ten minutes before the on-sale, the catalog database is at 90% CPU — almost all of it from SELECTs for the event page. The team adds two read replicas and routes reads to them. CPU drops to 30%. Then the support channel lights up: "I paid, the confirmation e-mail arrived, but My tickets is empty!" Nothing was lost. The fan's next read simply went to a replica that had not yet received the write.

Replication — keeping a copy of the same data on several machines — is the most used scaling and availability tool in databases, and it comes with exactly this kind of surprise. This page covers the three ways to replicate (single-leader, multi-leader, leaderless), what replication lag does to users, and what happens when the leader dies.

Why replicate

Replication does not scale writes: every copy must apply every write. For that you need partitioning.

Single-leader replication

One node, the leader (primary), accepts all writes. It records each change in its log — the write-ahead log (WAL) in PostgreSQL, the binlog in MySQL — and ships the log to followers (replicas, standbys), which replay it in the same order. Reads can go to any node¹.

flowchart LR
    app["catalog service"] -->|"writes"| P[("primary")]
    P -->|"WAL stream"| R1[("replica 1")]
    P -->|"WAL stream"| R2[("replica 2")]
    app -->|"reads"| R1
    app -->|"reads"| R2
    app -->|"reads that must be fresh"| P

This is the model of PostgreSQL streaming replication, MySQL replication, SQL Server Always On, MongoDB replica sets and most managed services (Amazon RDS read replicas, Cloud SQL, Azure Database). It is simple to reason about because there is exactly one order of writes — the leader's.

Synchronous, asynchronous, semi-synchronous

The key decision is when the leader tells the client "committed":

sequenceDiagram
    participant C as Client
    participant P as Primary
    participant S as Sync standby
    participant A as Async replica
    C->>P: COMMIT hold A-12
    P->>P: write WAL locally
    P->>S: ship WAL
    S-->>P: flushed
    P-->>C: committed
    P->>A: ship WAL (later)
    Note over A: may lag ms … s behind

In PostgreSQL this is the synchronous_commit and synchronous_standby_names settings⁵; ANY 1 (s1, s2) means "wait for any one of the two standbys" — a quorum that tolerates one slow standby. Within one region, a synchronous round trip costs around a millisecond; across continents it costs tens to hundreds of milliseconds, which is why cross-region replicas are almost always asynchronous.

Replication lag and its anomalies

An asynchronous replica is behind the leader by the replication lag: usually milliseconds, but seconds or minutes when the leader takes a burst of writes, a replica replays a huge transaction, or the network is congested. Lag creates three user-visible anomalies²:

sequenceDiagram
    participant F as Fan
    participant O as order service
    participant P as Primary
    participant R as Replica (lag 800 ms)
    F->>O: POST /orders/9001/confirm
    O->>P: UPDATE orders SET status = 'PAID'
    P-->>O: committed
    O-->>F: 200 "Payment confirmed"
    F->>O: GET /me/tickets
    O->>R: SELECT … WHERE customer_id = 7
    R-->>O: (no row yet)
    O-->>F: "You have no tickets"

How likely is it? Under an illustrative model — lag exponentially distributed around a mean, and the fan's next click going to a replica — the answer depends entirely on the lag at that moment:

On a quiet day the bug is invisible; during the on-sale — exactly when the most fans are watching — most of them hit it. That is why lag bugs survive testing.

Fixing read-your-writes

Monitor lag as a first-class metric — pg_stat_replication.replay_lag in PostgreSQL, ReplicaLag in Amazon RDS — and alert on it (see Observability). A replica that is minutes behind is worse than no replica.

/**
 * Two connection pools — primary and replica — behind one DataSource.
 * A transaction marked @Transactional(readOnly = true) gets a replica connection;
 * every other transaction gets a primary connection. The choice happens when the
 * transaction runs its first statement (that is what "lazy" means here).
 */
@Configuration
public class DataSourceConfig {

    @Bean
    @ConfigurationProperties("inventory.datasource.primary")
    HikariDataSource primaryDataSource() {
        return new HikariDataSource();
    }

    @Bean
    @ConfigurationProperties("inventory.datasource.replica")
    HikariDataSource replicaDataSource() {
        return new HikariDataSource();
    }

    @Bean
    @Primary
    DataSource dataSource(HikariDataSource primaryDataSource, HikariDataSource replicaDataSource) {
        var routing = new LazyConnectionDataSourceProxy(primaryDataSource);
        routing.setReadOnlyDataSource(replicaDataSource);     // Spring Framework 6.2+
        return routing;
    }

Try it. In the db-replicas scenario, a primary takes the writes and replicas serve the reads. Add replicas and watch read throughput rise while the primary's load stays flat; then increase the write rate and watch the replication lag grow — and with it, the share of stale reads.

Connection pooling

Replicas multiply where queries can go; connection pools decide how many can run at once. In PostgreSQL each connection is a separate operating-system process with its own memory, so thousands of idle connections cost real RAM and CPU — and the database does not get faster beyond a few concurrent queries per CPU core.

Size pools with Little's Law, not with guesses: busy connections = queries per second × query time. At 2,000 queries/s × 5 ms = 10 connections busy on average. A pool of 20–30 handles bursts; a pool of 500 only adds contention.

The catch in microservices: 20 pods × a pool of 50 = 1,000 connections to one primary. A connection pooler such as PgBouncer (or RDS Proxy, or Cloud SQL Auth Proxy with pooling) sits between them, multiplexing thousands of client connections onto a few dozen server connections. In transaction pooling mode a server connection is lent per transaction, so session state (SET, temporary tables, some prepared-statement uses) must not leak across transactions. You will measure this in Hands-on 6.

; transaction pooling: a server connection is lent for ONE transaction, then returned
pool_mode = transaction
; real connections to PostgreSQL per (database, user) pair
default_pool_size = 20
; client connections PgBouncer accepts (all services and replicas together)
max_client_conn = 1000

Failover: when the leader dies

If a follower dies, it reconnects and catches up from the log. If the leader dies, the system must fail over:

sequenceDiagram
    participant M as Failover manager (e.g. Patroni + etcd)
    participant P as Old primary
    participant S as Standby
    participant A as Apps
    M->>P: health check
    Note over P: no answer for 10 s
    M->>S: promote to primary
    S-->>M: promoted (new timeline)
    M->>A: update DNS / virtual IP / proxy
    A->>S: writes resume
    Note over P: if it comes back, it must be fenced<br/>and rejoin as a follower

Each step hides a hard problem:

• Detection uses timeouts. Too short: a GC pause or network blip triggers an unnecessary failover. Too long: minutes of write downtime.
• Lost writes. With asynchronous replication, writes acknowledged by the old leader but not yet shipped are gone. If those were seat holds, the new primary may sell the same seats again.
• Split brain. If the old leader was only partitioned, not dead, two nodes may accept writes. Systems prevent this with consensus on who the leader is (Patroni stores the leader key in etcd; see Consensus) and fencing: the old leader is cut off (STONITH, "shoot the other node in the head") or its writes are rejected by fencing tokens.
• Clients must follow. Through DNS, a virtual IP, a proxy or a driver that knows the topology.

Managed services package all of this — Amazon RDS Multi-AZ with a synchronous standby, Aurora with quorum storage, Cloud SQL high availability, Azure zone-redundant HA — with failovers that typically take from tens of seconds to a couple of minutes; the design question for you is what the application does meanwhile (retries with backoff, see Reliability).

# pg_rewind copies back only the blocks that changed since the timelines split,
# discarding the writes the old primary made that never reached the new one.
pg_rewind --target-pgdata="$PGDATA" --progress \
  --source-server="host=$NEW_PRIMARY user=$POSTGRES_USER password=$POSTGRES_PASSWORD dbname=$POSTGRES_DB"

# Same as pg_basebackup -R: start as a standby, streaming from the new primary.
touch "$PGDATA/standby.signal"
echo "primary_conninfo = 'host=$NEW_PRIMARY user=replicator password=${REPLICATION_PASSWORD:-replicator}'" \
  >> "$PGDATA/postgresql.auto.conf"

Try it. The db-failover scenario is a flash-sale checkout on a cluster with one primary and one asynchronous follower: 1,500 req/s, half of them writes. At t = 90 s the primary dies. Press Run and read the report: with the default seed about 1,600 writes are lost (acknowledged by the old primary, never shipped to the follower) and availability falls to about 90.6%, because every write fails during the 30 s failover. Select orders-db and set Replication to sync: lost writes drop to 0, but availability does not move. Set it back to async and the Failover time to 5 s: availability rises to about 98%, and the writes are lost again. The two knobs answer different questions: replication mode sets how much you lose (the RPO), detection and promotion set how long you are down (the RTO). The possible solution (open it in the full playground ↗) combines quorum replication with 2 followers, 2 shards and a 5 s failover: 0 lost writes, about 99.2% availability.

Multi-leader replication

In multi-leader (multi-primary, active-active) replication, several nodes accept writes and replicate to each other asynchronously. Typical reasons: one leader per region so writes are local; offline-first clients (a mobile app or a collaborative editor is effectively a leader); a second data center that must accept writes during a partition.

The price is write conflicts: two leaders accept different writes to the same record concurrently.

Leaderless replication

Amazon's Dynamo paper (2007)³ popularized a third design, used by Cassandra, ScyllaDB and Riak: no leader at all. The client (or a coordinator node) sends each write to all N replicas of a key and considers it successful when W of them acknowledge; it reads from R replicas and takes the newest version.

If R + W > N, every read set overlaps every write set in at least one replica, so a read sees the latest acknowledged write (with caveats under concurrent writes and failures — quorums alone are not linearizable). With N = 3:

Missed writes are repaired by read repair (a read that sees an old version writes the new one back), hinted handoff (a neighbour holds writes for a down node and replays them later; with a sloppy quorum those temporary holders even count toward W) and anti-entropy (background comparison of replicas with Merkle trees). Cassandra exposes the choice per query as consistency levels ONE, QUORUM, LOCAL_QUORUM, ALL.

Comparing the designs

How it connects

flowchart LR
    WHY["why replicate: availability,<br/>read scale, latency"] --> SL["single leader"]
    SL --> SYNC["sync vs async<br/>durability vs commit latency"]
    SL --> LAG["replication lag"]
    LAG --> RYW["read-your-writes,<br/>monotonic reads"]
    RYW -.-> CM["consistency models<br/>→ Distributed"]
    SL --> FO["failover: Patroni + etcd<br/>→ Distributed consensus"]
    SL --> POOL["connection pooling<br/>PgBouncer"]
    WHY --> ML["multi-leader<br/>conflicts, LWW"]
    WHY --> LL["leaderless<br/>R + W > N"]
    LL --> PART["partitioned stores<br/>→ Partitioning"]

Palette components: sql (a primary with read replicas; a synchronous standby for seats and money) · nosql (leaderless quorums: Cassandra, DynamoDB) · app (routes reads and writes, retries with backoff during a failover) · cache (sits in front of the catalog replicas) · dns (one way clients follow the new leader). Scenarios: db-replicas, db-failover, multi-region. Continues in: Partitioning, Distributed — consistency, Distributed — consensus, Reliability — disaster recovery, Hands-on 6 (read replica, PgBouncer), Exercise: cache + database.