The transaction outbox: what it guarantees, what it does not, and what it costs

The task

db.insert(order)
db.commit()
broker.publish(OrderPlaced(order.id))   # <- the process died here

The order exists, the event does not — the consumer will never learn about it. Swap the lines: the event exists, the order does not — the consumer processes a phantom.

There is no third order.

The solution

The event becomes a row of the same database:

CREATE TABLE outbox (
    id           bigserial PRIMARY KEY,
    payload      jsonb       NOT NULL,
    published_at timestamptz
);

db.insert(order)
db.insert(Outbox(payload={"type": "OrderPlaced", "order_id": order.id}))
db.commit()          # both rows or neither

A separate process — the relay — reads the rows and sends them to the broker.

What it costs

The outbox row adds 30 % to the transaction: it travels in the same WAL and commits with the same fsync. A separate transaction for the event costs twice as much — two fsyncs instead of one.

Which means the unsafe way is also the slower one.

The mistake that never announces itself

The most natural relay remembers how far it got:

SELECT id, payload FROM outbox WHERE id > :last_id ORDER BY id;   -- wrong

bigserial issues the number at INSERT, while the row becomes visible at COMMIT. A transaction that started earlier can commit later — and then appears behind what has already been read. The mark has moved on, and the row is lost for good.

No exception, no log line. The lag metric reads zero.

The right way is to remember nothing:

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

SKIP LOCKED also separates two relays: the first takes rows 1–3, the second 4–6, with no overlap.

What the pattern does not give

Ordering. While transactions do not overlap in time, insert order and commit order coincide. Let two overlap and that is that: the application inserted A, then B, and delivery will be B, then A. Log reading gives the same. Order survives only within a single key: put the order id into the message key, and the events of one order arrive in sequence — as long as there is one relay, because two relays with SKIP LOCKED overtake each other.

“Exactly once”. The process can crash between publishing and marking too. Swapping them is not an option — then the event would be lost, which is worse. So duplicates are unavoidable, and the consumer has to be idempotent:

INSERT INTO processed (event_id) VALUES (:id) ON CONFLICT DO NOTHING;
-- 0 rows inserted -> a duplicate, do not do the work

Inserting the id and doing the work go in one transaction. Otherwise you have rebuilt the very dual write you started from.

Two things you have to get right in production

A partial index. The relay spends almost all of its time asking “is there work” and hearing “no”. Without an index, on a 71 MB table that is 24 ms for every idle poll.

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

53 microseconds instead of 24 milliseconds. And the index does not grow with the table: with an empty queue it is 8192 bytes, while the table is 71 MB.

Cleanup. DELETE does not give the space back: half a million rows delete in 0.55 s and the table is still the same 71 MB. VACUUM returns the pages for reuse (11 MB afterwards) but hands the operating system only the tail of the file. For a noticeable flow: partitions by time and a DROP of the partition.

When you do not need it

If the recipient is the same database, write in one transaction. If the event can be lost without consequence, publish directly.

It is mandatory where the event causes an irreversible action — a charge, a shipment, a letter — and a divergence will not be noticed at once.

A problem with no direct solution

The handler does two things: it writes the order to the database and tells the broker about it.

def place_order(request):
    order = Order(total=request.total)
    db.insert(order)
    db.commit()
    broker.publish(OrderPlaced(order.id))

Between commit() and publish() the process can die. Not “in theory”: a deploy, the OOM killer, a lost connection to the broker, a timeout. Swap the lines and it does not get better — it gets different.

Both orders are wrong, and in different ways. A lost event is quiet: each system believes everything is fine, and you find out from a user. A phantom event is loud: the consumer reserves stock or sends a letter about an order that does not exist.

There is no third order. As long as the broker takes no part in the database's transaction, there is always a moment between the two writes.

A distributed transaction across two resources would solve this problem — but the price is a coordinator and locks held for the duration of the agreement. And for a broker it is not an option at all: Kafka does not take part in two-phase commit.

The solution: make the event a row of the same database

CREATE TABLE outbox (
    id           bigserial PRIMARY KEY,
    payload      jsonb       NOT NULL,
    published_at timestamptz
);

def place_order(request):
    order = Order(total=request.total)
    db.insert(order)
    db.insert(Outbox(payload={"type": "OrderPlaced", "order_id": order.id}))
    db.commit()          # both rows or neither

Notice what is not here. There is nothing specific to the pattern: this is ordinary transactional atomicity. The whole invention is that the message stopped being a message and became a row.

Next you need a second process — the relay — which reads the rows and sends them to the broker. That is where all the real questions live.

payload jsonb is not a schema, it is the absence of one

In the table above payload is a jsonb column, and that is a simplification you pay for later. The relay neither reads nor validates its contents:

So between producer and consumer there is not one point where an incompatible change would be noticed. It surfaces at the consumer, in production, on messages that can no longer be un-sent.

Debezium's reference shape for the table is therefore wider than three columns:

