Skip to content

Distributed processing and the log

Capturing changes from a database

Why reading a table again and again misses changes, how change data capture reads the changes a database already records, how a snapshot joins the changes without a gap, and what a row change does not say.

Learn with an AI assistant

Copy the prompt and paste it into a chat with an AI assistant.

You are a tutor for the course "Stream Processing".
Topic: "Capturing changes from a database"
In short: Why reading a table again and again misses changes, how change data capture reads the changes a database already records, how a snapshot joins the changes without a gap, and what a row change does not say.
Topic text in Markdown: https://stream-processing-systems.github.io/distribution/capturing-changes-from-a-database.md
Base your explanations on the topic text. First ask how I want to study: a short summary, a step-by-step walkthrough, or a deep dive with questions that check my understanding. Use the text's example and terms. If you add anything the text does not say, point it out.

Our AI assistant charges for tokens by plan. The plans live in a PostgreSQL database, in a table plans, one row per account:

account_idplanprice_per_1k_tokensmonthly_limit
acmeteam0.4050,000
betateam0.4050,000
gammastarter0.605,000

The support team changes this table through its own tool. At 14:20 on February 2, acme moves to the business plan, with a lower price and a larger limit. The usage processor reads usage records from the log, and it needs the plans too, to turn tokens into money and to warn customers who approach their limit. A table is not a log: it holds the current rows and does not tell anyone that a row has changed. How can every change in plans reach the processor, in order and without gaps?

The simplest idea is to ask the database again and again. Every five minutes, the processor runs a query for the rows changed since its last run, using a column updated_at that records when each row was last changed.

At 14:20, support moves acme to business: the price drops from 0.40 to 0.30 per thousand tokens, and the limit rises to 500,000. At 14:22, they correct the limit to 400,000 without changing the price. The query at 14:25 returns one row: price 0.30, limit 400,000, updated_at 14:22.

The processor misses the intermediate limit of 500,000. It also cannot tell when the price changed: updated_at now points to the limit correction, overwriting the time of the plan change.

Reading rows misses intermediate changes The limit becomes 500,000 at 14:20 and 400,000 at 14:22; polling at 14:25 sees only 400,000, while CDC records both changes. Limit in plans two changes 500,000 400,000 Polling one current row 400,000 CDC both changes 500,000 400,000 14:20 14:22 14:25 TIME

Deleted rows are worse. When gamma closes its account and its row is removed, a query over the rows that exist returns nothing about it. And the query relies on updated_at. A script that fixes rows directly and forgets to set it makes its changes invisible.

Asking more often narrows the gaps without closing them, and every query adds load to the database that serves the support tool.

A database already writes down every change, for its own sake. Before PostgreSQL changes the files that hold a table, it records the change in its write-ahead log (WAL) and stores that record safely. After a crash, it repeats the logged changes that had not yet reached the files.1 So the WAL holds every change in the order the database made it, including deletes.

Reading changes from there and turning them into records for other systems is called change data capture, or CDC. The WAL describes changes in terms of how the database stores them. PostgreSQL can decode them into changes of table rows that a program can understand; this is called logical decoding.2

Debezium, a widely used CDC tool, does this for PostgreSQL and several other databases. It writes the changes of each table to its own Kafka topic, one record per changed row, with the row’s primary key as the record’s key.3 By default, PostgreSQL passes only the primary key of the old row, so the plans table is set to REPLICA IDENTITY FULL to include all its previous values.3 The change at 14:20 becomes a record like this, slightly shortened:

{
"before": { "account_id": "acme", "plan": "team", "price_per_1k_tokens": 0.40, "monthly_limit": 50000 },
"after": { "account_id": "acme", "plan": "business", "price_per_1k_tokens": 0.30, "monthly_limit": 500000 },
"op": "u",
"source": { "table": "plans", "lsn": 2471839104, "ts_ms": 1770042000000 }
}

The record holds the row before and after the change and the kind of change: u for update; inserts and deletes have their own letters.3 The lsn is the change’s position in the WAL, and ts_ms is when the database made it. The correction at 14:22 becomes a second record, so the processor sees both values of the limit and the moment of each.

A deleted row gives a record with the letter d and an after that is null. Debezium follows it with a tombstone: a separate record with the same key and a null value, so that a compacted topic can remove the account completely.3 Since each change replaces the previous row for its key, the plans topic is a natural candidate for compaction.

The WAL does not keep changes forever. The database removes old parts once it no longer needs them, so the rows created last year are no longer in it as changes. A processor that started reading the WAL today would learn about beta and gamma only when someone changed them.

