Data building blocks
How changes are shipped
The four ways a database describes a change to its replicas, statement, physical, logical and change data capture, and the failure each one invites.
Every replication setup must answer one question: what does the leader send? The answer shapes what you can upgrade, what you can replicate and what can go wrong. There are four common approaches.
The big idea
A replica can receive the statement that ran, the bytes that changed on disk, the row changes in a logical form, or a stream of changes for other systems to read. Each choice trades off simplicity, compatibility and correctness.
| Mechanism | What is shipped | Used by | Pitfalls |
|---|---|---|---|
| Statement-based | The SQL text, such as UPDATE ... SET ... | Early MySQL default | Non-deterministic functions such as NOW() and RAND() diverge on replicas |
| Physical (WAL shipping) | Byte-level changes to disk pages | Postgres streaming replication, Oracle Data Guard | Replicas need the same major version and architecture, and cannot replicate a subset |
| Logical (row-based) | "Row with key 42 in orders: status is now SHIPPED" | MySQL binlog in row format, Postgres logical replication | More bytes for bulk updates; schema changes vary |
| Change data capture (CDC) | Logical changes exposed to outside consumers | Debezium reading the binlog or WAL into Kafka | Ordering, schema changes and snapshots need care |
Walking through it
A statement-based replica can drift from its leader. Here is an order being marked shipped:
Statement shipped: UPDATE orders SET shipped_at = NOW() WHERE id = 42
Leader runs it at 10:00:00.120 → shipped_at = 10:00:00.120
Replica runs it at 10:00:00.480 → shipped_at = 10:00:00.480
Same statement, two different values. The replica now disagrees with the leader.Row-based logical replication avoids this. The leader ships the computed value, shipped_at = 10:00:00.120, so every replica stores the same thing.
Logical replication also makes zero-downtime major-version upgrades possible. Replicate from Postgres 15 into a new Postgres 17 cluster, let it catch up, and then cut over. A physical stream cannot do this, because it describes disk pages from one version.
CDC applies the same idea to other systems. Every change streams into Kafka, and a search indexer, a cache invalidator and a data warehouse each consume it. The database's replication log becomes the integration point for the rest of the system. See event streaming and search.
Common mistakes
- Using statement-based replication with non-deterministic functions. Replicas then disagree about timestamps and random values.
- Assuming physical replicas can cross versions. Plan upgrades with logical replication.
- Treating CDC as simple. Ordering, schema changes and initial snapshots need explicit handling.
Check yourself
-
Why is
UPDATE ... SET shipped_at = NOW()unsafe for statement-based replication?Answer: each replica runs the statement at its own time, so the same row can end up with different timestamps.
-
Why does logical replication enable zero-downtime major upgrades?
Answer: it describes row changes, not disk pages from one version, so a newer version can receive them.
Key takeaways
- The shipping mechanism decides what a replica can do, such as cross-version upgrades or subset replication.
- Statement-based replication is unsafe with non-deterministic functions.
- Logical replication ships row changes, which makes version upgrades possible.
- CDC turns the replication log into a feed for other systems.
Further learning
Next, see what happens when the leader fails and a follower takes over, in failover and split brain.
Related
Replication
Keeping copies of the same data on several machines, the three topologies that databases use, and the anomalies that replication lag causes. Start here for the replication map.
Single-leader replication
One node accepts every write and streams its log to followers. The choice between synchronous and asynchronous commits decides whether a crash can lose an acknowledged write.
Event streaming and Kafka
How a durable log lets many consumers read the same events at their own pace, and how Kafka builds that log from partitions, replicas and consumer groups.