System Design (HLD)Change Data Capture

Change Data Capture (CDC)

LevelAdvanced

Duration75 mins

TopicChange Data Capture

2 / 5

Database Log-Based CDC

The Source of Truth: Transaction Logs

Every production database maintains a transaction log—a sequential record of every change made to the data. This log exists for crash recovery: if the database fails mid-transaction, it can replay the log to restore consistency.

What makes log-based CDC revolutionary is a simple insight:

The database already knows exactly what changed, in exactly what order, with exactly what values. We just need to read it.

Rather than polling tables, triggering on changes, or relying on applications to emit events, log-based CDC reads the same authoritative record the database uses internally. This approach is simultaneously the most accurate, most efficient, and most decoupled method of capturing changes.

What You Will Learn

This page takes you deep into the mechanics of log-based CDC. You'll understand how databases structure their transaction logs, how CDC systems read and parse these logs, and the technical considerations that make log-based CDC the definitive approach for production data pipelines.

Understanding Transaction Logs

Before we can understand log-based CDC, we must understand what transaction logs are and why databases maintain them.

The Write-Ahead Logging (WAL) Protocol:

Almost all modern databases use some form of Write-Ahead Logging (WAL). The protocol is simple but powerful:

Before modifying any data page in memory or on disk, write a log record describing the change
Ensure the log record is durably written to storage (fsync)
Only then apply the change to the actual data pages

This guarantees that even if the system crashes mid-transaction, the log contains enough information to either complete or rollback any in-progress work.

Converting Mermaid diagram...

What the Log Contains:

A typical transaction log record includes:

Component	Purpose
Log Sequence Number (LSN)	Unique, monotonically increasing identifier for this record
Transaction ID	Which transaction made this change
Operation Type	INSERT, UPDATE, DELETE, or internal operations
Table/Page Reference	Where in the database this change applies
Before Image	The data values before the change (for UPDATE/DELETE)
After Image	The data values after the change (for INSERT/UPDATE)
Timestamp	When the change was logged
Commit/Rollback Markers	Transaction boundaries

The LSN is particularly important for CDC—it provides an ordered position in the log that CDC systems use to track their progress and resume after failures.

Physical vs. Logical Logs

Databases may use physical logging (records exactly which bytes changed on which page) or logical logging (records the SQL-like operation). Some use a hybrid. CDC systems prefer logical logs because they're easier to parse and contain semantic information about what changed, not just byte offsets.

Log Formats Across Major Databases

Each database implements its transaction log differently. Understanding these differences is crucial for CDC implementation and choosing the right tools:

PostgreSQL: Write-Ahead Log (WAL) with Logical Decoding

PostgreSQL maintains a WAL that contains all changes to data files. For CDC, PostgreSQL offers Logical Decoding—a framework that converts the physical WAL into logical change streams.

Key Concepts:

WAL Files: Stored in pg_wal/ directory, 16MB segments by default
Logical Replication Slots: Bookmarks that track CDC consumer position and prevent WAL cleanup
Output Plugins: Convert WAL records to various formats (pgoutput, wal2json, decoderbufs)
Publications: Define which tables to include in logical replication

Configuration for CDC:

-- postgresql.conf
wal_level = logical            -- Enable logical decoding
max_replication_slots = 10     -- Slots for CDC connectors
max_wal_senders = 10           -- Connections that can stream WAL

-- Create a replication slot for CDC
SELECT pg_create_logical_replication_slot(
    'debezium_slot', 
    'pgoutput'
);

-- Create a publication (what to capture)
CREATE PUBLICATION cdc_publication FOR TABLE orders, products;

Important Considerations:

Slots prevent WAL deletion; unused slots cause disk space exhaustion
Logical decoding consumes CPU; large transactions may spike load
FULL replica identity needed for UPDATE/DELETE before-images:
```
ALTER TABLE orders REPLICA IDENTITY FULL;
```

How CDC Connectors Read Logs

CDC connectors act as specialized database clients that read the transaction log as if they were replica nodes. Here's the detailed process:

Converting Mermaid diagram...

Step-by-Step Breakdown:

1. Connection Establishment

The connector connects to the database using replication protocol:

# PostgreSQL replication connection
host=db.example.com
dbname=inventory
user=cdc_user
replication=database  # Special replication mode

The database treats the connector as a replica, streaming changes continuously.

2. Initial Snapshot (Optional but Common)

Before streaming changes, most connectors take an initial snapshot of existing data:

