system-design · intermediate

Change Data Capture (CDC) — Streaming Database Mutations

The Central Question

Consider an enterprise e-commerce platform running on the MessageLab platform (messagelab.com) processing 100,000,000 database mutations per day:

• Primary application data is stored in a PostgreSQL relational database.
• Four downstream data systems must stay in sync with the primary database in real time:

If application developers attempt to update all four downstream targets inside application code whenever a SQL mutation occurs, dual-write failures, race conditions, and application code pollution occur.

If an application pod crashes after updating PostgreSQL but before writing to Elasticsearch, search results become permanently out of sync (Data Drift Hazard).

To stream database modifications reliably without application dual-writes, cloud architectures deploy Change Data Capture (CDC).

Change Data Capture (CDC) is a software pattern that continuously monitors low-level database transaction logs (such as PostgreSQL WAL or MySQL Binlog) and streams every data modification (INSERT, UPDATE, DELETE) as a real-time event stream to downstream consumers.

This lesson answers one central question: How does Log-Based Change Data Capture (Debezium) mine transaction logs to eliminate dual writes, maintain ordering using WAL offsets, and stream outbox events with zero application performance degradation?

The Core Problem: Why Dual-Writes and Dual-Queries Fail

To understand why CDC is mandatory at scale, consider the two traditional alternative approaches to synchronizing database changes:

flowchart TD
  subgraph Dual-Write Pattern (High Failure Risk)
    App1[Application Code] -->|Step 1: SQL UPDATE| DB1[(PostgreSQL DB)]
    App1 -->|Step 2: Sync Write| ES1[(Elasticsearch)]
    App1 -->|Step 3: Sync Write| Redis1[(Redis Cache)]
    Note1["Crash Hazard! App crash mid-step causes catastrophic state divergence."]
  end
subgraph Periodic Polling Pattern (High Overhead)
    Poller[Background Batch Job] -->|SELECT * FROM table WHERE updated_at > t| DB2[(PostgreSQL DB)]
    Poller -->|Bulk Sync| ES2[(Elasticsearch)]
    Note2["Overhead Hazard! Full table scans hit DB CPU hard; misses DELETE statements."]
  end

Figure 1: Architectural comparison highlighting vulnerabilities in Dual-Writes and Polling.

Why Dual Writes Fail

1. No Transactional Atomicity: Updating a database and writing to an external search engine are two separate network operations. There is no distributed transaction spanning PostgreSQL and Elasticsearch.
2. Race Conditions: Concurrent requests Tx 1 and Tx 2 can update PostgreSQL in order Tx 1 -> Tx 2, but arrive at Elasticsearch in order Tx 2 -> Tx 1, leaving the search engine permanently corrupt.

Log-Based Change Data Capture Mechanics

Modern CDC engines (such as Debezium or AWS DMS) use Log-Based CDC, tapping directly into the database's non-volatile transaction log:

flowchart LR
  App[Application Code] -->|1. Standard SQL Mutations| Primary[(Primary PostgreSQL DB)]
  
  subgraph Internal Database Storage Engine
    Primary -->|2. Write WAL Record| WAL[(Write-Ahead Log / Binlog)]
  end
  
  subgraph CDC Streaming Pipeline
    WAL -->|3. Read WAL Delta Stream| Debezium[Debezium CDC Connector]
    Debezium -->|4. Publish Event Stream| Kafka((Kafka Broker Topic))
  end
  
  subgraph Downstream Consumers
    Kafka --> C1[Elasticsearch Sync Worker]
    Kafka --> C2[Redis Cache Invalidator]
    Kafka --> C3[Snowflake Data Lake Loader]
  end

Figure 2: Log-Based CDC streaming Write-Ahead Log (WAL) records directly to Kafka.

Operational Rules of Log-Based CDC

1. Zero Application Overhead: Application microservices issue standard SQL statements to the database without any CDC code or extra network calls.
2. Log Mining: The CDC engine (e.g. Debezium) connects to the database as a logical replication follower, reading raw WAL binary records.
3. Capture DELETES: Unlike SQL polling, log-based CDC captures row DELETE operations from the WAL and emits tombstone events.

Debezium Event Payload Schema

A typical Debezium CDC event contains a rich, structured representation of the exact before and after row states:

{
  "schema": { "type": "struct", "optional": false },
  "payload": {
    "before": {
      "id": 89041,
      "email": "alice@oldmail.com",
      "status": "ACTIVE"
    },
    "after": {
      "id": 89041,
      "email": "alice@newmail.com",
      "status": "ACTIVE"
    },
    "source": {
      "version": "2.4.0.Final",
      "connector": "postgresql",
      "db": "messagelab_db",
      "table": "users",
      "lsn": 240591024,
      "ts_ms": 1721820000100
    },
    "op": "u",
    "ts_ms": 1721820000105
  }
}

Operation Codes (op)

• "c": Create (INSERT)
• "u": Update (UPDATE)
• "d": Delete (DELETE)
• "r": Read (Initial snapshot dump)

Debezium Outbox Event Router

• The Outbox Event Router intercepts rows written to the outbox_events table.
• It transforms the raw table record into a clean, domain-driven event payload (OrderPlaced), sets the Kafka topic name dynamically based on aggregate_type, and routes the event to the appropriate topic (orders.events).
• This decouples internal relational database table schemas from public microservice event contracts.

Lock-Free Incremental Snapshots (Debezium Watermark Snapshots)

• The CDC engine reads small chunks (e.g. 10,000 rows) of the historical table using primary key range queries (WHERE id BETWEEN 1 AND 10000).
• It inserts signal watermark records into a signal table to track concurrent live WAL writes occurring during the chunk read.
• This allows CDC connectors to snapshot 100M+ row tables incrementally without taking database locks or disrupting live user transactions.

