hard Replication Log Formats: statement vs logical vs physical

What actually crosses the wire from leader to follower

A leader can ship a write to its followers in one of three fundamentally different forms, and the form it chooses decides how much freedom the follower has to recompute the result versus just copying bytes: replay the original SQL (statement-based), apply an already-computed row change (row-based / logical), or overwrite storage pages verbatim (WAL / physical). Each option trades determinism against portability against raw speed — the same three axes that decide almost every replication design.

All three exist in production systems today, often as literal configuration knobs (MySQL's binlog_format, Postgres's physical vs. logical replication), so recognizing which one a system uses tells you immediately what bugs it is exposed to and what it can and cannot do.

1. Statement-based replication

Mechanism: the leader logs the SQL statement text it executed (or a normalized form of it) and ships that text to followers; each follower re-parses and re-executes the same statement against its own copy of the data. It is the most compact format — one short string replaces however many rows the statement touches.

The catch is nondeterminism. Re-executing a statement only reproduces the leader's result if the statement is guaranteed to compute the same thing on both sides. Several common SQL constructs are not:

• NOW(), CURRENT_TIMESTAMP — evaluated again on the follower at a different wall-clock instant.
• RAND(), UUID() — a fresh random value on each side.
• Auto-increment ordering under concurrent statements — interleaving can assign IDs in a different order on the follower.
• UPDATE ... LIMIT without a deterministic ORDER BY — which rows get touched depends on scan order, which can differ.
• Triggers and stored functions with side effects — they fire again on the follower, so an auditing trigger that inserts its own NOW()-stamped row fires twice with two different timestamps.

Real system — MySQL binlog_format=STATEMENT: this is exactly the historical default that got MySQL a reputation for silent replica drift, and is why row-based became the safer default.

2. Row-based / logical replication

Mechanism: the leader executes the statement once, computes the resulting row change — the actual before/after values for an update, the literal inserted row, the deleted row's key — and ships that already-computed row image. The follower does not re-run any expression; it just applies the row image as given.

Because NOW(), RAND(), and similar calls are evaluated only on the leader and shipped as plain literals, the nondeterminism problem in §1 disappears entirely. This format is also decoupled from how the storage engine represents rows internally, which is what makes it useful for:

• Change Data Capture (CDC) — tools like Debezium decode the row-change stream into events for Kafka, search indexes, or caches.
• Cross-version / cross-engine replication — a newer follower version, or even a different consumer entirely, can apply the same row images without understanding the leader's exact on-disk format.
• Selective replication — you can filter to a subset of tables since each change is a self-contained row event, not an opaque byte range.

The cost is size: a single UPDATE touching a million rows ships a million row images instead of one short SQL string.

Real systems: MySQL binlog_format=ROW; PostgreSQL logical replication (the pgoutput plugin, built in since PG10, or wal2json for decoding to JSON).

3. WAL / physical replication

Mechanism: every storage engine already maintains a write-ahead log (WAL) of raw page-level byte changes for its own crash recovery — physical replication simply ships that same log to followers, which apply the identical bytes to their own on-disk pages. Nothing is re-parsed or re-derived; the follower's pages become byte-for-byte copies of the leader's.

This is the tightest coupling and the fastest path: it reuses machinery the engine already pays for (durability logging), so there is no extra transformation step, and it produces the most exact possible replica. The price is that the follower must speak the exact same on-disk format as the leader — same storage engine, same major version, often the same page layout down to the byte. You cannot physically replicate PostgreSQL WAL to MySQL, and you generally cannot physically replicate across a PostgreSQL major-version upgrade, because the page format itself can change between major versions.

Real system — PostgreSQL streaming (physical) replication: standbys connect to the primary's WAL stream and replay WAL records to produce byte-identical hot standbys — the classic mechanism behind synchronous HA and disaster-recovery replicas.

Traced example — one write, three outcomes

The leader executes INSERT INTO orders(id, created_at) VALUES (1, NOW()) at wall-clock 10:00:03.512. Follow what each format ships and what the follower ends up storing:

Format	What the leader ships	What the follower does	Follower's created_at
Statement-based	INSERT ... VALUES(1, NOW()) (SQL text)	re-executes NOW() at replay time, seconds later	10:00:07.881 — different from the leader
Row-based / logical	{id: 1, created_at: '10:00:03.512'} (computed row image)	inserts the literal row as given, no recomputation	10:00:03.512 — matches the leader
WAL / physical	raw WAL bytes for the heap page containing the new row	overwrites its own page bytes with the leader's bytes	10:00:03.512 — byte-identical to the leader

Only the statement-based follower disagrees with the leader — and it disagrees silently: no error, no warning, just a different value sitting in a supposedly-replicated row. That silent divergence is the whole reason row-based and physical formats exist.