1. Lock tables (or use consistent snapshot)
2. Record current log position (LSN/binlog position)
3. SELECT * FROM each table
4. Emit 'read' events for all existing rows
5. Release locks
6. Switch to streaming from recorded position

This ensures downstream systems receive complete data, not just changes from connector start.

3. Log Streaming

After snapshotting, the connector streams changes in real-time:

WAL Record → Parse → Extract table, operation, values → 
    Enrich with schema → Serialize to JSON/Avro → 
    Publish to Kafka topic

4. Offset Management

The connector tracks its position (LSN, binlog file:position, resume token):

{
  "postgres": {
    "lsn": "2F/ABC123",
    "txId": 571,
    "ts_usec": 1704067200000000
  }
}

This offset is committed to durable storage (typically Kafka itself). On restart, the connector resumes from the last committed offset, ensuring no data loss.

The Snapshot Challenge

Initial snapshots can take hours or days for large databases. During snapshotting, the connector holds database resources and connections. Many CDC deployments fail at this stage—plan for snapshot time, consider incremental snapshots, or evaluate schema-aware snapshot strategies that can be paused and resumed.

Log Position and Offset Tracking

Reliable offset tracking is the foundation of CDC durability. Without it, changes would be lost or duplicated on every restart. Let's examine how this works in detail:

CDC Offset Tracking Logic
TypeScript
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
interface CDCOffset {
  // Database-specific position
  position: {
    // PostgreSQL: LSN (Log Sequence Number)
    lsn?: string;  // e.g., "2F/ABC123"
    
    // MySQL: binlog file and position
    binlogFile?: string;  // e.g., "mysql-bin.000042"
    binlogPosition?: number;  // e.g., 12345678
    gtid?: string;  // e.g., "3E11FA47-71CA-11E1-9E33:1-21"
    
    // MongoDB: resume token
    resumeToken?: string;  // Opaque base64 token
  };
  
  // Metadata for debugging and recovery
  timestamp: number;  // Wall clock when change was captured
  transactionId: string;  // Database transaction ID
  serverName: string;  // Logical name of source database
}
 
class CDCConnector {
  private currentOffset: CDCOffset | null = null;
  private offsetStore: OffsetStore;  // Backed by Kafka, Redis, or file
  
  async start(): Promise<void> {
    // 1. Load last committed offset
    this.currentOffset = await this.offsetStore.load();
    
    if (this.currentOffset) {
      console.log(`Resuming from offset: ${JSON.stringify(this.currentOffset)}`);
      await this.resumeFromOffset(this.currentOffset);
    } else {
      console.log('No offset found, starting initial snapshot');
      await this.performSnapshot();
    }
    
    // 2. Begin streaming changes
    await this.streamChanges();
  }
  
  private async streamChanges(): Promise<void> {
    for await (const change of this.database.streamReplicationChanges()) {
      // 3. Process each change
      const event = this.convertToEvent(change);
      
      // 4. Publish to Kafka (batched for efficiency)
      await this.producer.send({
        topic: `${this.serverName}.${event.source.schema}.${event.source.table}`,
        messages: [{
          key: this.extractKey(event),
          value: JSON.stringify(event),
          headers: { 'lsn': change.lsn }
        }]
      });
      
      // 5. Update current offset (not yet committed)
      this.currentOffset = {
        position: { lsn: change.lsn },
        timestamp: Date.now(),
        transactionId: change.txId,
        serverName: this.serverName
      };
    }
  }
  
  // Called periodically or after batch confirmation
  async commitOffset(): Promise<void> {
    if (this.currentOffset) {
      await this.offsetStore.commit(this.currentOffset);
      console.log(`Committed offset: ${this.currentOffset.position.lsn}`);
    }
  }
}

Offset Storage Strategies:

Storage	Pros	Cons	Best For
Kafka Connect Offsets Topic	Native integration, exactly-once with Kafka	Kafka-specific	Kafka Connect deployments
Database Table	Simple, queryable	Additional dependency	Small deployments
Redis	Fast, distributed	No transactions	High-throughput pipelines
File	Simplest	Not distributed	Development/testing

The Exactly-Once Challenge:

Achieving exactly-once requires atomic commit of both the message and the offset:

1. Produce message to Kafka
2. Commit offset
--- Crash here? ---
3. Confirm both succeeded

If the connector crashes between steps 2 and 3, is the offset committed? If between 1 and 2, the message might never be produced on restart.

Solution: Transactional Produces

