Ismail Enegadi

Inside Replication Logs

6 min readSystems
On this page7

So far we’ve talked about the replication log as if it’s a single, obvious thing: the leader records its writes and streams them to followers. But how exactly is each write recorded? What goes in the log? This question has four different answers in practice, and the format you choose affects everything from rolling upgrades to external analytics.

Four approaches to replication logs

SQL levelStatementlogs the SQLstatement itselfMySQL (legacy),VoltDBStorage levelWAL Shippingships storageengine bytesPostgreSQL,Oracle Data GuardRow levelLogical Logrow-level changedescriptionsMySQL binlog(row-based mode)App levelTrigger-Basedcustom triggerslog to a tableGoldenGate,Databus, Bucardo
Hover an approach to see its trade-offs. All four replicate the same write — they differ in what they put in the log.

Statement-Based Replication

The simplest approach: the leader logs every write statement — every INSERT, UPDATE, or DELETE — and forwards it to followers, who execute it as if they were the original client.

The appeal: the log is small and human-readable. You can look at it and understand exactly what happened. MySQL used this approach before version 5.1, and you can still configure it.

The problem: SQL statements are not always deterministic.

  • A statement like UPDATE orders SET updated_at = NOW() will produce a different timestamp on every replica, because NOW() is evaluated at execution time.
  • INSERT INTO items VALUES (DEFAULT, ...) with an auto-incrementing primary key needs every replica to generate the same ID — but concurrent inserts can cause different ordering on different nodes.
  • Stored procedures and triggers that have side effects may behave differently depending on state that isn’t in the statement.

You can work around these specific cases — the leader can resolve NOW() to a fixed value before logging, and auto-increments can be coordinated — but the surface area for divergence is large. Any non-deterministic function you didn’t account for is a silent divergence waiting to happen.

Write-Ahead Log (WAL) Shipping

Every storage engine already maintains a write-ahead log for crash recovery: before writing to data files, it appends the intended change to the WAL, so it can replay incomplete writes after a crash. The simplest replication strategy is to send this log directly to followers.

PostgreSQL and Oracle Data Guard both use WAL shipping. It’s efficient — the log already has to be written for durability, so shipping it adds relatively little overhead.

The catch: the WAL describes data at the storage engine’s own internal level — which disk blocks changed, which bytes were written. It’s tied tightly to the internal data format of the specific database version.

That coupling creates an operational headache: leader and followers must run exactly the same version of the database software. If the storage format changes between versions (which it often does), a follower on the newer version can’t process a WAL from the older leader, or vice versa.

This makes upgrades painful. A typical rolling upgrade — bring followers up to the new version one at a time, then promote one and take the old leader offline — is difficult or impossible with WAL shipping. You usually have to take downtime to upgrade both nodes simultaneously.

Logical (Row-Based) Log Replication

The insight behind logical replication is to decouple the replication log format from the storage engine’s internal format. Instead of describing which bytes changed on disk, the log describes what changed at the row level:

  • For an INSERT: the new values of all columns in the inserted row.
  • For an UPDATE: enough to identify the row (usually the primary key) plus the new values of every changed column.
  • For a DELETE: enough to identify the deleted row (usually the primary key, or all columns if there’s no unique key).

MySQL’s binlog uses this format when configured in row-based mode. The name “logical” distinguishes it from the lower-level physical (WAL) format.

Why this is usually the right choice:

Because the log format is decoupled from storage internals, leader and followers can run different versions of the database. You can upgrade followers to a new version one at a time while the leader runs the old version — as long as the logical log format is compatible — then promote an upgraded follower and retire the old leader. Rolling upgrades with no downtime become possible.

The other major benefit: logical logs are easy for external systems to parse. This enables change data capture (CDC) — the practice of subscribing to a database’s change stream and feeding it into other systems like a search index, cache, data warehouse, or audit log. Tools like Debezium and Maxwell consume MySQL’s row-based binlog for exactly this purpose.

Trigger-Based Replication

The most flexible approach operates entirely at the application layer. Database triggers fire on every write and run custom code — typically logging the changed row to a separate tracking table. A separate process reads that table and applies the changes to the target system.

Tools like Oracle GoldenGate, LinkedIn’s Databus, and Bucardo work this way.

When you’d reach for this:

  • Replicating a subset of data (only certain tables or rows) rather than everything.
  • Replicating to a different type of database — from PostgreSQL to MongoDB, for example.
  • Applying transformations to data as it moves.

What you give up:

  • Triggers execute inside every transaction, so they add measurable overhead — sometimes doubling the cost of a write.
  • Custom trigger code is more complex and more likely to contain bugs than the built-in replication mechanisms.

Trigger-based replication is the escape hatch you reach for when the built-in options can’t do what you need, not the default choice.

Comparison

ApproachLog containsRolling upgradesCDC-friendlyOverhead
StatementSQL statements✓ReadableLow
WAL shippingStorage bytes✗HardVery low
Logical logRow changes✓✓ EasyLow
Trigger-basedCustom table entries✓PossibleHigh

For most modern systems, logical log replication hits the right balance: deterministic, version-independent, and easy to consume externally. WAL shipping is the right choice when you control both ends tightly and want maximum efficiency. Statement-based is mostly historical. Trigger-based replication covers cases the others can’t — at a cost.

Key Takeaways

  • Statement-based replication is simple but breaks on non-deterministic SQL — any NOW(), RAND(), or auto-increment across concurrent writes can cause replicas to diverge.
  • WAL shipping is efficient but ties leader and follower to the same database version, making rolling upgrades painful.
  • Logical log replication describes changes at the row level, decoupled from storage internals — enables rolling upgrades and powers change data capture (CDC).
  • Trigger-based replication is the most flexible but most expensive — use it when you need cross-database replication or data transformation.

In the next post, we’ll step back from the mechanics and look at the problems that emerge when replication lags — the subtle consistency issues that appear when followers fall behind the leader.

References

Subscribe via RSS