Transactional Outbox Pattern for Job Enqueue

The dual-write problem is where exactly-once vs at-least-once delivery starts, not ends: before a message can be delivered once, it has to be published at all, exactly when the database transaction that justified it commits. This guide, part of Queue Fundamentals & Architecture, implements the outbox pattern that makes the two agree.

Problem Statement

Your checkout handler writes an orders row and then publishes a send_receipt job. Two failure modes are already in production. If the publish fails after the commit, the order exists with no receipt — silent data loss that only surfaces when a customer complains. If the commit is rolled back after a successful publish, a worker processes a receipt for an order that does not exist and the job dead-letters. You want the job to be published if and only if the transaction commits.

Prerequisites

  • A transactional database — Postgres or MySQL here; the pattern needs real transactions.
  • A broker your relay can publish to, with at-least-once semantics accepted downstream.
  • Idempotent consumers, because the outbox guarantees at-least-once publication, never exactly-once.
  • Permission to run a background relay process (or a change-data-capture pipeline).

Step 1 — Understand Why the Dual Write Cannot Be Fixed Directly

There is no ordering of "commit" and "publish" that is safe, because the process can die between them and the two systems cannot participate in one atomic operation without distributed transactions.

Dual write versus the transactional outbox The top row shows committing first then publishing, where a crash after the commit loses the job. The middle row shows publishing first then committing, where a rollback leaves a job referring to data that does not exist. The bottom row shows the outbox: the message row is written inside the same transaction as the business data, and a relay publishes it afterwards, so publication happens if and only if the transaction committed. Two systems, one atomic decision commit → publish COMMIT crash order exists, no job — silent loss publish → commit PUBLISH ROLLBACK job for an order that never existed outbox one transaction INSERT order + INSERT outbox row relay polls + publishes broker Published if and only if committed. Possibly more than once — never zero times. The cost is latency: the job appears on the broker one relay interval after the commit.

Step 2 — Create the Outbox Table

CREATE TABLE job_outbox (
    id            BIGSERIAL PRIMARY KEY,
    queue         TEXT        NOT NULL,
    task          TEXT        NOT NULL,
    payload       JSONB       NOT NULL,
    dedup_key     TEXT        NOT NULL,        -- consumers use this for idempotency
    created_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
    published_at  TIMESTAMPTZ,                 -- NULL = still pending
    attempts      INT         NOT NULL DEFAULT 0
);

-- The relay's only query: unpublished rows, oldest first. A partial index keeps
-- it fast even when the table holds millions of already-published rows.
CREATE INDEX job_outbox_pending
    ON job_outbox (id) WHERE published_at IS NULL;

The partial index is the difference between a relay that stays fast and one that degrades as the table grows. Without it, every poll scans an ever-larger table for the small pending set.

Step 3 — Write the Message Inside the Business Transaction

from sqlalchemy import text

def place_order(session, user_id: int, items: list) -> int:
    order_id = session.execute(
        text("INSERT INTO orders (user_id, total) VALUES (:u, :t) RETURNING id"),
        {"u": user_id, "t": total_of(items)},
    ).scalar_one()

    # Same session, same transaction: this row cannot exist without the order,
    # and the order cannot exist without this row.
    session.execute(
        text("""INSERT INTO job_outbox (queue, task, payload, dedup_key)
                VALUES (:q, :task, :payload, :dedup)"""),
        {"q": "receipts", "task": "send_receipt",
         "payload": json.dumps({"order_id": order_id}),
         "dedup": f"receipt:{order_id}"},
    )
    return order_id      # caller commits; both rows land or neither does

No broker call appears anywhere in the request path. That is the entire point: the request only touches one system, so there is nothing to be inconsistent with.

Step 4 — Run the Relay

The relay claims a batch, publishes it, and marks it published. FOR UPDATE SKIP LOCKED lets several relay instances run concurrently without processing the same row.

CLAIM = text("""
    SELECT id, queue, task, payload, dedup_key
    FROM job_outbox
    WHERE published_at IS NULL
    ORDER BY id
    LIMIT :batch
    FOR UPDATE SKIP LOCKED
""")

def relay_once(session, broker, batch: int = 200) -> int:
    rows = session.execute(CLAIM, {"batch": batch}).mappings().all()
    for row in rows:
        # Publish BEFORE marking. A crash here republishes on the next pass,
        # which is why consumers must be idempotent.
        broker.publish(queue=row["queue"], task=row["task"],
                       payload=row["payload"],
                       headers={"dedup_key": row["dedup_key"]})
        session.execute(
            text("UPDATE job_outbox SET published_at = now(), attempts = attempts + 1 "
                 "WHERE id = :id"), {"id": row["id"]})
    session.commit()
    return len(rows)

Publish-then-mark is deliberate. Marking first would lose a message if the publish failed, which reintroduces exactly the problem the outbox exists to solve. At-least-once publication plus an idempotent consumer is the correct end state.

Step 5 — Keep the Table Small

