CDC Performance: When Your Demo Crashes and Burns in Production

CDC works fine until it doesn't. Usually when someone does a bulk import at 2AM and crashes everything.

PostgreSQL WAL: The Disk Space Killer

I've been woken up at 3AM too many times by "disk full" alerts. PostgreSQL logical replication keeps WAL files around until ALL replication slots advance. One slow table holds up everything.

-- This query saved my ass more than once
SELECT slot_name, 
       active,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) as lag_size
FROM pg_replication_slots 
ORDER BY pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) DESC;

Settings that actually matter (learned the hard way):

• `max_slot_wal_keep_size=4GB` - Set this or WAL will eat your entire fucking disk. I watched a server die at 95% disk usage because PostgreSQL just... stops working.
• `max_replication_slots=10` - Default 10 is a joke for production. We hit the limit with like 3 connectors.
• `wal_level=logical` - Yeah, obviously required, but I've spent an hour debugging "replication slot doesn't exist" only to find someone reset this to 'replica'
• `shared_preload_libraries='wal2json'` - Way better performance than pgoutput. Don't ask me why, it just is.

The PostgreSQL docs are actually decent on this stuff, unlike most database documentation.

MySQL: Even More Ways to Fail

MySQL binlog is somehow even more fragile. Debezium's MySQL connector tracks binlog positions, and if you lose position tracking, you're fucked. Either missing data or reprocessing everything from the beginning.

-- MySQL settings that prevent disasters
SET GLOBAL binlog_format = 'ROW';
SET GLOBAL binlog_row_image = 'FULL'; 
SET GLOBAL expire_logs_days = 7;
SET GLOBAL max_binlog_size = 1073741824;

Lost binlog position twice in production. First time was a MySQL restart without proper GTID configuration. Second time was a Debezium version upgrade that reset offsets. Both times = fun weekend debugging sessions.

Connection Pool Hell

CDC connectors hold database connections forever for replication slots. Meanwhile your app starts throwing FATAL: sorry, too many clients already during peak traffic.

PostgreSQL's default `max_connections=100` is a joke for production. Bump it to 300+, deploy PgBouncer or pgpool, and set up connection monitoring before this bites you:

SELECT count(*) FROM pg_stat_activity WHERE state = 'active';

The Kafka Complexity Explosion

Standard CDC setup: PostgreSQL → Debezium → Kafka → Kafka Connect → Target. Five systems that each fail in creative ways:

• Network hiccups add 200ms latency spikes
• Kafka rebalancing stops everything for 30+ seconds
• Schema Registry goes down with zero useful error messages
• Offset commits fail and you lose an hour of data

Each component needs its own monitoring, alerting, and someone who understands its failure modes. The Debezium monitoring docs are actually helpful here, which is rare.

Memory and Resource Management

Debezium Memory Leaks: Debezium versions before 2.x have known memory leaks with large transactions. Processing a 2M row batch update at 4 AM can crash your connector with OOM errors.

TOAST Field Problems: PostgreSQL's TOAST mechanism for large fields (JSON, TEXT) causes Debezium to load entire field contents into memory. A single 50MB JSON document can crash your connector.

Kafka Connect Heap Issues: Default 1GB heap size isn't enough for high-throughput CDC. Most production deployments need 4-8GB heap with proper GC tuning:

## Kafka Connect JVM tuning for CDC workloads
export KAFKA_HEAP_OPTS=\"-Xmx4g -Xms4g\"
export KAFKA_JVM_PERFORMANCE_OPTS=\"-server -XX:+UseG1GC -XX:MaxGCPauseMillis=100\"

Network and Cross-AZ Latency

AWS Multi-AZ Reality: Vendors demo everything in single availability zones. Production requires multi-AZ deployment where cross-AZ latency averages 2-3ms but spikes to 50ms during peak hours.

Kafka Connect Distributed Mode makes this worse - connectors constantly rebalance when network hiccups, losing progress and creating lag spikes.

Performance Impact (your mileage will definitely vary):

• Single AZ: Usually around 200-300ms CDC latency, sometimes spikes to who-knows-what
• Multi-AZ: Anywhere from 2-5 seconds average, but I've seen it hit 30+ seconds when AWS is having a bad day

Fix: Deploy CDC infrastructure components in the same AZ despite the availability trade-offs. For most use cases, shorter consistent latency beats high availability promises.

The Schema Evolution Performance Trap

Schema Changes Kill Performance: Adding a NOT NULL column requires scanning every row to populate default values. During schema migration, CDC lag can spike from milliseconds to hours.

The Downstream Cascade: Schema changes trigger updates across the entire pipeline:

