Change Data Capture: Tracking Database Changes in Real Time

Introduction

Your application writes to a PostgreSQL database. Somewhere downstream, an analytics system needs to know what changed. A search index needs to update. A cache needs to invalidate. A data warehouse needs to load.

You could poll the database every few seconds, but that is expensive at scale and adds latency. You could modify your application to write to a message queue alongside the database, but that couples your application logic to your data pipeline. Change Data Capture (CDC) solves this by reading the database’s transaction log and streaming changes as they happen.

CDC captures inserts, updates, and deletes from database tables and publishes them as events to a message broker or streaming platform. The source database is untouched. The application is unmodified. Changes flow automatically. This guide compares Debezium and trigger-based capture, then covers event structure, snapshots, capacity and operating costs, and failure recovery.

How CDC Works

Most CDC implementations read the database’s write-ahead log (WAL) or transaction log. This log records every modification made to the database at the storage level. CDC tools tail this log and translate log entries into events.

flowchart LR
    App[Application] -->|writes| DB[(PostgreSQL)]
    DB -->|WAL| CDC[CDC Agent]
    CDC -->|events| Kafka[Kafka / Message Broker]
    Kafka --> Consumer1[Search Index]
    Kafka --> Consumer2[Cache]
    Kafka --> Consumer3[Data Warehouse]

The key advantage of log-based CDC is that it captures every change without touching the source tables. There is no polling, no added load on the source database, and no application code changes.

Debezium: The Open-Source CDC Platform

Debezium is the most widely-used CDC platform for the JVM ecosystem. It reads transaction logs from MySQL, PostgreSQL, MongoDB, and other databases, converting changes into events that publish to Kafka.

import io.debezium.config.Configuration;
import io.debezium.engine.DebeziumEngine;
import io.debezium.engine.format.ChangeEventFormat;

public class MySqlCdcExample {
    public static void main(String[] args) throws Exception {
        Configuration config = Configuration.create()
            .with("name", "mysql-cdc-connector")
            .with("connector.class", "MySqlConnector")
            .with("database.hostname", "localhost")
            .with("database.port", 3306)
            .with("database.user", "debezium")
            .with("database.password", "password")
            .with("database.server.id", "184054")
            .with("topic.prefix", "mysql-cdc")
            .with("schema.history.internal.kafka.bootstrap.servers", "localhost:9092")
            .with("schema.history.internal.kafka.topic", "schema-changes.inventory")
            .build();

        DebeziumEngine<ChangeEvent<String, String>> engine = DebeziumEngine.create(ChangeEventFormat.of(Connect.class))
            .using(config.asProperties())
            .notifying(record -> {
                System.out.println("Key: " + record.key());
                System.out.println("Value: " + record.value());
            })
            .build();
    }
}

Debezium handles the messy details: reading the correct position in the WAL, managing schema evolution, handling snapshots of existing data, and publishing events with consistent structure.

CDC Event Structure

A CDC event contains the before and after state of a changed row. The event envelope includes metadata:

{
  "op": "u",
  "ts_ms": 1711523456789,
  "before": {
    "id": 12345,
    "email": "alice@example.com",
    "loyalty_tier": "silver"
  },
  "after": {
    "id": 12345,
    "email": "alice@example.com",
    "loyalty_tier": "gold"
  },
  "source": {
    "db": "orders_db",
    "table": "customers",
    "lsn": 12345678
  }
}

The op field indicates the operation type: c for create (insert), u for update, d for delete, and r for read (snapshot). The before and after fields capture the row state before and after the change.

Snapshotting: Initial Load Problem

CDC tools need to know where to start reading the log. For a brand new source, the log beginning is fine. For an existing source with months of data, you need a snapshot of current data before CDC can begin streaming incremental changes.

Most CDC tools support snapshot modes:

• initial: Snapshot the database on first run, then stream changes
• schema_only: Skip snapshot, only stream changes from now on (loses existing data)
• when_needed: Snapshot when the offset is lost and cannot be recovered

Initial snapshots can be large. A table with 500 million rows takes time and resources to snapshot. Plan for this when setting up CDC for the first time on a large database.

Trigger-Based CDC

Before log-based CDC was practical, trigger-based CDC was the standard approach. You create database triggers on source tables that fire on INSERT, UPDATE, and DELETE operations, writing change records to a staging table.

CREATE TABLE customer_changes (
    change_id BIGSERIAL PRIMARY KEY,
    operation VARCHAR(1),
    customer_id BIGINT,
    email VARCHAR(255),
    loyalty_tier VARCHAR(20),
    changed_at TIMESTAMP DEFAULT NOW()
);

