Transactional outbox

The transactional outbox lets a service change its database and tell the rest of the system, never one without the other. The service writes the business row and an event row into an outbox table in one local transaction, and a separate relay later copies committed outbox rows to the message broker. You get “the state changed if and only if the event exists”; you pay with at-least-once delivery and a little latency.

Picture it first: the kirana store’s order register

A neighbourhood kirana store takes orders on the phone. For each one, the owner writes a bill in his register and tells the delivery boy what to take where.

Done as two acts, both orders go wrong. Shout first, and if the customer cancels mid-bill, the boy is already out with groceries nobody will pay for. Write first, and if a crowd walks in before he tells the boy, the order never leaves the shop.

So the owner adds a “to dispatch” column and writes the bill and its dispatch line together, as one act. Tear out a cancelled bill and its dispatch line goes too. Every few minutes the boy reads the register, takes every unticked line and ticks it as he leaves.

One flaw remains: if he is called away before ticking a line, he delivers it again next round. The customer says, “Bhaiya, already got it,” and checks the bill number.

Map it back: the register is your database, the bill is the business row, the dispatch column is the outbox table, the delivery boy is the relay, and the bill number is the event id consumers dedupe on.

The dual-write problem

Take a Swiggy-style food app. Placing an order must save it and announce OrderPlaced, so payments can capture the money, the restaurant can cook and notifications can tell the customer. The database and the broker share no transaction, so code that writes to both is a dual write, and neither order of the two writes is safe.

Publish, then commit: the phantom event

The service inserts the order, publishes OrderPlaced to Kafka, then commits. Kafka accepts the message, and then the commit fails: a deadlock, a constraint violation, a dropped connection, a failover. The order is rolled back but the event is out. Payments charges for an order that does not exist and the kitchen starts cooking it. You cannot unpublish a message.

Commit, then publish: the lost event

Flip it: commit, then publish. Now a deploy kills the process, the node dies or the broker is unreachable just after the commit. The customer saw “Order placed”, but nothing downstream ever hears about it. This failure is quieter, which makes it worse: it surfaces hours later as a support ticket.

Why retries and try/catch don’t fix it

The reflex is to add a retry loop or a try/catch. Each patch closes one hole and opens another:

• Retrying the publish survives a broker blip, but the retries live in memory; if the process dies mid-retry, the intent dies with it.
• Rolling back on a publish error means publishing before the commit, which brings back the phantom when the commit then fails. And a timeout can hide a publish that succeeded.
• A compensating OrderCancelled needs its own reliable publish: back to square one.

“Did the database commit?” and “did the broker accept it?” are two independent facts, and a crash can land between them. You need one fact that decides both.

Why not a distributed transaction?

Two-phase commit (XA) is the textbook answer: a coordinator asks both sides to prepare, then to commit. It is rarely available, and rarely worth it:

• Many brokers cannot join. Kafka cannot be enlisted in an XA transaction; its own transactions cover Kafka partitions, not your Postgres.
• It couples availability. A database at 99.95% and a broker at 99.9% give 0.9995 × 0.999 ≈ 99.85% together: about 65 minutes of failed orders in a 30-day month (43,200 minutes) instead of 22.
• It adds latency and blocking. Prepare and commit add round trips and forced log writes, and a prepared participant holds its locks until the coordinator decides.

Even where brokers support XA, teams often prefer an outbox to keep broker outages off the write path. See distributed transactions: two-phase commit vs sagas.

The pattern: one local transaction

The outbox takes the second write off the request path. The service writes to one system only, its own database, and turns “send this event” into a row:

1. In one local ACID transaction, write the business rows and insert an event row into an outbox table in the same database.
2. Commit. Both rows exist, or neither does.
3. A separate relay reads committed outbox rows and publishes them to the broker.
4. The relay marks each row as sent, or deletes it.

Atomicity now comes from the database alone. A rolled-back order takes its outbox row with it: no phantom. A committed order always has an outbox row that the relay will find later, even if everything else was down at commit time: no lost event.

A minimal outbox table for PostgreSQL. The id doubles as the event id that consumers dedupe on; seq gives the relay a cheap, indexed reading order.

