Skip to content

Change data capture

Reading a database's own change log (inserts, updates, deletes) and streaming each committed row change to other systems, so caches, search indexes and services follow the database instead of being written to twice.

On this page

What it is

Change data capture is a system that watches the data in a database and hands each change to other software that wants to react to it. The term is old: data warehouses needed a way to keep up with the transactional databases feeding them. Modern tools capture changes at the row level and publish them so that a consumer sees the events in the order the database applied them.

Where the changes come from. A database already keeps a log of its changes, and log-based CDC reads that log instead of the tables. MySQL 8.4's binary log defaults to row-based logging, where each entry says how individual rows were affected (statement-based and mixed formats also exist). PostgreSQL 17 calls the equivalent logical decoding: the write-ahead log, which describes changes at the storage level, is decoded into a stream of row changes, and a client reads that stream through a replication slot, which replays the changes of one database in the order they were made. A connector attaches to the log, emits events for each committed row change, and periodically records how far it has read so that it can resume after a restart.

What an event holds. A Debezium event is designed to be self-describing: its key and its value each pair a payload with a schema that describes it (the JSON converter can be set to leave the schema out), so a consumer can handle one event at a time even as the table's structure changes over the years. Depending on the database, a log-based event can include the old row state for an update or a delete, and metadata such as the transaction id. DynamoDB Streams exposes the same choice as a table setting: just the keys, the new item image, the old image, or both.

PropertyWhat the products stateWhat a consumer should do

Order

Most Debezium connectors write a table's events to one topic and keep them totally ordered there; DynamoDB Streams keeps the records for one item in the sequence of the modifications

Rely on order within a table or an item, not across tables

Duplicates

A connector on Kafka Connect delivers each event once while running normally and repeats some after a crash; a PostgreSQL 17 slot saves its position at checkpoints, so a crash can re-send recent changes

Keep the last position or a per-event id and skip what you've seen

Retention

DynamoDB Streams drops records after 24 hours; PostgreSQL 17 keeps WAL for as long as a slot still needs it

A consumer down longer than the retention loses changes; one that stops reading a slot fills the disk

The outbox. A service that writes a row and then publishes a message about it makes two separate writes, and there is no shared transaction covering both a database and a broker like Kafka. So one can succeed while the other fails: an order saved with no message, or a message sent for an order that was not saved. The outbox pattern writes the event as a row in an outbox table inside the same transaction as the business row. A log-based connector then captures the insert and relays it to the broker. The service keeps read-your-own-writes on its table, and the outbox row can be deleted right after it is inserted, because the connector reads the log, not the table.

When to use it

Use CDC when a second system has to follow the database: a cache or search index that must reflect edits, a warehouse or analytics store, or another service's local copy of the data. It fits best when you need deletes as well as inserts and updates, when you want each intermediate version rather than the latest state, when the delay should be seconds rather than a scheduled run, and when you'd rather not add a timestamp column to each table and trust each writer to maintain it. A connector that was down resumes from where it stopped in the log, so the gap is filled rather than lost.

Use the outbox form when a service must publish an event about its own write. The payload in the outbox row is designed for consumers, so the service can change its internal tables without breaking them, and an id column on the row lets consumers detect a repeated event after a restart.

When not to

Polling a last_updated column is the right choice when a refresh once an hour is enough and it doesn't matter that versions valid in between are missed. It needs no connector and no broker, just a change timestamp on each table and the time of the last run. Log-based CDC asks more of the source: MySQL has to run with row-level binary logging and a user allowed to read it, and a running connector adds load to the database.

CDC is asynchronous, so it doesn't fit a caller who needs the downstream effect before the request returns. If two tables in the same database must change together, use one transaction rather than a change stream. And think twice before making raw table events a contract for other teams: the outbox exists partly so that the shape consumers depend on is separate from the internal schema.

Don't create a replication slot without a consumer that keeps reading it. In PostgreSQL 17 a slot survives crashes and knows nothing about its receiver, so it holds WAL and catalog rows even with no connection attached; in extreme cases the server shuts down to prevent transaction id wraparound. Drop a slot you no longer need. A slot also serves one receiver at a time, so each consumer usually needs its own.

Compared

ApproachHow changes leave the databaseWhat it misses or costsWhen it's the right choice

Dual write from the service

The service writes the row, then publishes a message

Either write can fail while the other succeeds

A hint that may be lost without harm, such as a best-effort cache refresh

Polling a timestamp column

A query for rows changed since the last run

Deletes, intermediate versions, query load, and a timestamp column that writers or triggers must keep correct

Batch refresh where a delay of an hour is acceptable

Log-based CDC on tables

A connector reads the binlog or WAL

Exposes the table schema; a connector, a broker and offsets to run

Mirroring tables as they are into a cache, index or warehouse

Outbox through CDC

An event row commits with the business row and is read from the log

An extra table and a payload to design

A service publishing events about its own writes

A stream the database offers (DynamoDB Streams)

The database emits item changes itself, kept 24 hours

Consumers must keep up within the retention and handle shards

When the database you already run offers it

In a picture

writerecordsServiceDatabaseWrite-ahead log

Step 1 of 3

A write commits

The service writes a row inside a transaction. The database records the change in its write-ahead log; once the transaction commits, logical decoding can hand that change to a reader as a committed row change.

How to say it in an interview

The common mistake

Quiz

A nightly job copies rows whose last_updated is newer than the last run into a search index. What does it miss?
The consumer of a PostgreSQL 17 logical replication slot has been down for three days. What is the main risk?
Why does an outbox table fix the problem of writing a row and publishing a message as two steps?
A consumer of CDC events crashes and restarts. What should its code be written to expect?

Questions that use this

Related

Sources

  1. Debezium documentation: Frequently Asked Questions
  2. Debezium blog: Five Advantages of Log-Based Change Data Capture
  3. PostgreSQL 17 documentation: Logical Decoding Concepts
  4. Debezium blog: Reliable Microservices Data Exchange With the Outbox Pattern
  5. Amazon DynamoDB Developer Guide: Change data capture for DynamoDB Streams
  6. MySQL 8.4 Reference Manual: Binary Logging Formats
Sign in to report a mistake