await producer.beginTransaction();
await producer.send(messages);
await producer.sendOffsets(offsetsToCommit);
await producer.commitTransaction();  // Atomic: all or nothing

This ensures the message and offset commit atomically—Kafka treats them as a single transaction.

Schema Evolution Challenges

One of the most complex aspects of log-based CDC is handling schema changes. When a table adds a column, changes the type of a field, or drops a column, the CDC pipeline must adapt without losing data or breaking consumers.

Schema Evolution Scenarios

•Column Added: New events have the column, old events don't. Consumers must handle missing fields.
•Column Removed: Old events have the column, new events don't. Consumers processing old events fail if they require the column.
•Type Change: price changes from integer cents to decimal dollars. How do consumers interpret old vs new events?
•Column Renamed: Is customer_name the same as customerName? Semantic vs syntactic changes.
•Table Renamed: Change stream topic changes, consumers must redirect.
•Primary Key Change: Partitioning strategy may need adjustment; historical ordering may break.

Schema Registry to the Rescue:

Tools like Confluent Schema Registry or AWS Glue Schema Registry provide:

Centralized Schema Storage: All event schemas registered and versioned
Compatibility Checking: New schemas validated against compatibility rules
Evolution Rules: BACKWARD, FORWARD, FULL compatibility modes
Schema ID in Messages: Events reference schema IDs, not inline schemas

// Message with schema reference
{
  "magic_byte": 0,
  "schema_id": 42,  // Points to schema in registry
  "payload": { ... actual event data ... }
}

Compatibility Modes:

Schema Compatibility Rules
Mode	Definition	Typical Changes	Use Case
BACKWARD	New schema can read old data	Add optional fields, delete fields	Consumers upgraded first
FORWARD	Old schema can read new data	Delete optional fields, add fields	Producers upgraded first
FULL	Both BACKWARD and FORWARD	Add/delete optional fields only	Independent upgrades
NONE	No compatibility checking	Any change	Careful coordination required

Schema Evolution Example with Avro
JSON
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
// Schema Version 1
{
  "type": "record",
  "name": "Order",
  "fields": [
    {"name": "order_id", "type": "long"},
    {"name": "status", "type": "string"},
    {"name": "amount", "type": "int"}  // Cents
  ]
}
 
// Schema Version 2 (BACKWARD compatible)
{
  "type": "record",
  "name": "Order",
  "fields": [
    {"name": "order_id", "type": "long"},
    {"name": "status", "type": "string"},
    {"name": "amount", "type": "int"},
    {"name": "currency", "type": "string", "default": "USD"},  // New optional field
    {"name": "notes", "type": ["null", "string"], "default": null}  // New optional field
  ]
}
 
// Consumers using Schema V2 can read V1 data:
// - order_id, status, amount: present in both
// - currency: defaults to "USD" for V1 data
// - notes: defaults to null for V1 data

Schema Change Best Practices

Always add new fields as optional with defaults. 2. Never delete required fields without a migration period. 3. Never change field types—add a new field, deprecate the old. 4. Use schema registry from day one, not after you have incompatibility problems.

Performance Characteristics of Log-Based CDC

Understanding CDC performance is critical for capacity planning and operational stability. Log-based CDC has unique performance characteristics that differ significantly from traditional database workloads.

CDC Performance Dimensions
Dimension	Typical Value	Factors	Optimization
Latency	100ms - 2s	Log flush interval, polling frequency, batch size	Reduce flush intervals, smaller batches
Throughput	10K - 100K events/sec	Log read speed, network, serialization	Parallel readers, efficient serialization (Avro)
Source Impact	1-5% CPU increase	Log decoding, replication connections	Dedicated replication slots, off-peak snapshots
Memory Usage	256MB - 2GB per connector	Batch buffer size, schema cache	Tune batch settings, limit concurrent tables

Latency Breakdown:

                    End-to-End CDC Latency
├─────────────────────────────────────────────────────────────────┤
│ DB Commit│ Log Flush │ CDC Read │ Parse │ Network │ Broker Commit │
│  ~1ms    │  ~10-100ms│  ~10-50ms│ ~5-20ms│ ~5-50ms │   ~10-100ms   │
│          │           │          │        │         │               │
└──────────┴───────────┴──────────┴────────┴─────────┴───────────────┘
                    Total: 50ms - 500ms typical

Key Performance Insights:

Log flush is often the bottleneck: Most databases don't instantly persist WAL. The wal_writer_delay (PostgreSQL) or sync_binlog (MySQL) controls this.
Large transactions cause latency spikes: A transaction modifying millions of rows is captured as one huge batch, causing downstream processing delays.
Schema lookups add overhead: Each table needs its schema. Caching schemas is essential.
Network matters for geo-distributed CDC: Streaming logs across regions adds significant latency.

CDC Performance Tuning Configuration
Properties
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
# Debezium connector performance tuning
 
# Reduce latency by polling more frequently (tradeoff: more overhead)
poll.interval.ms=100
 
# Batch changes for throughput (tradeoff: increases latency)
max.batch.size=2048
 
# Snapshot performance for large tables
snapshot.max.threads=4
snapshot.fetch.size=10240
 
# Handle large transactions (prevent OOM)
max.queue.size=8192
max.queue.size.in.bytes=67108864  # 64MB queue limit
 
# Heartbeat to detect low-traffic periods
heartbeat.interval.ms=10000
 
# Schema cache size
schema.history.internal.kafka.topic=schema-history
schema.history.internal.kafka.bootstrap.servers=kafka:9092

The Large Transaction Problem

A transaction that modifies 10 million rows creates a 10-million-event batch in CDC. This can exhaust memory, cause timeout failures, and create massive downstream processing delays. Monitor for large transactions; consider breaking them into smaller batches if they originate from your applications.

Operational Considerations

Running log-based CDC in production requires careful attention to operational concerns. These are the areas that cause the most production incidents:

Critical Operational Areas

•Log Retention: The source database must retain transaction logs long enough for the CDC connector to consume them. If the connector falls too far behind, it can no longer resume—you need a full re-snapshot. Configure adequate wal_keep_size (PostgreSQL) or expire_logs_days (MySQL).
•Slot/Retention Management: Unused or stuck replication slots (PostgreSQL) prevent log cleanup, leading to disk exhaustion. Monitor slot lag and drop abandoned slots promptly.
•Connector Lag Monitoring: Track the delay between database commit time and CDC event publish time. Alert on sustained lag over SLA thresholds. Lag can indicate performance problems, network issues, or downstream slowness.
•Schema Drift Detection: If source schema changes in incompatible ways, CDC may fail or produce corrupted events. Implement schema change detection and alerting.
•Snapshot Recovery Planning: When CDC pipelines fail catastrophically (lost offsets, corrupted data), you need a recovery plan. Can you re-snapshot without impacting production? How long will it take?

CDC Monitoring Queries
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
-- PostgreSQL: Monitor replication slot lag
SELECT 
    slot_name,
    active,
    pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS lag_size,
    pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) AS lag_bytes,
    CASE 
        WHEN pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) > 1073741824 
        THEN 'CRITICAL: >1GB lag'
        WHEN pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) > 104857600 
        THEN 'WARNING: >100MB lag'
        ELSE 'OK'
    END AS status
FROM pg_replication_slots
WHERE slot_type = 'logical';
 
-- MySQL: Monitor binlog position lag
SHOW MASTER STATUS;
SHOW REPLICA STATUS\G  -- Compare Exec_Master_Log_Pos vs Read_Master_Log_Pos
 
-- PostgreSQL: Check WAL disk usage
SELECT 
    pg_size_pretty(sum(size)) as wal_size,
    count(*) as wal_files
FROM pg_ls_waldir();
 
-- Alert: WAL exceeds threshold (shell script)
-- WAL_SIZE=$(psql -t -c "SELECT sum(size) FROM pg_ls_waldir()")
-- if [ $WAL_SIZE -gt 10737418240 ]; then alert "WAL > 10GB"; fi

Recovery Runbook Template:

Scenario	Detection	Recovery Action	RTO
Connector crash	No events for 5 min	Restart connector, resumes from offset	<5 min
Offset corruption	Events with wrong sequence	Stop, delete offset, re-snapshot	Hours
Slot deleted accidentally	Connector fails to connect	Recreate slot, re-snapshot	Hours
Source DB failover	Connection errors	Reconnect to new primary, may need re-snapshot	Minutes-Hours
Schema incompatibility	Serialization errors	Pause CDC, update consumers, resume	Hours

Summary: Database Log-Based CDC

We've deeply explored the technical mechanics of log-based CDC. Let's consolidate the key insights:

Key Takeaways

