All notes
Programming

Transactional outbox: the order committed, the event vanished

Writing to the database and then publishing to a broker is a write to two systems, and if the process dies in between, the event silently disappears. Here's how an outbox table, a SKIP LOCKED relay and an idempotent consumer close that gap.

The problem: the order exists, the event doesn't

Picture this: a user places an order. A Django view writes an Order row to PostgreSQL, then publishes an order.created event to a Redis Stream (RabbitMQ or Kafka would behave the same). Billing reads the event and creates an invoice, the warehouse reserves stock, the notification service sends an SMS.

It works for weeks. Then, during a deploy, a pod gets SIGKILL — exactly in the milliseconds after COMMIT finished but before publish() was called. The order is in the database, the customer has paid, and billing has no idea. There's no error in the logs, because nothing errored: the process simply died.

The reverse happens too: the message reaches the broker, then the transaction rolls back. Now the warehouse is reserving stock for an order that doesn't exist.

This is the dual write problem: one business action is written to two independent systems with no shared transaction between them. Today we'll close that gap with the transactional outbox pattern: the table, the relay process, SKIP LOCKED, ordering, duplicates and consumer-side idempotency.

Why the naive approaches break

The first attempt usually looks like this:

# orders/services.py — NAIVE
from django.db import transaction

from orders.events import publish
from orders.models import Order


def place_order(user, cart):
    with transaction.atomic():
        order = Order.objects.create(user=user, total=cart.total)
        publish("order.created", {"order_id": order.pk, "total": str(order.total)})
        # if this raises, the order rolls back,
        # but the event is already in the broker
        reserve_stock(order, cart)
    return order

Here publish() runs inside the transaction. If reserve_stock() raises, the order rolls back while the message is already in the broker — a phantom event. On top of that, if the broker is slow, the transaction stays open and keeps its row locks. If the broker is down, orders fail entirely: a secondary system now blocks your core business flow.

An experienced developer "fixes" this with transaction.on_commit():

def place_order(user, cart):
    with transaction.atomic():
        order = Order.objects.create(user=user, total=cart.total)
        reserve_stock(order, cart)
        transaction.on_commit(
            lambda: publish(
                "order.created",
                {"order_id": order.pk, "total": str(order.total)},
            )
        )
    return order

No more phantoms: the callback runs only after a successful COMMIT. But there's still a window between COMMIT and the callback. If the process dies there, the broker is unavailable at that second, or the network times out, the event is gone, with no trace left to retry from. on_commit lives in memory, not on disk.

The next idea is two-phase commit. PostgreSQL has PREPARE TRANSACTION, but Redis and most brokers can't take part in a distributed transaction, and the coordinator becomes its own reliability problem. In practice almost nobody goes this way.

You can't write atomically to two systems. You can write atomically to one — the database. That's the whole idea of the outbox: write the event to the database too.

The mechanism: an outbox table in the same transaction

The idea in three steps:

  1. Business data (Order) and the event (OutboxEvent) are written in one transaction to one database. Either both commit or neither does.
  2. A separate process — the relay (sometimes called a poller) — reads unpublished rows from the outbox, publishes them to the broker and sets published_at.
  3. The relay can die after publishing but before marking. So delivery is at-least-once, and the consumer must be ready for duplicates.

The result: "the message was lost" turns into "the message arrived twice". The second problem is far easier to solve with idempotency.

The model:

# orders/models.py
import uuid

from django.db import models


class OutboxEvent(models.Model):
    id = models.BigAutoField(primary_key=True)
    event_id = models.UUIDField(default=uuid.uuid4, unique=True, editable=False)
    aggregate_type = models.CharField(max_length=50)
    aggregate_id = models.CharField(max_length=64)
    event_type = models.CharField(max_length=100)
    payload = models.JSONField()
    created_at = models.DateTimeField(auto_now_add=True)
    published_at = models.DateTimeField(null=True, blank=True)
    attempts = models.PositiveIntegerField(default=0)
    last_error = models.TextField(blank=True, default="")

    class Meta:
        indexes = [
            models.Index(
                fields=["id"],
                name="outbox_pending_idx",
                condition=models.Q(published_at__isnull=True),
            ),
        ]

