The transactional outbox: saving data and sending a message without losing either

Here is a small piece of code that looks fine and is quietly wrong:

tx.Exec("INSERT INTO orders ...")
tx.Commit()
broker.Publish("order.created", payload)

If the process dies between the commit and the publish, the order exists and nobody hears about it. Swap the two lines and you get the opposite failure: a message about an order that was rolled back. The database and the message broker are two systems, and no single line of code makes a change in both atomic.

The transactional outbox is the usual fix. It is simple enough to build in an afternoon.

The idea

Instead of sending the message directly, you write it to a table in the same database, inside the same transaction as the business change. Either both rows commit or neither does. A separate process, often called the relay, reads the table and publishes the messages to the broker.

flowchart LR
  A[Request] --> B[(Database transaction)]
  B --> C[orders row]
  B --> D[outbox row]
  D --> E[Relay]
  E --> F[Message broker]
  F --> G[Consumers]

The pattern is described on microservices.io, which lists the benefits plainly: no two-phase commit, messages are sent if and only if the transaction commits, and they go out in the order the application produced them.

A minimal table

CREATE TABLE outbox (
  id uuid PRIMARY KEY,
  topic text NOT NULL,
  payload jsonb NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now(),
  sent_at timestamptz
);

CREATE INDEX outbox_unsent ON outbox (created_at) WHERE sent_at IS NULL;

The partial index matters. Only unsent rows are interesting, and a partial index keeps the relay's query fast even when the table holds millions of old rows. If you want a refresher on why that works, see how database indexes speed up reads.

The write path then becomes one transaction:

BEGIN;
INSERT INTO orders (id, customer_id, total) VALUES ($1, $2, $3);
INSERT INTO outbox (id, topic, payload)
VALUES ($4, 'order.created', '{"order_id": "..."}');
COMMIT;

Use time-ordered IDs if you can, so that sorting by id also sorts by creation.

The relay

The relay loop is short: read a batch of unsent rows in order, publish each, mark it sent. You can poll every second or so, or tail the database's write-ahead log if you need lower latency. Polling is the right place to start. It needs no extra infrastructure and is easy to reason about.

If you run more than one relay for availability, they must not grab the same rows. Postgres has a tool for exactly that, which I cover in a job queue built on SKIP LOCKED.

Duplicates are part of the deal

Now the honest part. The relay can publish a message, then crash before it records sent_at. On restart it publishes the same message again. The outbox gives you at-least-once delivery, not exactly-once. The microservices.io page says the same: consumers should be idempotent, for example by remembering the IDs of messages they have already handled.

That is not a big extra burden, because brokers redeliver messages for other reasons anyway. A consumer can do something like this:

INSERT INTO processed_messages (message_id) VALUES ($1)
ON CONFLICT DO NOTHING;
-- if zero rows were inserted, skip the work

Do the insert and the business effect in the same transaction on the consumer side, so a crash cannot leave one without the other. The same thinking applies to HTTP clients; idempotency keys are the request-side version of this idea.

Note

The message ID must be created when the outbox row is written, not when it is published. A fresh ID on every publish defeats deduplication.

Practical details

• Cleanup. Delete or archive sent rows on a schedule. A table that only grows will eventually slow the relay.
• Ordering. Order is only guaranteed per relay and per query ordering. If you need ordering per entity, include an entity key and keep one consumer per key.
• Payload size. Store the facts the consumer needs, or a reference to them. Very large payloads in the outbox make the table heavy.
• Forgetting to write. The page lists this as the main drawback: a developer can update the database and forget the outbox row. Put the two writes in one function, so that the only way to change an order also records the event.
• Monitoring. Track the age of the oldest unsent row. A growing number there is the earliest sign that the relay or the broker is in trouble.

When is polling not enough?

Polling adds up to one polling interval of delay and a steady trickle of queries. If you need events within milliseconds, or you have very high volume, reading the database's change log removes both costs, at the price of more moving parts to operate. Most teams never need it. Measure the delay you actually have before changing the design.

When not to bother

If a lost event is harmless, say a best-effort analytics ping, publishing directly is fine. The outbox earns its keep when the message is what makes something else happen: an email sent, a payment captured, a workflow started. In those cases the extra table is a small price for knowing that every committed change has a message waiting for it.

Sources

• Transactional outbox pattern, microservices.io