Log-Based vs Query-Based Capture

## 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.