A few decisions worth explaining:

  • id is a BigAutoField, used for ordering. event_id is a UUID: a global identifier the consumer uses to detect duplicates. The internal id never leaves the database; event_id travels with the event.
  • payload is a JSONField. Put an explicit, versioned dict there, not a serialized ORM object. Convert Decimal and datetime to strings yourself: the default JSON encoder can't serialize them.
  • The partial index covers only rows with published_at IS NULL. Even with millions of published rows in the table, the relay query walks a tiny index.

Creating an order now looks like this (reserve_stock is just a function in your project):

# orders/services.py
from django.db import transaction

from orders.models import Order, OutboxEvent


def place_order(user, cart):
    with transaction.atomic():
        order = Order.objects.create(user=user, total=cart.total)
        reserve_stock(order, cart)
        OutboxEvent.objects.create(
            aggregate_type="order",
            aggregate_id=str(order.pk),
            event_type="order.created",
            payload={
                "version": 1,
                "order_id": order.pk,
                "user_id": user.pk,
                "total": str(order.total),
            },
        )
    return order

Notice that there's no network call to the broker on the request path. The transaction is short, and orders are accepted even while the broker is down — events simply wait in the table.

The relay: parallel reads with SKIP LOCKED

Every few hundred milliseconds the relay grabs unpublished events. If several relay instances run at once (say, two pods), they must not pick the same row twice and must not wait on each other. PostgreSQL's FOR UPDATE SKIP LOCKED does exactly that:

# orders/outbox.py
import json

import redis
from django.conf import settings
from django.db import transaction
from django.utils import timezone

from orders.models import OutboxEvent

r = redis.Redis.from_url(settings.REDIS_URL, socket_timeout=5)


def publish_to_stream(event: OutboxEvent) -> None:
    r.xadd(
        f"events:{event.aggregate_type}",
        {
            "event_id": str(event.event_id),
            "type": event.event_type,
            "aggregate_id": event.aggregate_id,
            "payload": json.dumps(event.payload),
        },
        maxlen=1_000_000,
        approximate=True,
    )


def relay_batch(publish=publish_to_stream, batch_size: int = 100) -> tuple[int, bool]:
    """Publish one batch of pending events. Returns (sent, failed)."""
    with transaction.atomic():
        events = list(
            OutboxEvent.objects.select_for_update(skip_locked=True)
            .filter(published_at__isnull=True)
            .order_by("id")[:batch_size]
        )
        now = timezone.now()
        touched, failed = [], False
        for event in events:
            try:
                publish(event)
            except Exception as exc:
                event.attempts += 1
                event.last_error = repr(exc)[:2000]
                touched.append(event)
                failed = True
                break  # later events must not overtake this one
            event.published_at = now
            touched.append(event)
        if touched:
            OutboxEvent.objects.bulk_update(
                touched, ["published_at", "attempts", "last_error"]
            )
    return len(touched) - int(failed), failed

Django turns this queryset into essentially the following SQL:

SELECT *
FROM orders_outboxevent
WHERE published_at IS NULL
ORDER BY id
LIMIT 100
FOR UPDATE SKIP LOCKED;

What happens inside: FOR UPDATE takes a row lock on the selected rows. SKIP LOCKED skips rows already locked by another transaction instead of waiting for them. The first relay takes rows 1–100; a second one arriving at the same moment doesn't see them and takes the next 100. Nobody waits, and no row ends up in two relays. The locks are released when the relay's transaction commits or rolls back.

The subtle part: the locks are held during the network call to the broker. That's why the Redis client has a socket_timeout and the batch is small: if the broker hangs, the transaction must not stay open forever.

On error, the relay stops right there (break): the failed event stays at the head of the queue, and later events don't overtake it. Everything sent so far is saved with published_at. The failed event's attempts counter goes up — you'll need it for alerting.

