Replication

• Replication copies every write from the leader to its replicas.
• Copies give failover, read capacity and copies near users.
• The cost: a copy lags, and a failover can lose what it never received.

A replica is always a little behind the leader. A read there can miss a change the same user just made.

Postgres streams its write-ahead log: the replica replays the exact bytes, not the statements.

I start with one leader and asynchronous standbys, and I add a synchronous standby when losing the last commits on failover is not acceptable.

Guarantees from the Postgres 16 documentation, with a synchronous standby set. off can lose up to 3 × wal_writer_delay: 600 ms at the default 200 ms. Latency: median of 500 commits, primary and standby on one laptop; across regions add one network round trip.

I choose the durability level per transaction. Payments wait for a synchronous standby to flush; logs only wait for the local disk.

Writes go to the leader. Reads go to standbys, except a read that must see the user's own write, which waits for a token or goes to the leader.

Postgres gives me ordered WAL streaming, per-transaction durability, and LSN functions for read-your-writes. Leaderless stores give me per-query consistency levels instead.

write(value, W):
  send value to all N replicas
  wait for W acks1                         // the write set

read(R):
  ask all N replicas
  wait for R replies2                      // the read set
  RETURN the reply with the newest version3

R + W > N4  =>  every read set meets every write set

// Outcome is what a write with W acknowledgements, then a read with R replies, can return,
// over every choice of which W nodes acknowledge and which R replicas reply.
type Outcome struct {
  WriteOK bool // W nodes were reachable
  ReadOK  bool // R replicas were reachable
  Cases   int  // pairs (write set, read set)
  Stale   int  // pairs where no reply carries the write: the read returns the old value
}

// Enumerate tries every write set and every read set. A read sees the write when one of its R
// replies comes from a node in the write set; it returns the newest version among the replies.
func Enumerate(n, r, w int, p Pattern) Outcome {
  wt, rt := targets(n, p)
  o := Outcome{WriteOK: len(wt) >= w, ReadOK: len(rt) >= r}
  if !o.WriteOK || !o.ReadOK {
    return o
  }
  for _, acks := range subsets(wt, w) {
    for _, reads := range subsets(rt, r) {
      o.Cases++
      if !overlaps(acks, reads) {
        o.Stale++
      }
    }
  }
  return o
}

A write waits for W acknowledgements and a read for R replies. With R plus W greater than N, the two sets always share a replica, and the read takes the newest version.

Writes survive N − W failed replicas; reads survive N − R.

N = 3 with W = 2 and R = 2 survives one replica down for both reads and writes, and every read is fresh.

Every N from 1 to 5, every R and W, four patterns: 220 settings. Each enumerates all write sets and read sets.

I check two things: can each operation still gather enough replicas, and does every read set meet every write set.

Replica lag breaks three promises: users see their own writes, data never goes backwards, and an answer never appears before its question.

For read-your-writes I give the client its commit position. The replica waits until it has replayed that far, or the router reads the leader.

write(name):                  // leader
  UPDATE profiles SET name = ...
  token = WAL position        // after the commit1
  RETURN token

read(token):                  // replica
  WHILE replay position < token2:
    wait 2 ms                 // until a deadline3
  SELECT name ...

// WriteName changes a name on the leader and returns a version token: the WAL position just past
// the commit. It reads the position after the commit, in a second statement; a position read
// inside the transaction comes before the commit record.
func WriteName(ctx context.Context, leader *pgx.Conn, id int, name string) (string, error) {
  if _, err := leader.Exec(ctx, token["write_name"], id, name); err != nil {
    return "", fmt.Errorf("write name %d: %w", id, err)
  }
  var lsn string
  if err := leader.QueryRow(ctx, token["token"]).Scan(&lsn); err != nil {
    return "", fmt.Errorf("read commit position: %w", err)
  }
  return lsn, nil
}

// ReadNameAfter reads a name from a replica once the replica has replayed the WAL up to the token,
// so the caller sees its own write. It gives up at the context deadline; the caller can then read
// from the leader.
func ReadNameAfter(ctx context.Context, replica *pgx.Conn, id int, lsn string) (string, error) {
  for {
    var caught bool
    if err := replica.QueryRow(ctx, token["replayed"], lsn).Scan(&caught); err != nil {
      return "", fmt.Errorf("check replay position: %w", err)
    }
    if caught {
      break
    }
    select {
    case <-ctx.Done():
      return "", fmt.Errorf("replica has not replayed %s: %w", lsn, ctx.Err())
    case <-time.After(2 * time.Millisecond):
    }
  }
  return ReadName(ctx, replica, id)
}

UPDATE profiles SET name = $2 WHERE id = $1;

-- After the commit: the WAL position just past the commit record is the version token.
SELECT pg_current_wal_insert_lsn()::text;

-- On the replica: has it replayed the WAL up to the token?
SELECT pg_last_wal_replay_lsn() >= $1::pg_lsn;

right after

'Ada': the standby is 300 ms behind.

with token

'Grace', after a wait of about 300 ms.

remote_apply

'Hopper': the commit itself waited.

The commit position is a version token. A replica that has replayed past it has the write.

1. Detect: the leader stops renewing its key. Patroni gives the key a 30 s time to live by default.
2. Choose: the standby with the most replayed WAL loses the least.
3. Promote it and raise the term in the consistent store.
4. Fence: cut the old leader off, or make storage refuse its term.
5. Point clients at the new leader: DNS, a proxy, or the router's leader key.

I promote the standby with the most WAL, raise the term, and fence the old leader so its late writes are refused.

• Multi-leader: each region has a leader and accepts writes locally.
• Leaderless: any replica takes a write; quorums and read repair keep copies close.
• Both need a rule for two writes to the same key at the same time.

I avoid multi-leader writes unless users in several regions must write locally. Then I give each record a home region or merge conflicts with a CRDT.

Demand example: 1 billion reads a day is 1,000,000,000 / 86,400 ≈ 11,574 a second. Replica capacity is the lab measurement times the copy count. Light in fibre covers about 200 km per ms, so 4,000 km is 20 ms each way.

I start with one Postgres and archived WAL. Then I add a standby for failover, replicas for reads, and a synchronous standby when no commit may be lost.