CREATE TABLE outbox (
  id             uuid        PRIMARY KEY,           -- event id, the dedupe key
  seq            bigint      GENERATED ALWAYS AS IDENTITY,
  aggregate_type text        NOT NULL,              -- 'order', 'payment': picks the topic
  aggregate_id   text        NOT NULL,              -- 'ord_9f3': becomes the message key
  event_type     text        NOT NULL,              -- 'OrderPlaced'
  payload        jsonb       NOT NULL,
  created_at     timestamptz NOT NULL DEFAULT now(),
  published_at   timestamptz                        -- NULL until the relay sends it
);
CREATE INDEX outbox_pending ON outbox (seq) WHERE published_at IS NULL;

And the write path, where the two inserts share one fate:

BEGIN;
INSERT INTO orders (id, customer_id, restaurant_id, total_paise, status)
VALUES ('ord_9f3', 'cus_17', 'res_88', 45000, 'PLACED');

INSERT INTO outbox (id, aggregate_type, aggregate_id, event_type, payload)
VALUES (gen_random_uuid(), 'order', 'ord_9f3', 'OrderPlaced',
        '{"orderId":"ord_9f3","restaurantId":"res_88","totalPaise":45000}');
COMMIT;

Steps 1 and 2 are the request; steps 3 to 5 happen in the background.

Two side benefits. The service keeps read-your-own-writes, so “my orders” shows the order as soon as the commit returns. And if Kafka is down for an hour, orders still succeed while events wait.

In a NoSQL store the outbox is a field on the same item or a document in the same partition: Cosmos DB’s transactional batches are ACID only within one logical partition, so Microsoft’s reference design keeps each event document beside its business document.

Relay option 1: the polling publisher

The simplest relay is a worker that keeps asking the outbox “what hasn’t been sent yet?” A safe version for PostgreSQL:

BEGIN;
SELECT id, aggregate_type, aggregate_id, event_type, payload
FROM   outbox
WHERE  published_at IS NULL
ORDER  BY seq
LIMIT  500
FOR UPDATE SKIP LOCKED;

-- publish all 500 to the broker and wait for every ack, then:
UPDATE outbox SET published_at = now() WHERE id = ANY($1);
COMMIT;

SKIP LOCKED makes a second relay skip rows the first has locked instead of waiting for them. PostgreSQL’s documentation calls the result an inconsistent view, wrong for general queries but right for a queue-like table with several consumers. The transaction stays open while the batch is published, so give the broker call a tight timeout.

Select by state, never by watermark

A tempting shortcut is to remember “the last seq I sent was 1,041” and next ask for seq > 1041. That loses events. A sequence hands out its number when the INSERT runs, not at commit, so T1 can take 1,040, stall, and commit after T2 has taken and committed 1,041. A relay whose watermark has passed 1,041 never sees 1,040. Asking for published_at IS NULL catches it on the next poll.

Can a late commit reorder two events for the same order? Not if each transaction updates the order row before inserting its outbox row: the second waits for the first one’s row lock, so it draws its number only after the first commits.

Interval, latency and load

The main knob is the poll interval P. A committed event waits P/2 on average, and up to P, before the relay looks: at P = 200 ms, 100 ms on average, 200 ms at worst, plus the publish. Each poll is one indexed query, so three replicas polling 5 times a second add 3 × 5 = 15 queries a second when idle: nothing for a healthy primary.

Throughput comes from batching, not faster polling. If one round (select 500, publish, update) takes 50 ms, one relay moves 500 ÷ 0.05 s = 10,000 events a second while it loops without sleeping. Say the dinner rush peaks at 2,000 orders a second with 3 events each: 6,000 rows a second, 60% of one relay. Sleep only after an empty batch, so a backlog drains at full speed.

You gain a relay that works on any SQL database and can replay events by clearing published_at. You pay with queries on the primary, a latency floor near P, a worker to run, and ordering care once you run more than one.

Relay option 2: tailing the transaction log

Every committed change is already in the database’s log (the WAL in PostgreSQL, the binlog in MySQL). A change data capture connector reads that log as a replication client and turns committed inserts into messages. Point it at the outbox table and you have a relay that never queries it.

PostgreSQL’s logical decoding hands out transactions in commit order and never decodes a rolled-back one: exactly the contract a relay needs, with no watermark trap and no relay race.

Debezium’s Outbox Event Router

