Chapter 14 — Key Takeaways (Change Data Capture)

The page for setting up CDC, and for the night a slot fills a disk.

What CDC is

Reading the log the database was already writing for crash recovery. Ordered, complete, durable, and physical — which is why it needs logical decoding (wal_level = logical, set in Ch. 5 because changing it requires a restart).

What it solves, and the price

CDC for mutable, high-value, inconvenient-to-reload tables. Batch for the rest. Kestrel: CDC on 5 tables, full loads on 7. products at 47,000 rows and a 12-second load does not need connectors, slots, and snapshots.

Three kinds

The Debezium envelope — four things matter

{"op": "u",                        // c create · u update · d delete · r snapshot
 "before": {...}, "after": {...},  // before is null on create, after on delete
 "source": {"lsn": 55016704,       // ← ORDERING and DEDUP key
            "ts_ms": 1764340879002},   // ← EVENT time
 "ts_ms": 1764340879184}               // ← PROCESSING time

Dedup on LSN, not timestamp — two changes can share a millisecond, not an LSN. Use source.ts_ms for anything analytical.

⚠️ REPLICA IDENTITY defaults to DEFAULT — only the primary key goes in before. Every SCD2 build and audit silently gets nothing. The most common Debezium misconfiguration, and its symptom is not an error.

ALTER TABLE orders REPLICA IDENTITY FULL; — and it is not free: +31% WAL on orders, +18% on customers at Kestrel. Decide per table, and record it beside the dimension design.

The initial snapshot — plan it before the stream

75% slower and the only operationally acceptable one. At 10× Kestrel the other two stop being options at all.

⚠️ Operating a replication slot

A slot guarantees the primary retains WAL until the consumer confirms it. That is what makes CDC reliable and what makes it dangerous.

consumer stops → WAL accumulates without bound → disk full
→ PostgreSQL CANNOT WRITE WAL → CANNOT COMMIT → THE STOREFRONT STOPS

Four controls, all required

1. max_slot_wal_keep_size — converts an outage into a broken stream. The one that would have prevented it. Size it for a weekend; a consumer down two weeks should break.
2. Monitor retained WAL — warn at 25% of the ceiling, page at 50%.
3. Alert on active = false ← the highest-value alert in the chapter — it fires at the crash, not when the disk is filling.
4. Drop unused slots. Name them cdc_orders_dataeng, never debezium_poc — the name is the only metadata a slot carries, and at 02:56 it is all you have.

And alert on the mechanism, not the symptom

du -sh $PGDATA/* | sort -h | tail -5      # pg_wal should be roughly CONSTANT

"pg_wal exceeds 4 × max_wal_size" fires in week two and names the cause. "Disk 90% full" fires in week ten and names nothing.

⚠️ Never delete files from pg_wal by hand

It converts a 50-minute incident into a restore. The four safe actions, filed under "database disk full", not under "CDC" — the person who meets this is a backend engineer:

1. SELECT slot_name, active, pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) FROM pg_replication_slots; (also check archive_command failures — same mechanism)
2. SELECT pg_drop_replication_slot('...'); if abandoned
3. Add disk to buy time
4. Never hand-delete WAL

Consuming

Order is per-key — Debezium keys by primary key, Kafka orders within a partition. Changes to different rows are not ordered relative to each other, even in one source transaction.

At-least-once, always. Dedup on LSN.

A delete emits TWO messages — the delete event, then a tombstone (null value) for log compaction. Naive consumers null-pointer on it.

Stream → table

MERGE INTO silver.orders t USING (
  SELECT * FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY order_id
                                              ORDER BY lsn DESC) rn
                   FROM bronze.orders_cdc) WHERE rn = 1) s
ON t.order_id = s.order_id
WHEN MATCHED AND s.op = 'd'           THEN DELETE
WHEN MATCHED AND s.lsn > t.source_lsn THEN UPDATE SET ...   -- ← IDEMPOTENT
WHEN NOT MATCHED AND s.op <> 'd'      THEN INSERT ...;

Keep a history table alongside. That is where the intermediate states live, and it is what nothing else gives you.

Two settings that prevent specific incidents

heartbeat.action.query — writes to a captured table so the slot can advance. Without it, a connector on a low-volume table can fill the disk of a high-volume database, because the slot only advances on messages the connector receives.

errors.tolerance: none — skipping an unconvertible record is silent data loss.

Five reasons NOT to use CDC

1. No replication privileges (common) → trigger-based or batch
2. Small enough that a full load is simpler (common)
3. You need cross-table transactional consistency
4. Nobody can operate it — the second operator is not optional here
5. The source is not a database

Two assertions worth stealing

-- Assert the SHAPE of events, not just their arrival.
SELECT COUNT(*) FROM bronze.customers_cdc
 WHERE op = 'u' AND NOT (before ? 'region' AND before ? 'segment');
-- expect 0

-- What fields ACTUALLY appear in before images?
SELECT jsonb_object_keys(before), COUNT(*) FROM bronze.customers_cdc
 WHERE op = 'u' GROUP BY 1 ORDER BY 2 DESC;

A fallback that produces a plausible answer hides its cause forever. When you write one, ask whether it is plausible — and if it is, raise instead, with a message that names the fix.