Change Data Capture Pattern
Change data capture streams every row mutation as an event by reading the transaction log, so downstream systems see changes in order without polling the source table.
Definition
Change data capture (CDC) is the pattern of reading the database transaction log to extract every row change as an ordered event stream, so that downstream consumers — search indexes, caches, warehouses — receive changes in commit order without a trigger or a polling query against the source table.
Context and scope
Applied in real-time data pipelines, cache invalidation, search index sync, and any integration where polling is too slow and triggers too invasive.
Common misconceptions
- CDC is just database triggers — triggers fire in the transaction; CDC reads the log after commit, avoiding the trigger overhead and the transaction-length coupling
- CDC guarantees exactly-once — it guarantees order and at-least-once; exactly-once requires idempotent consumers or a transactional outbox
Anti-patterns
- Polling the source table for changes with a timestamp column, missing updates that happen within the poll interval and deletes entirely
- Reading the transaction log without handling log rotation, so the consumer breaks when the source rotates and loses history
Related concepts
Authoritative source
What it is
Change data capture (CDC) is the pattern of reading the database transaction log to extract every row change as an ordered event stream, so that downstream consumers — search indexes, caches, warehouses — receive changes in commit order without a trigger or a polling query against the source table. Applied in real-time data pipelines, cache invalidation, search index sync, and any integration where polling is too slow and triggers too invasive. The data model and the access patterns are co-designed: a schema that is correct in the relational sense can be catastrophic for the queries the workload actually issues.
How it works
Indexing decisions follow the query shapes, not the column importance: a covering index turns a table scan into an index-only scan, but every index adds write amplification and storage cost. Partitioning aligns with the access dimension so a query touches one partition, not all. Transaction scope is minimized to hold locks briefly; long transactions are the root of contention and deadlock.
When to apply and pitfalls
Apply when data volume or query latency has a concrete business cost. The common pitfall is indexing for the current workload and ignoring the access patterns that growth will introduce — the index that helps today can serialize writes tomorrow. Another is treating the database as a black box, missing that the optimizer’s plan choice depends on statistics that drift as data changes.