delay.run

Posted on Nov 9

The transactional outbox, explained: how to save to the database and enqueue a job without losing either

#backend #database #architecture #queues

Here's a piece of code that looks completely reasonable:

def place_order(cart):
    order = db.insert_order(cart)      # 1. save to the database
    queue.enqueue("send_confirmation", order_id=order.id)  # 2. enqueue a job
    return order

And here's the problem. There are two separate systems involved: the database and the queue. Nothing makes those two steps happen together.

If the process crashes between step 1 and step 2, the order exists but the confirmation email is never sent.
If you swap the order, enqueue first and then save, a crash in between means the worker sends a confirmation for an order that doesn't exist.
If the database transaction rolls back after you enqueued inside it, same problem: a job for something that never happened.

This is sometimes called the dual-write problem. The transactional outbox pattern is the standard, boring and effective fix.

The idea in one sentence

Instead of writing to the database and the queue, write only to the database, including a record of the job you want to run, in the same transaction. A separate process then moves those records into the queue.

Step by step

1. Add an outbox table.

CREATE TABLE outbox (
  id           bigserial PRIMARY KEY,
  job_type     text        NOT NULL,
  payload      jsonb       NOT NULL,
  created_at   timestamptz NOT NULL DEFAULT now(),
  published_at timestamptz
);

2. Write the business change and the outbox row in one transaction.

def place_order(cart):
    with db.transaction():
        order = db.insert_order(cart)
        db.insert_outbox("send_confirmation", {"order_id": order.id})
    return order

Now the database guarantees it: either the order and its outbox row both exist, or neither does. There's no gap for a crash to fall into.

3. Run a relay that publishes outbox rows.

A small background process repeatedly picks up unpublished rows, sends them to the queue, and marks them published.

while True:
    with db.transaction():
        rows = db.query("""
            SELECT id, job_type, payload FROM outbox
            WHERE published_at IS NULL
            ORDER BY id
            LIMIT 100
            FOR UPDATE SKIP LOCKED
        """)
        for row in rows:
            queue.enqueue(row.job_type, **row.payload)
            db.execute("UPDATE outbox SET published_at = now() WHERE id = %s", row.id)
    if not rows:
        time.sleep(1)

FOR UPDATE SKIP LOCKED lets you run more than one relay without them grabbing the same rows.

The catch: at-least-once, not exactly-once

Look closely at the relay. If it enqueues a job and then crashes before marking the row published, the next run will enqueue it again.

That's not a bug in the pattern; it's the trade-off. The outbox converts "might lose the job" into "might deliver the job twice". Losing work silently is much worse than duplicating it, as long as the job can handle duplicates.

So the outbox pattern goes hand in hand with idempotent consumers: workers that check whether they've already handled a given outbox ID (or a business key, like "confirmation email for order 123") before acting. Pass the outbox row's ID along in the payload so consumers have something stable to deduplicate on.

Variations

Skip the queue entirely. If your workers can read from Postgres directly, the outbox table is the queue. Workers claim rows with SKIP LOCKED, do the work and mark them done. One fewer system to run.

Change data capture. Instead of polling, some setups read the database's write-ahead log and stream new outbox rows to a broker. Lower latency, more infrastructure.

Delayed jobs. Add a run_at column, and the relay only publishes rows whose time has come. Now scheduling a reminder for next week is part of the same transaction as creating the appointment.

Housekeeping

The outbox grows forever unless you clean it. Delete or archive published rows after a retention period, keeping them long enough to debug "did we ever send this?" questions. An index on unpublished rows keeps the relay's query fast:

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

And monitor two numbers: the count of unpublished rows and the age of the oldest one. If either keeps growing, the relay is stuck or too slow, and every job in your system is waiting behind it.

Ordering, and why you might not get it

Relays usually publish rows in ID order, but that doesn't guarantee consumers process them in that order. With several workers, a later job can easily finish before an earlier one. If order matters, for example "account created" must be handled before "account upgraded", either process jobs for the same entity on a single partition or worker, or make each consumer check the current state in the database rather than trusting the order of events. The second approach is usually simpler and more robust.

Testing it

The pattern is only as good as its failure behaviour, so test the failures directly. Kill the relay between enqueueing and marking a row published, and confirm the job is delivered again and handled safely. Roll back a transaction that wrote an outbox row, and confirm nothing is published. Stop the relay for ten minutes, and confirm your alert on the oldest unpublished row fires.

When you don't need it

If losing an occasional job is genuinely harmless, say a non-essential analytics event, the simple two-step code may be fine. And if your queue lives in the same database as your data, enqueuing inside the transaction already gives you the same guarantee.

The outbox earns its keep when the job matters: confirmations, payments, webhooks to customers, anything a person will notice if it never happens.

Why we care

"Did the job actually get scheduled?" is the question behind a surprising number of support tickets. Making scheduling part of the same commit as the change that caused it is one of the foundations we rely on at delay.run.

DEV Community