id            | uuid                   | not null
aggregatetype | character varying(255) | not null
aggregateid   | character varying(255) | not null
type          | character varying(255) | not null
payload       | jsonb                  |

Every column here carries something you would otherwise have to solve in code. aggregatetype decides the topic name, aggregateid becomes the message key (and therefore the partition, and therefore the order), type travels as a header, and id is what the consumer deduplicates by: You can use this ID, for example, to remove duplicate messages.

The difference from the bigserial in our DDL matters, and the next section comes back to it.

On the envelope itself. The moment there is more than one field, the message value stops being a bare payload:

What to do about a schema change. The compatibility rules need not be invented — they are written down in the formats built for this. Avro states them as reading rules, and both directions of compatibility follow directly:

The first rule covers an old reader against a new writer, the second a new reader against an old writer. Neither holds if the new field has no default: then the old message simply will not read.

Protocol buffers name the same three rules outright: Adding new fields is safe, Removing fields is safe and, against them, Changing field numbers for any existing field is not safe. Plus one caveat that has no counterpart in a JSON schema: a removed number must not be reused, which is what reserved is for.

The practical rule for an outbox falls out of this by itself: you cannot change the meaning of an existing field; you can add a field that has a default; you can remove one no consumer requires. Everything else is a new type, not a new version of the old one. And if you want a check rather than a gentlemen's agreement, Debezium names the way:

Replaying events: why it is not "just read the table again"

Sooner or later a topic has to be refilled: a consumer appeared after the events, the topic was deleted, the data was corrupted. Debezium lists the occasions outright:

And immediately after, the sentence this section exists for:

Appends, not replaces. The consumer gets a second copy of everything. This is not a defect of the tool: a topic has no "overwrite" operation, and cannot have one. So the whole weight of replaying falls on deduplication at the consumer — the very thing that at-least-once delivery makes mandatory, and which has a section of its own below:

And here the id bigserial in our DDL stops being good enough. Deduplication works on a stable event identifier, and a bigserial hands out a new number on replay — the same INSERT … ON CONFLICT DO NOTHING will let the duplicate through as a new event. That is why Debezium's id is a uuid set on insert rather than by a sequence. Changing this is cheaper before the first replay than after.

A second consequence, and it contradicts the section on table growth below. The advice to delete sent rows — DROP a partition rather than DELETE — saves space at precisely the cost of the ability to replay. A dropped partition is deleted history. There is one question to settle here, and better explicitly: how far back are you prepared to replay events. The partition retention follows from that answer, not the other way round.

As for the consumer side (reset the offset and read the topic again) — the same thing from the other end: duplicates come not only from re-sending but from re-reading, and one and the same table of processed identifiers protects against both.

What it costs

The objection to an outbox is almost always the same one: an extra write on the hot path. The objection is testable.

The outbox row adds 30 % to the transaction — it travels in the same WAL and commits with the same fsync as the order. Meanwhile the popular way to “keep the transaction light” — writing the event separately — costs twice as much, because it has two commits and two fsyncs. The unsafe option is the slower one.

This is the case where intuition points exactly the wrong way: people avoid the outbox for performance reasons and end up with the slower solution.

The relay: two ways, and not a matter of taste

The difference is not reliability: both of them deliver everything. There are five differences, and the usual deciding one is the last — the cost of running it:

Debezium, the main implementation of log reading, states the requirement outright:

Which means the table for these two ways looks different, and moving from one to the other is not a change of process but a schema migration.

The mistake that costs the most

The most natural implementation of a polling relay remembers how far it got:

SELECT id, payload FROM outbox WHERE id > :last_id ORDER BY id;

It is wrong, and it breaks not in a rare case but in the ordinary one.

The cause is that two moments in a row's life have come apart. bigserial issues the number at INSERT — from a sequence, outside the transaction. The row becomes visible to others at COMMIT. Between those two moments lies as much time as the transaction takes, and in that time anyone can take a larger number and commit sooner.

The rest is arithmetic: the relay saw row 2, raised its mark to 2, and row 1 appeared afterwards. The condition id > 2 will never find it again.

No exception, no log line. A “relay lag” metric built as max(id) - last_id reads zero: as far as it is concerned, everything has been sent.

The cure is to give up the memory — the state moves into the row itself:

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

The condition “not published” remembers nothing, so it finds the row again no matter how long it sat uncommitted. Log reading does not have this problem by construction: the log is a sequence of commits, and entries appear in it in the order the commits happened.

What the pattern does not give: ordering

Among the benefits on the pattern's canonical page stands this:

The promise holds while transactions do not overlap in time: then insert order and commit order coincide and there is nothing to argue about. Let two overlap and it stops being true. The experiment is exactly the one from the previous section: the application inserted A, then B, and they will be delivered B, then A — because B committed first.

Through log reading, the same. test_decoding shows the transaction carrying row id = 2 before the transaction carrying row id = 1, and the one that arrived first has the larger transaction number: it started later and committed earlier. So the order is neither by row number nor by transaction number, but by the moment of commit.