1. Source database DDL causes WAL spike
2. Debezium connector schema parsing slows down
3. Kafka Schema Registry compatibility checks
4. Downstream applications need schema updates
5. Target systems require DDL propagation

Best Practice: Test schema changes in staging with actual CDC load running. A 10-second schema change can cause 2+ hours of CDC lag under load.

When CDC Performance Actually Matters

Don't optimize what doesn't need optimizing:

• Tables with <10K changes/day: batch ETL is simpler
• Analytics workloads that can tolerate 5+ minute delays
• Compliance reporting that requires batch processing anyway

Optimize aggressively when:

• Real-time fraud detection (sub-second requirements)
• Live dashboards for customer-facing applications
• Event-driven microservices that need immediate consistency
• Financial trading systems where milliseconds matter

The key is matching your optimization effort to actual business requirements, not pursuing theoretical performance gains.

But if you're still trying to pick the right CDC tool for your use case, let's talk about what these vendors actually deliver versus what they promise in their marketing...

Most CDC setups work great until someone imports 50 million records on a Tuesday morning and everything falls over.

Why Your Single Connector Hits a Wall

Debezium processes everything through one thread per database. Doesn't matter if you have 20 Kafka brokers - that single thread becomes your bottleneck when processing millions of events.

Split your high-volume tables into separate connectors:

## Split the pain across multiple threads
connector-users:
  table.include.list: "users"
  
connector-orders:  
  table.include.list: "orders"
  
connector-events:
  table.include.list: "user_events"

Use primary key partitioning to keep per-entity ordering while enabling parallelism:

{
  "transforms.partitionByKey.partition.key.fields": "id"
}

Batch Processing: Stop the Query Storm

If you're enriching CDC events with reference data, processing them one-by-one is insanely slow. I've seen 30-second jobs take 30 minutes because someone was making 50,000 individual database queries.

Buffer events in 5-10 second windows and enrich in batches:

def process_batch(events):
    # One query instead of 1000
    user_ids = [event['user_id'] for event in events]
    user_data = fetch_users_bulk(user_ids)
    
    for event in events:
        event['user_metadata'] = user_data.get(event['user_id'])
        emit_enriched_event(event)

Went from like 5000 individual queries to 1 bulk query. Latency dropped from 10+ minutes to seconds. Took me 3 hours of staring at slow query logs to figure out this obvious optimization because I'm an idiot sometimes.

Deduplication: Stop Writing the Same Shit 10 Times

High-frequency tables spam CDC with duplicates. User updates their profile 10 times in 30 seconds, you get 10 events. Downstream only cares about the final state.

Buffer events in 30-60 second windows and keep only the latest per primary key. Maintain ordering within each entity's stream.

Cut database writes by like 90% with this approach. Eliminated an AWS RDS IOPS bottleneck that was somehow costing us $3K/month (AWS billing is such bullshit).

Memory Management: When JVMs Explode

Large transactions kill Debezium. Someone runs a bulk import with 2M rows and your connector crashes with OutOfMemoryError. Default 1GB heap isn't enough.

Bump the memory and fix GC settings:

export KAFKA_HEAP_OPTS="-Xmx8g -Xms8g"
export KAFKA_JVM_PERFORMANCE_OPTS="-XX:+UseG1GC -XX:+HeapDumpOnOutOfMemoryError"

## Debezium tuning
max.queue.size=16000
max.batch.size=4096

TOAST fields will fuck you over. PostgreSQL stores large JSON/TEXT in TOAST tables. CDC loads the entire field into memory. One 50MB JSON document can crash everything.

Exclude large fields if you don't need them:

column.exclude.list=user_profile.large_json_field,logs.full_stacktrace

Or use `REPLICA IDENTITY USING INDEX` with smaller columns.

Network Optimization: AWS Will Bite You

AWS cross-AZ latency averages 2-3ms but spikes unpredictably. During one incident, latency spiked to 50ms for 2 hours, causing CDC lag to grow from 200ms to 30 seconds.

Single-AZ Deployment Strategy: Deploy CDC components (source DB, Kafka, connectors) in the same AZ despite availability trade-offs. Most use cases benefit more from consistent low latency than theoretical high availability.

Network Monitoring: Set up latency monitoring between CDC components:

## Monitor inter-component latency
ping -c 10 postgres-host.internal
ping -c 10 kafka-broker-1.internal  
traceroute kafka-broker-1.internal

Monitoring That Actually Helps

The Funnel Approach: Track events at every stage of your CDC pipeline with tagged counters:

