mufassall

Cheat sheet · Architecture

Change data capture (CDC)

15 min · Advanced · mufassall.dtwin.uz/en/l/cdc/

← Open the lesson

The problem

An online shop keeps orders in SQL and has to mirror every change into a search index and a data warehouse. Writing to the database and to the event log from the same code loses events. Reading the database's own change log does not, as long as you handle ordering, restarts, the first snapshot and schema changes.

What you learn

  • Why a dual write to the database and the event log drifts apart
  • How a CDC connector tails the WAL or binlog and what a change event looks like
  • Ordering by primary key, stored offsets and duplicates after a restart
  • Initial snapshot plus stream, replication-slot disk usage, schema changes and the outbox
Final scene
40%60%

Diagram as code

The lesson's final design as text: paste it into Jira, GitLab, GitHub, Notion or your docs, where Mermaid renders by itself. There are D2 and PlantUML (Confluence, IntelliJ) versions too.

Mermaid: flowchart

flowchart LR
  n_client(["Shop app"])
  n_api["Orders API"]
  n_db[("Orders DB")]
  n_topic[["Event log"]]
  n_search["Search index"]
  n_connector("CDC connector")
  n_warehouse[("Data warehouse")]
  n_client --> n_api
  n_api --> n_db
  n_topic --> n_search
  n_db --> n_connector
  n_connector --> n_topic
  n_topic --> n_warehouse

Mermaid: request sequence

sequenceDiagram
  participant n_api as Orders API
  participant n_topic as Event log
  participant n_db as Orders DB
  participant n_connector as CDC connector
  participant n_search as Search index
  participant n_warehouse as Data warehouse
  Note over n_api,n_topic: The API writes twice: to the DB and to the event log
  n_api->>n_topic: PRODUCE orders-events
  n_topic-->>n_api: OK partition 3, offset 88123
  Note over n_api,n_topic: The commit succeeds, the publish does not
  n_api-xn_topic: 10:00:01.120 COMMIT order 5521 status=paid
  Note over n_db,n_connector: Read the database's own change log
  n_db->>n_connector: START_REPLICATION SLOT shop_cdc LOGICAL 0/1A2B3C0
  Note over n_connector,n_topic: What a change event carries
  n_connector->>n_topic: PRODUCE shop.public.orders
  Note over n_topic,n_search: Two changes of one order arrive in the wrong order
  n_topic->>n_search: search consumer, 2 threads, round-robin partitions
  Note over n_connector,n_topic: Partition by primary key, skip older versions
  n_connector->>n_topic: connector config: orders
  Note over n_connector,n_topic: The connector restarts and sends changes again
  n_connector->>n_topic: connector restarted, stored offset lsn=0/1A2B000
  Note over n_db,n_connector: The connector is down, and the database disk fills up
  n_db-xn_connector: pg_replication_slots: shop_cdc active=false since Saturday
  Note over n_db,n_connector: A new consumer: snapshot first, then stream
  n_db->>n_connector: BEGIN ISOLATION LEVEL REPEATABLE READ#59;
  Note over n_topic,n_warehouse: A column rename breaks a consumer
  n_topic->>n_warehouse: ALTER TABLE orders RENAME COLUMN total TO total_amount#59;
  n_warehouse-->>n_topic: warehouse: field total missing in 18400 events
  Note over n_api,n_db: Outbox plus CDC: publish events, not tables
  n_api->>n_db: BEGIN#59;

D2

direction: right

n_client: "Shop app" {
  shape: rectangle
}
n_api: "Orders API" {
  shape: rectangle
}
n_db: "Orders DB" {
  shape: cylinder
}
n_topic: "Event log" {
  shape: queue
}
n_search: "Search index" {
  shape: rectangle
}
n_connector: "CDC connector" {
  shape: rectangle
  style.multiple: true
}
n_warehouse: "Data warehouse" {
  shape: cylinder
}

n_client -> n_api
n_api -> n_db
n_topic -> n_search
n_db -> n_connector
n_connector -> n_topic
n_topic -> n_warehouse

PlantUML: request sequence

