Data engineering
Part 2 of 5 · CDC & DebeziumWAL Tailing vs Query-Based CDC — Log vs Poll Tradeoffs
Poll CDC reads a watermark column and misses hard deletes. Log CDC reads commit order from WAL or binlog and keeps a replication slot until the consumer confirms. Pick the log when you can operate it.
- 1Gist
- 2Maps
- 3Q&A
- 4Sandbox
Voice readout needs Web Speech Synthesis in this browser.
Two ways to notice a change
Prefer
Tail the commit log
WAL, binlog, or redo already ordered the change. The connector decodes inserts, updates, and deletes and confirms a position so the server can release log.
- Hard deletes are explicit records.
- Order follows commit, LSN, or GTID.
- The primary is not answering a continuous catch-up SELECT.
Alternative
Poll updated_at
A high-water mark and a periodic SELECT. Portable, and wrong for deletes, clocks, and hot tables unless you have already accepted those limits.
- A deleted row is gone, so the poll never sees it.
- Equal timestamps need a unique tie-break.
- Lag is at least the poll interval, plus query time.
Overview
“How would you sync Postgres to Elasticsearch?” If you say “poll updated_at,” the follow-ups are deletes, clock skew, and primary load. If you say “tail the WAL,” the follow-ups are slots, wal_level, and decoding plugins. This page is that comparison.
It is not a storage-engine lesson. How a checkpoint truncates the log, how REDO replays after a crash, and how a torn page is repaired stay in Write-Ahead Log — Durability, Checkpoints & Crash Recovery and Database Storage Engines — WAL, B-Trees & LSM Trees. Here you only need the log as a change feed.
The two strategies
| Query-based poll | Log-based tail | |
|---|---|---|
| Source of changes | Application timestamps or version columns | Database recovery log |
| Deletes | Often invisible unless you soft-delete | Explicit delete events |
| Ordering | Approximate (clock, then poll batch) | Commit, LSN, or binlog order |
| Primary load | Continuous SELECT pressure | Decode overhead, usually lighter |
| Latency | Poll interval plus query time | Near commit latency |
| Portability | Works almost anywhere | Needs logical decoding or binlog |
| Ops risk | Missed rows, hot tables | Slot retention and disk fill |
Query-based CDC
- The connector stores a high-water mark (
updated_at, id, version). - On a timer:
SELECTrows past the mark, ordered by the watermark columns, with a limit. - Emit the rows, advance the mark, sleep.
Why people pick it. The mental model is small. You need SELECT, not replication privileges. Some managed databases block log access.
Why it hurts.
- A hard delete removes the row. Nothing remains to select unless you soft-delete or keep a shadow table of keys.
- Two rows can share
updated_at. Without a unique tie-break (updated_at,id), a batch boundary drops or repeats rows. - A long transaction hides uncommitted rows until commit. A watermark that only looks at wall-clock time can still skip or double-see them depending on when you read.
- You need an index on the watermark. Under lag the poll becomes a hot query on the primary.
- Lag jitter is the poll interval. You cannot get “near commit” by wishing.
Log-based CDC (Postgres lens)
- Set
wal_level = logical. Create a publication and a replication slot. - The connector streams from the slot through a decoding plugin (
pgoutput,wal2json, or the Debezium decoder). - Decode insert, update, and delete with before and after images. Replica identity controls how much of the old row is in the WAL for updates and deletes: default primary key,
FULL, an index, orNOTHING. - The connector confirms a flush LSN. The slot retains WAL until that confirm.
Why it wins. Commit order. Deletes are events. The primary is not serving the catch-up query. A consistent snapshot can hand off to streaming; that handshake is the next lesson.
Why it can take the primary down. An idle or dead consumer does not confirm the LSN. The slot pins WAL. Disk fills. That is an operations problem, not a reason to pretend poll is equivalent. Heartbeats and slot alerts are in the Debezium and failure-mode lessons.
REPLICA IDENTITY FULL puts the whole old row in update and delete records. Use it when consumers need the before-image. Pay for it in WAL volume.
Flow
- 1
1. App COMMITs an update
- next2. Slot retains the WAL record
- 2
2. Slot retains the WAL record
- next3. Connector reads the next LSN
- 3
3. Connector reads the next LSN
- next4. Decode insert update or delete
- 4
4. Decode insert update or delete
- next5. Publish the change event
- 5
5. Publish the change event
- next6. Confirm the flush LSN
- 6
6. Confirm the flush LSN
Lesson map
WAL Tailing vs Query-Based CDC — Log vs Poll Tradeoffs
Poll CDC reads a watermark column and misses hard deletes. Log CDC reads commit order from WAL or binlog and keeps a replication slot until the consumer confirms. Pick the log when you can operate it.
Architecture. Architecture
Select a node to see why it exists, or an edge to see the protocol, direction, effect, and consequence.
Mermaid export
flowchart TB a["1. App COMMITs an update"] b["2. Slot retains the WAL record"] c["3. Connector reads the next LSN"] d["4. Decode insert update or delete"] a -->|1. App COMMITs an update| b b -->|2. Slot retains the WAL record| c c -->|3. Connector reads the next LSN| d
Failure paths you should say out loud
Walk the poll holes, then the log holes, then the runbook you would write.
Flow
- 1
1. Pick a capture strategy
- next2. Query poll
- 2
2. Query poll
- next3. Miss hard deletes
- 3
3. Miss hard deletes
- next4. Hot SELECT on the primary
- 4
4. Hot SELECT on the primary
- next5. Clock or updated_at skew
- 5
5. Clock or updated_at skew
- next6. WAL or binlog tail
- 6
6. WAL or binlog tail
- next7. Slot lag fills the disk
- 7
7. Slot lag fills the disk
- next8. Replica identity too thin
- 8
8. Replica identity too thin
- next9. Plugin or privilege gap
- 9
9. Plugin or privilege gap
- next10. Choose and write the runbook
- 10
10. Choose and write the runbook
When to pick which
- Default. Log-based, if you control privileges on Postgres or MySQL and you will operate slot or binlog retention.
- Poll. A legacy or SaaS database with no logical replication. A one-shot migration that only soft-deletes. A tiny table where the poll cost is boring.
- Hybrid. Bulk extract or dump first, then cut over to the log at a consistent LSN or GTID. That is the Debezium snapshot model, next page.
Do not use poll as a permanent design for a high-churn orders table and call it CDC.
MySQL and SQL Server, briefly
- MySQL. Row-based binlog plus a file position or GTID is the analogue of an LSN. Statement-based binlog does not carry reliable row images. CDC wants row format.
- SQL Server. CDC and change tracking are different APIs. The interview point is the same: log versus poll, ordering, deletes, load, retention.
Memorize properties, not every vendor knob.
Cursors (run this)
The poll cursor compares (updated_at, id) so two rows at the same timestamp both leave the batch. The log cursor compares LSN and therefore sees a delete that no longer exists as a row.
Press Run. Snippets must be self-contained — no network, files, or native modules.
Press Run. Snippets must be self-contained — no network, files, or native modules.
Cutover sketch
You already poll orders. You want the log.
- Prove the poll’s holes: hard deletes, tie-breaks, index load.
- Enable logical WAL, publication, and a slot in a maintenance window. Watch disk.
- Snapshot the table at a consistent point and record the start LSN. Streaming begins there. Details of
op=rversusc/u/dare the Debezium lesson. - Run the poll sink and the log sink into a shadow index. Compare counts and a sample of primary keys, including deletes.
- Swap the alias or the reader. Keep the slot healthy. Do not pause the connector “for a few days.”
Interview Q&A
Why can poll CDC miss deletes?
Answer
A hard delete removes the row. A later SELECT has nothing to return. You only observe the delete if the application soft-deletes, or if you keep a side table of removed keys. The log records the delete explicitly.
What is a replication slot?
Answer
A Postgres bookmark. It retains WAL until the consumer confirms an LSN, which is what lets logical decoding resume. If nobody confirms, WAL piles up on the primary.
Why wal_level=logical?
Answer
Logical decoding and publications need it. Replica and minimal levels are for physical standby and crash recovery, not for a row-level CDC stream. Recovery mechanics stay on the WAL durability lesson.
What is replica identity?
Answer
It controls how much of the old row is written into UPDATE and DELETE WAL. Default is the primary key. FULL logs the whole old row. NOTHING can make the before-image useless to consumers. An index identity is the middle ground.
Is poll always worse?
Answer
No. Use it when you cannot read the log, or when the table is tiny and only soft-deletes. Say the limits in the same sentence as the choice.
How do you bound poll lag?
Answer
A short interval, a bounded batch, an index on the watermark columns, a unique tie-break, and an alert on the age of the mark. You still will not see hard deletes.
Statement binlog versus row binlog?
Answer
Statement format logs the SQL, not the row images. CDC needs row format (or mixed, handled carefully). Position or GTID is the resume token, analogous to an LSN.
How does the initial load work?
Answer
A consistent snapshot, then stream from the logged start LSN or GTID. Duplicates at the boundary are acceptable if the sink is idempotent. Gaps are not. The snapshot protocol is the next lesson.
What goes wrong if you create a slot and pause the connector?
Answer
The slot keeps every WAL segment the consumer has not confirmed. A quiet connector over a weekend can fill the primary disk. That is the first CDC page-out, and it is unrelated to checkpoint tuning.
Pitfalls
Sketch the current poll query, the missing delete, and the index. Then sketch publication, slot, and the LSN you would cut over on. Write the one alert that pages before disk fills.