Change Data Capture Fundamentals: Reliably Capturing Data Changes

1. What Change Data Capture actually solves

Change Data Capture (CDC for short) describes a group of techniques for reliably capturing every change to database rows, meaning INSERT, UPDATE and DELETE, and forwarding it to downstream systems. Without CDC, downstream systems either have to periodically query the entire source table (polling) or application code has to explicitly emit events whenever a change happens. Both approaches have weaknesses: polling misses fast sequences of changes, and application code easily forgets individual code paths where a change happens without emitting an event.

Change Data Capture solves this problem at the source: changes are captured exactly where they are guaranteed to be fully visible, either in the database transaction log or via triggers that fire on every write operation. The following sections show why log based CDC is the most robust variant, how trigger based CDC works as an alternative and how the outbox pattern solves consistency problems between a database write and event dispatch.

2. Why polling hits its limits

The most obvious approach to capturing changes is periodic polling: a query against the source table every few seconds, filtered on an updated_at timestamp. That works for many cases but has two structural weaknesses. First, a plain timestamp filter does not detect deleted rows, because a deleted row simply no longer exists and no longer shows up in the query. Second, polling misses intermediate states: if a row is changed twice within a single polling interval, the downstream system only sees the last state, not the intermediate change.

For many reporting use cases that is acceptable, but not for audit trails, real time synchronization or event driven architectures. Change Data Capture solves exactly this problem by capturing every single change, in the order it actually happened, including deleted rows and intermediate states. Moving from polling to CDC is usually the point where data pipelines shift from batch like to event driven.

3. Log based CDC: the transaction log as a source

Log based Change Data Capture reads directly from the write ahead log (PostgreSQL) or the binary log (MySQL) of the database. Every database writes every change into this log anyway, before applying it to the actual data pages, in order to enable crash recovery. CDC tools such as Debezium attach as a logical replication client to this log and read changes without generating additional read load on the tables themselves.

This approach is the most performant, because it barely adds any load to the source database: the log gets written anyway, CDC merely reads along with it. In PostgreSQL, you set up a logical replication slot for this, which guarantees that no log entries get deleted before the CDC consumer has read them. Log based Change Data Capture automatically captures DELETE operations as well as the exact order of all changes, something that requires extra effort with trigger or polling based approaches.

-- PostgreSQL: create a logical replication slot for CDC
SELECT pg_create_logical_replication_slot(
  'cdc_orders_slot',
  'pgoutput'
);

-- Publication defines which tables are captured
CREATE PUBLICATION cdc_orders_publication
FOR TABLE orders, order_items;

-- Inspect the current replication lag of a slot
SELECT slot_name, active, confirmed_flush_lsn,
       pg_current_wal_lsn() - confirmed_flush_lsn AS lag_bytes
FROM pg_replication_slots
WHERE slot_name = 'cdc_orders_slot';

4. Trigger based CDC: logging changes yourself

Not every database or operational setup allows access to the transaction log, for instance in heavily restricted cloud database instances. In these cases, trigger based Change Data Capture is the alternative: a trigger on INSERT, UPDATE and DELETE writes a row into a separate change log table on every change. Downstream systems then read from this log table instead of the transaction log.

The downside of this Change Data Capture approach is the additional write overhead: every write to the source table triggers an additional write to the log table, within the same transaction. At very high write throughput that can become noticeable. The upside is independence from database internal replication mechanisms and easy portability across different database engines, since triggers and the log table are plain SQL.

-- Trigger-based CDC: log every change to a dedicated audit table
CREATE TABLE orders_changelog (
  changelog_id BIGSERIAL PRIMARY KEY,
  order_id INT NOT NULL,
  operation CHAR(1) NOT NULL, -- I, U, D
  changed_at TIMESTAMP NOT NULL DEFAULT NOW(),
  old_data JSONB,
  new_data JSONB
);

CREATE OR REPLACE FUNCTION log_order_change() RETURNS TRIGGER AS $$
BEGIN
  IF TG_OP = 'DELETE' THEN
    INSERT INTO orders_changelog (order_id, operation, old_data)
    VALUES (OLD.order_id, 'D', to_jsonb(OLD));
    RETURN OLD;
  ELSE
    INSERT INTO orders_changelog (order_id, operation, old_data, new_data)
    VALUES (NEW.order_id, LEFT(TG_OP, 1), to_jsonb(OLD), to_jsonb(NEW));
    RETURN NEW;
  END IF;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER orders_cdc_trigger
AFTER INSERT OR UPDATE OR DELETE ON orders
FOR EACH ROW EXECUTE FUNCTION log_order_change();

5. The outbox pattern for consistent events

A common problem when sending events after a database change: the application writes the change to the database and then separately sends an event to a message queue. If sending the event fails even though the database transaction succeeded, an inconsistency arises, known as the dual write problem. The outbox pattern, a specialized form of Change Data Capture, solves this problem.

