Transactional Outbox and Inbox

What we're doing: Show an outbox table left unbounded because it was treated as a permanent log, and the cleanup fix.

Outbox table has been running in production for
8 months, at a steady 50,000 events/day, with a
correctly-working relay marking rows published_at
on confirmed delivery -- but nothing ever DELETES
a published row.

Result after 8 months:
  ~12 million rows, 99.9%+ of them already
  published and never needed again, but still
  sitting in the table.

The relay's own query -- "find unpublished rows"
-- now has to scan through, or index around, 12
million mostly-irrelevant rows just to find the
small handful that are actually still pending.
The table that was meant to stay small and fast
degrades in performance purely from never having
a cleanup policy, even though the actual publish
logic was correct the entire time.

8

This is the accumulation — every one of these rows did its job successfully and then was simply never removed.

13

This is the real cost — a table designed to be small and fast for one narrow query now pays a growing tax for holding data it no longer has any use for.

Why this works: This is the concrete consequence of treating an outbox table as if it were a permanent event log — the publish logic itself can be entirely correct while the table still degrades, purely because retention was never designed as part of the table's lifecycle.

What we're doing: Show a polling outbox relay loop, including the crash-safety gap that makes at-least-once delivery unavoidable.

loop every 500ms:
  rows = SELECT * FROM outbox_events
         WHERE published_at IS NULL
         ORDER BY created_at
         LIMIT 100

  for row in rows:
    broker.publish(row.event_type, row.payload)
    -- if the process crashes on THIS line, after
    -- publish() succeeded but before the UPDATE below
    -- runs, the row is still published_at = NULL
    UPDATE outbox_events
      SET published_at = now()
      WHERE id = row.id

  -- next loop iteration will re-select and
  -- re-publish any row still showing NULL —
  -- this is why the pattern is at-least-once,
  -- never exactly-once, on the publish side

8

The publish call itself is not transactional with the UPDATE that follows it — a crash between the two is possible and must be assumed.

16

Any row still unmarked gets re-published on the next loop iteration — the relay guarantees at-least-once delivery, not exactly-once.

Why this works: The gap between "broker confirmed" and "row marked published" cannot be closed with a single local transaction the way the original outbox write could — the broker is a separate system, so the relay itself is only ever at-least-once, which is exactly why the inbox pattern on the consumer side is not optional, it is what makes that at-least-once delivery safe to act on.

What we're doing: Show a payment-confirmation consumer using an inbox table so a redelivered event does not double-credit an account.

-- event: {"event_id": "evt_9f2", "type": "payment_confirmed",
--          "account_id": 42, "amount": 500}

BEGIN;
  -- 1. try to record this event as processed FIRST
  INSERT INTO inbox_processed_events (event_id)
    VALUES ('evt_9f2');
  -- if evt_9f2 was already recorded, this INSERT fails
  -- with a unique-constraint violation right here —
  -- the transaction aborts before any balance changes

  -- 2. only reached if step 1 succeeded (first delivery)
  UPDATE accounts SET balance = balance + 500
    WHERE account_id = 42;
COMMIT;

-- Redelivery of the exact same evt_9f2 later:
BEGIN;
  INSERT INTO inbox_processed_events (event_id)
    VALUES ('evt_9f2');   -- fails: duplicate key
  -- transaction rolls back — balance update never runs
ROLLBACK;

6

The event ID is recorded FIRST, before any business effect — this ordering is what makes a duplicate detectable before it can do damage.

13

The balance update only executes if the insert above succeeded — both are in the same transaction, so there is no window where one runs without the other.

19

On redelivery, the insert alone fails and the whole transaction (including the balance update that was never reached) rolls back — the account is credited exactly once.

Why this works: A message being delivered twice is normal at-least-once behavior, not a rare edge case — without an inbox table, a redelivered payment-confirmed event would credit the account a second time, and no isolation level or retry logic on the sender's side can fix that, because the fix has to live on the consumer.