Debezium ships a transform, the Outbox Event Router, that turns raw change events from an outbox table into domain messages. It expects columns id, aggregatetype, aggregateid, type and payload by default, and it:

• routes by aggregate type: aggregatetype = 'order' goes to the topic outbox.event.order;
• keys each message by aggregate id, so one order’s events land in one partition, in order;
• puts the event id in a message header named id, for consumers to dedupe on;
• expects inserts only: it warns on UPDATEs by default and filters out DELETEs.

That last point enables a trick from the Debezium team’s 2019 write-up. Because the connector reads the log, the application can insert the outbox row and delete it in the same transaction. The log keeps the insert, which becomes a message; the router drops the delete. The table stays empty: no backlog, no clean-up job.

PostgreSQL even allows a table-less outbox. pg_logical_emit_message(true, 'outbox', payload) writes a message into the WAL as part of the current transaction, so it is decoded only if the transaction commits. Debezium’s PostgreSQL connector emits these as message events (with pgoutput, on PostgreSQL 14+), which a decoding transform can feed to the router. You pay with a Postgres-only design and pending events you can’t SELECT.

What log tailing costs

• A replication slot pins WAL. The database keeps every WAL segment the connector has not confirmed, so a stopped connector fills the primary’s disk; max_slot_wal_keep_size defaults to unlimited. Cap it and alert on slot lag; a slot pushed past the cap may be unable to resume.
• More moving parts: logical replication, Kafka Connect or Debezium Server, and a failover plan.
• Still at-least-once. After a crash, Debezium resumes from its last recorded offset and may re-emit events.

Duplicates: the relay is at-least-once

The outbox guarantees no lost events and no phantom events, not exactly one copy. Every relay has a moment when the broker has the message but the relay has not yet recorded it; a crash there resends the row.

The window exists in every variant: a poller crashing between the ack and its UPDATE, a publish that times out on the client although the broker stored it, a Debezium connector resuming from its last flushed offset. Kafka’s idempotent producer removes duplicates from its own retries, but a restarted relay re-reading an unmarked row is a fresh send.

So the consumer must be idempotent. In the same local transaction as its side effect, payments inserts the event id into a processed_events table with a unique constraint; a collision means “already handled”, so it acknowledges without charging again. That is the inbox pattern, covered in effectively-once: idempotent consumers and the inbox pattern.

Broker-side deduplication helps but does not replace this: AWS’s sample uses the outbox row id as the SQS FIFO deduplication id, and Microsoft’s sample enables Service Bus duplicate detection. Both catch quick resends within their limits; only the consumer check survives an hour-long outage. See delivery guarantees.

Ordering: per aggregate, not global

You rarely need a global order. Consumers need all events for one aggregate, one order or one payment, in the order they happened; different orders can interleave, which is what lets the system scale. Three links must each keep that order:

1. Commit to outbox. Transactions on one aggregate serialise on its row lock, so seq follows commit order.
2. Outbox to broker. The relay publishes one aggregate’s rows in seq order, keyed by aggregate id: one Kafka partition, one SQS FIFO message group (as in AWS’s sample) or one Service Bus session.
3. Broker to consumer. One consumer per partition; see message ordering.

Link 2 is where scaling the relay bites. A UPI-style payment, pay-42, goes PENDING then SUCCESS. Two relay replicas run the SKIP LOCKED query at once: relay A locks row 41 (PENDING), relay B skips it and takes row 42 (SUCCESS). A’s publish is slow, B’s is not, so the partition holds SUCCESS, then PENDING, and the app shows “payment pending” for money that has moved.

Three fixes, usually combined:

• One active relay. Run replicas for availability, but only the holder of a lock polls: a PostgreSQL advisory lock (pg_try_advisory_lock) or an etcd or ZooKeeper election (see distributed locking). One relay handles about 10,000 events a second by the arithmetic above; you pay with that ceiling and a pause while another replica takes over.
• Relays partitioned by aggregate. Store a shard column, a hash of aggregate_id modulo N, and have relay i poll only shard i. N relays give roughly N times the throughput; you pay when changing N, which needs a drain first.
• Never skip past a failure for the same key. If row 41 fails, hold back row 42 for that aggregate and retry later.