CREATE OR REPLACE FUNCTION capture_customer_changes()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        INSERT INTO customer_changes (operation, customer_id, email, loyalty_tier)
        VALUES ('I', NEW.id, NEW.email, NEW.loyalty_tier);
        RETURN NEW;
    ELSIF TG_OP = 'UPDATE' THEN
        INSERT INTO customer_changes (operation, customer_id, email, loyalty_tier)
        VALUES ('U', NEW.id, NEW.email, NEW.loyalty_tier);
        RETURN NEW;
    ELSIF TG_OP = 'DELETE' THEN
        INSERT INTO customer_changes (operation, customer_id, email, loyalty_tier)
        VALUES ('D', OLD.id, OLD.email, OLD.loyalty_tier);
        RETURN OLD;
    END IF;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_customer_cdc
AFTER INSERT OR UPDATE OR DELETE ON customers
FOR EACH ROW EXECUTE FUNCTION capture_customer_changes();

Trigger-based CDC adds overhead to every write operation on the source tables. For write-heavy workloads, this overhead can be significant. Log-based CDC is the preferred approach for this reason.

Trade-Off Table

Approach	Source database impact	Change capture and latency	Operational cost	Main limitation
Log-based CDC	Reads the transaction log; retention settings and replication slots still consume source storage.	Captures committed row changes, often within seconds.	CDC connector, broker, schema history, and log-retention monitoring.	Requires log access and enough retention to recover after downtime; delivery is usually at least once.
Trigger-based CDC	Adds work to each write and stores change rows in source tables.	Captures configured row changes during the write transaction.	Triggers, staging-table cleanup, and a publisher or polling job.	Triggers can increase write latency and may miss changes made through paths that bypass them.
Query polling	Repeatedly reads source tables, often with a timestamp or version filter.	Depends on polling interval; deletes and same-timestamp updates need extra handling.	Scheduler and checkpoint state, with little extra infrastructure.	Adds query load and can miss changes without a reliable cursor or soft-delete strategy.

Choose log-based CDC when the database exposes a supported transaction log and downstream systems need a low-latency stream. Triggers can fit when only a few tables need capture and log access is unavailable, but their write-path cost must be measured. Polling is often simpler for low-volume, periodic syncs where minutes of delay are acceptable.

Ordering Guarantees

CDC events capture database changes in the order they were committed. Within a single table, order is preserved. Across tables in the same database, if they share a transaction, the changes are emitted together.

Cross-database CDC ordering depends on the database’s transaction log behavior. Most log-based CDC tools provide at-least-once delivery. Design your consumers to be idempotent.

Capacity Estimation for CDC

CDC capacity planning comes down to event volume, broker throughput, and consumer processing speed.

Event volume estimation:

A database with 1,000 writes per second and 500-byte rows produces CDC events at roughly 3-5x the raw write volume (before/after states, metadata, envelope). At 1,000 writes/sec, expect 3,000-5,000 CDC events per second.

For a 500-byte average row with full before/after capture:

• 1,000 writes/sec × ~1 KB/event ≈ 1 MB/sec or ~86 GB/day
• A Kafka broker doing 50 MB/sec per broker handles this without breaking a sweat
• Partition count drives parallel consumer throughput — one partition per source table is a reasonable starting point

Snapshot capacity:

An initial snapshot of a 500 million row table at 100,000 rows/sec runs about 80 minutes. While the snapshot runs, CDC keeps capturing WAL changes. The snapshot must finish before the WAL offset it started from gets purged. Set snapshot.lock.timeout.ms appropriately for large tables — PostgreSQL’s wal_keep_segments must retain enough WAL from snapshot start until the snapshot completes.

Sizing the CDC agent:

Debezium runs as a single-threaded connector per source database by default. For high-throughput sources, increase max.batch.size and max.queue.size to buffer bursts. Watch lag, queue utilization, and schema history progress.

When to Use CDC

Use CDC when your source system cannot emit change events directly and you need near-real-time movement without polling. Multiple independent consumers on the same change stream is another good signal — CDC decouples source databases from downstream systems cleanly.

Do not use CDC when your managed cloud database has limited WAL access. Do not use it for simple periodic batch loads where ETL is easier. Do not use it if write latency overhead is unacceptable for sensitive OLTP workloads. And if you need to capture application-level logic changes rather than physical database writes, triggers are the better tool.

Common CDC use cases:

• Data warehouse feeding: Capture changes from OLTP databases and feed them into a data warehouse without touching the source system.
• Search index updates: Keep Elasticsearch or OpenSearch indexes synchronized with database state.
• Microservices data sharing: Instead of sharing databases across services, each service consumes the CDC stream from a shared source.
• Cache invalidation: Update or invalidate caches when source data changes, without application-level cache management.

Common Pitfalls / Anti-Patterns

