What is the difference between log-based and query-based change data capture?
answer
- two ways: ask the table, or read the log
- one sees state, the other sees history
- polling cannot report what no longer exists
- the log already exists for replicas
- cost moves from query load to operations
basics
~20 sLog-based capture reads the database's own replication log, so it sees every committed insert, update and delete in commit order. Query-based capture repeatedly polls the tables, so it only sees rows that still exist at poll time.
solid answer
~50 sQuery-based capture runs a scheduled query such as `SELECT * FROM orders WHERE updated_at > :last_seen` and remembers the highest marker it saw. It needs nothing from the database but a read-only login and a suitable column, so it works against almost any source — but it reads *current state*, not history: hard deletes never come back in a query, several updates between polls collapse into the newest version, and every poll costs the source a scan. Log-based capture attaches to the sequential change log the engine already writes for replication — the MySQL binary log, a Postgres logical replication slot, the SQL Server transaction log — and emits one event per committed change, with an operation type, before/after images and a log position. It sees deletes, sees every intermediate version, and adds almost no query load. Its costs are operational: elevated privileges, source configuration, and a log position that must keep advancing or the server retains log files.
code
sql · 5 lines-- query-based: state at poll time, filtered by a marker column
SELECT order_id, status, total, updated_at
FROM orders
WHERE updated_at > :last_seen
ORDER BY updated_at;go deeper
Be able to state the fork in one breath: polling asks the table what looks new, log-based capture reads the database's replication log. Then name the headline consequence — polling cannot see a deleted row.
Explain the mechanics: which column a poll filters on, what a log record contains (operation, before, after, position), and why intermediate updates collapse under polling but not under log reading.
Show that you have paid the log-based bill: privileges and source settings, a consumer position that must keep advancing, retained log files, and the fact that the capture stream is only as correct as the sink applying it.
Own the choice as a platform decision — which sources can even support log-based capture, what freshness and delete-accuracy you are prepared to promise per source, and who carries the operational burden of the slots and log positions.
## The problem both approaches solve Something downstream — a warehouse, a search index, a cache, another service — needs to know when rows in a source database change. The source database is the authority; the only question is how you find out. There are exactly two families of answer: ask the source repeatedly what looks new (query-based, or polling), or read the log the source already writes as it commits (log-based). ## Query-based capture Query-based capture runs a scheduled query against the source tables and keeps a marker of how far it got. The usual marker is a monotonic column — an `updated_at` timestamp, a `version` counter, or an auto-increment id for append-only tables: ```sql SELECT * FROM orders WHERE updated_at > :last_seen ORDER BY updated_at; ``` The extractor stores the highest value it saw and asks again on the next tick. Nothing special is required of the database: a read-only login and a suitable column. That is why polling works against a managed service you cannot reconfigure, a read replica, a view, or a vendor system that only exposes a REST endpoint. ## What query-based capture structurally cannot see Polling reads *state*, not *history*, and three consequences fall straight out of that: - **Hard deletes are invisible.** A row that has been `DELETE`d matches no query, so nothing ever tells the extractor it is gone; the downstream copy keeps it forever. Only a soft delete — a `deleted_at` flag the application sets instead of deleting — makes deletes pollable, and that is an application design decision the pipeline does not control. - **Intermediate versions collapse.** If a row is updated five times between two polls, the poll returns the fifth version. For a warehouse fact that is often fine; for an audit trail or anything that reacts to a specific transition ("the order passed through CANCELLED"), the missing states are lost data. - **The marker column must be trustworthy.** A write path that forgets to touch `updated_at`, or a bulk update that stamps it from a stale application clock, produces rows the extractor never picks up. ## Log-based capture Every durable relational engine already writes a sequential record of committed changes so replicas can follow it: the write-ahead log in Postgres, surfaced through logical decoding on a replication slot; the binary log in MySQL; the transaction log in SQL Server; redo and archive logs in Oracle. Log-based CDC registers as a consumer of that stream and turns each record into a change event — operation type, the row before, the row after, and a position (an LSN, a GTID, an SCN) identifying where in the log it came from. Because it reads the log, it sees what the database actually committed: deletes appear as delete events, every intermediate update appears as its own event, and events arrive in commit order. It also imposes almost no query load — the log is written anyway, and reading it sequentially does not scan tables, take table locks, or evict the application's working set from the buffer pool. ## What log-based capture costs The cost does not disappear; it moves from query load to operations. - **Source configuration and privileges.** Logical WAL level and a replication role in Postgres; row-based binary logging with full row images in MySQL; supplemental logging in Oracle. Some vendor-controlled databases grant none of it. - **Retained log.** A consumer position that stops advancing forces the server to keep log files it would otherwise recycle. An abandoned Postgres replication slot filling the primary's disk is the classic CDC outage. - **Before-image completeness.** What a delete or update event carries about the *old* row is decided by source settings, not by the capture tool. - **Schema and type fidelity.** The log speaks the engine's physical types and DDL, so the pipeline must keep interpreting them as the source schema evolves. ## Latency and load Polling's latency floor is the poll interval, and shortening it multiplies scan cost: a one-minute interval scans the table 1,440 times a day whether or not anything changed. Log-based capture is continuous, so latency is bounded by how fast the reader drains the log — typically sub-second — and its cost does not grow when you want fresher data. ## When query-based is still the right call If the source is a third party you cannot reconfigure, if the table is append-only and small, if hourly freshness is enough, or if nobody will own a replication slot at 3 a.m., a polling extract is the honest engineering choice. The failure to avoid is choosing polling and then quietly promising delete-accurate replication downstream.
- At one-minute freshness, which approach costs the source database more, and why?Polling, by a wide margin. Each tick runs a filtered scan against live tables — 1,440 scans a day per table whether or not anything changed — competing with the application for I/O and buffer pool. Log-based capture reads a sequential log the engine writes anyway, so halving latency does not double its cost.
- What must a source database expose before log-based capture is even possible?A readable, row-level change log and the privilege to consume it: logical-level WAL plus a replication role in Postgres, row-based binary logging in MySQL, supplemental logging in Oracle. Managed and vendor-hosted databases frequently withhold one of these, which is the usual reason a team ends up polling.
- Does log-based capture guarantee the downstream sink is never wrong?No. It guarantees the *stream* contains every committed change in commit order; correctness downstream still depends on the sink applying events idempotently, on the capture position surviving restarts, and on the initial state being loaded consistently before streaming begins.
Polling is walking the shop's shelves every hour and inferring what changed; log-based capture is reading the till receipts as they print.
saying these in an interview costs you the question
- Claiming polling catches deletes if you poll often enough
- Saying log-based CDC has no cost to the source
- Treating a timestamp column as guaranteed monotonic and always set
- Describing log-based capture as running queries against the tables
- Assuming every managed database allows a replication slot