The nightly job selects every order row where updated_at is later than yesterday’s run. It looks complete. Then finance asks why 412 cancelled orders still show as revenue, and the answer is that those rows were deleted, so no query on updated_at could ever see them. Polling only finds rows that still exist and still remember changing.
Change data capture (CDC) is a technique that turns every committed insert, update and delete in a database into an ordered stream of events. Log-based CDC reads the database’s transaction log, such as the PostgreSQL write-ahead log or the MySQL binlog, so it captures deletes and intermediate states, adds little load to the source, and delivers changes to analytics within seconds.
Why does polling with updated_at miss changes?
Polling sees snapshots, not changes. A hard delete leaves nothing behind to select. An update that skips the updated_at column, often a bulk fix run by hand, is invisible. A row updated three times between runs shows only its last state, so the history analysts asked for never arrives. And every poll is a scan on the production primary.
Change data capture fixes this by reading what the database already writes for its own durability. Each committed transaction lands in the log in commit order, which is the same ordering guarantee behind ACID transaction management. Read the log, and you get every change exactly as it was committed.
| Approach | Captures deletes and history | Cost to the source |
|---|---|---|
| Query polling updated_at filter |
No deletes; only the latest state. | Repeated scans on the primary. |
| Triggers audit table |
Yes, if every table has one. | Extra write inside every transaction. |
| Log-based CDC e.g. Debezium |
Yes, in commit order. | Reads the log; no extra writes. |
How does log-based change data capture work?
On PostgreSQL, set wal_level = logical, and the server can decode its write-ahead log into row changes through logical decoding. A connector such as Debezium creates a replication slot, reads changes through the built-in pgoutput plugin and publishes one Kafka topic per table. Each event carries an op code (c, u, d, or r for snapshot reads), plus before and after row images.
1{ 2 "name": "orders-cdc", 3 "config": { 4 "connector.class": "io.debezium.connector.postgresql.PostgresConnector", 5 "database.hostname": "orders-db.internal", 6 "database.port": "5432", 7 "database.user": "debezium", 8 "database.password": "${file:/secrets/pg.properties:password}", 9 "database.dbname": "shop", 10 "topic.prefix": "shop", 11 "plugin.name": "pgoutput", 12 "slot.name": "orders_cdc_slot", 13 "table.include.list": "public.orders,public.payments" 14 } 15}
Changes to public.orders land on the topic shop.public.orders, as described in the Debezium PostgreSQL connector documentation. In the warehouse, keep the raw change table append-only and derive current state from it with a ROW_NUMBER() window: newest event per key wins, and deleted keys drop out.
1WITH ranked AS ( 2 SELECT *, 3 ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY lsn DESC) AS rn 4 FROM orders_changes 5) 6SELECT order_id, status, amount_paise 7FROM ranked 8WHERE rn = 1 AND op <> 'd' 9ORDER BY order_id; 10-- sample log: 101 c→u (shipped), 102 c→d, 103 r (delivered) 11-- result: (101, 'shipped', 129900), (103, 'delivered', 89900)
When is change data capture the wrong tool?
When a full reload is cheap. A 50,000-row reference table refreshed nightly needs no slot, no Kafka topic and no on-call runbook. CDC also adds little if the business only reads daily aggregates, because second-level freshness is then a cost with no consumer.
Plan for two behaviours before going live. Debezium delivers events at least once, so a connector restart can replay changes, and every consumer must be idempotent. The ranking query above already is: a duplicate event carries the same log position and changes nothing. Schema changes also flow downstream. A column added on the source appears in the event payload, and the warehouse loader must either accept it or fail loudly, never drop it silently.
A stopped connector can fill the primary’s disk. A replication slot holds every WAL segment the connector has not yet confirmed. If the connector stays down over a long weekend, the write-ahead log grows until the volume fills. On PostgreSQL 13 and later, cap this with max_slot_wal_keep_size, alert on slot lag, and drop slots belonging to retired connectors.
Full before images need REPLICA IDENTITY FULL on the table, which adds WAL volume on every update. Enable it only where analytics actually reads old values.
- Change data capture turns committed inserts, updates and deletes into an ordered event stream instead of periodic snapshots.
- Polling on an updated_at column cannot see hard deletes, unflagged updates or the intermediate states between runs.
- Log-based CDC on PostgreSQL uses logical decoding with the pgoutput plugin and a replication slot per connector.
- Keep raw change events append-only in the warehouse and derive current state with a ROW_NUMBER() window over the log position.
- An unconsumed replication slot retains WAL indefinitely, so cap it with max_slot_wal_keep_size and alert on slot lag.
Conclusion
Start with one high-value table, prove the slot monitoring works, then widen the include list. When the source itself is split across nodes, each shard needs its own connector and slot, which is where database sharding and partitioning starts to shape your CDC design.
Frequently Asked Questions
What is change data capture?
Change data capture, or CDC, is a technique that records every committed insert, update and delete in a database and delivers them as an ordered stream of events. Downstream systems such as warehouses, search indexes and caches consume that stream to stay in sync without repeatedly querying the source database.
What is the difference between log-based and query-based CDC?
Query-based CDC polls tables for rows with a recent timestamp, so it misses hard deletes and intermediate updates and adds scan load to the source. Log-based CDC reads the database’s transaction log, capturing every change in commit order, including deletes, with little extra load on the source.
Does Debezium put load on the source database?
Very little during streaming, because it reads changes from the write-ahead log rather than querying tables. The initial snapshot does scan the captured tables once. The bigger operational risk is a replication slot retaining WAL when the connector stops, which can fill the database server’s disk.
How do I stop a replication slot from filling the disk?
On PostgreSQL 13 and later, set max_slot_wal_keep_size to cap how much WAL any slot can retain. Also monitor slot lag, restart failed connectors promptly, and drop slots that belong to connectors you have retired. Without a cap, an idle slot keeps WAL indefinitely.
How do I get the current state of a table from CDC events?
Store the raw events append-only, then select the newest event per primary key using ROW_NUMBER() partitioned by the key and ordered by log position descending. Keep rows where the rank is 1 and the operation is not a delete. That gives the current table state.