•Transaction logs are the foundation — Every change is already recorded in the database's WAL/binlog/oplog. CDC reads this authoritative record.
•Each database has unique log formats — PostgreSQL uses logical decoding, MySQL uses binlog, SQL Server has native CDC or log reading, MongoDB uses oplog/change streams.
•Connectors act as replica nodes — CDC connectors connect using replication protocols, streaming changes exactly as database replicas would.
•Offset tracking enables reliability — By recording position in the log, connectors can resume after failures without data loss.
•Schema evolution requires careful handling — Use schema registries and compatibility modes to handle schema changes gracefully.
•Performance tuning balances latency vs throughput — Smaller batches reduce latency; larger batches increase throughput. Tune for your SLAs.
•Operational concerns dominate production — Log retention, slot management, lag monitoring, and recovery planning are essential for reliable CDC.

What's Next:

Now that you understand how log-based CDC works at a technical level, we'll examine the tools that implement these patterns. The next page covers Debezium and other CDC tools—comparing capabilities, architectures, and when to use each.

Page Complete

You now understand the deep mechanics of log-based CDC—how databases write transaction logs, how CDC systems read them, offset tracking for reliability, schema evolution challenges, and operational considerations. Next, we'll explore the ecosystem of tools that bring these concepts to life.

2 / 5

Loading learning content...

System Design (HLD)Change Data Capture

Change Data Capture (CDC)

LevelAdvanced

Duration75 mins

TopicChange Data Capture

2 / 5

Database Log-Based CDC

The Source of Truth: Transaction Logs

What makes log-based CDC revolutionary is a simple insight:

The database already knows exactly what changed, in exactly what order, with exactly what values. We just need to read it.

What You Will Learn

Understanding Transaction Logs

Before we can understand log-based CDC, we must understand what transaction logs are and why databases maintain them.

The Write-Ahead Logging (WAL) Protocol:

Almost all modern databases use some form of Write-Ahead Logging (WAL). The protocol is simple but powerful:

Before modifying any data page in memory or on disk, write a log record describing the change
Ensure the log record is durably written to storage (fsync)
Only then apply the change to the actual data pages

This guarantees that even if the system crashes mid-transaction, the log contains enough information to either complete or rollback any in-progress work.

Converting Mermaid diagram...

What the Log Contains:

A typical transaction log record includes:

Component	Purpose
Log Sequence Number (LSN)	Unique, monotonically increasing identifier for this record
Transaction ID	Which transaction made this change
Operation Type	INSERT, UPDATE, DELETE, or internal operations
Table/Page Reference	Where in the database this change applies
Before Image	The data values before the change (for UPDATE/DELETE)
After Image	The data values after the change (for INSERT/UPDATE)
Timestamp	When the change was logged
Commit/Rollback Markers	Transaction boundaries

The LSN is particularly important for CDC—it provides an ordered position in the log that CDC systems use to track their progress and resume after failures.

Physical vs. Logical Logs

Log Formats Across Major Databases

Each database implements its transaction log differently. Understanding these differences is crucial for CDC implementation and choosing the right tools:

PostgreSQL: Write-Ahead Log (WAL) with Logical Decoding

PostgreSQL maintains a WAL that contains all changes to data files. For CDC, PostgreSQL offers Logical Decoding—a framework that converts the physical WAL into logical change streams.

Key Concepts:

WAL Files: Stored in pg_wal/ directory, 16MB segments by default
Logical Replication Slots: Bookmarks that track CDC consumer position and prevent WAL cleanup
Output Plugins: Convert WAL records to various formats (pgoutput, wal2json, decoderbufs)
Publications: Define which tables to include in logical replication

Configuration for CDC:

-- postgresql.conf
wal_level = logical            -- Enable logical decoding
max_replication_slots = 10     -- Slots for CDC connectors
max_wal_senders = 10           -- Connections that can stream WAL

-- Create a replication slot for CDC
SELECT pg_create_logical_replication_slot(
    'debezium_slot', 
    'pgoutput'
);

-- Create a publication (what to capture)
CREATE PUBLICATION cdc_publication FOR TABLE orders, products;

Important Considerations:

Slots prevent WAL deletion; unused slots cause disk space exhaustion
Logical decoding consumes CPU; large transactions may spike load
FULL replica identity needed for UPDATE/DELETE before-images:
```
ALTER TABLE orders REPLICA IDENTITY FULL;
```

How CDC Connectors Read Logs

CDC connectors act as specialized database clients that read the transaction log as if they were replica nodes. Here's the detailed process:

Converting Mermaid diagram...

Step-by-Step Breakdown:

1. Connection Establishment

The connector connects to the database using replication protocol:

# PostgreSQL replication connection
host=db.example.com
dbname=inventory
user=cdc_user
replication=database  # Special replication mode

