Change Data Capture: How to Stream Database Changes Without Breaking Production

Everyone thinks CDC is just 'read the binlog and send to Kafka.' Then your payment service goes down at 3 AM because the CDC connector ran out of memory trying to process a 2GB transaction. CDC is deceptively simple in theory and brutally complex in production. The problem it solves is real: how do you react to database changes without polling every second and hammering your DB with SELECT queries? You need a stream of changes — for caches, search indexes, analytics, or event-driven architectures. After reading this, you'll know exactly how CDC works under the hood, which failure modes will bite you, and how to build a production-grade pipeline that doesn't fall over when a DBA runs an UPDATE without a WHERE clause.

Why Polling Is a Trap and CDC Is the Escape

Before CDC, teams polled tables with SELECT * FROM orders WHERE updated_at > ?. That works until your table has 100M rows and you're polling every 5 seconds. The DB spends CPU on repeated full scans. Writes slow down. Connection pools exhaust. CDC sidesteps this by reading the transaction log — the database's own record of changes. No SELECTs, no load. The trade-off: you now depend on log retention and connector health. But for any system that needs low-latency change streams (cache invalidation, search indexing, analytics), polling is a dead end.

// io.thecodeforge — System Design tutorial

// Polling approach (bad)
SELECT * FROM orders WHERE updated_at > '2025-01-01 00:00:00';
// Every 5 seconds. Full table scan. Kills DB at scale.

// CDC approach (good)
// Debezium reads PostgreSQL WAL via logical replication slot.
// Outputs JSON like:
{"op":"c","after":{"id":123,"status":"shipped","updated_at":"2025-01-01T00:00:05Z"}}
// No SELECT. No load. Real-time.

How CDC Reads the Transaction Log Without Breaking the Database

CDC connectors attach to the database's transaction log. In PostgreSQL, that's a logical replication slot. In MySQL, it's the binlog position. The connector reads the log sequentially, parses each transaction into row-level events, and emits them to a message bus (Kafka, Pulsar, etc.). The key insight: the log is append-only and sequential. The connector never runs SELECT. It just streams. But there's a catch: the log has a retention window. If your connector falls behind, the DB may recycle old segments, and you lose data. That's why monitoring replication lag is critical.

// io.thecodeforge — System Design tutorial

// Debezium connector config for PostgreSQL (JSON)
{
  "name": "orders-connector",
  "config": {
    "connector.class": "io.debezium.connector.postgresql.PostgresConnector",
    "database.hostname": "postgres-primary",
    "database.port": "5432",
    "database.user": "debezium",
    "database.password": "${DB_PASSWORD}",
    "database.dbname": "ecommerce",
    "database.server.name": "ecommerce-pg",
    "plugin.name": "pgoutput",
    "slot.name": "debezium_slot",
    "publication.name": "debezium_pub",
    "publication.autocreate.mode": "filtered",
    "table.include.list": "public.orders",
    "max.queue.size": 8192,
    "max.batch.size": 2048,
    "snapshot.mode": "when_needed",
    "tombstones.on.delete": "false"
  }
}

The Three Failure Modes That Will Wake You Up at 3 AM

CDC in production fails in three predictable ways. First: the connector falls behind and the DB recycles the log. You get ERROR: replication slot "debezium_slot" is active but the server's wal_level is insufficient. Fix: increase wal_keep_segments or max_slot_wal_keep_size. Second: the connector OOMs because a bulk UPDATE floods the queue. Fix: rate-limit with max.queue.size and max.batch.size. Third: schema changes break the connector. If you ALTER TABLE ADD COLUMN, the connector may fail to deserialize old events. Fix: use Avro with Schema Registry and enable schema evolution.

// io.thecodeforge — System Design tutorial

// Check replication lag in PostgreSQL
SELECT slot_name, pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) AS lag_bytes
FROM pg_replication_slots
WHERE slot_name = 'debezium_slot';
// If lag_bytes > 1GB, alert immediately.

// Check connector offset lag in Kafka
// Run kafka-consumer-groups:
kafka-consumer-groups --bootstrap-server kafka:9092 --group connect-orders --describe
// Look at LAG column. If > 100000, investigate.

Exactly-Once Semantics: The Holy Grail and How to Get Close

CDC connectors promise at-least-once delivery by default. That means duplicates. For idempotent consumers (e.g., upsert into a cache), duplicates are fine. For non-idempotent (e.g., sending emails), duplicates are a disaster. To get exactly-once, you need: (1) connector with exactly-once support (Debezium 2.0+ with Kafka exactly-once source), (2) transactional outbox pattern in the source app, and (3) idempotent consumer with dedup by event ID. Even then, edge cases like connector crash after emit but before commit can cause duplicates. The only true exactly-once is in the consumer — make it idempotent.

// io.thecodeforge — System Design tutorial

// Python consumer with idempotent upsert
import psycopg2

def handle_event(event):
    event_id = event['payload']['source']['ts_ms'] + '-' + event['payload']['after']['id']
    # Check if already processed
    cur.execute("SELECT 1 FROM processed_events WHERE event_id = %s", (event_id,))
    if cur.fetchone():
        return  # Skip duplicate
    # Process: upsert into cache
    cache.upsert(event['payload']['after']['id'], event['payload']['after'])
    # Mark processed
    cur.execute("INSERT INTO processed_events (event_id) VALUES (%s)", (event_id,))
    conn.commit()

Schema Evolution: Why Your Connector Will Break on Monday Morning

Databases evolve. You add a column, rename one, or change a type. CDC connectors capture the schema at snapshot time. When the schema changes, the connector may fail to deserialize old events in the log. Solution: use a schema registry (Confluent Schema Registry) with Avro or Protobuf. The registry stores every version. The connector emits events with schema ID. Consumers fetch the schema and handle backward/forward compatibility. Without this, you'll see org.apache.kafka.connect.errors.DataException: JsonConverter failed to deserialize JSON.

// io.thecodeforge — System Design tutorial

// Debezium connector with Avro and Schema Registry
{
  "key.converter": "io.confluent.connect.avro.AvroConverter",
  "key.converter.schema.registry.url": "http://schema-registry:8081",
  "value.converter": "io.confluent.connect.avro.AvroConverter",
  "value.converter.schema.registry.url": "http://schema-registry:8081",
  "schema.name.adjustment.mode": "avro"
}

// Consumer reads Avro
// Schema evolves: add column 'discount' with default 0.0
// Old events have no 'discount' field. Consumer handles via schema compatibility.

When Not to Use CDC: The Overkill Trap

CDC is powerful but not free. You need to run Kafka (or similar), a connector cluster, and schema registry. That's operational overhead. If your use case is: (1) batch processing with hourly updates, (2) a single table with <1M rows, or (3) you don't need real-time, then polling with a timestamp column is simpler and cheaper. Also, if your database is a small SQLite or a read replica, CDC adds complexity without benefit. Don't use CDC for everything. Use it when you need sub-second change propagation and can't afford to poll.