CDC (Change Data Capture)
Change data capture (CDC) is a way of copying a database by streaming its changes instead of re-reading its tables. Every insert, update and delete is captured as an event, in commit order, within seconds of happening, and delivered to other systems: a warehouse, a search index, a cache or another service.
The reliable way to do it is log-based CDC: read the database’s own transaction log (the PostgreSQL WAL, the MySQL binlog, the SQL Server transaction log, the MongoDB oplog). Every database already writes this log for crash recovery and replication, so CDC gets a complete, ordered history of changes without querying the tables at all.
Watch it happen
| NO. | STEP | WHERE | OP | INFO |
|---|---|---|---|---|
| 1 | 01 | app → pg | INSERT | INSERT INTO orders VALUES (7, 'NYC', 'new') |
| 2 | 02 | app → pg | UPDATE | UPDATE orders SET status = 'paid' WHERE id = 7 |
| 3 | 03 | app → pg | DELETE | DELETE FROM orders WHERE id = 5 |
| 4 | 05 | kafka | op=c | key 7 · before null · after {id 7, NYC, new} |
| 5 | 05 | kafka | op=u | key 7 · before {id 7, NYC, new} · after {id 7, NYC, paid} |
| 6 | 05 | kafka | op=d | key 5 · before {id 5, TOR, new} · after null |
- An app writes to Postgres. Order 5 already exists. Below the table sits the WAL, the log Postgres writes before changing any data.
- The app inserts order 7. The row appears and an INSERT record is appended to the WAL.
- Order 7 is paid. The row changes in place, but the WAL keeps an UPDATE record: history is preserved.
- Order 5 is deleted. A table scan would never notice it is gone; the WAL has a DELETE record.
- Debezium reads the WAL through a replication slot, like a replica would. It never queries the table.
- Each record becomes a change event with before and after images, keyed by primary key, so all changes to one order land in order on one partition. (Full before images on update and delete need
REPLICA IDENTITY FULL; by default Postgres logs only the key.)
Ways to capture changes
| Method | How | Catches deletes? | Load on source | Verdict |
|---|---|---|---|---|
| Log-based | Read WAL / binlog / oplog | Yes | Very low | The default. Needs log access and config |
| Trigger-based | Triggers write each change to an audit table | Yes | High: every write does extra work | Only when you can’t read the log |
| Query-based (timestamp / ID) | WHERE updated_at > :last_run on a schedule |
No (hard deletes vanish) | Medium, repeated scans | Fine for append-only tables; misses intermediate states |
Query-based “CDC” is really incremental extraction. It misses deletes, misses a row that changed twice between polls, and depends on every writer updating updated_at correctly.
Setting it up on PostgreSQL
- Set
wal_level = logicaland restart. - Create a publication for the tables to capture, and a user with the
REPLICATIONattribute. - Point the connector (Debezium on Kafka Connect, Debezium Server, Flink CDC, or a managed service) at the database. It creates a replication slot, takes an initial snapshot of existing rows, then switches to streaming the WAL from the exact position where the snapshot ended.
MySQL is similar with binlog_format = ROW and binlog_row_image = FULL.
Common mistakes
- An abandoned replication slot. If the connector stops, Postgres keeps every WAL segment the slot hasn’t confirmed, and the disk fills up. Monitor slot lag and set
max_slot_wal_keep_size. - Ignoring schema changes. An
ALTER TABLEchanges the event shape. Use a schema registry with Avro or Protobuf so consumers can evolve. - Assuming exactly-once delivery. After a restart, a connector can re-send events. Make consumers idempotent: upsert by primary key, using the log position (LSN) to ignore older versions.
- Replicating to the warehouse with plain INSERTs. Updates and deletes must become
MERGEstatements, or you get one row per version. - Missing old values on update. In Postgres, set
REPLICA IDENTITY FULLon tables where you need the fullbeforeimage.
Tools in 2026
- Open source: Debezium (Kafka Connect or standalone Debezium Server), Flink CDC, Kafka Connect JDBC (query-based only).
- Managed: AWS DMS, Google Datastream, Azure Data Factory CDC, Confluent’s managed Debezium connectors, Fivetran, Airbyte.
- Built into databases: Postgres logical replication, MySQL binlog replication, SQL Server CDC, MongoDB change streams, Snowflake and Databricks streams / change data feed on their own tables.
Learning Resources
Docs
Books and talks
- Designing Data-Intensive Applications, Chapter 11 “Stream Processing”: Martin Kleppmann
- Turning the database inside-out with Apache Samza: Martin Kleppmann
- Streaming Change Data Capture
Videos
- Change Data Capture (CDC) Explained (with examples)
- Data Pipelines: Using CDC to Ingest Data into Kafka
See also: ELT, Messaging and Streaming Platforms