Most developers learn about database transaction logs in the context of crash recovery. The log records every change before it is applied to the data files; on crash, the database replays the log to get back to a consistent state. That mental model is correct as far as it goes, but it under-sells what the log can do.
When you build a change data capture pipeline that reads the transaction log directly, the log becomes a stream of every committed change in the database, in commit order, with full transactional context. That is a powerful primitive, but only if you understand what it actually contains and what it does not.
Here are the things developers building log-based CDC pipelines for the first time tend to miss.
Photo by Brett Sayles on Pexels
The log is not the data files
When you query a table, you read the current state of the data files. When you read the transaction log, you see the sequence of operations that produced that state. These are two different views of the same database, and they have different properties.
The data files give you point-in-time snapshots. They are easy to query but they lose history; if a row was updated three times today, the data file shows you only the third version.
The log gives you the full sequence: insert, update, update, update. Every transition is visible, every commit timestamp is recorded, and the order is preserved across transactions on the same database connection. This is what makes the log a useful CDC source. It is also what makes it operationally more complex than a polling-based approach.
For PostgreSQL the log is called the write-ahead log (WAL); for MySQL it is the binlog. The wire formats are different, but the structural promises are similar: every committed change, in order, with enough metadata to reconstruct the change downstream.
Logical replication is not physical replication
The first confusion most developers hit is the difference between logical and physical log replication.
Physical replication ships byte-level changes from the source database's storage layer to a replica. The replica is identical to the source at the storage level. This is great for high availability and for read scaling, but the byte-level format is not portable to a different database, a search index, or a Kafka topic.
Logical replication ships the change as a higher-level event: INSERT this row, UPDATE these columns, DELETE this row. The downstream consumer does not need to understand the source database's storage format; it just needs to apply the changes.
CDC pipelines almost always use logical replication. Physical replication is for replicas of the same database engine.
The setup for logical replication is database-specific. On PostgreSQL, you configure the WAL level to logical and create a replication slot. On MySQL, you configure the binlog format to ROW and grant the appropriate replication user. The Wikipedia entry on database replication covers the conceptual model; the specific database documentation covers the wire-format details.
Transactions matter more than you think
When the log shows you a sequence of changes, the changes are grouped into transactions. A single transaction can contain dozens of inserts, updates, and deletes across many tables. The downstream consumer needs to know the transactional boundaries.
Two reasons:
First, atomicity. A bank transfer that updates two accounts in one transaction should be visible downstream as a single atomic change. If the downstream consumer sees the debit before the credit, the intermediate state is incorrect. The transaction boundary is what tells the consumer when to commit its own view.
Second, ordering. Changes within a transaction have a defined order on the source side. The downstream consumer often depends on that order being preserved. Most log-based CDC tools preserve transaction boundaries explicitly; rolling your own without preserving them produces subtle correctness bugs that take weeks to find.
This is one of the strongest arguments for using a battle-tested tool like Debezium instead of reading the log yourself. The transaction boundary handling is one of the things Debezium gets right and a hand-rolled implementation usually does not.
Schema evolution is harder than the demo shows
Every CDC demo runs on a static schema. The columns do not change, the tables do not get renamed, the data types stay constant. Real production databases do not work this way.
When a column gets added to a source table, the log starts emitting events that reference the new column. The downstream consumer needs to know about the schema change before it can correctly interpret the new events. If it does not, the new column values are dropped or misinterpreted.
When a column gets renamed, the log shows the rename as a metadata operation followed by changes that reference the new name. The downstream consumer needs to handle the metadata operation correctly to keep its schema in sync.
When a table gets dropped, the log shows the drop. The downstream consumer needs to decide what to do with its existing copy of the data.
The standard answer is a schema registry: a separate service that tracks the source schema and emits versioned schemas to consumers. Tools like Confluent Schema Registry, AWS Glue Schema Registry, and the Apicurio project handle this for Kafka-based pipelines. For non-Kafka pipelines, the schema-tracking responsibility falls on the consumer, which is operationally harder.
Plan for schema evolution before you write the consumer logic. Retrofitting schema-evolution handling onto a pipeline that assumed a static schema is a deeper refactor than it looks.
The log has a finite lifetime
This is the gotcha that catches every team running log-based CDC for the first time. The source database does not keep the transaction log forever. It keeps a window large enough for recovery and for any active replication slots, and then it reclaims the disk space.
If your CDC consumer falls behind for too long (because of a downstream outage, a deployment that pauses the consumer, a network partition), the database may reclaim the log space the consumer was about to read. At that point, the consumer cannot catch up; the gap is permanent, and the only recovery is a full source-table snapshot followed by a fresh CDC stream from the snapshot timestamp.
Two mitigations:
First, monitor the available log retention relative to the consumer's current position. Alert when the consumer is within 25 percent of the edge.
Second, configure the database to keep enough log retention to survive realistic consumer outages. The PostgreSQL max_wal_size setting and the MySQL binlog_expire_logs_seconds setting are the relevant knobs. The right values depend on your specific outage tolerance, but a baseline of "enough log retention to survive a four-hour consumer outage at peak write rate" is reasonable for most teams.
Initial snapshot is harder than ongoing capture
When you first turn on a CDC pipeline, the downstream consumer has nothing. You need a one-time snapshot of every source table, applied before the first ongoing change is applied. The handoff between snapshot and stream is where most CDC bugs live.
The naive approach is to take a snapshot of the source table, then start reading the log from the current position. This produces a gap: changes committed between the snapshot read and the log start are lost.
The right approach is to read the snapshot inside the same database connection that starts the log read, so the snapshot and the log are consistent at the same transactional boundary. Most CDC tools support this as a configuration option ("initial snapshot then stream"), and rolling your own correctly is harder than it looks.
For more on the specific CDC patterns and when to use each, the 137Foundry guide on implementing change data capture without polling covers log-based, trigger-based, and timestamp-based patterns with decision rules for picking between them.
The deletes problem disappears
This is the upside that polling-based pipelines do not get: log-based CDC sees deletes. The DELETE event is recorded in the log, the consumer sees it, the downstream system can mirror it. Polling-based pipelines have to use soft-delete or accept that deletes are invisible; log-based pipelines do not have this problem.
This single property is often what justifies the operational cost of log-based CDC for teams running pipelines where data quality matters. The polling pipeline that "almost" works in development becomes a data integrity problem in production, and the cleanest fix is log-based capture.
Build vs buy
Reading the transaction log directly is technically possible. There are libraries for parsing the PostgreSQL WAL and the MySQL binlog. You can build a CDC pipeline from scratch in a few weeks.
You probably should not. The standard tools (Debezium, AWS DMS, the various managed CDC services) handle all of the issues above: transaction boundaries, schema evolution, log retention monitoring, initial snapshot, deletes, ordering. Building your own gives you control at the cost of recreating every one of these problems yourself.
The 137Foundry data integration service handles the build-vs-buy tradeoff on CDC infrastructure for client teams; the honest answer is that small teams almost always benefit from managed services, and even large teams should think hard before rolling their own.
The transaction log is a powerful primitive, but it is more powerful when consumed through a tool that has already solved the operational problems. Use the tool. Save the engineering time for the application logic that actually makes your product different.
Top comments (0)