Pitfalls

• Statement-based nondeterminism is silent. There is no error when NOW(), RAND(), or a trigger re-fires on the follower — the replica simply ends up with different data, discovered only later (e.g. an audit mismatch or a customer complaint).
• Row-based bloats the log for bulk writes. An UPDATE or bulk DELETE touching a million rows ships a million row images; statement-based would have shipped one line. Plan binlog/WAL retention and network bandwidth accordingly.
• MySQL's MIXED mode switches formats mid-stream. It defaults to statement-based but silently falls back to row-based for statements it detects as unsafe — a downstream consumer built assuming one fixed format (e.g. a naive CDC parser) can choke on the switch.
• Physical replication cannot cross a major-version upgrade. Teams doing a zero-downtime Postgres major-version upgrade discover physical standbys don't help — they need logical replication (or a tool built on it) instead, because the WAL/page format is not guaranteed stable across major versions.
• Logical replication needs row identity. Postgres logical replication requires a REPLICA IDENTITY (normally the primary key) to know which row an UPDATE/DELETE targets on the subscriber — a table without a primary key and without an explicit replica identity cannot replicate updates/deletes correctly.

When to use which (and when not)

• Statement-based — choose only when every replicated statement is provably deterministic and bandwidth is the binding constraint; it buys the smallest possible wire format. It costs real risk of silent divergence the moment anyone writes a non-deterministic statement or trigger — which is why MySQL no longer defaults to it.
• Row-based / logical — the default modern choice: pick it when you need CDC into Kafka or a search index, cross-version or cross-engine replication, or selective per-table replication. It costs bandwidth/storage (row images instead of statement text) and requires row identity for updates/deletes. Prefer physical replication instead when you just need an exact, whole-database hot standby and don't need selectivity or cross-version reach.
• WAL / physical — choose for the fastest, lowest-CPU-overhead replica that must be an exact mirror of the primary: synchronous HA pairs, disaster-recovery standbys. It costs total lock-in to one engine and one on-disk format — no cross-version, no cross-engine, no selecting a subset of tables. Prefer logical/row-based the moment any of those flexibility needs shows up.
• Named real examples: MySQL's binlog_format is literally this three-way choice — STATEMENT, ROW, or MIXED (auto-fallback to row for unsafe statements). PostgreSQL ships both as separate, purpose-built features: physical streaming replication (WAL shipping, for hot standbys) and logical replication (pgoutput, for CDC/cross-version/selective use) — the split exists precisely because one format cannot serve both goals well.

Takeaways

• What crosses the wire trades determinism for compactness: SQL text (small, nondeterministic) — row images (portable, bigger, CDC-friendly) — WAL bytes (fastest, version-locked).
• Statement-based replication breaks silently on NOW()/RAND()/UUID(), non-deterministic UPDATE ... LIMIT, and re-firing triggers — the historical reason MySQL moved its default away from it.
• Row-based/logical is the CDC and cross-version answer; physical/WAL is the fastest but locks the follower to the leader's exact storage-engine version.
• MySQL exposes all three as one setting (binlog_format); Postgres deliberately ships physical and logical replication as two separate features because they solve different problems.

---

Sources: Martin Kleppmann, “Designing Data-Intensive Applications,” ch. 5 (leaders and followers — statement-based, write-ahead log shipping, and logical (row-based) log replication); MySQL Reference Manual, “Replication Formats” (binlog_format: STATEMENT/ROW/MIXED); PostgreSQL documentation, “Log-Shipping Standby Servers” (physical) and “Logical Replication” (pgoutput); Debezium documentation on log-based CDC. Re-authored for this guide.

I just read a lesson on **Replication Log Formats: statement vs logical vs physical** (System Design) and want to truly understand it. Explain Replication Log Formats: statement vs logical vs physical from first principles using ONE vivid real-world analogy and a visual mental model — draw it as ASCII art or a clear step-by-step diagram — with a concrete example using real numbers. Then ask me one question to check I got the mental picture, and wait for my reply. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.

Teach me **Replication Log Formats: statement vs logical vs physical** interactively. Ask me ONE guiding question at a time, wait for my answer, and adapt to my confusion — build the idea with me step by step instead of explaining it all at once. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.

Quiz me on **Replication Log Formats: statement vs logical vs physical** with 5 questions, easy to tricky, ONE at a time. Tell me if each answer is right; at the end, explain clearly what I got wrong and why. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.

Help me remember **Replication Log Formats: statement vs logical vs physical** for the long term: give the one-sentence intuition, a memorable hook/mnemonic, a tiny worked example, and 3 active-recall flashcards (Q -> A). If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.