backend / architecture design / 16_transactional_outbox.md

Transactional Outbox Pattern

6 interview angles 5 min read source

Transactional Outbox Pattern

The atomicity problem you hit the moment a service writes to a DB AND publishes a message: how do you guarantee both happen, or neither? The transactional outbox is the standard answer.

The dual-write problem

# WRONG — dual write, not atomic
def place_order(order):
    db.commit_order(order)            # step 1
    kafka.publish("OrderPlaced", order)   # step 2

Failure cases:

  • Step 1 succeeds, step 2 fails → DB has the order, no event published. Downstream consumers never know.
  • Step 1 succeeds, step 2 succeeds, then step 1 transaction rolls back (e.g., a later constraint) → event published, no order.
  • Step 2 partially succeeds (broker accepted but didn’t replicate) → maybe published, maybe not. You don’t know.

No combination of try/except saves you. There’s no 2PC across a database and a broker.

The outbox pattern

Write the event into an outbox table in the same DB transaction as the business change. A separate relay reads the outbox and publishes.

+--------------+      +-----------+      +-------+
| place_order  +----->| Postgres  |      | Kafka |
| TX:          |      |  orders   |      |       |
|  INS orders  |      |  outbox   +-->[relay]-->topic
|  INS outbox  |      +-----------+      +-------+
| COMMIT       |
+--------------+
BEGIN;
  INSERT INTO orders (id, ...) VALUES (...);
  INSERT INTO outbox (aggregate, event_type, payload, created_at)
       VALUES ('order', 'OrderPlaced', :json, now());
COMMIT;

Either both INSERTs happen or neither. The DB transaction is the atomicity unit.

The relay process polls:

SELECT id, event_type, payload
FROM outbox
WHERE published_at IS NULL
ORDER BY id
LIMIT 100
FOR UPDATE SKIP LOCKED;     -- so multiple relay replicas don't collide

Publishes:

for row in rows:
    kafka.publish(row.event_type, row.payload, key=row.aggregate_id)
    db.execute("UPDATE outbox SET published_at = now() WHERE id = :id", id=row.id)

Delivery is at-least-once — the relay can crash between publish and update. The next iteration may publish the same row again. Consumers must be idempotent (see 10_message_queues/celery/04_idempotency.md).

SQLAlchemy implementation

class Outbox(Base):
    __tablename__ = "outbox"
    id            = Column(BigInteger, primary_key=True, autoincrement=True)
    aggregate_id  = Column(String, nullable=False)
    event_type    = Column(String, nullable=False)
    payload       = Column(JSONB, nullable=False)
    created_at    = Column(DateTime(timezone=True), default=func.now())
    published_at  = Column(DateTime(timezone=True), nullable=True)

# In application code
with session.begin():
    order = Order(...)
    session.add(order)
    session.flush()                       # populates order.id
    session.add(Outbox(
        aggregate_id=str(order.id),
        event_type="OrderPlaced",
        payload={"order_id": order.id, "user_id": order.user_id, "total": order.total},
    ))

The relay loop

import time

def relay():
    while True:
        with session.begin():
            rows = session.execute(
                select(Outbox).where(Outbox.published_at.is_(None))
                              .order_by(Outbox.id).limit(100)
                              .with_for_update(skip_locked=True)
            ).scalars().all()

            if not rows:
                time.sleep(0.1)
                continue

            for row in rows:
                kafka.send(row.event_type, value=row.payload, key=row.aggregate_id).get()
                row.published_at = datetime.now(timezone.utc)

Notes:

  • FOR UPDATE SKIP LOCKED lets multiple relay replicas run safely; each picks distinct rows.
  • kafka.send(...).get() waits for the broker ack. Important — without it, publish may not have committed when you mark the row published.
  • Batch publish + batch update for throughput.

Retention

The outbox grows unbounded. Cleanup:

DELETE FROM outbox WHERE published_at < now() - INTERVAL '7 days';

Run periodically (Celery Beat, cron). Keep enough history to debug “did this event fire?” but don’t let the table grow forever.

Variant: CDC (Change Data Capture)