@startuml
participant "Orders API" as n_api
queue "Event log" as n_topic
database "Orders DB" as n_db
participant "CDC connector" as n_connector
participant "Search index" as n_search
database "Data warehouse" as n_warehouse
note over n_api, n_topic : The API writes twice: to the DB and to the event log
n_api -> n_topic : PRODUCE orders-events
n_topic --> n_api : OK partition 3, offset 88123
note over n_api, n_topic : The commit succeeds, the publish does not
n_api ->x n_topic : [lost] 10:00:01.120 COMMIT order 5521 status=paid
note over n_db, n_connector : Read the database's own change log
n_db -> n_connector : START_REPLICATION SLOT shop_cdc LOGICAL 0/1A2B3C0
note over n_connector, n_topic : What a change event carries
n_connector -> n_topic : PRODUCE shop.public.orders
note over n_topic, n_search : Two changes of one order arrive in the wrong order
n_topic -> n_search : [duplicate] search consumer, 2 threads, round-robin partitions
note over n_connector, n_topic : Partition by primary key, skip older versions
n_connector -> n_topic : connector config: orders
note over n_connector, n_topic : The connector restarts and sends changes again
n_connector -> n_topic : [duplicate] connector restarted, stored offset lsn=0/1A2B000
note over n_db, n_connector : The connector is down, and the database disk fills up
n_db ->x n_connector : [lost] pg_replication_slots: shop_cdc active=false since Saturday
note over n_db, n_connector : A new consumer: snapshot first, then stream
n_db -> n_connector : BEGIN ISOLATION LEVEL REPEATABLE READ;
note over n_topic, n_warehouse : A column rename breaks a consumer
n_topic -> n_warehouse : ALTER TABLE orders RENAME COLUMN total TO total_amount;
n_warehouse --> n_topic : warehouse: field total missing in 18400 events
note over n_api, n_db : Outbox plus CDC: publish events, not tables
n_api -> n_db : BEGIN;
@enduml

PlantUML: components

@startuml
left to right direction
agent "Shop app" as n_client
component "Orders API" as n_api
database "Orders DB" as n_db
queue "Event log" as n_topic
component "Search index" as n_search
collections "CDC connector" as n_connector
database "Data warehouse" as n_warehouse
n_client --> n_api
n_api --> n_db
n_topic --> n_search
n_db --> n_connector
n_connector --> n_topic
n_topic --> n_warehouse
@enduml

Recap

Six rules for change data capture.

1) Never write to the database and the event log as two separate steps. 2) Let a connector read the database's own log, so a committed change is always published. 3) Key events by primary key: order holds per row, not across the topic. 4) Delivery is at least once; consumers skip by log position. 5) Watch the replication slot, or the database disk fills. 6) Start with a consistent snapshot, then stream; treat schema as a contract, or publish through an outbox.

Numbers: at the start and at the end

MetricStartEnd
Orders missing in search00
Search staleness1 s2 s
Retained WAL2 GB3 GB
Duplicate events00

Steps

  1. Case The API writes twice: to the DB and to the event log

    When an order is paid, the Orders API updates the row in the database and then publishes an event, so the search index can show the new status.

  2. Problem The commit succeeds, the publish does not

    The database commits paid.

  3. Fix Read the database's own change log

    The database already writes every committed change to its log, the WAL in PostgreSQL or the binlog in MySQL, before anything else.

  4. Case What a change event carries

    Each event says which row changed, how (c create, u update, d delete), the row before and after, and where in the log the change sits.

  5. Problem Two changes of one order arrive in the wrong order

    The connector spreads events over partitions round-robin, and the search consumer reads partitions in parallel.

  6. Fix Partition by primary key, skip older versions

    The connector now uses the primary key as the message key.

  7. Case The connector restarts and sends changes again

    The connector saves its position, the offset, every 10 seconds.

  8. Problem The connector is down, and the database disk fills up

    The connector stopped on Saturday and nobody noticed.

  9. Fix A new consumer: snapshot first, then stream

    The connector is back and a data warehouse joins.

  10. Problem A column rename breaks a consumer

    A developer renames total to total_amount.

  11. Fix Outbox plus CDC: publish events, not tables

    Instead of streaming raw tables, the API writes a business event to an outbox table in the same transaction as the change.

  12. Recap Recap

    Six rules for change data capture.

Terms

CDC (change data capture)
Reading the database's own log (WAL or binlog) and publishing every row change as an event. The application never writes to two places, so the database and the events cannot diverge.
Dual write
The application writes one change to the database and then to a broker (or search index). If the second write fails or the order gets mixed up, the two systems silently diverge.