The Transactional Outbox: Writing to a Database and a Queue Without Losing Events

When an order is paid, two things need to happen: the row in the database moves to paid, and an event lands on a queue so other services can react. Code usually does this with two consecutive lines. The problem is that those two writes are not part of one transaction. If the database commit succeeds and the publish fails, the event is lost. If you publish first and the transaction then rolls back, the rest of the system has heard about a payment that never happened. This post explains why this "dual write" problem cannot be fixed with coding discipline alone, and how to build the transactional outbox pattern correctly.

Why reordering the calls does not help

The first instinct is to fix the order: commit, then publish, and retry the publish if it fails. That assumes the process never dies between the commit and the publish. In practice that gap is exactly where a pod gets terminated during a rollout, a process hits its memory limit, or the broker connection drops. If the retry logic lives in memory, it dies with the process.

The reverse order is no better. Putting the publish inside the transaction and committing only after the broker acknowledges means making a network call while holding database locks. When the broker slows down, row locks are held longer, the connection pool fills up, and a messaging hiccup turns into a database outage.

The textbook answer to atomic writes across two systems is a distributed transaction (two-phase commit). Most modern brokers do not participate in one, and where they do, the operational cost is high. The outbox pattern attacks the problem from a different angle: it removes the second write.

The core idea

Instead of writing the event to the broker, you write it to a table in the same database as your business data. Both writes are now in the same local transaction, so either both happen or neither does. A separate relay reads that table and moves the events to the broker.

CREATE TABLE outbox (
    id             bigserial PRIMARY KEY,
    aggregate_type text        NOT NULL,
    aggregate_id   text        NOT NULL,
    event_type     text        NOT NULL,
    payload        jsonb       NOT NULL,
    created_at     timestamptz NOT NULL DEFAULT now(),
    published_at   timestamptz
);

CREATE INDEX outbox_pending ON outbox (id) WHERE published_at IS NULL;

On the application side the write looks like this:

BEGIN;
UPDATE orders SET status = 'paid' WHERE id = 42;
INSERT INTO outbox (aggregate_type, aggregate_id, event_type, payload)
VALUES ('order', '42', 'OrderPaid', '{"order_id": 42, "amount": 1500}');
COMMIT;

From here the guarantee is clear: every committed state change has a durable event record. What remains is delivering that record to the broker at least once.

Two ways to relay: polling and change data capture

Polling. A process periodically fetches unpublished rows, sends them to the broker, and marks them once acknowledged.

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

-- send rows to the broker, wait for acknowledgement

UPDATE outbox SET published_at = now() WHERE id = ANY($1);
COMMIT;

FOR UPDATE SKIP LOCKED lets several relay instances share the work without fighting over the same rows. Latency equals the polling interval. In PostgreSQL you can use LISTEN/NOTIFY to wake the relay and keep the interval long, but notifications can be missed, so polling should stay as the fallback.

Change data capture (CDC). The relay does not query the table; it reads the database's transaction log. Tools built on PostgreSQL logical replication or the MySQL binlog (Debezium is the most common, and it ships a ready-made event router for outbox tables) pick up every insert almost immediately. No query load, low latency.

Which one? If you are below a few hundred events per second and nobody on the team already runs CDC, start with polling. A hundred-line relay is easy to understand and easy to debug. The real cost of CDC is not setup, it is operations: replication slots, a connector cluster, configuration that breaks on schema changes. Move to CDC when your latency budget drops below a second or polling queries become visible load on the database.

Pitfalls

Sequence order is not commit order. It is tempting to write the poller as "fetch everything with an id greater than the last one I saw." But a bigserial value is assigned at insert time, while the row becomes visible at commit time. If the transaction that wrote row 101 commits after the one that wrote row 102, the relay reads 102, advances its cursor, and row 101 is skipped forever. Use the published_at IS NULL condition instead of a cursor, so the state lives on the row itself.

The relay delivers at least once, not exactly once. If the relay dies after the broker acknowledges but before the UPDATE commits, the same event is sent again. This is inherent to the pattern and cannot be removed. The fix belongs on the consumer side: give every event a unique id, and have the consumer record processed ids in its own database, in the same transaction as the side effect.

BEGIN;
INSERT INTO processed_events (event_id) VALUES ($1)
ON CONFLICT DO NOTHING;
-- if zero rows were affected, the event was already handled: stop here
-- otherwise do the actual work
COMMIT;

Parallel relays break ordering. Run three relays with SKIP LOCKED and the OrderCreated and OrderPaid events for the same order can land on different instances and be published in reverse. If order matters, partition by aggregate_id: each relay takes only its own partition, and the same key is used on the broker so events for one entity end up in one partition.

The table quietly grows. If published rows are never deleted, the outbox becomes your largest table, and the steady stream of UPDATE and DELETE leaves dead tuples behind in PostgreSQL. Run a job that deletes published rows in small batches. At very high volume, a time-partitioned table where you drop old partitions wholesale is far cheaper.

A CDC slot can fill your disk. A logical replication slot retains the transaction log until its consumer reads it. If the connector stops and nobody notices, WAL fills the disk and the database stops accepting writes. If you use CDC, alert on slot lag, and on PostgreSQL 13 or later set an upper bound with max_slot_wal_keep_size.

The payload is a contract. Dumping the business row straight into the event body is easy, but from that moment your table schema becomes other teams' contract. Design the payload separately and carry an event_type and a version field. For the same reason, pointing CDC at the outbox table rather than directly at business tables is healthier: you keep the freedom to change your internal schema.

Measure relay health. The single most important metric is the age of the oldest unpublished row. The relay can be dead while the application reports no errors at all, because the write path is entirely local. Without this metric the problem surfaces as a user complaint that a notification never arrived. Alert when the age goes beyond a few minutes.

When not to use it

If the code that consumes the event uses the same database, you do not need an outbox; do the work in the same transaction or use a job queue backed by that database. If losing an event is acceptable (analytics counters, cache warming), a plain "publish after commit" is enough and an extra moving part is not worth running.

There is also an alternative pattern: write the event to the broker first and let the service update its own database by consuming that event. This works cleanly in systems that have fully committed to event-driven design, but it gives up the guarantee that you can immediately read what you just wrote. For most CRUD-heavy services, the outbox is the less surprising choice.

The short rule: if an inconsistency between your business data and the outgoing message touches a customer or money, build an outbox, start the relay with polling, make consumers tolerant of duplicates, and watch the age of the oldest pending event.