Change Data Capture for Feeding an AI Pipeline

A pipeline that re-reads a whole table every hour is doing two things badly: it is expensive in proportion to the table, and it is stale in proportion to the interval. Change data capture replaces both with a stream of the rows that actually changed — and the way you obtain that stream determines what it can and cannot tell you.

Why a pipeline needs changes, not snapshots

The concrete case: a search index or embedding store built from a products table. A full rebuild reads 40 million rows to find the 12,000 that changed, and the cost of the rebuild scales with the table while the useful work scales with the churn. Worse, the interval between rebuilds is the index’s staleness, so making it fresher makes it quadratically more expensive in aggregate.

CDC inverts that: cost scales with churn, and staleness is bounded by transport latency rather than by a schedule. For anything that keeps a derived store in sync — a vector index, a denormalised read model, a feature table — this is the right shape, and the freshness question it answers is the same one framed from the retrieval side in index freshness and index updates.

The two mechanisms

Query-based CDC polls. It keeps a high-water mark — usually an updated_at column or a monotonic version — and periodically asks for rows above it. It requires no database privileges beyond reading, works on any engine, and is easy to understand, which is why almost everyone starts here.

Log-based CDC reads the database’s own write-ahead log: PostgreSQL’s logical decoding, described in the PostgreSQL documentation, or MySQL’s binary log in ROW format, described in the MySQL reference manual. Debezium is the common connector implementation across both; its PostgreSQL connector documentation is the primary reference for the configuration names below. The log already exists — the database writes it for durability and replication — so reading it adds no query load, and it contains every change in commit order with before and after images.

Three things query-based CDC silently loses, and they are the reason the extra setup for log-based is usually worth it. Deletes: a deleted row is simply absent from the next poll, and absence is not observable in a query that returns rows above a watermark, so deletes require soft-delete columns that the application must remember to use. Intermediate states: a row updated three times between polls yields one event with the final value, so any consumer that cares about transitions — a state machine, an audit trail, a feature counting status changes — sees one where three occurred. Rows whose watermark did not move: any write path that updates a row without touching updated_at, including a bulk migration or a trigger-free admin fix, is invisible forever.

A worked latency comparison

Assumptions, stated so you can substitute your own: a 5-minute poll interval, a poll query that takes 8 seconds against an indexed watermark column, a WAL flush and decode path of about 200 ms, and a message broker adding about 300 ms end to end.

query-based, 5-minute poll

  best case   change lands just before a poll starts
              latency = query time                        =   8 s
  worst case  change lands just after a poll starts
              latency = 300 s + 8 s                       = 308 s
  mean        interval/2 + query time = 150 + 8           = 158 s

  cost per hour: 12 polls x full index scan of the watermark range

log-based

  commit -> WAL flush -> decode                           = 0.2 s
  decode -> broker -> consumer                            = 0.3 s
  mean latency                                            = 0.5 s
  worst case bounded by broker backlog, not by a schedule

  ratio of means: 158 / 0.5 = 316x

Reducing the poll interval does not close that gap cheaply. At a 30-second interval the mean falls to 23 seconds — still 46 times worse — while the query rate rises tenfold, and each of those queries competes with production traffic. That is the real argument: the polling approach trades database load against freshness on a curve that gets bad quickly, and log-based CDC is not on that curve at all.

Four operational hazards

• The replication slot is a disk-space bomb. A PostgreSQL logical replication slot causes the server to retain WAL segments until the consumer confirms it has read them. A consumer that stops — crashed, or paused for a deploy, or blocked on a downstream outage — makes WAL accumulate indefinitely until the data directory fills and the database refuses writes. This is the single most common way a CDC deployment causes an outage in the system it was reading. Monitor slot lag in bytes with an alert well below the available disk, and have a documented decision about when to drop the slot and re-snapshot.
• The initial snapshot. A new connector must first read the existing table, then switch to the log at a consistent point. On a large table that snapshot can take hours, during which the slot is retaining WAL, and the two phases have to be stitched without gaps or duplicates. Incremental snapshotting — chunking the table and interleaving chunks with live changes — exists precisely because the naive version blocks.
• Schema changes. A column added upstream appears in the stream mid-flight, and consumers with a fixed schema either ignore it or fail. A column dropped or retyped is worse, because historical messages in the topic still carry the old shape. A schema registry with a compatibility mode set deliberately — backward compatibility means new readers can read old data, which is usually what a replay needs — is not optional at any scale.
• Ordering is per key, not global. CDC events are usually partitioned by primary key, so two changes to one row are ordered and two changes to different rows are not. Any consumer logic that depends on cross-row ordering — a parent row existing before its child arrives — must handle out-of-order arrival explicitly, as described in stream processing architectures.

What the downstream consumer must handle

A CDC event carries an operation — create, update, delete, and usually a distinct read operation for snapshot rows — plus before and after images. A consumer maintaining a derived store must be idempotent, because at-least-once delivery means it will see some events twice, and the natural way to get that is to make every write an upsert keyed on the primary key with a version guard: apply only if the incoming log position is newer than the one stored.

Deletes deserve specific attention. Log-based connectors typically emit a delete event followed by a tombstone — a message with the key and a null value — so that a compacted topic eventually forgets the key entirely. A consumer that ignores null values will keep deleted records forever in its derived store, which for a vector index means deleted products keep being returned by search, and for anything covered by a deletion request is a compliance problem rather than a bug.

Finally, decide what a change means for expensive downstream work. If every update triggers a re-embedding, a bulk price update touches every row and costs a full rebuild anyway. Compare the before and after images on the fields that actually feed the derived artefact, and skip the work when they are unchanged — the event stream gives you both images specifically so that this comparison is possible, and it is the cheapest optimisation available in a CDC-fed pipeline.