Ship every write statement to a follower and re-run it — statement-based replication — and a call to now() or random() is evaluated again, producing a different row. You run one INSERT … now(), random() as leader, replay the identical text as follower, and watch the two rows disagree — which is why real databases ship the computed row, not the SQL.
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.
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.
The surprise is about what the leader sends to followers. These help; each line notes where it shows up.
now(), random(), nextval() return different values each call/instant; re-executing them on a follower does not reproduce the leader's value.postgres:16 (PostgreSQL 16.14) instance.
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.
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.
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.sqlTwo 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.)
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.
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.
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.
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.now/random). Predict whether the two rows match now (they should) — showing it's nondeterminism, not statements, that's the problem.CREATE PUBLICATION / CREATE SUBSCRIPTION) between two instances, insert random() on the publisher, and confirm the subscriber holds the identical value — the fix, for real.Sources: DDIA 2e, Ch. 6, §"Statement-based replication", §"Implementation of Replication Logs" · the MySQL docs on statement vs row binlog formats.