The Outbox Pattern, in Three Databases
The dual-write problem kills distributed systems quietly. You update the database, then publish the event. Between those two operations, the process crashes — the database has the new state, the event broker does not. Downstream consumers never learn what happened.
The outbox pattern solves this by making the event publication part of the same transaction as the business write. But "same transaction" means very different things depending on which database you are using, and each database creates its own failure modes.
Outbox Semantics
The core idea is simple: instead of publishing directly to a message broker, write a row to an outbox table inside the same database transaction as your business write. A separate relay process reads from the outbox and publishes to the broker.
The relay delivers at-least-once. Consumers must be idempotent — deduplication belongs downstream, not in the relay.
Postgres: The Reference Implementation
Postgres gives you two delivery mechanisms: polling and CDC via logical replication.
Schema
CREATE TABLE outbox (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
aggregate_type TEXT NOT NULL, -- 'Order', 'Payment', etc.
aggregate_id TEXT NOT NULL,
event_type TEXT NOT NULL,
payload JSONB NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
published_at TIMESTAMPTZ -- NULL = unpublished
);
CREATE INDEX ON outbox (published_at, created_at)
WHERE published_at IS NULL; -- partial index for relay efficiencyPolling Relay
import psycopg2
from time import sleep
def polling_relay(conn, broker_client, batch_size=100, poll_interval_ms=500):
while True:
with conn.cursor() as cur:
# Lock rows for this relay instance only
cur.execute("""
SELECT id, aggregate_type, aggregate_id, event_type, payload
FROM outbox
WHERE published_at IS NULL
ORDER BY created_at
LIMIT %s
FOR UPDATE SKIP LOCKED
""", (batch_size,))
rows = cur.fetchall()
if not rows:
sleep(poll_interval_ms / 1000)
continue
ids = []
for row in rows:
broker_client.publish(
topic=row["aggregate_type"],
key=row["aggregate_id"],
value=row["payload"],
headers={"event_type": row["event_type"]}
)
ids.append(row["id"])
with conn.cursor() as cur:
cur.execute(
"UPDATE outbox SET published_at = NOW() WHERE id = ANY(%s)",
(ids,)
)
conn.commit()FOR UPDATE SKIP LOCKED is the mechanism that lets you run multiple relay instances without them stepping on each other. It is a Postgres-specific primitive — MySQL and DynamoDB each require different approaches.
CDC via Logical Replication
Polling adds latency and database load. For high-throughput systems, use logical replication (via Debezium or pg_logical) to stream WAL changes to Kafka.
# Debezium connector config (runs in Kafka Connect)
{
"connector.class": "io.debezium.connector.postgresql.PostgresConnector",
"database.hostname": "postgres",
"database.dbname": "mydb",
"table.include.list": "public.outbox",
"plugin.name": "pgoutput",
"slot.name": "outbox_slot",
"transforms": "outbox",
"transforms.outbox.type": "io.debezium.transforms.outbox.EventRouter",
"transforms.outbox.table.field.event.key": "aggregate_id",
"transforms.outbox.table.field.event.type": "event_type",
"transforms.outbox.route.by.field": "aggregate_type"
}CDC failure mode: the replication slot accumulates WAL if Debezium is down. Postgres will not reclaim that WAL space. Set max_slot_wal_keep_size to cap disk usage, and alert on slot lag above a threshold.
MySQL: Polling Only, With Caveats
MySQL 8 supports row-based binary log replication, and tools like Debezium support MySQL CDC. But MySQL's transaction isolation model creates a subtle ordering problem.
Schema
CREATE TABLE outbox (
id CHAR(36) PRIMARY KEY,
aggregate_type VARCHAR(128) NOT NULL,
aggregate_id VARCHAR(256) NOT NULL,
event_type VARCHAR(128) NOT NULL,
payload JSON NOT NULL,
created_at DATETIME(6) NOT NULL DEFAULT NOW(6),
published_at DATETIME(6) NULL,
INDEX idx_unpublished (published_at, created_at)
) ENGINE=InnoDB;Polling Relay
MySQL does not have SKIP LOCKED on older versions (it was added in 8.0.1). For older MySQL, use an explicit lock_token column:
-- Claim batch for this relay instance
UPDATE outbox
SET lock_token = 'relay-instance-1', locked_at = NOW(6)
WHERE published_at IS NULL
AND (lock_token IS NULL OR locked_at < NOW(6) - INTERVAL 60 SECOND)
ORDER BY created_at
LIMIT 100;
-- Then SELECT WHERE lock_token = 'relay-instance-1'
-- Publish, then clear lock_token and set published_atMySQL CDC Ordering Problem
MySQL's READ COMMITTED isolation (common in high-throughput setups) means two transactions can commit out of order relative to the binlog. Transaction A starts, Transaction B starts, B commits first, A commits second — but the binlog may show B before A even though A had a lower created_at. This is not a bug; it is how MySQL works.
Mitigation: use the binlog position (file + offset) or GTID as your ordering key downstream, not created_at. Debezium emits these as event metadata.
DynamoDB: Streams Instead of Polling
DynamoDB has no concept of cross-table transactions that would let you write to an outbox in the same transaction as your business item — unless both items are in the same table. DynamoDB Transactions (TransactWriteItems) do support writing to multiple items atomically, but those items can be in different tables only within the same region.
Single-Table Design (Preferred)
Put the outbox records in the same table as your business entities, using a different entity prefix:
import boto3
from datetime import datetime, timezone
import json
dynamodb = boto3.resource('dynamodb')
table = dynamodb.Table('main-table')
def write_order_with_event(order: dict, event: dict):
"""Atomic write: business entity + outbox record, same table."""
table.meta.client.transact_write(
TransactItems=[
{
"Put": {
"TableName": "main-table",
"Item": {
"PK": f"ORDER#{order['id']}",
"SK": "METADATA",
**order
}
}
},
{
"Put": {
"TableName": "main-table",
"Item": {
"PK": f"OUTBOX#{order['id']}",
"SK": datetime.now(timezone.utc).isoformat(),
"event_type": event["type"],
"payload": json.dumps(event),
"published": False,
"ttl": int(datetime.now(timezone.utc).timestamp()) + 86400
},
"ConditionExpression": "attribute_not_exists(PK)"
}
}
]
)DynamoDB Streams Relay
Enable DynamoDB Streams (new image, KEYS_ONLY, or NEW_AND_OLD_IMAGES) and trigger a Lambda or a stream processor:
def process_stream_record(record: dict):
if record["eventName"] != "INSERT":
return
new_image = record["dynamodb"]["NewImage"]
pk = new_image["PK"]["S"]
if not pk.startswith("OUTBOX#"):
return
payload = json.loads(new_image["payload"]["S"])
broker_client.publish(
topic=new_image["event_type"]["S"],
key=pk.replace("OUTBOX#", ""),
value=payload
)
# DynamoDB Streams guarantees at-least-once delivery
# The record is not "deleted" after processing — it ages out via TTLDynamoDB Failure Modes
Stream shard exhaustion. Each DynamoDB partition maps to one stream shard. Heavy write traffic on a hot partition can overwhelm the shard. Spread writes across partitions; do not use a single monotonic outbox PK.
Lambda cold start lag. Stream-triggered Lambda has up to 1s cold start + shard polling delay. If you need sub-second latency, run a dedicated stream processor (KCL or the DynamoDB Streams Kinesis Adapter) on ECS.
TTL cleanup is async. DynamoDB TTL deletion can lag by hours. Your outbox records accumulate. Size your table billing mode accordingly, and do not rely on TTL for exact cleanup timing.
Deduplication Downstream
All three implementations deliver at-least-once. Your consumers need deduplication. The standard approach:
-- In the consumer's database
CREATE TABLE processed_events (
event_id TEXT PRIMARY KEY,
processed_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
-- On receipt:
INSERT INTO processed_events (event_id) VALUES ($1)
ON CONFLICT DO NOTHING
RETURNING event_id;
-- If no row returned, event was already processed — skipKeep processed event IDs for at least as long as your broker's redelivery window (typically 7 days for Kafka, configurable for SQS).
Polling vs CDC: Decision Matrix
| Concern | Polling | CDC |
|---|---|---|
| Latency | 100ms–2s (configurable) | < 100ms |
| Database load | Moderate (SELECT + UPDATE) | Low (reads WAL/stream) |
| Operational complexity | Low | High (connector management) |
| Ordering guarantee | Weak (concurrent commits) | Strong (WAL order) |
| Schema dependency | High (queries outbox table) | Low (event-driven) |
For most teams starting out: poll. Add CDC when polling latency or database load becomes a measurable problem.
Key Takeaways
- Write the outbox record in the same transaction as the business mutation — never after.
- Postgres polling with
FOR UPDATE SKIP LOCKEDis the simplest multi-instance relay; CDC via Debezium trades operational complexity for latency and throughput. - MySQL's concurrent commit ordering makes
created_atunreliable as an ordering key — use binlog position or GTID instead. - DynamoDB requires single-table design for true atomicity; cross-table
TransactWriteItemsworks but adds latency and cost. - Deduplication belongs in the consumer, not the relay — the outbox guarantees at-least-once, not exactly-once.
- Set replication slot
max_slot_wal_keep_sizein Postgres or your disk will fill when the relay falls behind.