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

Which Relay to Start With, and When to Move

One recommendation, with timing: start with a polling relay that claims rows with FOR UPDATE SKIP LOCKED and is woken by a signal after each commit. Move to log-based CDC (or to a broker) when one of the signals below appears. Do not start with CDC for a single service, because it adds a connector, a broker and a replication slot that nobody needs yet. Do not stay with polling after the signals appear, because every extra relay adds claim and delete load to the one database you cannot scale out.

A wake-up signal is a small message that the writer sends to the relay right after its transaction commits, so the relay does not wait for the next timer tick. The timer stays as a safety net, because a signal can be lost (the process restarts, or a row is written by another system). The step-by-step version, with the numbers for 6,000 events a second, is in Outbox loop, step 2.1.

Synthesizing vector architecture diagram...

Follow the arrows from the top. The outbox table and the business transaction stay the same at every step. Only the way the row is noticed and sent changes. Each arrow is labelled with the signal that justifies paying for the next step.

The signals, in the words of the two guides:

Wake-up options compared, including the LISTEN/NOTIFY ceiling

The ceiling: PostgreSQL takes a global lock during the commit of every transaction that ran NOTIFY, so those commits happen one at a time. One published write-up measured about 2,900 writes a second as the limit. A writer that commits thousands of transactions a second should not use NOTIFY for its outbox. This is the same figure that Outbox loop, step 2.1 teaches; it is a measurement from one source, so measure your own system before relying on it.

Three Ways to Claim an Outbox Row

All three use FOR UPDATE SKIP LOCKED, which lets each worker lock the rows it takes and skip rows another worker has locked. They differ in how long the claim lasts and in what is left behind when the work is done. The first two are claim shapes. The third is a choice of ending that can follow either of them.

Synthesizing vector architecture diagram...

Choose the claim by how long the work takes, then choose the ending by whether you need the rows later.

Rule of thumb: hold the row lock across a short publish; commit a lease when handlers run long; keep and mark only when you will use the kept rows, and purge them on a schedule. Whatever you choose, count a reclaim as an attempt so that a message that keeps killing its worker ends in the dead-letter table.