How Database Transaction Logs Enable CDC

Database transaction logs make change data capture possible by keeping changes that table queries cannot recover. Because they record changes in order, they allow low-latency capture without source table scans and include hard deletes and intermediate row states. Those advantages are structural, but reliable delivery depends on how each database exposes its log, retains unread events, and handles consumer progress.

Why transaction logs exist before CDC ever touches them

Databases need a durable record of changes so they can recover after a crash and replicate data internally. With write-ahead logging, the log is written before the changed data page reaches the data files. That ordering makes recovery possible after an interruption. PostgreSQL calls this record the Write-Ahead Log, or WAL.

MySQL and MariaDB expose changes for replication through the binary log, or binlog, while InnoDB uses a separate redo log for crash recovery. MongoDB maintains an operations log, called the oplog, for replica set replication.

How log-based CDC reads that existing record instead of querying tables

Log-based CDC connects as a replication consumer and reads committed changes from the database’s existing record. It translates those changes into structured events without scanning tables or running them through the query executor. Because the database already writes the record, capture needs neither a new write path nor a new source schema object.

A change event identifies the affected table and operation, with row values available according to the database’s configuration, a timestamp, and a position marker. The consumer stores that position as its offset so it can resume after a restart. This cursor is central to recovery, although retries can still produce duplicate delivery.

Query-based polling instead runs a query such as WHERE updated_at > last_run on a schedule. It consumes source CPU, depends on reliable tracking columns, and returns only the row state available when the query runs. Hard deletes disappear from its view entirely. Trigger-based CDC can record changes separately, but it adds overhead to the write path.

What the log actually captures that no query can reconstruct

A deletion produces an explicit DELETE event rather than simply removing a row from the next query result. A query against the remaining table cannot reconstruct the deletion from that absence alone.

The same distinction applies to rapid updates. When a row is updated five times between polling intervals, the log has five distinct committed events, while timestamp-based extraction returns the row’s final state. For example, an order created, fulfilled, and cancelled within a five-minute polling window appears as three events in the change history. A poll finds a cancelled row, or no row, without the intervening sequence.

That history lets audits use intermediate row states in commit order. These capabilities follow from recording operations as they happen, rather than inferring them from a later snapshot. Their usefulness still depends on the workload. Batch and incremental loads remain appropriate for reporting where hourly or daily freshness is sufficient, but they cannot reconstruct history already overwritten or deleted.

How PostgreSQL's WAL works and what it requires for CDC to function

PostgreSQL exposes WAL changes through logical replication, beginning with wal_level = logical. This server setting requires a restart and provides the information needed for row-level logical decoding. A publication then scopes the tables whose changes the consumer receives. Each WAL record has a monotonically increasing Log Sequence Number, or LSN, which identifies its position.

A replication slot records confirmed consumer progress in a named, crash-safe cursor. PostgreSQL retains WAL that the slot still needs, allowing the consumer to resume after downtime. Replica identity sets which old-row values are in UPDATE and DELETE events. The chosen identity, including FULL or an index, thus sets which before-image information can be used downstream.

The slot’s guarantee also creates its principal risk. An inactive or lagging consumer prevents required WAL from being discarded, and the retained data can fill the disk. Operators should monitor pg_replication_slots for active = false and large retained WAL, and drop slots that are no longer needed. Setting max_slot_wal_keep_size bounds retention so a dead slot is invalidated rather than exhausting storage.

Failover adds a version-specific condition to resumability. PostgreSQL 17 and later support logical slots surviving failover when created with the failover option and when sync_replication_slots = on and hot_standby_feedback = on are set on the standby. In PostgreSQL 16 and earlier, slots remain on the primary and are lost on failover unless an extension such as pg_failover_slots is in place. Managed service behavior varies by provider.

Logical replication also has a separate boundary. It transmits INSERT, UPDATE, DELETE, and, from PostgreSQL 11 onward, TRUNCATE, but does not propagate DDL through logical decoding.

How MySQL's binlog and MongoDB's oplog differ in structure and what that changes for CDC

MySQL exposes its binlog through the replication protocol used for primary-replica replication. A consumer tracks progress through the binlog file name and position, or through GTID in later versions. The binlog format must be ROW rather than STATEMENT or MIXED for CDC to receive actual before-and-after row values rather than the SQL statement responsible for the change.

MongoDB Change Streams similarly taps an existing replication record, the oplog, adding virtually no database overhead. Change Streams requires a replica set or sharded cluster because standalone instances do not support it. Each event includes a unique _id that serves as a resume token. A consumer can supply that bookmark after a crash to resume from its recorded position.