The database treats the connector as a replica, streaming changes continuously.

2. Initial Snapshot (Optional but Common)

Before streaming changes, most connectors take an initial snapshot of existing data:

1. Lock tables (or use consistent snapshot)
2. Record current log position (LSN/binlog position)
3. SELECT * FROM each table
4. Emit 'read' events for all existing rows
5. Release locks
6. Switch to streaming from recorded position

This ensures downstream systems receive complete data, not just changes from connector start.

3. Log Streaming

After snapshotting, the connector streams changes in real-time:

WAL Record → Parse → Extract table, operation, values → 
    Enrich with schema → Serialize to JSON/Avro → 
    Publish to Kafka topic

4. Offset Management

The connector tracks its position (LSN, binlog file:position, resume token):

{
  "postgres": {
    "lsn": "2F/ABC123",
    "txId": 571,
    "ts_usec": 1704067200000000
  }
}

This offset is committed to durable storage (typically Kafka itself). On restart, the connector resumes from the last committed offset, ensuring no data loss.

The Snapshot Challenge

Log Position and Offset Tracking

Reliable offset tracking is the foundation of CDC durability. Without it, changes would be lost or duplicated on every restart. Let's examine how this works in detail:

CDC Offset Tracking Logic
TypeScript
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
interface CDCOffset {
  // Database-specific position
  position: {
    // PostgreSQL: LSN (Log Sequence Number)
    lsn?: string;  // e.g., "2F/ABC123"
    
    // MySQL: binlog file and position
    binlogFile?: string;  // e.g., "mysql-bin.000042"
    binlogPosition?: number;  // e.g., 12345678
    gtid?: string;  // e.g., "3E11FA47-71CA-11E1-9E33:1-21"
    
    // MongoDB: resume token
    resumeToken?: string;  // Opaque base64 token
  };
  
  // Metadata for debugging and recovery
  timestamp: number;  // Wall clock when change was captured
  transactionId: string;  // Database transaction ID
  serverName: string;  // Logical name of source database
}
 
class CDCConnector {
  private currentOffset: CDCOffset | null = null;
  private offsetStore: OffsetStore;  // Backed by Kafka, Redis, or file
  
  async start(): Promise<void> {
    // 1. Load last committed offset
    this.currentOffset = await this.offsetStore.load();
    
    if (this.currentOffset) {
      console.log(`Resuming from offset: ${JSON.stringify(this.currentOffset)}`);
      await this.resumeFromOffset(this.currentOffset);
    } else {
      console.log('No offset found, starting initial snapshot');
      await this.performSnapshot();
    }
    
    // 2. Begin streaming changes
    await this.streamChanges();
  }
  
  private async streamChanges(): Promise<void> {
    for await (const change of this.database.streamReplicationChanges()) {
      // 3. Process each change
      const event = this.convertToEvent(change);
      
      // 4. Publish to Kafka (batched for efficiency)
      await this.producer.send({
        topic: `${this.serverName}.${event.source.schema}.${event.source.table}`,
        messages: [{
          key: this.extractKey(event),
          value: JSON.stringify(event),
          headers: { 'lsn': change.lsn }
        }]
      });
      
      // 5. Update current offset (not yet committed)
      this.currentOffset = {
        position: { lsn: change.lsn },
        timestamp: Date.now(),
        transactionId: change.txId,
        serverName: this.serverName
      };
    }
  }
  
  // Called periodically or after batch confirmation
  async commitOffset(): Promise<void> {
    if (this.currentOffset) {
      await this.offsetStore.commit(this.currentOffset);
      console.log(`Committed offset: ${this.currentOffset.position.lsn}`);
    }
  }
}

Offset Storage Strategies:

Storage	Pros	Cons	Best For
Kafka Connect Offsets Topic	Native integration, exactly-once with Kafka	Kafka-specific	Kafka Connect deployments
Database Table	Simple, queryable	Additional dependency	Small deployments
Redis	Fast, distributed	No transactions	High-throughput pipelines
File	Simplest	Not distributed	Development/testing

The Exactly-Once Challenge:

Achieving exactly-once requires atomic commit of both the message and the offset:

1. Produce message to Kafka
2. Commit offset
--- Crash here? ---
3. Confirm both succeeded

If the connector crashes between steps 2 and 3, is the offset committed? If between 1 and 2, the message might never be produced on restart.

Solution: Transactional Produces

await producer.beginTransaction();
await producer.send(messages);
await producer.sendOffsets(offsetsToCommit);
await producer.commitTransaction();  // Atomic: all or nothing

