CSE Roadmap

Common tier · about 4 hours · 7 parts

Part 4 of 7 · 3 min read

Change data capture

Read the database's own record of changes and send it to everything else.

Think of a courthouse clerk who records every ruling in an official ledger. Journalists do not ask the judges. They copy new ledger entries in order. Everyone who follows the ledger ends up with the same story.

A database already writes down every change in order: its replication log (Level 4) or write-ahead log. For decades this was considered an internal detail, not a public interface. DDIA says that made it hard to get all changes out and send them to a search index, cache or warehouse.

Change data capture (CDC) is observing all data changes written to a database and extracting them in a form that can be copied to other systems. It works best when the changes are available as a stream, immediately.

Change data capture (CDC)

Change data capture is the process of observing all changes made to a database and publishing them, in order, as a stream of change events that other systems can consume.

Example: a tool reads a database's log and publishes events such as "row 41 in products changed: price 5 to 7" to a log topic. A search indexer consumes them.

Then the search index and the other derived systems are just consumers of the change stream. CDC makes one database the leader and turns the other systems into followers. A log-based broker (page 3) is a good way to carry the events, since it keeps their order and avoids the reordering of page 1.

The race of section 2 disappears. The database decides the order, and the log carries that order, so the index sees A and then B and ends with B.

How the changes are read:

Way Idea Weakness
Database triggers A trigger on each table writes a row to a changelog table The book calls them fragile, with large performance cost
Parsing the replication log A tool reads the log the database already writes Needs care with changes of table layout (schema)

The book names tools of the second kind: Debezium and Maxwell for MySQL's binlog, Bottled Water for PostgreSQL, Mongoriver for MongoDB, GoldenGate for Oracle, and at large scale LinkedIn's Databus and Facebook's Wormhole. Some databases now offer change streams as a built-in feature. Kafka Connect connects CDC tools to Kafka.

Like message brokers, CDC is usually asynchronous. The database does not wait for the consumers before it commits. The upside is that a slow consumer does not hurt the database. The downside is that all the issues of replication lag apply: a consumer may be behind.

A worked example. A shop updates product 41's price from 5 to 7 at 10:00:00.

Time Event Database Search index
10:00:00.000 Application writes price 7 7 5
10:00:00.010 CDC tool reads the log entry, publishes it 7 5
10:00:00.150 Indexer consumes the event, updates the index 7 7

For 150 milliseconds, search shows the old price. Under a backlog (page 1) this could be minutes.

The application is unchanged: it writes only to the database and need not know about CDC.

Common mistake

Having the application also write to the search index "just in case" next to CDC. This brings back the dual write race. Let the change stream be the only writer of the derived system.

Check yourself:

  • Why does CDC remove the ordering problem of dual writes?
  • Which is more fragile, triggers or reading the log, according to DDIA?