Outbox and change data capture

Generated by Claude through GigAI (karthik446/gigai-learning-center 4c0cb514) on 2026-10-13.

Concept: Outbox and change data capture · also covers Message queues and event streams, Data pipelines, PostgreSQL, Kafka · Lesson 18 of 31 in System design · version 1

Practice this

On this page

A service often needs to change its own database and tell other systems about it, and doing those as two separate writes will eventually leave them out of sync. The outbox pattern makes the message part of the same local transaction, and change data capture reads the database's own log to publish it reliably. Together they turn the database commit log into the single source of ordered truth that everything else derives from.

Read more

How it is done in practice

A team adopting this starts with one service whose events others depend on. They add an outbox table, change the write path to insert the event in the same transaction, and deploy a Debezium connector on Kafka Connect with the outbox event router, capturing only the outbox table. Events get a stable schema in a registry and a version field. Consumers add an inbox table for deduplication. Operations monitor replication slot lag, connector status and end to end delay from commit to consumption, and schedule cleanup of old outbox rows. Raw table CDC is kept for internal uses such as search indexing and warehouse loading.

Technologies

DebeziumKafka ConnectApache KafkaPostgreSQL logical decodingMySQL binlogAmazon DynamoDB StreamsSchema Registry

Diagram

flowchart LR
    S[Order service] -->|one transaction| DB[(Orders table and outbox table)]
    DB -->|write ahead log| CDC[Debezium connector]
    CDC -->|outbox event router| K[Kafka topic keyed by aggregate id]
    K --> C1[Shipping service with inbox]
    K --> C2[Search indexer]
    K --> C3[Cache invalidator]
flowchart LR
    S[Order service] -->|one transaction| DB[(Orders table and outbox table)]
    DB -->|write ahead log| CDC[Debezium connector]
    CDC -->|outbox event router| K[Kafka topic keyed by aggregate id]
    K --> C1[Shipping service with inbox]
    K --> C2[Search indexer]
    K --> C3[Cache invalidator]

Code

sql
BEGIN;
UPDATE orders SET status = 'PAID' WHERE id = 4711;
INSERT INTO outbox (id, aggregatetype, aggregateid, type, payload)
VALUES (gen_random_uuid(), 'order', '4711', 'OrderPaid',
        '{"orderId": 4711, "amount": 99.50}');
COMMIT;
json
{
  "connector.class": "io.debezium.connector.postgresql.PostgresConnector",
  "plugin.name": "pgoutput",
  "database.hostname": "HOST",
  "database.dbname": "orders",
  "topic.prefix": "shop",
  "table.include.list": "public.outbox",
  "transforms": "outbox",
  "transforms.outbox.type": "io.debezium.transforms.outbox.EventRouter"
}

Subtopics

The dual write problem

Why two writes fail

A handler that commits to its database and then publishes to a broker has no transaction covering both. If the process crashes after the commit, the event is never sent and downstream systems never learn of the change. If it publishes first and the commit then fails, consumers act on something that did not happen. Retrying does not help, because the crash can occur between the two steps on any attempt. Concurrent requests can also publish in a different order than they committed. The result is silent drift that is found much later through reconciliation.

The principle

The fix is to write to exactly one system synchronously and derive everything else from it. Since the service's database already provides atomic local transactions and an ordered commit log, it becomes the single place where the decision is recorded. Publishing becomes an asynchronous, retryable step driven from that record. This gives eventual consistency between services with a clear direction of flow, and no distributed transaction across the database and the broker.

The transactional outbox table

How it works

The service adds an outbox table to its own database. In the same transaction that changes business tables, it inserts a row describing the event: a unique event id, the aggregate type and id, an event type and a payload. Either both the state change and the event row commit, or neither does. A separate relay then reads new outbox rows and publishes them to the broker. If the relay crashes after publishing but before recording progress, it publishes again, so delivery is at-least-once and consumers must deduplicate on the event id.

Relay options

A polling relay queries the table for unpublished rows, publishes and marks or deletes them. It is simple but adds latency and load, and ordering needs care when transactions commit out of sequence order. A log based relay uses change data capture to read the inserts from the database log in commit order with low latency and no polling queries. Either way the outbox table needs cleanup, since it is a transit buffer and not a history store. The payload is a deliberate public contract, decoupled from the internal table schema.

Log based change data capture

Reading the database log

Every database writes changes to a durable log before applying them: the write ahead log in PostgreSQL, the binlog in MySQL. Change data capture tails that log and emits one event per committed row change, in commit order, without touching application code. Debezium does this as a set of Kafka Connect source connectors. For PostgreSQL it uses logical decoding through a replication slot and an output plugin such as pgoutput, takes an initial snapshot of existing rows, then streams inserts, updates and deletes with before and after images of the row.

Operational concerns

A replication slot makes the database retain log segments until the connector confirms it has consumed them. If the connector stops, the log accumulates and can fill the disk, so slot lag must be monitored and alerted on. The connector stores its log position as offsets and resumes from there after a restart, which gives at-least-once delivery. Schema changes flow through as changed event shapes, so consumers need a compatibility policy. Raw table CDC exposes internal schema to consumers; routing an outbox table through CDC avoids that coupling.

Routing outbox events and consuming them safely

From outbox rows to topics

Debezium provides an outbox event router, a single message transform that reshapes captured outbox inserts into clean events. By default it expects columns for the event id, aggregate type, aggregate id, event type and payload. It routes each event to a topic derived from the aggregate type and uses the aggregate id as the record key, so all events for one aggregate go to one partition and keep their order. The event id is carried along so consumers can detect duplicates.

Consumer side

Consumers treat delivery as at-least-once. The standard approach is an inbox: record each processed event id in the consumer's own database in the same transaction as the effect, and skip ids already present. Where possible make the effect naturally idempotent, such as an upsert keyed by aggregate id with a version check that ignores older versions. Because events are ordered per key only, a consumer must not assume order between different aggregates.

The log as source of truth for derived data

Turning the database inside out

A change stream generalizes beyond service integration. Caches, search indexes, analytics tables and read models are all derived data: each can be seen as a materialized view computed from an ordered log of changes. Instead of the application writing to the database, the cache and the index separately, it writes once, and stream consumers maintain each view. A view that is wrong or needs a new shape can be rebuilt by replaying the log from the start. The cost is that views lag the source, so readers must tolerate or work around staleness, for example by reading their own writes from the primary.

Sources

Related lessons

Practice this
Cost as a design constraintDesign exercise: URL shortener and key-value service