You cannot atomically write a database row and publish to a message broker without distributed transactions, and distributed transactions across Postgres and Kafka usually create worse problems than they solve. So teams dual-write. Dual-write failure modes show up in production; they are not theoretical.
I already covered how Kafka's transactions deliver exactly-once inside Kafka. This post is the application-level counterpart: how to keep business state and domain events consistent when the database is the source of truth and the broker is downstream. The outbox pattern, a CDC or polling relay, and an inbox on the consumer side.
The dual-write that looks fine in code review
def place_order(order_data):
db.insert(orders, order_data) # Step 1
broker.publish("OrderPlaced", order) # Step 2
Four failure modes, all of which show up in real systems:
Support tickets from case 1 look like "order stuck." Tickets from case 2 look like "ghost shipments." Neither is fixed by "add a retry" unless you also design for duplicates.
The outbox: one transaction, two facts
Stop writing to two systems. Write to one, the database, and treat event publishing as part of the data model:
BEGIN;
INSERT INTO orders (id, customer_id, total, status)
VALUES (42, 'c-100', 99.99, 'placed');
INSERT INTO outbox (event_type, aggregate_id, payload)
VALUES ('OrderPlaced', '42',
'{"id":42,"customer_id":"c-100","total":99.99}');
COMMIT;
Either both rows commit or neither does. There is no "order without event" and no "event without order." A separate relay reads unpublished outbox rows and publishes to the broker, then marks them published.
Relay option 1: polling
def relay():
while True:
events = db.query(
"SELECT * FROM outbox "
"WHERE published_at IS NULL ORDER BY id LIMIT 100")
for event in events:
broker.publish(event.type, event.payload)
db.update(
"UPDATE outbox SET published_at = NOW() WHERE id = ?",
event.id)
sleep(poll_interval)
Boring, debuggable, portable across databases. Latency equals the poll interval, which is usually fine when "a few seconds late" is within the business SLO. Too-aggressive polling taxes the DB; too-slow polling delays downstream. Start at 1-5 seconds and measure lag as a metric.
Relay option 2: CDC (Debezium et al.)
Change Data Capture tails the WAL (or binlog / change stream) and streams inserts into the outbox topic with sub-second latency and no polling load. The cost is operational: replication slots, connector health, schema evolution, and another distributed system in the critical path.
Prefer polling until throughput or latency SLOs force CDC. Do not start with Debezium because it looks more impressive in a design review.
Semantics: at-least-once, so consumers must be idempotent
The relay can publish successfully and crash before setting published_at. On restart it publishes again. Outbox means at-least-once. Exactly-once end-to-end is something you assemble: at-least-once from the outbox plus idempotent consumers. It is not a checkbox on the broker alone.
(Kafka transactions help when both sides of a processing step live inside Kafka. The moment you touch an external database as the system of record, you are back to outbox/inbox territory.)
Schema that does not fall over
CREATE TABLE outbox (
id BIGSERIAL PRIMARY KEY,
event_type VARCHAR(255) NOT NULL,
aggregate_id VARCHAR(255) NOT NULL,
aggregate_type VARCHAR(255),
payload JSONB NOT NULL,
metadata JSONB,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
published_at TIMESTAMPTZ NULL
);
CREATE INDEX idx_outbox_unpublished
ON outbox (id) WHERE published_at IS NULL;
The partial index keeps "find unpublished" cheap as the table grows. Without cleanup, it grows forever:
- Delete after publish: smallest table, no audit trail.
- Archive: move to cold storage / archive table; keep forensics.
- TTL: retain N days, then delete; middle ground.
Plan cleanup before the on-call page about disk, not after.
The inbox: consumer-side dedupe
Idempotency needs a durable gate, not a comment that says "make sure this is safe."
CREATE TABLE inbox (
message_id VARCHAR(255) PRIMARY KEY,
consumed_at TIMESTAMPTZ NOT NULL
);
def handle_event(event):
try:
with transaction:
db.insert(
"INSERT INTO inbox (message_id, consumed_at) "
"VALUES (?, NOW())", event.message_id)
apply_business_logic(event)
except UniqueConstraintViolation:
pass # already processed
Insert into inbox and apply business logic in the same transaction. If the process dies after applying logic but before committing, the inbox row is not there either, so retry is safe. If the message is a duplicate, the unique constraint rejects it and you skip. Outbox + inbox is the reliability loop:
Ordering, errors, and the unglamorous rest
Insert order is not delivery order once you have parallel relays or partitioned topics. If order matters for an aggregate, include a sequence number or process per aggregate_id partition key and keep consumers ordered within that key.
When the broker is down, events accumulate in the outbox. That is a feature. When serialization fails or a poison payload loops, you need a dead-letter path and metrics on outbox_lag / oldest unpublished age. A relay without alerting is a silent backlog.
When to use it / when not to
Use outbox when a state change must reliably trigger downstream work: order → fulfillment, ledger postings, saga steps, controlled read-model updates (prefer explicit events over "CDC every table").
Skip it for best-effort notifications, sampled metrics, and pure event-sourced stores where the event log is the state. There is nothing to dual-write.
Outbox vs "just use Kafka transactions"
Kafka's transactional producer can atomically commit offsets and output records within Kafka. That shines for stream processors that never leave the log. The moment step one is UPDATE accounts in Postgres and step two is publish, Kafka transactions do not cover the database. You either adopt the outbox (DB as truth) or accept dual-write risk. Teams sometimes bolt "idempotent producer" onto a dual-write and call it done. That only dedupes the broker side; it does not resurrect the missing DB row or the missing event.
Minimal production checklist
- Outbox write in the same transaction as the state change, enforced by code review and an integration test that crashes between the two inserts (should be impossible).
- Relay with leases or
FOR UPDATE SKIP LOCKEDso multiple workers do not double-claim without a plan. - Metrics: unpublished count, oldest unpublished age, publish error rate, relay lag.
- Inbox (or equivalent) on every consumer that mutates money, inventory, or entitlements.
- Runbook: broker outage → outbox backlog drains; DB outage → no silent "events only" path.
Key takeaways
- Dual-write between DB and broker is not atomic; the four failure modes are routine in production.
- Outbox: business row + outbox row in one DB transaction; relay publishes asynchronously.
- Polling relays are enough at modest scale; CDC when latency/throughput demand it.
- Outbox gives at-least-once; pair with inbox (or equivalent idempotency) for practical exactly-once effects.
- Partial indexes and cleanup keep the outbox operable; lag metrics keep it honest.
- Reserve the pattern for state changes that must fan out, not for every log line.
The outbox is not elegant. It is proven, understandable, and reliable, which matters more than elegance once money and fulfillment are on the line.