Notesreplication🌱 seedling

Replication Log

Also: Statement-Based Replication · Write-Ahead Log Shipping · WAL Shipping · Logical Replication · Row-Based Replication · Binlog

The ordered stream of changes a leader sends to its followers. What it records (SQL statements, storage-engine bytes, or row changes) decides how fragile replication is and whether replicas can run different versions.

Why it exists

Followers must end up holding the same data as the leader by replaying its changes in order. The format of those changes is a real design choice with consequences for correctness and operations.

How it works

  • Statement-based: ship the SQL text. Compact, but nondeterministic statements (RAND(), UUID(), UPDATE … LIMIT without ORDER BY) diverge across replicas.
  • Physical / WAL shipping: ship the storage engine's Write-Ahead Log. Exact, but it describes disk blocks, so all replicas must run the same major version.
  • Logical / row-based: ship row-level changes (insert this row, update row 7's columns, delete row 9). Decoupled from storage format, so it allows mixed versions and feeds Change Data Capture.
  • Trigger-based: triggers write changes to a side table that an external process ships. Flexible, slower, mostly legacy.
  • Positions in the log (an LSN, binlog coordinates, a GTID set) let a follower resume or find its place on a new leader.

Trade-offs

Format Gains Costs
statement small log, readable nondeterminism breaks replicas
physical WAL exact, cheap to produce version-locked; hard upgrades
logical rows version-independent; CDC-friendly bigger log for bulk updates

In practice

  • MySQL defaults to row-based binary logging and has deprecated the binlog_format setting, signalling row format as the future.
  • PostgreSQL uses physical streaming for standbys and logical replication (publications and subscriptions) for selective, cross-version replication.

Pitfalls

  • A follower that falls behind the leader's log retention can't catch up and must be rebuilt from a snapshot; pinning the log (replication slots) instead risks filling the leader's disk.

Recall

These cards are also in your review deck, scheduled with spaced repetition.