Cheat sheet · Architecture
Change data capture (CDC)
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
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_warehouseMermaid: 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_warehousePlantUML: 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;
@endumlPlantUML: 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
@endumlRecap
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
| Metric | Start | End |
|---|---|---|
| Orders missing in search | 0 | 0 |
| Search staleness | 1 s | 2 s |
| Retained WAL | 2 GB | 3 GB |
| Duplicate events | 0 | 0 |
Steps
- 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.
- Problem The commit succeeds, the publish does not
The database commits
paid. - 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.
- Case What a change event carries
Each event says which row changed, how (
ccreate,uupdate,ddelete), the row before and after, and where in the log the change sits. - 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.
- Fix Partition by primary key, skip older versions
The connector now uses the primary key as the message key.
- Case The connector restarts and sends changes again
The connector saves its position, the offset, every 10 seconds.
- Problem The connector is down, and the database disk fills up
The connector stopped on Saturday and nobody noticed.
- Fix A new consumer: snapshot first, then stream
The connector is back and a data warehouse joins.
- Problem A column rename breaks a consumer
A developer renames
totaltototal_amount. - Fix Outbox plus CDC: publish events, not tables
Instead of streaming raw tables, the API writes a business event to an
outboxtable in the same transaction as the change. - 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.