Your Postgres Outbox Can Silently Skip Events. Sequence Order Is Not Commit Order.

The Postgres outbox pattern fixes a real problem. A service updates a row and publishes an event, and one of the two steps fails. Write the event to an outbox table inside the same transaction as the update, and let a relay publish it afterwards. The pattern is sound. The relay most teams write first is not.

That first relay usually remembers the highest id it has published and asks for everything above it. It passes review, passes tests, and runs for months. Then a customer asks why one order never triggered its confirmation email, and the outbox table shows a row that was committed and never sent.

What the outbox promises, and what the relay has to add

The outbox gives you one guarantee: an event row exists if and only if the business change committed. It says nothing about the order in which rows become visible to other sessions. That is the relay's problem, and the relay is where the loss happens.

A typical first version looks like this: select rows where id is greater than the saved cursor, order by id, publish them, save the new cursor. It reads naturally. It also assumes that ids become visible in the order they were generated, and Postgres does not promise that.

How a Postgres outbox poller skips an event

Identity columns and sequences hand out a value when the insert runs, not when the transaction commits. Two concurrent transactions get two ids in one order and can commit in the other. Sequence values are also not rolled back, so gaps are normal and cannot be used to detect a missing row.

No error is raised and nothing is logged. The row sits in the table with a lower id than the cursor, and every later query steps over it. Ordering by a timestamp does not help: created_at is set at insert, so it has the same problem, and clocks differ between application servers on top of that.

Fix 1: drop the cursor and claim rows with SKIP LOCKED

The simplest repair is to stop tracking a position. Mark rows as published, and let each relay worker claim a batch of unpublished, committed rows. Rows that are still uncommitted are invisible to the query, so they are picked up on a later pass once they commit.

WITH batch AS (
  SELECT id
  FROM outbox
  WHERE published_at IS NULL
  ORDER BY id
  LIMIT 100
  FOR UPDATE SKIP LOCKED
)
UPDATE outbox o
SET published_at = now()
FROM batch
WHERE o.id = batch.id
RETURNING o.id, o.aggregate_id, o.type, o.payload;

This removes the loss. It does not give you ordering. In the timeline above, row 11 is published first and row 10 follows on the next pass. If two events for the same aggregate can land in that pattern, consumers see them reversed. For many workloads that is acceptable, particularly when events carry a version number and consumers ignore stale ones. For others it is the whole point of the outbox.

The row also has to survive a crash between claiming and publishing. Hold the claiming transaction open until the broker acknowledges the batch, or the published_at update will mark rows as sent that never left. Either way the guarantee is at-least-once, so consumers deduplicate on the event id.

Fix 2: poll by transaction visibility with xid8

If you need a stable order, record which transaction wrote each row and only read rows from transactions that are definitely finished. Since Postgres 13, pg_current_xact_id() returns an xid8, a 64-bit transaction id that does not wrap around. Store it on every outbox row, then compare it with the oldest transaction still in flight, which pg_snapshot_xmin(pg_current_snapshot()) reports.

CREATE TABLE outbox (
  id           bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  xact_id      xid8 NOT NULL DEFAULT pg_current_xact_id(),
  aggregate_id text NOT NULL,
  type         text NOT NULL,
  payload      jsonb NOT NULL
);
CREATE INDEX outbox_order_idx ON outbox (xact_id, id);

-- relay poll; $1/$2 are the saved cursor, start at '0' and 0
SELECT id, xact_id, aggregate_id, type, payload
FROM outbox
WHERE (xact_id, id) > ($1::xid8, $2::bigint)
  AND xact_id < pg_snapshot_xmin(pg_current_snapshot())
ORDER BY xact_id, id
LIMIT 500;

Oskar Dudycz walked through this approach in his write-up on ordering in a Postgres outbox. The reasoning is that any row with an xact_id below the snapshot minimum belongs to a transaction that has already committed or aborted, so no row can later appear behind the cursor.

The cost is that the relay is only as current as your oldest open transaction. A report query that runs for ten minutes, or a session left idle in transaction, holds the snapshot minimum back and the outbox stops advancing, even though nothing is wrong with the outbox itself. Alert on the age of the oldest transaction before you adopt this approach, not after.

Fix 3: read the WAL in commit order

Logical decoding streams changes in the order they commit, which is the property the poller was missing. Debezium can read the outbox table this way and ships an outbox event router for turning rows into messages. You get ordering and low latency, and you stop running a polling loop against the primary.

You also take on replication slots. A slot makes Postgres retain WAL until the consumer confirms it. If the connector is down for a day, the primary keeps a day of WAL on disk. Set max_slot_wal_keep_size so a dead consumer cannot fill the volume, and watch the slots view for inactive entries. Logical slots have also complicated major-version upgrades historically, so check your target version's behaviour before planning one.

Choosing between the three

Test the outbox for the race before it finds you

The race is reproducible on purpose. Open two sessions. In the first, begin a transaction and insert an outbox row, then pause. In the second, insert and commit. Run one relay pass, then commit the first session and run another. Assert that both events were published. A relay with a plain id cursor fails this test in about thirty lines of code, and it is worth keeping in CI because someone will eventually "simplify" the query.

Also add a reconciliation check: a scheduled query for committed outbox rows older than a few minutes that have no matching publish record. It will not prevent a loss, but it turns a customer complaint into an alert. Whichever relay you choose, the next thing worth examining is the consumer side, where the same at-least-once delivery shows up as duplicate handling.