An outbox that is never pruned becomes the largest table in the database within months.

-- Delete published rows older than the replay window you actually need.
DELETE FROM job_outbox
WHERE published_at < now() - INTERVAL '7 days';

Run it as a scheduled job with a LIMIT and a loop rather than one enormous statement, so it never holds a long transaction that blocks the relay. Seven days is a reasonable default: long enough to reconstruct what was published during an incident, short enough that the table stays in cache.

Step 6 — Monitor Outbox Lag

Outbox lag as the primary health signal A line showing the age of the oldest unpublished outbox row. In normal operation it hovers just above the relay poll interval. When the relay process stops, the line rises steeply and continuously, which is the signal to alert on, while pending row count alone would look like ordinary queue depth. Age of the oldest unpublished row 60s 0 healthy — ≈ one relay interval relay stopped — jobs exist but nothing is published Alert on age, not on pending count: a big batch is normal, an old row never is. This is the only signal that catches a silently dead relay. 
-- Export as a gauge; alert when it exceeds a few relay intervals.
SELECT COALESCE(EXTRACT(EPOCH FROM now() - MIN(created_at)), 0) AS lag_seconds
FROM job_outbox WHERE published_at IS NULL;

A dead relay is invisible everywhere else: the application succeeds, the database is healthy, the queue looks quiet because nothing is arriving. Lag is the only signal, and it belongs in the alerting practice described in SLOs & alerting for job queues.

Step 7 — Decide Whether You Need It

The outbox is not free: a table, an index, a background process, a pruning job, and a lag alert. It is worth that cost for some jobs and clearly not for others, and applying it uniformly is how a team ends up with a relay publishing analytics events at ten thousand rows a second.

Job characteristic Outbox? Why
Money moves, or a legal record is created Yes Silent loss is unacceptable at any rate
The job is the only trigger for a user-visible effect Yes Nothing else will notice it never ran
A reconciliation job would catch the gap anyway No The sweep already provides the guarantee
Analytics, metrics, cache warming No Loss is tolerable; volume makes the table expensive
The publish target is the same database No Use a table-backed queue and one transaction

The middle row is the one worth thinking about. Many systems already run a nightly reconciliation — "find orders with no receipt and send them" — and that sweep provides an eventual guarantee more cheaply than an outbox does. If such a sweep exists and its latency is acceptable, the outbox is duplicated effort. If it does not exist, note that you are choosing between building the outbox and building the sweep, and the outbox generally wins because it bounds the gap at seconds rather than a day.

The last row is a genuine simplification people miss: if the "queue" can live in the same database as the business data, a jobs table polled with FOR UPDATE SKIP LOCKED gives real transactional enqueue with no relay at all. That stops scaling somewhere in the low thousands of jobs per second — above that a dedicated broker plus an outbox is the right shape.

When the outbox earns its operational cost Three columns. Work where money moves or a legal record is created justifies the outbox. Work already covered by a reconciliation sweep does not, because the sweep provides the guarantee more cheaply. High-volume analytics events do not, because loss is tolerable and the table cost is significant. Not every enqueue needs an outbox Yes — build it money moves a legal record is created nothing else would notice a gap a user is waiting on the effect No — already covered a nightly reconciliation exists its latency is acceptable the sweep is cheaper to run and easier to reason about No — too expensive analytics and metrics events cache warming loss is tolerable at this volume the table would dominate the database If the queue can live in the same database, a jobs table with SKIP LOCKED removes the relay entirely.

Verification

  1. Rollback publishes nothing. Force an exception after the outbox insert; the table must contain no row and the broker no message.
  2. Crash after commit still publishes. Kill the process between commit and the next relay pass; the job must appear on the broker within one interval.
  3. Concurrent relays do not duplicate. Run three relay instances against 10,000 pending rows; each row must be published once — SKIP LOCKED is what guarantees it.
  4. Lag stays flat. Under sustained load, oldest-pending age must hover near the poll interval rather than trending upward.

Gotchas & Edge Cases

Ordering is per-relay, not global. Multiple relays publish concurrently, so messages can reach the broker out of id order. If a consumer needs ordering, partition the outbox by key and give each partition a single relay — the same reasoning as queue partitioning strategies.

Polling adds latency. A one-second interval means a receipt job starts up to a second later than a direct publish would. For latency-critical work, notify the relay with LISTEN/NOTIFY on commit and keep polling as the fallback.

The relay is a singleton per partition, and it can die quietly. Give it a liveness check driven by lag rather than by process existence; a relay stuck on a poison row is "running" and publishing nothing.

A poison row blocks the batch. One row whose payload the broker rejects will be retried forever, and ORDER BY id means it blocks everything behind it. Track attempts and move rows past a threshold to a quarantine table, mirroring dead-letter queue handling.

Change data capture is the alternative. Debezium and similar tools tail the write-ahead log instead of polling, removing the relay's latency and load. The trade is significant operational surface — for most teams a 200-row poll every 500ms is simpler and fast enough.

Related