Change Data Capture: Streaming the Database's Inner Monologue

TL;DR: Every committed row change is already written to a durable log (Postgres WAL, MySQL binlog). Change Data Capture (CDC) exposes that log as a public event stream so downstream systems (warehouse, search, cache, audit) stay in sync without the application dual-writing to each of them. Log-based CDC via Debezium is the default for most teams. The transactional outbox pattern is the alternative when you control the application and want atomicity without connector infrastructure. Shopify processes tens of millions of Kafka messages per second at peak using outbox-driven CDC¹. Netflix’s DBLog introduced watermark-based incremental snapshots that let you backfill new consumers without locking the source². Dual writes are the enemy; the database log is the cure.

Learning Objectives

After this module, you will be able to:

• Explain log-based CDC and how Debezium reads Postgres WAL or MySQL binlog
• Compare log-based, trigger-based, and query-based CDC approaches
• Implement the transactional outbox pattern as an alternative to CDC
• Handle schema evolution, backfill, and ordering guarantees
• Design a CDC pipeline feeding a warehouse, cache, and search index

Intuition

You keep a personal journal. Every night you write down what happened that day. Your spouse reads the journal to stay informed. Your accountant reads it to track expenses. Your therapist reads it to spot patterns.

None of these people ask you to repeat yourself. None of them need you to call them individually after every event. They all consume the same journal at their own pace. If your accountant goes on vacation for a week, they catch up by reading the entries they missed.

Your database already keeps this journal. Postgres calls it the write-ahead log (WAL). MySQL calls it the binary log (binlog). Every INSERT, UPDATE, and DELETE is recorded there for crash recovery and replication. Change Data Capture is the practice of opening that journal to the outside world so that search indexes, data warehouses, caches, and audit systems can all read it independently, at their own speed, without you writing to each of them separately.

The alternative is calling each consumer yourself after every database write. That is the dual-write problem, and it is the single most common source of silent data inconsistency in microservice architectures. This chapter shows you why dual writes break, how CDC fixes them, and when to use the outbox pattern instead.

Theory

The dual-write problem

Consider this pseudocode in a checkout service:

def place_order(order):
    db.execute("INSERT INTO orders ...", order)
    kafka.produce("order.created", order)

Two writes. Two systems. No shared transaction. Three things can go wrong:

1. Partial failure. The INSERT commits, the process crashes before kafka.produce, and the event is silently lost. The warehouse never sees the order.
2. Race condition. Two concurrent requests write status=shipped and status=cancelled to the same order. The database and Kafka see the writes in different orders. They permanently disagree.³
3. No detection. Both operations “succeed” from the application’s perspective. No error is raised. The divergence is invisible until a customer complains.

Martin Kleppmann named this problem explicitly in 2015: dual writes produce permanent inconsistency, not eventual consistency.³ You cannot fix it with retries. You cannot fix it with distributed transactions (Kafka does not support XA). The only fix is to pick one source of truth and derive everything else from it.

sequenceDiagram
    participant App as Checkout Service
    participant DB as Postgres
    participant K as Kafka
    participant ES as Elasticsearch

    App->>DB: INSERT INTO orders (id=42)
    DB-->>App: OK
    Note over App: Process crashes here
    App-xK: produce("order.created", id=42)
    Note over K,ES: Event never arrives
    Note over DB,ES: DB has order 42, ES does not. Forever.

CDC eliminates dual writes by making the database log the single source of truth. Downstream systems consume that log. The application writes to one place. Everything else is derived.

Log-based CDC

A log-based CDC tool impersonates a database replica. It opens a replication slot (Postgres) or registers as a binlog client (MySQL), reads committed changes in commit order, enriches them with schema metadata, and publishes to Kafka.⁴

Postgres logical decoding. Since Postgres 10, the pgoutput plugin decodes WAL entries into a logical stream of row changes. Debezium opens a replication slot, consumes the stream, and publishes one Kafka message per row change. The slot tracks the consumer’s position (the restart_lsn), so Postgres retains WAL segments until the consumer acknowledges them.⁵

MySQL binlog. MySQL’s binary log in ROW format records the before and after images of every modified row. Debezium registers as a replication client with REPLICATION SLAVE privileges and reads the binlog in real time. Unlike Postgres, MySQL uses time-based retention (binlog_expire_logs_seconds) rather than consumer-driven retention.

The Debezium envelope. Every change event carries a standard envelope:

{
  "before": null,
  "after": {"id": 1, "email": "anne@example.com"},
  "source": {"connector": "postgresql", "txId": 555, "lsn": 24023128},
  "op": "c",
  "ts_ms": 1559033904863
}

The op field encodes the operation: c (create), u (update), d (delete), r (snapshot read). For updates, before contains the prior row (if REPLICA IDENTITY FULL is set) and after contains the new row. For deletes, before is populated and after is null, followed by a tombstone for Kafka log compaction.⁴

