Transactional outbox and change data capture

Many services must update their database and also tell others about it: save the order, then publish OrderPlaced to Kafka. Doing these as two separate steps is the dual-write problem. If the database commit succeeds and the publish fails (broker down, process killed), the order exists but nobody hears about it. If you publish first and the commit fails, consumers act on an order that does not exist. There is no shared transaction across Postgres and Kafka.

The transactional outbox fixes this by using only the database's own transaction. The service inserts the business row and an outbox row (the event) in the same local transaction. Either both commit or neither does. A separate relay then reads committed outbox rows and publishes them to the broker, retrying until it succeeds. Delivery is at-least-once, so consumers deduplicate by event id.

Change data capture (CDC) reads the database's replication log (Postgres WAL via logical decoding, MySQL binlog, MongoDB change streams, DynamoDB Streams) and turns each committed insert, update and delete into an event. Debezium is the most common open-source tool; it runs on Kafka Connect. CDC can be used two ways: tail the outbox table (the outbox pattern with a log-based relay; Debezium ships an outbox event router for this), or capture changes from the business tables directly (useful for syncing search indexes, caches and warehouses).

The difference matters. Outbox events are designed, stable domain events (OrderPlaced with the fields consumers need). Raw table CDC exposes your internal schema to every consumer, so a column rename can break them. Use outbox for service-to-service events, raw CDC for data replication you own end to end.

1. 1Create an outbox table: id (UUID, used as the event id), aggregate_type, aggregate_id (the partition key, for example order_id), event_type, payload (JSON or Avro/Protobuf bytes), created_at, and published_at for the polling approach.
2. 2In the same transaction as the business change, insert the outbox row. No call to Kafka happens inside the request path, which also keeps latency low.
3. 3Relay option A, polling: a worker selects unpublished rows in order with FOR UPDATE SKIP LOCKED, publishes them with key = aggregate_id, then marks them published. Simple, works anywhere, but adds polling load and some delay (typically 100 ms to 1 s).
4. 4Relay option B, log tailing: Debezium reads the Postgres WAL through a logical replication slot (pgoutput plugin) and emits each new outbox row to Kafka, routing by event type and keying by aggregate id. Lower latency, no polling queries, but more infrastructure.
5. 5Keep the outbox small: delete published rows after a short time (or, with log-based CDC, delete them right after insert in the same transaction, since the WAL still contains the insert).
6. 6Ordering: use the aggregate id as the Kafka key so events for one order stay in one partition. The WAL already gives commit order; with polling, order by an increasing column per aggregate.
7. 7Consumers are idempotent: they record the outbox id as processed in the same transaction as their own side effect (see delivery guarantees).
8. 8Operate CDC carefully: a Postgres replication slot keeps WAL until the connector confirms it. If the connector stops for hours, WAL piles up and can fill the disk, so alert on slot lag and set max_slot_wal_keep_size.

-- Write the business row and the event in ONE local transaction.
BEGIN;
INSERT INTO orders (id, user_id, total, status)
VALUES ('ord_981', 42, 54900, 'PLACED');

INSERT INTO outbox (id, aggregate_type, aggregate_id, event_type, payload, created_at)
VALUES (gen_random_uuid(), 'order', 'ord_981', 'OrderPlaced',
        '{"order_id":"ord_981","user_id":42,"total":54900}', now());
COMMIT;

-- Relay (polling version): many relays can run safely with SKIP LOCKED.
-- In one transaction: lock a batch, publish it to Kafka (key = aggregate_id),
-- then mark it sent. A crash before the UPDATE means the batch is published
-- again, so consumers must dedupe on outbox.id.
SELECT id, aggregate_id, event_type, payload
FROM outbox
WHERE published_at IS NULL
ORDER BY created_at
LIMIT 500
FOR UPDATE SKIP LOCKED;

UPDATE outbox SET published_at = now() WHERE id = ANY(:published_ids);