• Events captured from source database (by table)
• Events published to Kafka (by topic/partition)
• Events consumed by downstream systems (by consumer group)
• Events ignored/filtered (by reason)

Critical Alerts (Prometheus queries):

-- PostgreSQL WAL lag (alert at 1GB)
SELECT pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) 
FROM pg_replication_slots WHERE slot_name = 'debezium';

-- Kafka consumer lag (alert at 10k messages)
kafka-consumer-groups.sh --bootstrap-server localhost:9092 --describe --group debezium-group

Dashboard Recommendations: Build custom Grafana dashboards showing:

• End-to-end latency (source database to target system)
• WAL/binlog growth rate and retention
• Kafka topic lag by partition
• Connector task status and error rates
• Target system write throughput and backpressure

The Heartbeat Table Trick

Solving Mixed-Throughput Problems: When one table sees heavy writes while others are idle, WAL accumulates because idle table replication slots don't advance. This forces PostgreSQL to retain WAL files.

Heartbeat Solution (using pg_cron):

-- Create heartbeat table
CREATE TABLE cdc_heartbeat (
    id BIGINT NOT NULL PRIMARY KEY,
    last_updated TIMESTAMP NOT NULL
);

-- Schedule regular updates  
SELECT cron.schedule(
    'cdc_heartbeat', 
    '* * * * *',  -- Every minute
    'INSERT INTO cdc_heartbeat (id, last_updated) VALUES (1, NOW()) 
     ON CONFLICT (id) DO UPDATE SET last_updated = NOW();'
);

Result: All replication slots advance regularly, preventing WAL accumulation even when some tables are idle.

When NOT to Optimize

Don't over-engineer for imaginary scale:

• Tables with <1000 changes/hour don't need parallelism
• Analytics workloads that can tolerate 5-minute delays don't need sub-second optimization
• Simple replication scenarios don't need complex deduplication

Start simple, optimize when you measure actual pain points. Most CDC performance problems are solved by proper database configuration, not fancy architectures.

Speaking of pain points, let me share the most common performance disasters I've seen and how to fix them when you're getting paged at 3AM...

OK so here are some advanced patterns we've tried. Some worked, some didn't, all of them were way more complicated than they needed to be. This is what happens when you're debugging CDC at 3AM and making questionable architectural decisions...

Snapshots: Where CDC Goes to Die

Initial snapshots take forever and usually break. Single-threaded table scans on a 500GB table took like 18 hours when I tried it. And that's if nothing goes wrong, which it always does.

Split large tables by primary key ranges:

-- Figure out the ranges first
SELECT min(id), max(id), count(*) FROM users;

-- Then run parallel snapshots
Snapshot 1: WHERE id BETWEEN 1 AND 1000000
Snapshot 2: WHERE id BETWEEN 1000001 AND 2000000  

RisingWave figured out lock-free snapshots by consuming WAL in parallel with the initial snapshot. No table locking, no production impact.

Snapshot Monitoring: Track snapshot progress and performance:

-- Monitor long-running queries during snapshot
SELECT pid, now() - pg_stat_activity.query_start AS duration, query 
FROM pg_stat_activity 
WHERE (now() - pg_stat_activity.query_start) > interval '5 minutes';

Multi-Sink Architecture: Fan-Out From Hell

Production never has one target system. You need CDC data going to your data warehouse, Redis cache, Elasticsearch, and some analytics platform management decided on last month.

Fan-Out Strategy:

## Kafka topic serves multiple consumers
source-database → debezium → kafka-topic → [snowflake-sink, redis-sink, elasticsearch-sink]

Independent Consumer Scaling: Each sink can scale independently without affecting others. Snowflake sink failures don't impact Redis updates.

Backpressure Isolation: Use separate Kafka topics or consumer groups to prevent slow sinks from blocking fast ones:

{
  "connector.class": "io.confluent.connect.jdbc.JdbcSinkConnector",
  "consumer.group.id": "snowflake-sink-group",
  "max.poll.records": 1000,
  "max.poll.interval.ms": 300000
}

Cross-Region CDC Patterns

Global Data Synchronization: Multi-region applications need CDC data replicated across geographic regions with different latency and consistency requirements.

Active-Passive CDC:

• Primary region: Real-time CDC processing
• Secondary regions: Batch replication every 5-15 minutes
• Failover: Promote secondary to active CDC during outages

Performance Considerations:

• Cross-region network latency: 50-200ms baseline
• Data transfer costs: $0.02-0.12 per GB depending on regions
• Regulatory compliance: GDPR, data residency requirements

Implementation Pattern:

## Primary region (us-east-1)  
postgres-primary → debezium → kafka-primary → local-sinks
                              ↓
