Change Data Capture

Define the problem

One database is rarely the only store that must know a fact. A product change must reach the search index, the cache and the analytics store.

The direct answer is the dual write. The application writes the database, then writes each other store in turn.

A crash between those writes splits the truth. The database has the change, the index never hears of it, and no error is recorded anywhere.

Polling is the other direct answer. Each store keeps asking the database what changed, which wastes queries and still learns late.

Reading the log

A database already keeps a write-ahead log. Every change is written there first, in the order it happened, so the database can recover after a crash.

Change data capture reads that log as a stream. Each entry becomes one change event for the systems downstream.

A change event names the table, the key of the row, and the value before and after the change. Deletes travel as events too, marked as deletes.

Order is the prize. Changes to the same row arrive downstream in the order they were made, because the log itself is ordered.

The application writes once, to its database, and stays out of the distribution business entirely.

Estimate the stream

A busy table sees 10,000,000 updates a day, as a stated demo figure.

10,000,000 updates ÷ 86,400 seconds = 116 change events per second on average.

At 500 bytes per event, the stream carries 10,000,000 × 500 bytes = 5,000,000,000 bytes a day, about 5 GB.

Keeping 7 days of the stream for replay takes 5 GB × 7 = 35 GB of log storage.

The stream is modest next to the database itself. The cost of capture is mostly the consumers keeping up, not the reading of the log.

Consumers and replay

Each consumer reads the stream at its own pace. A search indexer applies product changes, a cache consumer deletes stale entries, and an analytics loader appends rows.

Every consumer remembers its position in the log. After a crash, it restarts from that position and replays what it missed.

Replay means some events arrive twice. Consumers are built idempotent, or they dedupe events they have already applied, so a second delivery changes nothing.

A new consumer can start from the very beginning of the retained log. That is how a fresh search index is built without stopping the application.

Where it breaks

The shape of the stored data changes over time. When a table gains a field, old events lack it and new events carry it, and every consumer must cope with both shapes.

Retention is a hard window. A consumer that stays down longer than the log is kept loses its place, and it must rebuild from a fresh snapshot instead of replaying.

Downstream systems are eventually current, not instantly current. Under load the stream lags, and a search can miss a change made a moment ago.

The log is the database's own machinery. Tying consumers to its format couples them to the database, and swapping databases means rebuilding the capture layer.

Trade-offs and wrap-up

Against the dual write, capture wins on truth. There is exactly one write, so there is nothing to split.

Against polling, capture wins on freshness and load. Changes flow as they happen, and no store burns queries asking whether anything changed.

The main idea is reuse. The database already writes the perfect record of its changes for its own recovery. Change data capture simply lets everyone else read it.

Three ways to learn about a change