Change Data Capture (CDC) - AI-Optimized Technical Reference

Technology Overview

Change Data Capture (CDC) streams database changes to other systems in real-time by tapping into database transaction logs. Eliminates 6-hour data lag from batch ETL processes and prevents pipeline failures from schema changes.

Implementation Methods

Log-Based CDC (Recommended for Production)

• Latency: Milliseconds
• Source Impact: 1-3% overhead on database
• Change Types: All (Insert/Update/Delete)
• Complexity: High
• Best For: Production systems, real-time analytics

Critical Configuration:

• PostgreSQL: Set max_slot_wal_keep_size to prevent disk space issues
• MySQL: Monitor binlog I/O during high-write periods
• SQL Server: Tune tempdb to prevent CDC impact

Trigger-Based CDC

• Latency: Near real-time
• Source Impact: Severe performance degradation on busy tables
• Change Types: All (Insert/Update/Delete)
• Complexity: Medium
• Best For: Small-scale, audit requirements only

Query-Based CDC

• Latency: Minutes to hours
• Source Impact: Depends on query frequency
• Change Types: Insert/Update only (misses deletes)
• Complexity: Low
• Best For: Batch processing, simple use cases

Production Implementation Requirements

Resource Requirements

• Infrastructure Cost: $2-5k/month for Kafka cluster
• Engineering Time: 20% of one engineer's time for maintenance
• Total Budget: $50-100k/year including people, infrastructure, monitoring

Critical Failure Modes

WAL Retention Hell (PostgreSQL)

• Problem: WAL files fill disk when CDC falls behind
• Impact: Server stops responding at 95% disk usage
• Solution: Set max_slot_wal_keep_size, monitor with SELECT pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) FROM pg_replication_slots;

MySQL Binlog Position Loss

• Problem: Lose track of binlog position
• Impact: Missing data or full reprocessing required
• Solution: Monitor Kafka offset topics, backup position tracking

Schema Evolution Breaks

• Safe: Adding nullable columns
• Dangerous: Renaming columns, changing data types (VARCHAR to INT)
• Deadly: Dropping columns
• Solution: Test all schema changes in dev environment first

Memory and Performance Issues

Debezium Memory Leaks

• Problem: Debezium 1.9.x has memory leaks with large transactions
• Impact: Connector dies during batch updates (2M+ rows)
• Solution: Upgrade to 2.x or restart connectors weekly

Kafka Connect Failures

• Problem: Random connector deaths
• Solution: Set connect.log.level=DEBUG, monitor and restart automatically

Monitoring Requirements

Essential Alerts

• Replication lag > 10 minutes
• WAL usage > 10GB (PostgreSQL)
• Kafka topic size > 100GB per topic
• Disk space > 95%

Debug Commands

-- PostgreSQL WAL monitoring
SELECT pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) FROM pg_replication_slots;

-- Check replication slot status
SELECT slot_name, database, active, restart_lsn FROM pg_replication_slots;

Tool Selection Matrix

Tool	Cost	Reliability	Setup Complexity	Operational Overhead
Debezium	Free	Medium	High	High (6 months learning curve)
Airbyte	Medium	Medium	Low	Medium (random failures)
AWS DMS	High	High	Medium	Low (slow but reliable)
Fivetran	Very High	Very High	Very Low	Very Low

Tool-Specific Issues

Debezium

• Learning Curve: 6 months to production readiness
• Documentation: Scattered across 47 pages
• Support: Slack community more useful than docs
• Memory: Default 1GB heap insufficient for large transactions

Airbyte

• Pros: Easy UI, faster setup
• Cons: Mysterious connector restarts, costs money
• Operations: Ops teams love UI, hate random failures

When NOT to Use CDC

Use Batch ETL Instead When:

• Tables with <10k changes/day
• Heavy transformations required
• Compliance mandates batch processing
• Team lacks streaming expertise
• <1000 changes/day total volume

Cost-Benefit Threshold

CDC becomes cost-effective when:

• Data freshness requirements <1 hour
• Multiple downstream systems need sync
• Source system can't handle ETL query load
• DELETE operations must be captured

Common Production Scenarios

Network Partition Recovery

• Duration: CDC can't reach Kafka for 30+ minutes
• Impact: Lag metrics spike, potential data loss
• Recovery: Automatic catchup if WAL/binlog retained

Database Crash Recovery

• PostgreSQL: Replication slots survive, WAL files may be cleaned
• MySQL: Binlog position stored in Kafka topics
• Worst Case: 2-8 hours downtime for fresh snapshot

Duplicate Event Handling

• Cause: At-least-once delivery semantics
• Triggers: Network failures, connector restarts, rebalancing
• Solution: Implement idempotent downstream processing

Critical Warnings

Schema Change Disasters

• VARCHAR(50) to VARCHAR(100): Usually safe
• INT to VARCHAR: Will break CDC pipeline
• Column renames: Breaks everything, plan downtime
• ALTER TABLE on MySQL: Locks table, use pt-online-schema-change

Hidden Operational Costs

• 24/7 monitoring required (3am pages guaranteed)
• Kafka expertise mandatory for troubleshooting
• Database administrator involvement for WAL/binlog tuning
• DevOps overhead for connector lifecycle management

Performance Degradation Scenarios

• Large transactions (1M+ rows) cause memory issues
• High-frequency small transactions can overwhelm CDC
• Schema with many columns increases serialization overhead
• Network latency between database and Kafka affects throughput

Success Criteria

CDC implementation succeeds when:

• Replication lag consistently <5 minutes
• Schema changes deploy without CDC pipeline failures
• Ops team can troubleshoot common issues without escalation
• Cost per GB of data transferred <$0.10
• Downstream systems receive 99.9% of change events