## Cross-region replication
                    kafka-mirror-maker → kafka-secondary (eu-west-1)
                                        ↓
                                      regional-sinks

Event Ordering at Scale

Maintaining Order Across Partitions: CDC events must preserve database transaction order, but Kafka partitions enable parallelism. These requirements conflict at scale.

Partition Key Strategy: Use table primary key as partition key to maintain per-entity ordering:

{
  "transforms": "extractKey",
  "transforms.extractKey.type": "io.debezium.transforms.ExtractNewRecordState",
  "transforms.extractKey.add.fields": "table,ts_ms"
}

Transaction Boundary Handling: PostgreSQL transactions spanning multiple tables create ordering challenges. Advanced patterns use transaction IDs and commit timestamps:

-- Track transaction boundaries in CDC events
SELECT txid_current(), statement_timestamp(), pg_current_wal_lsn();

Out-of-Order Event Recovery: Despite best efforts, events arrive out of order. Downstream systems need idempotent processing:

def process_event(event):
    if event.timestamp <= last_processed_timestamp[event.entity_id]:
        # Ignore out-of-order event
        return
    
    apply_change(event)
    last_processed_timestamp[event.entity_id] = event.timestamp

Resource Optimization Patterns

Dynamic Resource Allocation: CDC workloads are bursty - quiet during nights and weekends, spikes during business hours and bulk operations.

Auto-Scaling Configuration (Kubernetes HPA):

## Kubernetes HPA for Kafka Connect
apiVersion: autoscaling/v2
kind: HorizontalPodAutoscaler
spec:
  scaleTargetRef:
    name: kafka-connect
  minReplicas: 2
  maxReplicas: 10
  metrics:
  - type: Pods
    pods:
      metric:
        name: kafka_consumer_lag
      target:
        type: AverageValue
        averageValue: "10000"

Cost Optimization: Use spot instances for non-critical CDC components, reserved instances for core infrastructure:

AWS Cost Breakdown:

• Spot instances: 50-70% cost reduction for Kafka brokers
• Reserved instances: 30-50% reduction for PostgreSQL RDS
• S3 storage optimization: Use IA storage class for CDC archives

Disaster Recovery Patterns

CDC Pipeline Recovery: When CDC pipelines fail, recovery strategy depends on how much data loss is acceptable and how long rebuilds take.

Recovery Time Objectives:

• RTO < 15 minutes: Multi-region active-active CDC with automatic failover
• RTO < 2 hours: Standby CDC infrastructure with manual failover
• RTO < 24 hours: Full rebuild from database snapshots

Recovery Strategies:

## Fast recovery: Resume from last known offset
kafka-consumer-groups.sh --bootstrap-server localhost:9092 \
  --group debezium-group --reset-offsets --to-latest

## Conservative recovery: Replay last 24 hours  
kafka-consumer-groups.sh --bootstrap-server localhost:9092 \
  --group debezium-group --reset-offsets --to-datetime 2025-09-01T12:00:00.000

## Complete rebuild: Start fresh snapshot
curl -X DELETE localhost:8083/connectors/debezium-connector
## Reconfigure and restart with snapshot.mode=initial

Data Validation: After recovery, validate data consistency between source and target:

-- Row count validation
SELECT COUNT(*) FROM source_table WHERE updated_at > '2025-09-01';
SELECT COUNT(*) FROM target_table WHERE updated_at > '2025-09-01';

-- Checksum validation for critical data
SELECT SUM(HASH(primary_key, updated_at)) FROM critical_table;

The Performance Ceiling Reality

When optimization hits diminishing returns:

• Single-threaded connector limits: Cannot exceed source database single-core performance
• Network bandwidth ceilings: Cross-region replication limited by WAN bandwidth
• Storage IOPS limits: WAL writes bounded by disk performance
• Memory constraints: Large transactions require proportional RAM

Know when to architect around limits rather than optimize through them. Sometimes the solution is splitting databases, not tuning CDC tools.

Final Reality Check: Most CDC performance problems are solved by proper configuration, not exotic optimizations. Master the basics before pursuing advanced patterns.

Look, at the end of the day, CDC performance comes down to three things: tune your database properly, monitor the shit out of everything, and don't believe vendor marketing about "seamless" anything. Plan for 6 months of debugging, budget 3x what they quote you, and make sure someone on your team can debug Kafka at 3AM.

But when it works? When you finally get CDC humming along reliably? Your data lag drops from hours to seconds, your engineers stop fighting ETL schedules, and you can actually build the real-time features the business has been asking for. Just don't expect it to be easy.