flowchart LR
    App[Checkout Service] -->|writes| PG[(Postgres\nWAL + pgoutput)]
    PG -->|replication slot| DZ[Debezium\nKafka Connect]
    DZ -->|dbz.public.orders| K[Apache Kafka]
    DZ -->|dbz.public.line_items| K
    K --> ES[Elasticsearch\nindexer]
    K --> SF[Snowflake\nsink connector]
    K --> R[Redis\ncache updater]
    K --> F[Flink\nstream processor]

Trigger-based and query-based CDC

Not every database exposes a consumable log. Two older strategies fill the gap:

Trigger-based CDC. Database triggers on INSERT, UPDATE, and DELETE write a row to a shadow changelog table. A worker polls the changelog and publishes events. Uber’s Schemaless Triggers uses exactly this pattern: each mutation fires a trigger that feeds trip processing, billing, and warehouse pipelines.⁶ The cost is real: every write pays extra latency in the commit path (trigger invocation plus shadow-table INSERT), adding significant write overhead.

Query-based CDC (polling). A worker issues SELECT * FROM t WHERE updated_at > :checkpoint on a timer, publishes results, and advances the checkpoint. LinkedIn’s original Oracle data capture used SQL-based polling before moving to Oracle GoldenGate-based streaming.⁷ It requires zero infrastructure beyond a cron job and works against read replicas. But it cannot capture deletes (deleted rows do not match the WHERE clause), adds query load proportional to polling frequency, and races on the checkpoint timestamp.

Use log-based CDC when your database supports it. Use triggers only when it does not. Use polling only for prototypes or low-frequency batch sync.

The transactional outbox pattern

The outbox pattern pushes coordination into the application. Within the same local ACID transaction as the business write, insert a row into an outbox table. A relay publishes outbox rows to Kafka asynchronously.⁸

BEGIN;
INSERT INTO purchase_orders (id, customer_id, total) VALUES (...);
INSERT INTO outbox (aggregate_type, aggregate_id, type, payload)
  VALUES ('Order', '42', 'OrderCreated', '{"total": 99.95}');
COMMIT;

Since both rows commit atomically, the outbox row exists if and only if the business row does. No distributed transaction. No dual write.

The relay can be a simple poller or, more elegantly, Debezium itself reading the outbox table’s WAL entries. Gunnar Morling’s implementation uses a clever trick: the application persist()s the outbox row and immediately remove()s it in the same transaction. Debezium reads the INSERT from the WAL (publishing the full event) and ignores the DELETE (via the Outbox Event Router SMT filtering op == 'd'). The outbox table stays empty on disk, so no housekeeping cron is needed.⁸

sequenceDiagram
    participant Client
    participant Svc as Order Service
    participant DB as Postgres
    participant DZ as Debezium
    participant K as Kafka
    participant Ship as Shipment Service

    Client->>Svc: POST /orders
    Svc->>DB: BEGIN
    Svc->>DB: INSERT purchase_orders
    Svc->>DB: INSERT outbox (persist + remove)
    Svc->>DB: COMMIT
    Svc-->>Client: 201 Created
    Note over DB,DZ: WAL entry visible
    DB-->>DZ: INSERT event via replication slot
    DZ->>K: produce to Order.events (key=aggregate_id)
    K-->>Ship: consume Order.events
    Ship->>Ship: dedupe by event_id, create shipment

The aggregate_id field becomes the Kafka message key, so all events for one order hash to the same partition. This preserves per-aggregate ordering without global coordination.

Ordering, partitioning, and schema evolution

Ordering. Message Queues and Streaming established that Kafka guarantees ordering within a partition but not across partitions. CDC tools use the primary key (or aggregate_id in the outbox case) as the message key. All events for the same row land on the same partition, giving per-entity total order with consumer parallelism across entities.⁴

Transaction boundaries. Debezium optionally emits transaction metadata (BEGIN/END events with txId:LSN and per-table event counts) so consumers can reconstruct multi-table transactions when needed.

Schema evolution. Logical decoding does not replicate DDL.⁴ A migration that adds a NOT NULL column or drops a column can break downstream consumers. The defense is a Schema Registry with Avro serialization and BACKWARD compatibility mode: new consumers can read old data, so you may only add nullable fields with defaults or remove fields. Test every migration against the registry’s compatibility rules before deploying.

TOAST columns. On Postgres with default REPLICA IDENTITY, update events omit unchanged TOASTed (large) columns. Debezium substitutes the placeholder __debezium_unavailable_value. If your consumers need full before-images, set ALTER TABLE t REPLICA IDENTITY FULL on the affected tables (expect a moderate CPU increase on the source; one benchmark showed peak CPU rising from 30% to 35%).⁵

Incremental snapshots and backfill

A new consumer (say, a fresh Elasticsearch index) needs the full dataset, not just future changes. The naive approach, a SELECT * dump, locks the table and stalls streaming for hours.

Netflix’s DBLog solved this with watermark-based chunked snapshots.² The algorithm:

1. Write a LOW watermark to a dedicated watermark table.
2. SELECT a chunk of rows (default 1,024 rows in Debezium’s port).⁹
3. Write a HIGH watermark.
4. Any log events between LOW and HIGH that collide with chunk rows win. Drop the stale chunk rows.
5. Emit non-colliding chunk rows, then resume live streaming.

sequenceDiagram
    participant DB as Source DB
    participant CDC as DBLog / Debezium
    participant Out as Kafka

    CDC->>DB: UPDATE watermark = LOW
    DB-->>CDC: log event LOW
    CDC->>DB: SELECT chunk (1024 rows)
    CDC->>DB: UPDATE watermark = HIGH
    DB-->>CDC: log events in window
    DB-->>CDC: log event HIGH
    Note over CDC: Drop chunk rows that collide with log events
    CDC->>Out: emit non-colliding chunk rows
    CDC->>Out: resume live log streaming

This runs without table locks, is triggerable per-table or per-primary-key at any time, and was ported into Debezium 1.6+ as incremental snapshots via a signal table.⁹

Real-World Example

Shopify: outbox-driven CDC at commerce scale

Shopify’s modular-monolith Rails application uses the transactional outbox pattern to reliably emit domain events. The service writes the business row and an outbox row in the same transaction on its shop-sharded MySQL cluster. A relay (Kafka Connect with Debezium or a Shopify-internal reader) publishes outbox events to Kafka.¹

Scale. During Black Friday / Cyber Monday 2024, Shopify moved 57.3 PB of data, processed 10.5 trillion database queries and 1.17 trillion writes, and sustained 80 million requests per minute on application servers. Kafka handled tens of millions of messages per second at peak.¹

Architecture. Regional Kafka clusters mirror into a single aggregate cluster that feeds the data warehouse. Application-specific consumers (search index, analytics, inventory stream) subscribe to the aggregate topics. The Pods architecture shards stores across isolated pods, each with its own database cluster, so outbox scaling is horizontal by shop_id.

Key decisions:

• Aggregate cluster pattern. Regional clusters mirror to one aggregate so downstream warehouse and analytics have a single topic to consume, simplifying consumer fan-out.
• StatefulSets on Kubernetes. Brokers run on persistent volumes with inter-pod anti-affinity so they never share a node. Rack-awareness maps brokers across zones. Readiness probes gate rolling restarts so only one broker restarts at a time.
• Own their Kafka image. Shopify builds its own Kafka container image rather than depending on a third-party registry, because a changed upstream image in a payment-critical pipeline is unacceptable.

The lesson: the outbox pattern scales to trillion-write workloads when combined with horizontal sharding and a well-operated Kafka tier.

Trade-offs

Common Pitfalls

Exercise

You operate a Postgres cluster serving checkout at 5,000 writes/sec. Design a CDC pipeline that keeps Elasticsearch, a Snowflake warehouse, and a Redis cache in sync. Specify lag budgets, failure handling, and how you backfill a new consumer without impacting production.

Key Takeaways

• Dual writes (DB + message bus without a shared transaction) produce permanent inconsistency, not eventual consistency. CDC eliminates them by making the database log the single source of truth.
• Log-based CDC reads the existing replication stream (WAL, binlog) with minimal source impact. Use it as the default.
• The transactional outbox pattern provides atomicity without connector infrastructure by writing business rows and event rows in one local transaction.
• Debezium’s incremental snapshot (ported from Netflix DBLog) lets you backfill new consumers without table locks or stalling the live stream.
• Per-key partitioning (primary key or aggregate ID as Kafka message key) gives per-entity total order with consumer parallelism across entities.
• Schema evolution requires a Schema Registry with compatibility rules. Test migrations against the registry before deploying.
• Replication slot bloat on Postgres is the most common operational failure. Set max_slot_wal_keep_size, enable heartbeats, and alert on retained WAL size.

Further Reading

• Using logs to build a solid data infrastructure (or: why dual writes are a bad idea) - Kleppmann’s 2015 Craft Conference talk that names the dual-write problem and frames CDC as the solution; the intellectual foundation for this chapter.
• DBLog: A Generic Change-Data-Capture Framework - Netflix’s watermark-based snapshot algorithm explained with diagrams; read this to understand how incremental snapshots work without locks.
• Reliable Microservices Data Exchange With the Outbox Pattern - Morling’s implementation guide for the outbox pattern including the persist-then-remove trick and consumer deduplication.
• Mastering Postgres Replication Slots - The definitive guide to preventing WAL bloat, monitoring slot health, and configuring failover slots on Postgres 17+.
• Debezium connector for PostgreSQL - The reference for envelope format, replica identity, topic naming, TOAST handling, and all connector properties.
• Open sourcing Databus: LinkedIn’s low latency CDC system - The original three-tier CDC architecture (relay, bootstrap, client) that influenced every subsequent system including DBLog.
• Incremental Snapshots in Debezium - How the DBLog watermark algorithm was ported into Debezium 1.6+ with signal-table triggers and chunk reconciliation.
• Pattern: Transactional outbox - Chris Richardson’s concise pattern reference; useful as a quick refresher on the mechanics.

Flashcards

References