The page about the polling relay admits the same difficulty — Tricky to publish events in order sits among its drawbacks. The measurement sharpens what kind of difficulty it is: not one that care can overcome, but a property of any relay on PostgreSQL.

What to do about it. You will not have a global order — you cannot lean on one, and you must not write the consumer as if it existed. Order can be preserved only within a single key, and only while a single relay reads the table: the two relays with SKIP LOCKED from the section below break that too. That is what the production implementation is built on: Debezium puts the aggregate id into the Kafka message key — This is important for maintaining correct order in Kafka partitions. Events of one order land in one partition and arrive in order; events of different orders are not ordered against each other — and should not be.

What the pattern does not give: “exactly once”

The relay does two things: it publishes and it marks. Between them, as at the very start of this article, the process can crash.

You cannot swap them: then a crash between them would lose the event, which is strictly worse than a duplicate. So the choice fell on the duplicate, and it is final — at the sender's level this problem has no solution at all.

Which means idempotency has to live at the consumer. The usual way is a table of processed ids:

INSERT INTO processed (event_id) VALUES (:id) ON CONFLICT DO NOTHING;
-- 0 rows inserted -> a duplicate, do not do the work

The key point is that inserting the id and doing the work go in one transaction. Otherwise you have rebuilt the very dual write you started from.

Two relays

One relay is a single point of failure and a ceiling on throughput. Two relays on one table block each other unless you take a specific measure: the second waits for the first to release its rows.

Checked: with SKIP LOCKED the first relay took rows 1–3 and the second 4–6, with an empty intersection. Without it the second relay dies on a lock_timeout = 300ms without taking a single row — it does not speed anything up, it idles.

The caveat about an inconsistent view is not fine print here. It is precisely what means the order is broken once more: the second relay overtakes the first. For a queue that is acceptable exactly because we no longer have an order.

Before adding a second relay it is worth turning the cheaper knob. A batch of one gives 1,627 rows/s; a batch of a thousand, 104,656 — almost all of the difference is commits, and one process with a large batch outruns several processes with a small one. And with a single relay, order within a key is still intact.

The table that grows

Two things break an outbox in production, and both are about size.

The first is the idle poll. The relay spends nearly all of its time asking “is there work” and hearing “no”. If the queue is empty while the published rows sit in the same table, a query with no suitable index scans everything that has piled up: on a 71 MB table that is 24 milliseconds for every idle poll.

A partial index fixes this.

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

24 milliseconds turn into 53 microseconds. And the crucial property: the index does not grow with the table. With an empty queue it occupies 8192 bytes — one page, the minimum — while the table next to it is 71 MB. An ordinary index on published_at cannot do this: it holds an entry for every row.

The second is cleanup. Published rows have to be deleted, and DELETE does not give the space back: half a million rows delete in 0.55 s, and the table is still the same 71 MB. VACUUM will return the pages for reuse (0.10 s, 11 MB afterwards), but it hands the operating system only the tail of the file.

Hence the usual advice for a noticeable flow: partitions by time and a DROP of the partition instead of a DELETE. DROP removes the file whole — and that is the only operation that returns space immediately.

When you do not need an outbox

The pattern solves one task: reconciling a write to the database with a message going out. If there is no such task, it is redundant.

• If the recipient is the same database, no outbox is needed: write in one transaction.
• If the message can be lost without consequence (a metric, a log line, a cache warm-up), the pattern does not pay for itself: publish directly.
• If the consumer re-reads state from the database regularly anyway, the event is only a hint to look sooner, and losing it costs latency rather than a divergence.

And one case where it is mandatory: when the event causes an irreversible action somewhere — a charge, a shipment, a letter — and a divergence will not be noticed at once.

The drawback is named precisely: you can forget to insert the outbox row too. The difference is that a forgotten row is a mistake in one place in the code, visible in review, whereas a diverging dual write is a property of the architecture, visible nowhere.

Reproducing the numbers

Two scripts, both against a live database, both printing what PostgreSQL returned.

createdb -p 5433 outbox_bench
python3 relay.py     # eight observations, not a single timing
python3 cost.py      # four blocks of measurements

The scripts live in the repository's measurement directory, next to a write-up of what came out. psycopg 3 is required; the database address comes from OUTBOX_DSN. Section 5 of relay.py reads the WAL and needs wal_level = logical — at any other level it says so and skips itself, while the other seven run anywhere.

The published run: PostgreSQL 16.13, synchronous_commit = on, fsync = on, psycopg 3.3.4, Python 3.11.15, August 2026. Everything measured here bottoms out in fsync, so on another disk the absolute values will differ — what carries over are the ratios within a block and the orders of magnitude.

What measured this

The numbers in this article come from these scripts. Each one opens from here, together with the record of the run: what it was measured on, what came out, and with what spread.

• bench/outbox/cost.py
• bench/outbox/relay.py