So capture starts with a snapshot: a reading of the current rows of the table at one moment. Debezium writes each row of the snapshot as a record of its own, marked with the letter r for read, and then continues with the changes from the WAL.3

These snapshot records mean “this row existed when capture started.” They are not new accounts. A processor that greeted every r record as a new customer would send a welcome email to everyone on the day capture began.

The snapshot and the changes have to meet at one point. Suppose the snapshot reads acme as team at 14:19, and reading the WAL starts at a position after the change of 14:20. The processor then has acme on team and never sees the change. If reading the WAL starts too early instead, some changes arrive a second time. A change repeated right after itself is harmless: it sets the row to the same after value again. A repeat that arrives after a newer change is not. If the change of 14:20 came again after the correction of 14:22, the limit would go back to 500,000 until the correction arrived once more.

Debezium joins them like this: inside one transaction, it first reads the current WAL position, then reads the tables, and when the snapshot is done, it reads the WAL from the position it noted.3 The snapshot describes the table at that position, and the changes start right after it. It is the same rule as in a consistent checkpoint: the state and the position must describe the same moment.

A handoff without a gap The snapshot describes the table at the captured WAL position and capture continues with changes after that same boundary. CAPTURE POSITION Database state at the boundary Snapshot rows at the boundary WAL changes read from the boundary WAL POSITION

After the snapshot, the reading position has to survive restarts of the capture. Debezium saves its position, the lsn it has reached, in its own offset storage. PostgreSQL keeps the WAL that this position still needs through a replication slot: the database does not remove the parts of the WAL that a slot still needs, even while nobody is connected. If Debezium stops for a day, it resumes from its saved position without losing changes, as long as the slot still exists and the WAL it needs is still there.4 The cost falls on the database: the WAL grows on its disk until the reader returns, and a slot that nobody uses anymore has to be removed before the disk fills.2

After a crash of the database, a slot can also return to an earlier position, and recent changes are sent again.2 So readers of the plans topic must handle repeats, including old changes that come after newer ones. A reader can keep, with each row, the position of the change that set it, and skip a change that is not newer, as with the versions of a total. Applying changes then becomes idempotent. In our example, the lsn of the change at 14:22 is greater than that of 14:20, so the old limit cannot come back.

The record from 14:22 says that acme’s limit went from 500,000 to 400,000. It does not say why. A support worker may have corrected a typo, or the customer may have chosen a smaller limit. For billing, the difference matters: a correction means the limit was never 500,000 for the customer, while a choice means it was, for two minutes. The row records the current state, and a change of state is not the same as the event that caused it.

When the reason matters, the application can write it down as an event. The support tool changes the row and, in the same transaction, adds a record “limit corrected” to an outbox table. Debezium captures the outbox table and publishes the event to the log only after the transaction commits.5

A captured table also becomes something its readers depend on. Its columns are now the fields of records that other programs read. Renaming a column in the database changes those records for every reader of the topic.

The processor now has two inputs: usage records, which add up, and plan changes, which replace each other and form a table that is current at any moment. A request by acme at 14:19:58 has its usage record arrive at 14:20:03, after the plan change of 14:20 has already reached the processor. Which price applies to it, and how should a stream of events be combined with a table that keeps changing?

  • Asking a table for changed rows sees its current state, not every change: it misses intermediate values and deleted rows.
  • Change data capture reads the changes a database already records in its own log, in order and including deletes, and writes them as records keyed by the row’s primary key.
  • Capture starts with a snapshot of the current rows, and the snapshot and the changes must meet at one position. Readers must handle repeated changes, for example by keeping the position of the change that set each row.
  • A row change records a new state, not its reason. When the reason matters, the application writes an event, for example through an outbox.
  1. PostgreSQL 18: Write-Ahead Logging (WAL).

  2. PostgreSQL 18: Logical Decoding Concepts.

  3. Debezium 3.7: PostgreSQL connector, Snapshots and Data change events.

  4. Debezium 3.7: PostgreSQL connector, PostgreSQL becomes unavailable.

  5. Debezium 3.7: Outbox Event Router.

  • Martin Kleppmann and Chris Riccomini, Designing Data-Intensive Applications, 2nd ed. (O’Reilly Media, 2026), chap. 12, “Stream Processing.” The section on databases and streams explains why other systems need a database’s changes, why an initial snapshot must match a position in the change log, and how capturing changes differs from recording events.

© 2026 Grig Alex · CC BY-NC-SA 4.0