Instead of a relay process polling the outbox, use Debezium to read the Postgres WAL directly. Every row inserted into outbox becomes a Kafka message automatically.

Postgres WAL → Debezium → Kafka topic

Trade-offs:

  • No relay process to maintain.
  • Lower latency (Debezium streams, no polling).
  • Survives outages well.
  • Operational complexity of running Debezium + Kafka Connect.
  • Logical decoding requires wal_level = logical on Postgres.

For Postgres + Kafka shops, Debezium-based CDC is the modern preferred approach.

You can even skip the outbox table and have Debezium read directly from your business tables. But: this couples your event schema to your DB schema, leaks internal structure, and makes it hard to evolve. Outbox + Debezium reading the outbox table is the cleaner pattern.

Variant: at-least-once publish without outbox

For scenarios where you can afford rare duplicate events AND your downstream is idempotent:

# Subscribe to DB change feed (Postgres LISTEN/NOTIFY)
def on_order_inserted(notification):
    kafka.publish("OrderPlaced", notification.payload)

db.add_listener("orders_changed", on_order_inserted)

Or transactional callbacks:

@event.listens_for(session, "after_commit")
def publish_after_commit(session):
    for obj in session.info.get("pending_events", []):
        kafka.publish(...)

These are less safe (no replay; lose events on consumer crash) but simpler. Don’t use for anything load-bearing.

Ordering

If consumers care about event order per-aggregate (orders for the same user_id in order):

  • Outbox id (auto-increment) preserves total order globally.
  • Use aggregate_id as Kafka key so all events for the same aggregate go to the same partition (preserves per-key order).
  • Single-relay process publishes in id order; multi-relay needs care.

Common gotchas

  • Forgetting to mark published_at after publish. Infinite republish.
  • Publishing inside the business transaction. Destroys the atomicity benefit — if commit fails, you’ve already published.
  • No retention policy. Outbox grows forever, hurts performance.
  • Relay process is single-instance and dies. Events stop flowing silently. HA the relay or use advisory-lock-based singleton.
  • Wrong aggregate ID as Kafka key. Same logical entity hashes to different partitions; ordering breaks.
  • Forgetting to wait for broker ack. Marking row published before broker confirms = lost events on broker crash.

Comparison to event sourcing

Event sourcing: events ARE the source of truth; state is rebuilt by replay. Outbox: state in DB is the truth; events are derived.

They overlap but solve different problems:

  • Outbox = “publish reliably from a state-based system.”
  • Event sourcing = “make events the system.”

You can outbox events from an event-sourced system. They compose.

When to skip the outbox

  • Single-database transactions only (no events crossing services).
  • Eventually consistent and you’re OK losing rare events.
  • You have a workflow engine (Temporal) handling the state — the engine’s history is the outbox.

Interview angle

  • “What’s the dual-write problem and how do you solve it?” — atomicity between two systems (DB + broker) without 2PC. Solution: transactional outbox. Write the event to an outbox table in the same DB transaction as the business change; a separate relay polls the outbox and publishes. Delivery is at-least-once; consumers must be idempotent.
  • “Why not just publish, then write to DB?” — if the publish succeeds and the DB write fails, you’ve published a phantom event. Outbox makes the DB write authoritative.
  • “How do you avoid duplicate events from the outbox?” — you don’t (delivery is at-least-once). Downstream consumers dedupe by message ID. The outbox guarantees at-least-once; consumer idempotency guarantees exactly-once processing.
  • “What’s CDC and how does it relate to outbox?” — Change Data Capture (Debezium) reads the Postgres WAL and emits row changes as Kafka events. Skips the relay process. Combine with outbox table (Debezium reads outbox) for clean event schemas.
  • “How do you preserve event ordering?” — outbox.id (auto-increment) is global order. Use aggregate_id as the Kafka key so all events for the same entity go to the same partition (preserves per-aggregate order even with multi-partition consumers).
  • “Why use outbox vs publishing on commit hooks?” — outbox survives broker outages (events accumulate in DB until relay can publish), supports replay (DB has the events), separates business logic from messaging. Commit hooks lose events on broker downtime.
Previous
Event-Driven Architecture and Sagas