This ensures the message and offset commit atomically—Kafka treats them as a single transaction.

Schema Evolution Challenges

Schema Evolution Scenarios

•Column Added: New events have the column, old events don't. Consumers must handle missing fields.
•Column Removed: Old events have the column, new events don't. Consumers processing old events fail if they require the column.
•Type Change: price changes from integer cents to decimal dollars. How do consumers interpret old vs new events?
•Column Renamed: Is customer_name the same as customerName? Semantic vs syntactic changes.
•Table Renamed: Change stream topic changes, consumers must redirect.
•Primary Key Change: Partitioning strategy may need adjustment; historical ordering may break.

Schema Registry to the Rescue:

Tools like Confluent Schema Registry or AWS Glue Schema Registry provide:

Centralized Schema Storage: All event schemas registered and versioned
Compatibility Checking: New schemas validated against compatibility rules
Evolution Rules: BACKWARD, FORWARD, FULL compatibility modes
Schema ID in Messages: Events reference schema IDs, not inline schemas

// Message with schema reference
{
  "magic_byte": 0,
  "schema_id": 42,  // Points to schema in registry
  "payload": { ... actual event data ... }
}

Compatibility Modes:

Schema Compatibility Rules
Mode	Definition	Typical Changes	Use Case
BACKWARD	New schema can read old data	Add optional fields, delete fields	Consumers upgraded first
FORWARD	Old schema can read new data	Delete optional fields, add fields	Producers upgraded first
FULL	Both BACKWARD and FORWARD	Add/delete optional fields only	Independent upgrades
NONE	No compatibility checking	Any change	Careful coordination required

Schema Evolution Example with Avro
JSON
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
// Schema Version 1
{
  "type": "record",
  "name": "Order",
  "fields": [
    {"name": "order_id", "type": "long"},
    {"name": "status", "type": "string"},
    {"name": "amount", "type": "int"}  // Cents
  ]
}
 
// Schema Version 2 (BACKWARD compatible)
{
  "type": "record",
  "name": "Order",
  "fields": [
    {"name": "order_id", "type": "long"},
    {"name": "status", "type": "string"},
    {"name": "amount", "type": "int"},
    {"name": "currency", "type": "string", "default": "USD"},  // New optional field
    {"name": "notes", "type": ["null", "string"], "default": null}  // New optional field
  ]
}
 
// Consumers using Schema V2 can read V1 data:
// - order_id, status, amount: present in both
// - currency: defaults to "USD" for V1 data
// - notes: defaults to null for V1 data

Schema Change Best Practices

Always add new fields as optional with defaults. 2. Never delete required fields without a migration period. 3. Never change field types—add a new field, deprecate the old. 4. Use schema registry from day one, not after you have incompatibility problems.

Performance Characteristics of Log-Based CDC

CDC Performance Dimensions
Dimension	Typical Value	Factors	Optimization
Latency	100ms - 2s	Log flush interval, polling frequency, batch size	Reduce flush intervals, smaller batches
Throughput	10K - 100K events/sec	Log read speed, network, serialization	Parallel readers, efficient serialization (Avro)
Source Impact	1-5% CPU increase	Log decoding, replication connections	Dedicated replication slots, off-peak snapshots
Memory Usage	256MB - 2GB per connector	Batch buffer size, schema cache	Tune batch settings, limit concurrent tables

Latency Breakdown:

                    End-to-End CDC Latency
├─────────────────────────────────────────────────────────────────┤
│ DB Commit│ Log Flush │ CDC Read │ Parse │ Network │ Broker Commit │
│  ~1ms    │  ~10-100ms│  ~10-50ms│ ~5-20ms│ ~5-50ms │   ~10-100ms   │
│          │           │          │        │         │               │
└──────────┴───────────┴──────────┴────────┴─────────┴───────────────┘
                    Total: 50ms - 500ms typical

Key Performance Insights:

Log flush is often the bottleneck: Most databases don't instantly persist WAL. The wal_writer_delay (PostgreSQL) or sync_binlog (MySQL) controls this.
Large transactions cause latency spikes: A transaction modifying millions of rows is captured as one huge batch, causing downstream processing delays.
Schema lookups add overhead: Each table needs its schema. Caching schemas is essential.
Network matters for geo-distributed CDC: Streaming logs across regions adds significant latency.

CDC Performance Tuning Configuration
Properties
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
# Debezium connector performance tuning
 