CDC introduces complexity. The change stream is a new system to operate, monitor, and troubleshoot.

Schema changes on the source table require careful handling. Adding a column produces CDC events with the new column. If your consumers do not handle this gracefully, they break.

Lag monitoring is critical. If the CDC agent falls behind the WAL, you build up delay in your downstream systems. Monitor the consumer lag and set alerts.

Exactly-once delivery is hard to guarantee across the full CDC-to-consumer path. Most CDC tools provide at-least-once. Idempotent consumers are essential.

Production Failure Scenarios

• A snapshot outlasts log retention. The database purges WAL or binlog segments before the initial snapshot finishes. The connector can no longer continue from its starting position. Estimate snapshot duration, reserve enough log retention for the full window plus recovery time, and alert on replication-slot or log growth before storage becomes critical.
• A connector is down past the retained offset. When it restarts, its saved offset points to a segment that has already been removed. Take a new snapshot or use the connector’s documented recovery procedure; do not silently reset offsets if downstream state depends on the missing changes.
• An offset is lost or restored from a mismatched backup. The connector may replay events or skip a range if its offset store and downstream topics are restored to different points. Back up connector offsets with the related schema history and broker state, then test recovery and make consumers idempotent.
• A DDL change outpaces schema history or consumer deployment. A renamed or dropped column can make the connector fail or produce events consumers cannot parse. Coordinate DDL and consumer releases, keep schema history available, and test compatibility against the connector’s actual emitted event shape.

For each recovery, reconcile the source log position with the downstream checkpoint before resuming. Replaying is safe only when consumers can handle duplicates and any required missing range can still be read or rebuilt from a snapshot.

Security and Compliance Notes

• Restrict source access. Give the connector a dedicated replication identity with only the required replication, schema, and table permissions. Limit network access to the database and connector hosts, and rotate credentials through the normal secret-management process.
• Minimize sensitive fields. Capture only required tables and columns where the connector supports it. Mask or tokenize sensitive values before they reach broadly accessible topics; filtering only in consumers still exposes the raw values in the broker.
• Protect the stream. Use TLS for database, connector, and broker connections. Apply broker authentication and topic-level ACLs so a consumer cannot read unrelated tables.
• Set retention and deletion rules. CDC topics, snapshots, schema history, dead-letter topics, and backups may all retain personal data. Set retention and deletion procedures across each copy, and document how a source deletion is propagated to indexes, warehouses, and derived stores.
• Audit access and configuration. Record credential use, connector configuration changes, and topic access. Keep sensitive values out of connector logs and diagnostic dumps.

Observability for CDC

CDC runs in the background. When it breaks, you usually find out because downstream consumers start missing events — not because the agent pages you.

What to track:

• CDC agent heartbeat: confirm the agent is alive and publishing. A Debezium connector that goes silent is a disaster waiting to happen.
• WAL lag: how far behind the agent is reading from the WAL. Rising lag means the broker cannot keep up with source writes, or the agent is stuck.
• Snapshot progress: when running initial snapshots, track rows scanned vs total. Large tables take hours.
• Schema history age: for databases with DDL changes, the schema history topic grows. Make sure something is consuming and cleaning it.
• Consumer lag per topic partition: each Kafka partition representing a source table should have a low consumer lag.

Alerts to set:

• CDC agent is down for more than 60 seconds
• WAL lag exceeds 5 minutes of wall-clock time
• Consumer group has no active members
• Schema history topic has unprocessed messages older than 1 hour

Quick Recap

• Log-based CDC reads the WAL — every committed change captured, no source tables touched, no application changes needed.
• Debezium is the standard open-source CDC tool for Kafka. Snapshots, schema evolution, offset management — all handled.
• CDC events carry before/after row state. The op field tells you insert, update, or delete.
• Make consumers idempotent. CDC gives at-least-once, not exactly-once.
• Watch WAL lag, agent heartbeat, and snapshot progress. A silent CDC agent cascades into downstream failures.

Interview Questions

Further Reading

• Extract-Transform-Load — compare CDC with batch-oriented pipeline loading.
• Pipeline Orchestration — coordinate data workflows that consume CDC streams.
• Event-Driven Architecture — place database change events in the broader messaging model.
• Debezium Documentation — connector setup, snapshots, and operational behavior.
• PostgreSQL Logical Decoding — the database features behind PostgreSQL log-based CDC.

Conclusion

Log-based CDC publishes committed database changes without adding polling queries or application-level queue writes. Debezium can handle snapshots and offsets for Kafka pipelines, but delivery is generally at least once, so consumers need idempotent handling. Keep enough WAL for recovery and monitor connector health, WAL lag, and consumer lag before a delay becomes a data gap.