Change Data Capture (CDC) & Outbox Pattern

1. What It Is & Why It Exists

The Core Problem: The Dual-Write Desynchronization Trap

In distributed systems, microservices frequently need to update a primary database and simultaneously notify other systems (e.g. invalidating a Redis cache, updating an Elasticsearch index, or broadcasting an event to Apache Kafka):

Synthesizing vector architecture diagram...

If the application attempts to execute two separate network write operations ("Dual-Writing"):

1. DB Succeeds, Broker Fails: Downstream consumers never receive the event (permanent data loss).
2. Broker Succeeds, DB Fails: Downstream consumers process ghost records that do not exist in the primary database.
3. Concurrent Races: Two concurrent updates execute in order $A \to B$ in SQL, but arrive in order $B \to A$ in Kafka, corrupting search indexes.

The First-Principles Solution: Transactional Outbox & Log-Based CDC

To guarantee atomicity without a distributed transaction (two-phase commit across the database and the broker), the event publication must be bounded inside the same local ACID database transaction as the business data mutation. A relay (a log reader or a poller) then publishes it after the commit, at least once. See Change Streams & the Transactional Outbox.

2. Core Mechanics: Transactional Outbox vs. Log-Based CDC

Synthesizing vector architecture diagram...

Follow the numbered steps from the top. In the "Single Local ACID Transaction" panel, the service begins one transaction (1), inserts the order (2) and an outbox event describing it (3), and commits (4), so both rows are saved or neither is, and no event can exist for an order that was never saved. In the "Log-Based CDC Extraction Tier" panel, Debezium or DMS reads the database's WAL or binlog, picks out new outbox rows, and publishes them to Kafka or Kinesis. In the "Asynchronous Consumer Execution" panel, consumers update search, delete stale cache keys, and trigger billing. The service never writes to two systems in one step, which is exactly what makes dual writes inconsistent; delivery is at least once, so consumers must handle duplicates.

Outbox Table Schema & DDL

sqlCopy
CREATE TABLE outbox_events (
    event_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    aggregate_type VARCHAR(64) NOT NULL,    -- e.g. "ORDER"
    aggregate_id VARCHAR(64) NOT NULL,      -- e.g. "ord_10294"
    event_type VARCHAR(64) NOT NULL,        -- e.g. "ORDER_CREATED"
    payload JSONB NOT NULL,                 -- Full event payload
    headers JSONB,                          -- Trace ID, correlation ID
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
-- With log-based CDC the row only has to exist in the WAL, not in the table: Debezium's outbox
-- event router reads the INSERT from the log, so the application may DELETE the row in the same
-- transaction (or a sweeper can purge it later) and the table never bloats.
-- Application Atomic Write Transaction
BEGIN;
INSERT INTO orders (order_id, user_id, amount_cents, status) 
VALUES ('ord_10294', 'usr_881', 4999, 'PAID');

INSERT INTO outbox_events (aggregate_type, aggregate_id, event_type, payload) 
VALUES ('ORDER', 'ord_10294', 'ORDER_CREATED', '{"order_id": "ord_10294", "amount_cents": 4999}');
COMMIT;

3. Comparison Matrix: CDC Extraction Mechanisms