Learn With Bagus

Appearance

Change Data Capture: Streaming Every Data Change in Near Real-Time ​

7 min readdatabasedata-pipeline

Imagine an e-commerce application that stores product data in PostgreSQL. The same data turns out to be needed in many other places:

  • Elasticsearch for the product search feature.
  • Redis as a fast-access catalog cache.
  • A data warehouse for the business team analytics dashboard.
  • A notification service that sends an email when a price changes.

The most intuitive way to keep them in sync: every time the application finishes writing to the database, it also sends an event to Kafka and updates Elasticsearch. This pattern is called dual write, and this is where the problem begins.

text
Application: BEGIN -> UPDATE product -> COMMIT (success)
Application: send "product changed" event to Kafka (fails: network error)

The database already holds the new price, but Elasticsearch still holds the old price. There is no mechanism that guarantees both writes succeed together, because two different systems are not inside one transaction. This kind of inconsistency is usually only discovered when a customer complains that search results do not match.

Change Data Capture (CDC) solves this problem by reversing the direction: do not let the application sync the other systems manually. Take the changes directly from the source of truth, which is the database.

What is CDC? ​

Change Data Capture is a pattern to capture every data change in a database, whether INSERT, UPDATE, or DELETE, and then stream it as events that other systems can consume, in near real-time.

Three core characteristics of CDC:

  • The database stays the single source of truth. Other systems no longer write data copies on their own; they follow the stream of changes from the database.
  • Every change becomes an event, including DELETE, which is exactly the change most often missed by simple polling approaches.
  • The stream runs automatically, without changing every code path that writes to the database. There is one infrastructure addition, not changes in dozens of services.

Mental Model: where are the changes captured from? ​

CDC can be implemented in several ways. The difference matters because it decides reliability and system load.

Query-based CDC (polling) ​

The simplest way: a job runs periodically and looks for data that changed since its last run:

sql
SELECT *
FROM products
WHERE updated_at > :last_check
ORDER BY updated_at;

This way is easy to understand and needs no extra tools, but it has basic weaknesses:

  • DELETE is invisible: a deleted row never appears in the query result, so the other systems never know that the product no longer exists.
  • Latency equals the polling interval: if the job runs every 1 minute, the newest data arrives up to 1 minute late.
  • It needs an updated_at column maintained consistently in every table, which is often forgotten in older code.
  • Repeated queries add load to the same database the application uses.

Trigger-based CDC ​

A database trigger listens to every change and writes its record to a special table, then another process reads that table. This way captures DELETE and is more timely, but triggers add load inside the database and are often forgotten during schema maintenance.

Log-based CDC ​

The most efficient way: take the changes directly from the change log the database already writes naturally, which is the WAL in PostgreSQL or the redo log in MySQL InnoDB.

Further reading

The WAL is an append-only log the database writes to disk for every change as a durability guarantee. To understand how it works, including why writing to this log is fast and how it relates to COMMIT, read the article Write-Ahead Log: Why Databases Write Twice but Stay Fast.

Log-based CDC works with a simple principle: the database already records every change in the WAL, you only need to read it again. No repeated queries, no extra triggers, no updated_at column to maintain. This is why almost every modern CDC tool, such as Debezium, uses this approach.

Loading diagram...

How does log-based CDC work? ​

The full flow, component by component:

  1. The application writes as usual. There is no change in the application code at all. Writes to the database still produce WAL records, exactly like before.
  2. The CDC connector reads the log. A tool like Debezium connects to the database as a replication client. In PostgreSQL, this works through a logical replication slot, a built-in mechanism that lets a client receive a stream of changes in order without disturbing database operations.
  3. Changes are converted into uniform events. One single-row change becomes one JSON event containing the before and after state: { "before": { "price": 100000 }, "after": { "price": 120000 }, "op": "u" }. The op value explains the type: c (create), u (update), or d (delete).
  4. Events are sent to a message broker. Kafka is usually the middleman, then each other system (search index, cache, warehouse) becomes a consumer that subscribes to its own topic.

Two concepts are important to understand here:

  • Initial snapshot: the WAL stream only contains changes from the moment the connector starts. For data that already exists, the connector runs an initial snapshot, which means reading the whole table content once at the beginning, before it starts following the stream of changes.
  • Offset (log position): the connector records the last position it has read in the WAL. If the connector dies and comes back, it continues from that position, not from the start. This is what makes CDC reliable and prevents lost events.

Why is CDC more reliable than dual write? ​

The basic difference is the number of writers:

  • With dual write, many points can fail separately: the database succeeds but the broker fails, or the application crashes between the two writes. Nothing makes sure both sides stay consistent.
  • With CDC, the database is the only source, and events are pulled from a log whose durability is already guaranteed by the database itself. If a data change is already COMMITted, its event can definitely be emitted. If the transaction is rolled back, the WAL produces no event, and no other system ever knows about that change.

Consistency between systems is no longer guarded by application code, but by the architecture.

When is CDC used at work? ​

CDC fits needs like these:

  • Real-time synchronization from a database to a search index or a cache.
  • Streaming changes into a data warehouse or stream processing without periodic batch export.
  • Building an event-driven architecture on top of an existing database, without changing application code.
  • Audit logs: recording who changed what, including DELETE.

On the other hand, do not use CDC when the need is simple: to validate data between systems once a day or to run a periodic report, simple polling or a direct query is enough and much cheaper to operate.

Operational caution

A log-based CDC connector reads from a replication slot. If the connector dies for a long time and the slot is not monitored, the WAL will pile up because the database does not dare clean a log that has not been read, and the database disk can become full. Monitor the consumption lag and the slot size in your monitoring.

Summary ​

  • CDC captures every data change (INSERT, UPDATE, DELETE) from a database and streams it as events to other systems, in near real-time.
  • The polling (query-based) approach is simple but misses DELETE and adds query load. Log-based CDC is the most efficient approach.
  • Log-based CDC reads the WAL or redo log the database already writes naturally, converts the changes into uniform events, and sends them to a message broker.
  • An initial snapshot copies existing data at the start, and an offset makes sure the connector continues from its last position when it comes back.
  • CDC removes the dual write problem: the database stays the single source of truth, and consistency between systems is guarded by the architecture, not application code.

References ​