Handling Schema Drift in CDC Pipelines

• Debezium streams schema DDL events to a dedicated schema-changes.products Kafka topic.
• Downstream ingestion connectors (e.g. Snowflake Sink) automatically apply the DDL migration to target warehouse tables before inserting rows, guaranteeing zero schema drift outages.

LSN Checkpointing and Disaster Recovery

• If a Debezium container instance crashes and restarts on a new Kubernetes worker node, it reads its last committed LSN token from Kafka storage.
• It resumes reading PostgreSQL WAL logs from the exact LSN offset where it left off, guaranteeing Zero Event Loss across infrastructure restarts.

Database Replica Identity (REPLICA IDENTITY FULL)

• By default, PostgreSQL WAL logs only record modified columns in update events.
• Setting replica identity to FULL forces PostgreSQL to log all un-modified column values as well, providing complete before-and-after snapshots for downstream cache invalidation engines.

PostgreSQL Logical Decoding Plugins (pgoutput)

Complete Worked Example: Go Debezium WAL CDC Log Streaming Engine

Let's inspect a complete Go implementation of a WAL CDC Log Streaming Engine for the MessageLab platform (messagelab.com).

package main
import (
	"context"
	"fmt"
	"sync"
	"time"
)
type WALLogEntry struct {
	LSN       uint64    // Log Sequence Number
	TableName string
	Op        string    // "c", "u", "d"
	Before    map[string]interface{}
	After     map[string]interface{}
	Timestamp time.Time
}
type CDCConnector struct {
	mu           sync.RWMutex
	lastLSN      uint64
	kafkaChannel chan WALLogEntry
}
func NewCDCConnector(bufferSize int) *CDCConnector {
	return &CDCConnector{
		lastLSN:      0,
		kafkaChannel: make(chan WALLogEntry, bufferSize),
	}
}
func (c *CDCConnector) StartWALReader(ctx context.Context, walStream <-chan WALLogEntry) {
	fmt.Println("[CDC ENGINE INIT] Connected to PostgreSQL Logical Replication Slot.")
for {
		select {
		case <-ctx.Done():
			return
		case entry, ok := <-walStream:
			if !ok {
				return
			}
			c.processWALEntry(entry)
		}
	}
}
func (c *CDCConnector) processWALEntry(entry WALLogEntry) {
	c.mu.Lock()
	if entry.LSN <= c.lastLSN {
		c.mu.Unlock()
		fmt.Printf("[CDC WARN] Duplicate LSN %d detected. Skipping.\n", entry.LSN)
		return
	}
	c.lastLSN = entry.LSN
	c.mu.Unlock()
fmt.Printf("[CDC WAL STREAM] LSN: %d | Table: %s | Op: %s | Time: %s\n",
		entry.LSN, entry.TableName, entry.Op, entry.Timestamp.Format(time.RFC3339))
// Publish CDC Event to Kafka Channel
	select {
	case c.kafkaChannel <- entry:
		fmt.Printf("[CDC KAFKA PUBLISH] Streamed LSN %d to Kafka topic 'db.%s'\n", entry.LSN, entry.TableName)
	default:
		fmt.Printf("[CDC ALERT] Kafka channel buffer full! Applying backpressure on LSN %d\n", entry.LSN)
	}
}

Failure Modes and Engineering Mitigations

What You Should Remember

1. CDC eliminates application dual-writes: Mine low-level database transaction logs (WAL/Binlog) to stream real-time change events without application code changes.
2. Log-based CDC has zero query overhead: CDC connectors read transaction logs as logical replication followers, avoiding heavy SQL polling queries.
3. Capture DELETES and Tombstones: Unlike table polling, log-based CDC captures row DELETE operations and emits tombstone events.
4. Partition CDC topics by Primary Key: Always use the table primary key as the Kafka partition key to preserve per-row mutation order.
5. Monitor Replication Slot WAL Retention: Set max_slot_wal_keep_size on PostgreSQL to prevent disconnected CDC slots from filling database disks.

Glossary of Terms

Practice Scenario and Self-Assessment

Architecture Scenario

• Property listing database (100,000,000 rows in PostgreSQL).
• Elasticsearch cluster must mirror all property edits, price drops, and deleted listings within 500ms.

1. Formulate the Debezium CDC pipeline architecture, Kafka topic partitioning, and LSN offset tracking strategy.
2. Detail how your CDC engine handles initial 100M row bootstrapping without locking production tables.

Interactive Self-Assessment

Log-based CDC mines transaction logs atomically, capturing 100% of mutations without application code changes or dual-write crash risks.

Log-based CDC automatically formats persistent NVMe SSD disk drives on database servers.

Log-based CDC revokes client HTTPS TLS encryption certificates on edge load balancers.

Log-based CDC doubles the physical hardware clock speed of primary database CPUs.

PostgreSQL retains all WAL logs on disk for the disconnected slot until the hard drive reaches 100% capacity, crashing the database.

The replication slot automatically converts relational database primary key indexes into un-indexed CSV files.

The replication slot replaces public DNS nameservers with local hosts file entries.

The replication slot reboots operating system hypervisors across all database nodes.

What to Learn Next

• Dead-Letter Queues & Poison Messages: Master poison message quarantine workflows.
• Event-Driven Architecture (EDA): Revisit Transactional Outbox patterns.
• Publish-Subscribe (Pub-Sub) — Fan-Out Event Distribution: Revisit topic partitioning and consumer groups.

Track: Data, Storage and Messaging

Previous: CDC vs Dual Writes — Keeping Two Stores in Sync

Next: Dead-Letter Queues and Poison Messages — Quarantine Work That Keeps Failing

By Shubham Jain

All articles · Study paths

Shubham Jain · Learning Lab