Retention makes those bookmarks useful only within an available history. MySQL rotates and deletes binlog files on a configurable schedule, while MongoDB’s capped oplog retains a rolling record whose time span depends on its size and write volume. MongoDB’s default sizing uses a percentage of available disk up to a cap. Operators should size it for the maximum tolerable consumer outage, using replSetResizeOplog to adjust it without a restart.

Across PostgreSQL, MySQL, and MongoDB, the common mechanism is a durable position in an existing change history. The names differ, but the dependency does not. Resumption requires both a valid consumer bookmark and the records that follow it.

Why DynamoDB Streams is architecturally different and what that means for CDC reliability

DynamoDB Streams exposes item-level changes through a partitioned stream. When Streams is enabled, each underlying table partition has a corresponding stream shard. Records are ordered within each shard, without a cross-shard ordering guarantee. The partition mapping defines the scope of ordering.

Retention imposes an equally important constraint on recovery. Stream records expire after a fixed 24-hour window, which cannot be extended. If a consumer remains offline beyond that window, the missing records cannot be recovered from the stream. Rebuilding current state requires a full table rescan.

When DynamoDB splits a partition, its stream shard closes and two child shards open. A consumer that fails to enumerate and process those children misses their changes. Managed consumers such as Lambda and KCL handle this automatically, while hand-built consumers must implement explicit shard discovery.

These constraints define a narrower recovery envelope than a PostgreSQL slot, which retains unread WAL subject to storage and max_slot_wal_keep_size. DynamoDB operators cannot get a longer stream recovery window by keeping more data in storage.

Schema evolution: why DDL changes are the production failure mode log-based CDC doesn't solve automatically

PostgreSQL’s DDL gap explains why a healthy change stream can still produce a broken pipeline. An ALTER TABLE operation does not propagate through logical decoding as a schema-change instruction. The consumer may therefore continue interpreting subsequent row events against an outdated definition.

The consequences depend on what changed at the source. A new column may remain absent downstream until the consumer is reconfigured, while a type change can cause mismatch errors or silent coercion. A rename can appear as the removal of one column and the addition of another, potentially dropping data.

Production-grade CDC must detect source schema changes and evolve the consumer schema before the next affected DML event arrives. Monitoring the source catalog or intercepting DDL out-of-band provides a way to detect changes the row stream does not announce. A centralized schema management pattern is a community-standard mitigation. It addresses a separate concern from reading and acknowledging log positions.

Automatic schema evolution is therefore a substantive requirement for managed CDC services.

Log retention windows and consumer lag as the bounded reliability guarantee every CDC operator must plan for

PostgreSQL holds WAL for slots within storage and configured retention limits, MySQL deletes rotated binlogs on a schedule, and MongoDB’s oplog wraps at its size limit. DynamoDB Streams fixes the window at 24 hours. Each system eventually reaches a point beyond which an old consumer position is unusable.

A downstream failure, consumer crash, or network partition can push the reader beyond that boundary. Once the records it needs have expired, the reader cannot use its saved offset to read the missing changes. A full table resync is then required. It rebuilds current state, but the expired intermediate events remain lost.

Configurable retention should be sized for the maximum expected outage. Consumer lag must be monitored continuously by tracking confirmed progress against the log head and alerting before the unread history approaches expiry. Retention establishes the available recovery window. Lag shows how much of that window the consumer has consumed.

At-least-once systems can redeliver events after retries or reconnections, so a valid cursor does not by itself prevent duplicates. Exactly-once delivery needs a way to detect and deduplicate those events. That is a production requirement beyond simply retaining enough history to restart.

What knowing the log mechanics changes about how teams choose and operate CDC infrastructure

When choosing a tool, teams should test how it meets the log’s requirements, since low latency alone does not show that it is reliable. For PostgreSQL, that includes slot lifecycle management, retained-WAL monitoring, and warnings before disk pressure. Teams should also test how failover works with the version and provider they use. A tool that requires manual slot recreation offers a different recovery path from one that preserves consumer progress.

Tools with automatic schema evolution handle source changes without manual intervention, while tools with deduplication handle redelivery after reconnection. Resync capability determines what happens when a position expires. Teams should establish whether the tool initiates a full resync automatically and resumes incremental replication afterward.

PostgreSQL needs logical decoding and an appropriate replica identity, MySQL needs ROW format, and MongoDB needs an oplog sized for the intended outage window. DynamoDB instead requires an architecture that accepts its fixed retention boundary and can rescan when necessary. These choices define which guarantees the deployment can actually sustain.

CDC infrastructure turns that history into dependable delivery only by respecting its configuration, schema, position, and retention constraints.