Run the relay as its own process:

# orders/management/commands/outbox_relay.py
import time

from django.core.management.base import BaseCommand
from django.db import close_old_connections

from orders.outbox import relay_batch


class Command(BaseCommand):
    help = "Publish pending outbox events to the broker"

    def handle(self, *args, **options):
        backoff = 1.0
        while True:
            close_old_connections()
            sent, failed = relay_batch()
            if failed:
                time.sleep(backoff)
                backoff = min(backoff * 2, 30.0)
                continue
            backoff = 1.0
            if sent == 0:
                time.sleep(0.5)
python manage.py outbox_relay

Run it under systemd, supervisor or as a Kubernetes Deployment: if it crashes because the database connection dropped, the orchestrator restarts it. If latency matters, you can wake the relay with PostgreSQL LISTEN/NOTIFY instead of sleeping — NOTIFY is delivered only after commit. Keep the periodic poll anyway: a notification sent while the relay isn't connected is lost, a row in the outbox table isn't.

Ordering and duplicates

Two things need to be admitted honestly.

Duplicates are inevitable. If the relay dies after a successful xadd but before COMMIT, the transaction rolls back and the next relay sends the same event again. Or xadd times out on the client even though Redis actually accepted the message. This isn't a bug — it's the price of at-least-once.

There is no global order. For three reasons:

  • A sequence value is assigned at INSERT time, not at commit. Transaction T1 gets id=10, T2 gets id=11, but T2 commits first. The relay publishes 11, and 10 shows up later.
  • With several relays running in parallel via SKIP LOCKED, the second relay's batch can reach the broker before the first one's.
  • The broker and the consumers are parallel themselves.

What you usually need in practice is ordering within one aggregate (for example, one order). Ways to get it:

  • Serialize the transactions that change one aggregate: lock the order row with select_for_update(). Then one order's events get their ids in commit order.
  • If you need strict ordering, run a single relay, or add a partition column to the outbox (crc32(aggregate_id) % N) and have each relay read only its own partition.
  • Check versions in the consumer: if the payload carries the aggregate version, dropping a stale event is easy.

Idempotency on the consumer side

Since duplicates will arrive, the consumer must process each event_id exactly once. The most reliable way is a processed-events table written in the same transaction as the business effect:

# billing/models.py
from django.db import models


class ProcessedEvent(models.Model):
    event_id = models.UUIDField(primary_key=True)
    processed_at = models.DateTimeField(auto_now_add=True)
# billing/consumers.py
import json

import redis
from django.conf import settings
from django.db import transaction

from billing.models import Invoice, ProcessedEvent

r = redis.Redis.from_url(settings.REDIS_URL)
STREAM, GROUP = "events:order", "billing"


def handle(event_id: str, event_type: str, payload: dict) -> None:
    with transaction.atomic():
        _, created = ProcessedEvent.objects.get_or_create(event_id=event_id)
        if not created:
            return  # duplicate delivery: the effect is already applied
        if event_type == "order.created":
            Invoice.objects.create(order_id=payload["order_id"], amount=payload["total"])


def run(consumer: str) -> None:
    try:
        r.xgroup_create(STREAM, GROUP, id="0", mkstream=True)
    except redis.ResponseError as exc:
        if "BUSYGROUP" not in str(exc):
            raise
    while True:
        resp = r.xreadgroup(GROUP, consumer, {STREAM: ">"}, count=50, block=5000)
        for _stream, messages in resp or []:
            for msg_id, fields in messages:
                handle(
                    fields[b"event_id"].decode(),
                    fields[b"type"].decode(),
                    json.loads(fields[b"payload"]),
                )
                r.xack(STREAM, GROUP, msg_id)  # only after the DB commit

