The Transactional Outbox Pattern with PostgreSQL

Every backend that writes to a database and then publishes an event to a queue eventually hits the same bug: the database commit succeeds, but the message never makes it to the broker, or the reverse happens and a duplicate event goes out. This is the dual-write problem, and it shows up in payment systems, inventory updates, and any workflow where other services need to react to a change.

The transactional outbox pattern solves this by writing the event into the same database transaction as the business change, then relaying it out of band. It trades a small amount of latency and an extra table for a guarantee that is otherwise very hard to get: the event exists if and only if the business row exists. This post walks through the outbox table schema, the write path, a polling relay built on PostgreSQL row locking, ordering guarantees, and where the pattern is overkill.

Why two writes to two systems are never safe

The naive approach looks reasonable on paper: insert the order row, commit, then publish an order placed event to the message broker. The problem is that these are two separate resources with no shared transaction. Between the commit and the publish call, several things can go wrong.

• The process crashes after the database commit but before the publish call completes, so the event is silently lost.
• The publish call times out or the broker is briefly unreachable, so the caller retries and a duplicate event is sent, or the caller gives up and the event is lost.
• The database transaction rolls back after the event was already published, so downstream consumers react to a change that never actually happened.

None of these are exotic failure modes. They happen under normal network flakiness and normal process restarts, and they get worse under load, which is exactly when correctness matters most. The two-phase commit protocol was designed to solve this class of problem for distributed transactions, but it requires the broker to participate as a transactional resource, which most message queues and event brokers do not support.

The outbox table

The core idea is to add a plain table in the same database as your business tables, and treat writing to it as no different from writing to any other row. No new infrastructure, no distributed transaction coordinator, just a table that lives inside a transaction boundary you already control.

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,
  status         TEXT NOT NULL DEFAULT 'pending',
  available_at   TIMESTAMPTZ NOT NULL DEFAULT now(),
  attempts       INT NOT NULL DEFAULT 0,
  locked_at      TIMESTAMPTZ,
  locked_by      TEXT,
  created_at     TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX outbox_pending_idx
  ON outbox (available_at)
  WHERE status = 'pending';

The status and available_at columns exist so the relay can distinguish pending events from ones being retried later, and the locked_at and locked_by columns exist so multiple relay workers can run concurrently without double-processing the same row. The partial index on pending rows keeps the relay's polling query fast even once the table has millions of historical rows.

Writing the event with the business change

The write path is the entire trick: wrap the business insert and the outbox insert in one database transaction. If either write fails, the whole transaction rolls back and neither row exists. If the transaction commits, both rows exist. There is no window where one exists without the other.

await db.transaction(async (trx) => {
  const order = await trx('orders').insert({
    customer_id: customerId,
    total_cents: totalCents,
    status: 'placed',
  }).returning('id');

  await trx('outbox').insert({
    aggregate_type: 'order',
    aggregate_id: order[0].id,
    event_type: 'order.placed',
    payload: JSON.stringify({ orderId: order[0].id, totalCents }),
  });
});

The relay: polling without stepping on other workers

The relay is a background worker that repeatedly polls the outbox table for pending rows, publishes each one to the broker, and marks it as sent. Running more than one relay worker for throughput or high availability introduces a new problem: two workers could pick up the same row at the same time and publish it twice. PostgreSQL's row-level locking clauses solve this directly.

SELECT id, event_type, payload
FROM outbox
WHERE status = 'pending' AND available_at <= now()
ORDER BY id
LIMIT 50
FOR UPDATE SKIP LOCKED;

SKIP LOCKED tells PostgreSQL to skip any row that another transaction already has locked, rather than blocking until it is free. Each worker locks a batch, publishes it, updates the status, and commits, and any other worker polling at the same moment simply skips those rows and picks up the next unclaimed batch. This turns the outbox table into a safe multi-consumer queue without a distributed lock manager.

Keeping event order intact

Most consumers care about the order in which events for the same entity arrive, even if global ordering across all entities does not matter. Three practices keep ordering intact without adding a separate sequencing service.

1. Use a single auto-incrementing primary key on the outbox table and always select and publish in ascending order of that key, never by insertion timestamp, since clocks and batching can reorder timestamps.
2. Partition delivery by aggregate id when publishing to a partitioned topic, so all events for the same order or customer land on the same partition and are consumed in order by a single consumer instance.
3. Never parallelize publishing within a single aggregate id across relay workers, even though SKIP LOCKED allows concurrent processing across different aggregates.

Cleaning up a table that only grows

An outbox table that is never pruned becomes the slowest table in the database within a few months. Since every row is either pending or already sent, and sent rows are only useful for auditing and debugging, cleanup is straightforward.

• Run a scheduled job that deletes sent rows older than a retention window, typically seven to thirty days depending on how far back you need to replay or debug.
• Delete in small batches rather than one large statement, since a single delete touching millions of rows can hold locks long enough to stall the relay's own polling query.
• Consider partitioning the table by month if event volume is high, so old partitions can simply be dropped instead of deleted row by row.

When the pattern is not worth it

The outbox pattern adds a table, a relay worker, and operational surface area. It earns its keep when a missed or duplicated event has real business cost, such as a payment confirmation or an inventory adjustment. It is overkill in a few common situations.

• The event is purely advisory, like a cache invalidation hint, where an occasional lost message causes at most a stale read that self-corrects on the next write.
• Your consumer already tolerates duplicates and missed messages by re-deriving state from a periodic full sync, which makes strict delivery guarantees unnecessary.