With the outbox pattern, the application writes the event to be dispatched into the same transaction as the actual data change, into a separate outbox table. A CDC process then reads from this outbox table and reliably dispatches the events to the message queue, independent of the original transaction context. Because the data change and the outbox entry commit in the same transaction, the dual write problem is structurally excluded.

-- Outbox pattern: write the business change and the event
-- to be published within the same transaction
BEGIN;

UPDATE orders SET status = 'shipped' WHERE order_id = 1001;

INSERT INTO outbox_events (aggregate_type, aggregate_id, event_type, payload, created_at)
VALUES (
  'order',
  1001,
  'OrderShipped',
  '{"order_id": 1001, "status": "shipped"}'::jsonb,
  NOW()
);

COMMIT;

-- CDC reads from outbox_events and publishes to the message queue,
-- then marks the row as processed
UPDATE outbox_events SET processed_at = NOW()
WHERE event_id = 4821 AND processed_at IS NULL;

6. Schema evolution and CDC contracts

An often underestimated problem with Change Data Capture: if the source table's schema changes, for instance through a new column or a changed data type definition, every CDC consumer has to tolerate that change. A CDC consumer that expects a fixed schema breaks on a schema migration if it is not backed by a schema registry mechanism.

Proven practice is an explicit CDC schema contract: new columns are introduced only additively, existing columns are not renamed or dropped but marked deprecated and removed later. Tools such as Debezium support schema registries that inform consumers about changes before they go live, preventing a CDC consumer from failing unexpectedly.

-- Additive schema change: safe for existing CDC consumers
ALTER TABLE orders ADD COLUMN loyalty_points INT DEFAULT 0;

-- Deprecating a column instead of dropping it immediately
COMMENT ON COLUMN orders.legacy_status IS
  'Deprecated since 2026-07-30, use status_code instead. Remove after Q4.';

-- Consumers can check for column presence before relying on it
SELECT column_name
FROM information_schema.columns
WHERE table_name = 'orders' AND column_name = 'loyalty_points';

7. Consistency guarantees: at least once vs. exactly once

Change Data Capture delivers at least once semantics in most real world implementations: an event is guaranteed to be delivered at least once, but can also arrive multiple times after a failure and subsequent retry. Exactly once semantics, where an event is guaranteed to arrive exactly once, is technically far more involved and requires idempotent consumers or transactional guarantees across system boundaries.

In practice this problem is solved by designing consumers to be idempotent instead of enforcing exactly once at the infrastructure level. Every CDC event carries a unique ID, and the consumer checks before processing whether that ID has already been handled. This combination of at least once delivery and idempotent processing achieves effectively the same result as exactly once, without its infrastructure complexity.

8. Running CDC in production: monitoring and error handling

A production Change Data Capture process needs monitoring for replication lag, meaning the delay between a database change and its arrival at the consumer. If this lag grows uncontrollably, it usually indicates an overloaded consumer or a problem with the CDC tool that must be fixed urgently, before the replication slot grows large enough to burden the source database.

A common operational problem: a logical replication slot in PostgreSQL whose consumer has stopped prevents old write ahead log segments from being deleted. This continuously fills up disk space until the database itself runs into trouble. Alerting on slot size is therefore a mandatory part of every Change Data Capture setup, not just a nice to have.

-- Monitor replication slot size to catch a stuck consumer early
SELECT
  slot_name,
  active,
  pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal
FROM pg_replication_slots
ORDER BY pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) DESC;

-- Alert threshold example: flag slots retaining more than 5 GB of WAL
SELECT slot_name
FROM pg_replication_slots
WHERE pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) > 5 * 1024 * 1024 * 1024;

9. CDC approaches compared

The three central approaches to Change Data Capture differ significantly in effort, performance impact and accuracy.

Log based Change Data Capture is the gold standard for performance and completeness, but requires infrastructure such as Debezium and Kafka Connect. Trigger based CDC fits when there is no access to replication mechanisms. The outbox pattern is the right choice when the goal is not to capture arbitrary table changes but to exchange specifically defined business events between services.

10. Summary

Change Data Capture replaces periodic polling with reliable capture of every single data change, including deleted rows and intermediate states. Log based CDC reads directly from the transaction log and generates minimal additional load on the source database. Trigger based CDC is the alternative when there is no access to replication mechanisms. The outbox pattern additionally solves the dual write problem between a database transaction and event dispatch.

Anyone running Change Data Capture in production needs to actively monitor replication lag and slot size and design consumers to be idempotent in order to safely handle at least once delivery. This combination of the right CDC approach, schema contracts and monitoring makes event driven architectures robust enough for continuous production operation.

11. FAQ: Change Data Capture Fundamentals