# DDIA Ch. 6: the same SQL, two different rows

## Concept

The simplest way to replicate a database is to ship every write *statement* to
the followers and have them re-run it — statement-based replication. It sounds
obviously correct: same SQL, same result. But a statement that calls a
nondeterministic function — `now()`, `random()`, `nextval()` — is evaluated
*again* on each follower, at a different instant, and produces a **different
row**. The replica silently diverges from the leader. You will run one
`INSERT … now(), random()` as the leader and "replay" the identical text as a
follower, and watch the two rows disagree — which is exactly why real databases
ship the computed row (or the WAL), not the SQL.

## Provenance

*Designing Data-Intensive Applications*, 2nd ed. (Kleppmann & Riccomini,
2025), Chapter 6 — *Replication*, §"Statement-based replication":

> Any statement that calls a nondeterministic function, such as NOW … or RAND …
> is likely to generate a different value on each replica.

The book lists this as the first way statement-based replication "can break down,"
and why leaders moved to logical (row-based) and WAL shipping. Here you make it
break.

## Prerequisites

The surprise is about *what* the leader sends to followers. These help; each line
notes where it shows up.

1. **Replication log formats** — a leader can ship the SQL *statement*, the
   computed *row* (logical/row-based), or the raw *WAL* (physical). Statement-based
   is the tempting-but-fragile one.
2. **Deterministic vs. nondeterministic functions** — `now()`, `random()`,
   `nextval()` return different values each call/instant; re-executing them on a
   follower does not reproduce the leader's value.
3. **Replica divergence** — when followers hold *different data* than the leader,
   not just older data. Divergence is corruption; lag is not.

### Where to learn them

- **Replication log formats (#1):** DDIA §"Implementation of Replication Logs"
  (statement-based, WAL shipping, logical/row-based).
- **Why row-based won (#2–#3):** the MySQL docs on statement vs row binlog
  formats — a real system that offers both and defaults to row for safety.

If only one is new, make it #1 — the choice of *what to ship* is the whole point.

## Environment

The failure is inherent, so it reproduces every run — but the *specific* values
differ each time (that non-reproducibility is the lesson):

- macOS 26.5.2 (Darwin 25.5.0), arm64
- Docker 29.6.2, a single `postgres:16` (PostgreSQL 16.14) instance

## Mental model

Postgres actually replicates via the WAL (physical) — it ships the *bytes that
changed*, so followers never re-evaluate anything and never diverge. To *see* why
that design exists, we simulate statement-based replication by hand on one
instance: run a write statement once as the "leader," then run the **identical
statement text** again as a "follower replaying the log." Because `now()` and
`random()` are computed at execution time, the follower's row differs from the
leader's. The demo is one SQL file, `code/statement_replication.sql`.

## Setup

```
cd study/ddia/ch06/code
docker run -d --name ddia-ch6-stmt -e POSTGRES_PASSWORD=study -e POSTGRES_DB=study postgres:16
sleep 3
docker cp statement_replication.sql ddia-ch6-stmt:/tmp/
```

Do **not** read the output yet — make each prediction first.

## Steps

### Step 1 — leader writes, follower replays the same statement (the surprise)

**Predict.** The leader runs `INSERT INTO events SELECT 'leader', now(),
random()`. A follower replays the *same statement text* as `'follower'`. Will the
two rows hold the same timestamp and the same random number?

```
docker exec ddia-ch6-stmt psql -U postgres -d study -f /tmp/statement_replication.sql
```

**Observe.**

```
the leader runs:   INSERT INTO events SELECT 'leader',   now(), random();
a follower REPLAYS the identical statement text (now/random re-evaluated locally):
    leader  |11:22:10.761848|0.594083
    follower|11:22:11.281067|0.478290
    -> different timestamp AND different random value: the replica has diverged from the leader.
```

**Two different rows from the same SQL.** The timestamps differ (the follower ran
the statement a moment later) and — worse — the random values differ entirely
(`0.594083` vs `0.478290`). A follower built this way doesn't *lag* the leader; it
*disagrees* with it, permanently. (Your exact numbers will differ every run — that
they differ *at all* between leader and follower is the whole point.)

### Step 2 — why shipping the row fixes it

**Predict.** If the leader instead sent the follower the *value it computed*
rather than the statement, would they match?

**Observe.** Yes — and that's what real replication does. Logical (row-based)
replication ships "row inserted: (`0.594083`)"; physical WAL shipping ships the
changed bytes. Either way the follower stores the leader's *result*, never
re-evaluating `random()`. The nondeterminism is computed exactly once, on the
leader, and copied verbatim.

## What you should see

- The leader's row and the follower's "replayed" row differ in **both** the
  timestamp and the random value.
- The divergence happens every run (though the specific values change) — it's
  inherent to re-executing nondeterministic functions.
- Shipping the computed row (logical) or the WAL (physical) avoids it entirely.

## Why

Replication has to answer one question: what does the leader send followers so
they end up with the same data? Statement-based replication sends the *recipe* —
the SQL — and trusts each follower to cook the same dish. That works only if the
recipe is deterministic. The moment a statement's result depends on something
other than the current database contents — the wall clock (`now()`), a random
source (`random()`), a sequence (`nextval()`), or even row ordering under a
`LIMIT` — each follower computes its *own* answer at its *own* instant, and the
copies drift apart. Here `random()` alone guarantees it: two independent draws,
two different numbers, one leader and one follower that will never agree about
what row was inserted.

The fix is to send the *dish, not the recipe*. Logical (row-based) replication
ships the concrete rows the leader produced — "insert (`'leader'`, `2026-…`,
`0.594083`)" — so the follower stores exactly what the leader computed, no
re-evaluation. Physical WAL shipping (what Postgres does by default, and the
substrate of the other Chapter 6 exercises) goes lower still: it ships the raw
page changes, so a follower is a byte-for-byte copy of the leader's storage. Both
move the nondeterminism to a single place — the leader, once — and then copy the
result. That's why modern systems default to row-based or WAL replication, and
why the naive "just re-run the SQL" approach, which looks the most obviously
correct, is the one that quietly corrupts your replicas.

**The boundary — statement-based isn't useless, it's just sharp.** Shipping
statements can be *more compact* than shipping rows (one `UPDATE … WHERE` that
touches a million rows is a few bytes of SQL versus a million row-images), so
systems like MySQL still offer it — with rules: forbid or specially-handle
nondeterministic functions, capture the leader's `now()`/insert-id as constants,
or fall back to row-based per-statement (MySQL's "mixed" mode). Postgres sidesteps
the whole question by only shipping the WAL. The lesson generalizes past
databases: whenever you replicate *operations* instead of *results*, every
operation must be deterministic — the same trap appears in event sourcing,
CRDTs, and state-machine replication, and the same fix (capture nondeterministic
inputs as data) applies.

### Go deeper

1. **Sequences diverge too.** Add `nextval('some_seq')` to the statement and
   replay it. Predict whether the "leader" and "follower" get the same id (they
   won't) — the autoincrement pitfall the book also names.
2. **Deterministic statements are safe.** Replay a statement with only constants
   and existing-column arithmetic (no `now`/`random`). Predict whether the two
   rows match now (they should) — showing it's *nondeterminism*, not statements,
   that's the problem.
3. **Row-based in Postgres.** Set up logical replication (`CREATE PUBLICATION` /
   `CREATE SUBSCRIPTION`) between two instances, insert `random()` on the
   publisher, and confirm the subscriber holds the *identical* value — the fix,
   for real.