As defence in depth, put an aggregate version (1, 2, 3 …) in every event and let consumers ignore anything older than what they have applied. A reordering bug then costs a dropped stale update, not a wrong final state.

Clean-up, bloat and monitoring

An uncleaned outbox grows fast: 6,000 rows a second × 86,400 seconds ≈ 518 million rows a day, about half a terabyte at roughly 1 KB a row. Pick a strategy on day one:

• Delete in the relay’s transaction instead of setting published_at. The table stays small, but every PostgreSQL delete leaves a dead row version; at thousands a second, tune autovacuum for this table or it bloats (see vacuum and bloat).
• Mark, then delete in batches, say rows published over an hour ago, 10,000 at a time, keeping a short replay window.
• Partition by time, say hourly, and drop a partition once all its rows are published. PostgreSQL’s documentation notes that dropping a partition is far faster than a bulk DELETE and avoids its VACUUM overhead. See table partitioning.
• Insert, then delete, with log tailing: nothing to clean. In Cosmos DB, a time-to-live does the same.

For monitoring, one number matters most: outbox lag, the age of the oldest unpublished row, which is how stale every consumer is right now.

SELECT now() - min(created_at) AS outbox_lag,
       count(*)                AS backlog
FROM   outbox
WHERE  published_at IS NULL;

A healthy polling relay keeps lag near its poll interval; alert when it stays far above that, say 30 seconds for a 200 ms relay. With Debezium, watch the connector’s MilliSecondsBehindSource metric and the WAL its slot holds in pg_replication_slots.

Size the relay for catch-up. A one-hour broker outage at 6,000 events a second queues 21.6 million rows. Draining at 10,000 a second while 6,000 keep arriving nets 4,000 a second: 21,600,000 ÷ 4,000 = 5,400 seconds, 90 minutes.

Designing outbox events

An outbox event is a public contract, not a table row. The Debezium team’s advice: treat it as part of the service’s API, change it carefully, and have consumers ignore unknown fields. A practical envelope:

{
  "eventId": "7d1c2f9e-4b0a-4c55-9a51-0f3e2d8b6a10",
  "eventType": "OrderPlaced",
  "schemaVersion": 2,
  "aggregateType": "order",
  "aggregateId": "ord_9f3",
  "aggregateVersion": 1,
  "occurredAt": "2026-10-27T19:42:11Z",
  "data": { "restaurantId": "res_88", "totalPaise": 45000, "itemCount": 3 }
}

• eventId is the dedupe key, aggregateId the message key, and aggregateVersion lets consumers reject stale events.
• Carry what consumers need, so they don’t call back and recreate the coupling; leave out internal columns.
• Evolve by adding, not renaming or removing. A schema registry with Avro, which the Outbox Event Router supports, can enforce this; see serialization and schema evolution.

This is the main difference from raw CDC on business tables. A CDC event for orders is a row image: it exposes column names, splits one action across orders and three order_items rows, and breaks consumers on a rename. The outbox publishes one event shaped on purpose.

Alternatives and when to pick them

• Listen to yourself. Publish only to the broker and have the service consume its own OrderPlaced to write its database. One write, so it is atomic. You pay with read-your-own-writes (the customer may not see the order just placed), and database-backed validation must happen before publishing. Pick it when the log is the system of record anyway, as with clickstream.
• Event sourcing. The events are the state and the event store doubles as the feed. You pay with projections, snapshots and upcasting. Pick it when you want full history anyway, as in a ledger; see event sourcing and CQRS.
• Raw CDC on business tables. No application change, and it catches manual SQL fixes too. You pay with row-shaped events and a leaked schema. Pick it to feed search indexes, caches and warehouses that want rows.
• Database-native change streams. In DynamoDB the event can be an attribute of the changed item, with DynamoDB Streams as the feed; in Cosmos DB, a transactional batch plus the change feed. The outbox with the relay half built in.

Going deeper: failure modes

Eager publish plus outbox. Publishing right after commit and letting the relay catch misses cuts latency, but two senders can now send the same row, so duplicates become routine.

Poison rows block a key. A row the broker always rejects, such as one over its maximum message size, stalls its aggregate if the relay retries in order. Park it after a few attempts and alert; skipping it breaks that aggregate’s order, so a human decides.