The important details:

  • The ProcessedEvent row and the Invoice live in one transaction. If the handler raises, both roll back and the event is processed again on the next attempt.
  • event_id is the primary key. If two workers receive the same event concurrently, PostgreSQL makes the second INSERT wait for the first to finish and then raises a unique violation; get_or_create catches it and returns created=False.
  • XACK happens after the commit. If the process dies in between, the message stays in the pending list and gets redelivered — ProcessedEvent filters it out. Use XAUTOCLAIM to take over pending messages from dead consumers.

Sometimes the effect is naturally idempotent: UPDATE ... SET status = 'paid' gives the same result twice, or a unique constraint on Invoice.order_id is enough. I've covered this in more depth in the posts on idempotency keys and Celery tasks.

CDC: the poller-free option

Instead of a poller you can use Change Data Capture. A tool like Debezium reads the WAL through PostgreSQL logical decoding (wal_level = logical, a replication slot, the pgoutput plugin) and streams every new outbox row to Kafka. Debezium ships an Outbox Event Router transformation for exactly this pattern.

Pros: no polling queries, events arrive in commit order, latency is low. Cons: you need Kafka Connect infrastructure, and the replication slot must be monitored — if the consumer stops, PostgreSQL keeps WAL for the slot and the disk can fill up. You still need the outbox table: CDC only replaces the relay.

Approach Lost event Phantom event What you need
publish() inside the transaction no, but a broker outage fails the order too yes nothing
on_commit + publish() yes, on a crash no nothing
Outbox + poller no no table, relay process, idempotent consumer
Outbox + CDC no no Debezium/Kafka Connect, replication slot monitoring

Production pitfalls

  • The outbox table grows. Delete published rows periodically — in batches, not with one huge DELETE. Every deleted row becomes a dead tuple, so watch that autovacuum keeps up with this table:
# orders/outbox.py (continued)
from datetime import timedelta


def purge_published(older_than=timedelta(days=7), batch_size=5000) -> None:
    cutoff = timezone.now() - older_than
    while True:
        ids = list(
            OutboxEvent.objects.filter(published_at__lt=cutoff)
            .values_list("id", flat=True)[:batch_size]
        )
        if not ids:
            break
        OutboxEvent.objects.filter(id__in=ids).delete()
  • A "poison" event blocks the queue. The relay stops at the first error, so one broken event holds back everything behind it. When attempts crosses a threshold, alert and let a human decide: fix the payload or set the event aside manually.
  • Don't let the relay read from a replica. SELECT ... FOR UPDATE must run on the primary; check your database router.
  • Long relay transactions. If the broker is slow, an open transaction gets in VACUUM's way. Timeouts and small batches are mandatory.
  • on_commit + Celery is not an outbox. task.delay() is also a network call to a broker and can be lost after commit in exactly the same way.
  • The payload schema is an API. Consumers will depend on it. event_type, a version, only the fields you need. Don't include personal data you don't need: the outbox is stored like a log and ends up in backups.
  • Broker durability. The outbox gets the event as far as the broker; after that, the broker is responsible. If you use Redis Streams, understand your persistence (AOF) settings and when maxlen starts trimming old messages.

Checklist

  • Business write and OutboxEvent inside one transaction.atomic().
  • No network calls to the broker on the request path.
  • A partial index on published_at IS NULL.
  • Relay: select_for_update(skip_locked=True), small batches, a broker timeout.
  • Don't rely on global ordering; for per-aggregate ordering use locks or partitions.
  • A global event_id on every event; on the consumer, ProcessedEvent and the effect in one transaction.
  • ACK to the broker only after commit.
  • Purge published rows in batches.
  • Monitor: age of the oldest unpublished event, the attempts threshold, autovacuum.

Takeaway

You can't write atomically to two systems, so the event goes into the database with the business data, and a separate relay carries it to the broker. That trades "lost message" for "repeated message", and the repeat is neutralized by an idempotent consumer. SKIP LOCKED lets you run relays in parallel safely, but ordering is yours to guarantee, and only within an aggregate.

// comments

Loading comments...

    // next step

    Let's build something together.

    Message me on Telegram
    aslbek@asldev: ~
    ❯