Every durable database already writes down every change it makes. Postgres appends to a write-ahead log before it touches a data page. MySQL appends to a binlog so replicas can follow. MongoDB appends to an oplog. These logs exist for crash recovery and replication, not for you, but they are a complete, ordered, committed record of everything that happened. Change data capture is the practice of reading that record and turning it into a stream other systems can consume.

That framing matters because it explains why the three common CDC techniques are not equally good. Two of them reconstruct the change history by looking at the current state of the data. One of them reads the history the database already wrote. The reconstruction approaches lose information that the log approach never had to guess at.

Query-Based Capture and What It Loses

Query-based CDC polls. You add an updated_at column, index it, and every thirty seconds you run SELECT * FROM orders WHERE updated_at > :last_seen. It takes an afternoon to build and it is the first thing most teams reach for.

It loses deletes. A row that is gone cannot be returned by a query, so a hard DELETE is invisible to the poller and the downstream copy keeps a record the source no longer has. The usual patch is soft deletes: add a deleted_at column and never actually remove rows. That works, but you have now changed the source schema and accepted unbounded table growth to serve a downstream consumer.

It loses intermediate states. If a row is updated four times between polls, the poller sees the fourth value and the other three never happened as far as any consumer is concerned. For a cache that only needs the latest value this is fine. For an audit trail, a fraud model that keys on transition patterns, or anything that must reproduce the sequence of states, it is a silent correctness hole.

It has a commit-order race that is subtle enough to survive code review. Suppose transaction A starts at 10:00:00, sets updated_at = 10:00:00, and does not commit until 10:00:07. Transaction B starts and commits at 10:00:03 with updated_at = 10:00:03. A poll at 10:00:05 sees B, and advances the high-water mark to 10:00:03. The next poll asks for rows newer than 10:00:03, and A, now committed but stamped 10:00:00, never appears. The row is permanently missed. The timestamp records when the statement ran, not when it became visible, and those are different moments.

It also costs the source database real work. Every poll is an index range scan on a hot table, running forever, whether or not anything changed. Shorten the interval to cut latency and you multiply that cost.

Query-based capture is still the right answer in three situations: the source is a managed service that does not expose its log at all, the table is append-only with a monotonically assigned id you can range over safely, or the change rate is low enough that a minute of latency and a periodic scan cost nothing. Reach for it knowing what you gave up, not by default.

Trigger-Based Capture

Trigger-based CDC installs AFTER INSERT, AFTER UPDATE, and AFTER DELETE triggers that write a row into a shadow audit table inside the same transaction as the original write. A separate process drains the audit table.

This fixes both of the information losses. Deletes fire a trigger. Every individual update fires a trigger, so intermediate states survive. The capture is transactionally consistent with the write by construction, because it is part of the same transaction.

The price is paid on the write path. Every write now performs two writes, inside the user's transaction, holding the user's locks for longer. On a table taking 5,000 writes per second, you have added 5,000 inserts per second to a table that is also being drained concurrently, and the drain has to delete rows it has consumed or the table grows forever. Write latency increases measurably, and the increase lands on the customer-facing path, not on a background job.

The operational coupling is worse than the performance cost. Triggers are schema objects. Every table you want to capture needs its own trigger set and its own audit table. Every column added to a source table needs the trigger body and the audit table updated in lockstep, or the new column is silently dropped from the capture. A schema migration that forgets a trigger produces a capture stream that is quietly incomplete.

Trigger-based capture earns its place when you need capture on a handful of specific tables, you cannot get log access, and you need deletes and intermediate states. It does not scale to capturing a whole database.

Log-Based Capture

Log-based CDC reads the log directly. The connector connects to the database the way a replica does, receives a stream of committed changes, and never runs a query against the tables it is capturing. The write path is untouched. Deletes appear because the log records deletes. Intermediate states appear because the log records every version. Commit order is exact because the log is ordered by commit, and the log is the definition of commit order for that database.

The mechanism differs per engine but the shape is the same:

• Postgres exposes logical decoding. The physical WAL records page-level changes, which are meaningless outside the exact binary layout of the same Postgres version. A logical decoding output plugin, usually pgoutput or wal2json, translates those page changes into row-level change records scoped by a PUBLICATION that names which tables to include. The consumer's position is a log sequence number, an LSN, which looks like 0/1A2B3C4D.
• MySQL exposes the binlog. Its usefulness depends entirely on binlog_format. In STATEMENT format the log holds the SQL text, so a statement like UPDATE orders SET status = 'shipped' WHERE ship_date < NOW() tells you nothing about which rows changed or what they held before. Only ROW format records the actual row images, and ROW is what CDC requires. The related setting binlog_row_image controls whether the before-image carries every column (FULL) or only the primary key and changed columns (MINIMAL); CDC wants FULL. Position is either a file-and-offset pair or a GTID.
• MongoDB exposes change streams over the oplog, and position is a resume token.
• SQL Server and Oracle expose their own log readers, with change tables and LogMiner or GoldenGate respectively.

In all four cases the consumer's entire durable state is one small opaque position value. That single fact is what makes log-based CDC restartable: a connector that dies mid-stream reconnects, presents its last confirmed position, and the server resumes from exactly there.

Replication Slots and the Guarantee That Cuts Both Ways

A log is only useful to you while it still exists. Databases recycle log segments aggressively, because the log's original purpose was crash recovery and once the changes are safely in the data files the log is dead weight. Postgres by default keeps a bounded amount of WAL and discards the rest.

A replication slot is the mechanism that changes that. Creating a slot tells the server: there is a consumer here, remember where it has read up to, and do not recycle any WAL it has not yet confirmed. The slot records a confirmed flush LSN, advanced only when the consumer acknowledges. MySQL's equivalent is coarser, a retention window controlled by binlog_expire_logs_seconds, and MongoDB's is coarser still, a fixed-size capped oplog.

This guarantee is exactly what you want. It is also the single most common way a CDC deployment takes down the source database. A slot with no consumer still holds its position, and the server, honoring the contract, keeps every WAL segment after that position forever. A connector that has been down over a long weekend can turn a healthy database into one that is out of disk, and running out of disk on the WAL volume takes Postgres down hard. We come back to this failure mode, and to how to detect it before it fires, in the production section.

The time-window mechanisms fail in the opposite direction, and more quietly. If a MySQL connector is offline longer than binlog_expire_logs_seconds, or a MongoDB connector falls further behind than the capped oplog's span, the position it holds no longer exists. The server cannot resume it. The connector's only remaining option is to throw away its position and take a fresh snapshot of the entire database. Nothing crashed, but the pipeline silently converted itself from an incremental stream into a full reload.

A Connector Is a Replica

The most useful mental model for log-based CDC is that the connector is just another replica that happens to write somewhere other than a copy of the database. It consumes the same channel. It has the same guarantees and the same failure modes.

Everything you know about replication lag transfers directly. The connector reads asynchronously, so it is always somewhat behind the source, and its lag grows under write bursts exactly as a follower's does. Failover of the source is a genuine event for it: an old-style MySQL connector tracking a file-and-offset position cannot follow a promotion, because offsets are per-server, which is why GTIDs exist. Postgres physical replication does not copy logical slots to a standby by default, so before Postgres 16 a failover meant recreating the slot and resnapshotting.

Because the connector is a replica, the load it adds to the source is the load of one more replica: a log reader and a network stream, not queries. That is the actual argument for log-based CDC over the alternatives. It is not that it is more real-time, though it is. It is that it is the only one of the three that gets a complete and correctly ordered change history without asking the source database to do any extra work on the write path.