# Reduce latency by polling more frequently (tradeoff: more overhead)
poll.interval.ms=100
 
# Batch changes for throughput (tradeoff: increases latency)
max.batch.size=2048
 
# Snapshot performance for large tables
snapshot.max.threads=4
snapshot.fetch.size=10240
 
# Handle large transactions (prevent OOM)
max.queue.size=8192
max.queue.size.in.bytes=67108864  # 64MB queue limit
 
# Heartbeat to detect low-traffic periods
heartbeat.interval.ms=10000
 
# Schema cache size
schema.history.internal.kafka.topic=schema-history
schema.history.internal.kafka.bootstrap.servers=kafka:9092

The Large Transaction Problem

Operational Considerations

Running log-based CDC in production requires careful attention to operational concerns. These are the areas that cause the most production incidents:

Critical Operational Areas

•Log Retention: The source database must retain transaction logs long enough for the CDC connector to consume them. If the connector falls too far behind, it can no longer resume—you need a full re-snapshot. Configure adequate wal_keep_size (PostgreSQL) or expire_logs_days (MySQL).
•Slot/Retention Management: Unused or stuck replication slots (PostgreSQL) prevent log cleanup, leading to disk exhaustion. Monitor slot lag and drop abandoned slots promptly.
•Connector Lag Monitoring: Track the delay between database commit time and CDC event publish time. Alert on sustained lag over SLA thresholds. Lag can indicate performance problems, network issues, or downstream slowness.
•Schema Drift Detection: If source schema changes in incompatible ways, CDC may fail or produce corrupted events. Implement schema change detection and alerting.
•Snapshot Recovery Planning: When CDC pipelines fail catastrophically (lost offsets, corrupted data), you need a recovery plan. Can you re-snapshot without impacting production? How long will it take?

CDC Monitoring Queries
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
-- PostgreSQL: Monitor replication slot lag
SELECT 
    slot_name,
    active,
    pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS lag_size,
    pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) AS lag_bytes,
    CASE 
        WHEN pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) > 1073741824 
        THEN 'CRITICAL: >1GB lag'
        WHEN pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) > 104857600 
        THEN 'WARNING: >100MB lag'
        ELSE 'OK'
    END AS status
FROM pg_replication_slots
WHERE slot_type = 'logical';
 
-- MySQL: Monitor binlog position lag
SHOW MASTER STATUS;
SHOW REPLICA STATUS\G  -- Compare Exec_Master_Log_Pos vs Read_Master_Log_Pos
 
-- PostgreSQL: Check WAL disk usage
SELECT 
    pg_size_pretty(sum(size)) as wal_size,
    count(*) as wal_files
FROM pg_ls_waldir();
 
-- Alert: WAL exceeds threshold (shell script)
-- WAL_SIZE=$(psql -t -c "SELECT sum(size) FROM pg_ls_waldir()")
-- if [ $WAL_SIZE -gt 10737418240 ]; then alert "WAL > 10GB"; fi

Recovery Runbook Template:

Scenario	Detection	Recovery Action	RTO
Connector crash	No events for 5 min	Restart connector, resumes from offset	<5 min
Offset corruption	Events with wrong sequence	Stop, delete offset, re-snapshot	Hours
Slot deleted accidentally	Connector fails to connect	Recreate slot, re-snapshot	Hours
Source DB failover	Connection errors	Reconnect to new primary, may need re-snapshot	Minutes-Hours
Schema incompatibility	Serialization errors	Pause CDC, update consumers, resume	Hours

Summary: Database Log-Based CDC

We've deeply explored the technical mechanics of log-based CDC. Let's consolidate the key insights:

Key Takeaways

•Transaction logs are the foundation — Every change is already recorded in the database's WAL/binlog/oplog. CDC reads this authoritative record.
•Each database has unique log formats — PostgreSQL uses logical decoding, MySQL uses binlog, SQL Server has native CDC or log reading, MongoDB uses oplog/change streams.
•Connectors act as replica nodes — CDC connectors connect using replication protocols, streaming changes exactly as database replicas would.
•Offset tracking enables reliability — By recording position in the log, connectors can resume after failures without data loss.
•Schema evolution requires careful handling — Use schema registries and compatibility modes to handle schema changes gracefully.
•Performance tuning balances latency vs throughput — Smaller batches reduce latency; larger batches increase throughput. Tune for your SLAs.
•Operational concerns dominate production — Log retention, slot management, lag monitoring, and recovery planning are essential for reliable CDC.

What's Next:

Page Complete

2 / 5