Events are only as durable as commits. With asynchronous replication, a failover can lose the last few commits after the relay has already published their events. Use synchronous replication where that matters; see data replication.

Cheat sheet

How to say it in the interview

“When the order service saves an order, it also has to tell payments, the restaurant and notifications. Writing to Postgres and Kafka separately is a dual write: publish first and a rollback leaves a phantom event; commit first and a crash loses it. Kafka can’t join XA, so I’d use a transactional outbox: the order row and an OrderPlaced row commit in one local transaction, and the API replies right after. A relay publishes outbox rows to Kafka keyed by order id, for per-order ordering: a SKIP LOCKED poller to start, Debezium tailing the WAL at scale. Delivery is at-least-once, so events carry a UUID and consumers dedupe. I’d alert on outbox lag.”

Why not publish after the commit and retry on failure? The retry lives in memory. If the process crashes after the commit but before the publish succeeds, nothing will ever retry it. The outbox makes the intent durable in the same commit as the change.

What if the broker is down for an hour? Orders keep succeeding, because the request path ends at the commit. Events queue in the outbox, outbox lag pages someone, and the relay drains the backlog afterwards: at 10,000 a second against 6,000 of new load, about 90 minutes.

How do you scale the relay without breaking ordering? Give each aggregate exactly one relay: a leader-elected relay, or N relays each owning a hash range of aggregate ids. Plain SKIP LOCKED across replicas can split one aggregate’s rows. Log tailing avoids this because the log is already in commit order.

Polling or CDC? Polling is a small worker on any SQL database, with latency near the poll interval and queries on the primary. CDC streams in seconds or less without touching the table, but you run a connector and a slot that keeps WAL while it is down. Start with polling; move to CDC for volume or latency.

Where to go next

• Change data capture: log-based CDC, snapshots and replication slots.
• Effectively-once: idempotent consumers and the inbox pattern: the consumer half of this pattern.
• Delivery guarantees: Kafka transactions, retries and dead-letter queues.
• Message ordering: partition keys, FIFO groups and hot keys.
• Two-phase commit vs sagas: outbox events drive each saga step.
• Event sourcing and CQRS: when the event store replaces the outbox.
• Event-driven architecture and Kafka: the systems the relay feeds.

References and further reading

1. Pattern: Transactional outbox (microservices.io, Chris Richardson)microservices.ioThe canonical statement of the problem: why 2PC is not an option, both dual-write failure orders, per-aggregate ordering, and why the relay can publish twice.
2. Reliable Microservices Data Exchange With the Outbox Pattern (Gunnar Morling, Debezium blog, 2019)debezium.ioKafka cannot join XA transactions, the listen-to-yourself trade-off, the insert-then-delete trick, consumer dedupe by event id, and events as part of a service’s API.
3. Debezium documentation: Outbox Event Routerdebezium.ioDefault outbox columns, topic routing by aggregate type, aggregate id as the message key, the event id header, and insert-only expectations.
4. Debezium documentation: PostgreSQL connectordebezium.ioLogical decoding message events (pgoutput, PostgreSQL 14+), at-least-once behaviour after crashes, WAL retention by replication slots, and the MilliSecondsBehindSource metric.
5. PostgreSQL documentation: SELECT, the locking clausepostgresql.orgFOR UPDATE … SKIP LOCKED and why it suits queue-like tables with several consumers.
6. PostgreSQL documentation: Logical decoding output plugin callbackspostgresql.orgConcurrent transactions are decoded in commit order, and rolled-back transactions are never decoded.
7. PostgreSQL documentation: System administration functions (pg_logical_emit_message)postgresql.orgWriting a transactional logical decoding message straight into the WAL: the table-less outbox.
8. PostgreSQL documentation: Table partitioningpostgresql.orgDropping a partition is far faster than a bulk DELETE and avoids its VACUUM overhead.
9. AWS Prescriptive Guidance: Transactional outbox patterndocs.aws.amazon.comBoth dual-write failures, an outbox relay to SQS using the aggregate id as the FIFO message group, and DynamoDB Streams as the CDC variant.
10. Azure Architecture Center: Transactional outbox pattern with Azure Cosmos DBlearn.microsoft.comOutbox in a NoSQL store: transactional batches within one logical partition, TTL clean-up, change feed ordering, Service Bus sessions and duplicate detection.