The Transactional Outbox Pattern: Solving Dual-Write in Distributed Systems

Most distributed systems have this bug hidden somewhere in their codebase: save the order to the database, then publish an order.created event to Kafka. When the publish fails — and eventually it will — the order exists but every downstream service is permanently blind to it. You can add retry logic, but that just moves the problem. The real fix is architectural: stop treating the database write and the broker publish as two separate operations.

The transactional outbox pattern solves this at the infrastructure level. It trades the illusion of atomicity across two systems for a genuine atomic write to one system, then propagates asynchronously with a separate relay process. The result is consistent, debuggable, and horizontally scalable.

The Dual-Write Problem

Any time you write to two separate systems in sequence, you are gambling on partial failure. The common culprit is writing to a relational database and a message broker in the same request handler:

// ❌ Dual-write — the publish can fail after the DB commit
async function createOrder(data: CreateOrderInput): Promise<Order> {
  const order = await db.orders.create(data);
  await kafka.publish("order.created", order); // what if this throws?
  return order;
}
 
// ✅ Single transaction — the event is part of the same write
async function createOrder(data: CreateOrderInput): Promise<Order> {
  return db.transaction(async (tx) => {
    const order = await tx.orders.create(data);
    await tx.outbox.insert({
      aggregateType: "order",
      aggregateId: order.id,
      eventType: "order.created",
      payload: order,
    });
    return order;
  });
}

The outbox row and the order row commit atomically. If the transaction rolls back, neither the order nor the outbox entry exists. If it commits, both exist. The message broker is never in the hot path of the transaction — it hears about the event later, from a relay process that reads committed outbox rows.

The Outbox Table Schema

The outbox table is the heart of the pattern. Design it to support ordered, reliable processing from day one:

CREATE TABLE outbox_events (
  id             UUID        DEFAULT gen_random_uuid() PRIMARY KEY,
  aggregate_type TEXT        NOT NULL,
  aggregate_id   TEXT        NOT NULL,
  event_type     TEXT        NOT NULL,
  payload        JSONB       NOT NULL,
  created_at     TIMESTAMPTZ DEFAULT now() NOT NULL,
  processed_at   TIMESTAMPTZ,
  attempts       INT         DEFAULT 0 NOT NULL,
  last_error     TEXT
);
 
CREATE INDEX idx_outbox_unprocessed
  ON outbox_events (created_at)
  WHERE processed_at IS NULL;

The partial index on processed_at IS NULL is critical. Once the table accumulates millions of processed rows, the relay process scans only the small unprocessed subset. Without it, every poll becomes a full sequential scan that degrades over time.

The attempts and last_error columns are not optional niceties — they are how you detect stuck events in production without grepping logs.

Building the Relay Process

The relay polls for unprocessed events and publishes them to the broker. Keep it simple and focus on correctness first:

async function processOutboxBatch(batchSize = 100): Promise<number> {
  const events = await db.$transaction(async (tx) => {
    // Lock rows to prevent concurrent relays from double-publishing
    const rows = await tx.$queryRaw<OutboxEvent[]>`
      SELECT * FROM outbox_events
      WHERE processed_at IS NULL
      ORDER BY created_at ASC
      LIMIT ${batchSize}
      FOR UPDATE SKIP LOCKED
    `;
 
    if (rows.length === 0) return [];
 
    await Promise.all(
      rows.map((event) =>
        kafka.publish(event.event_type, event.payload, {
          key: event.aggregate_id, // same aggregate → same partition → ordered delivery
        })
      )
    );
 
    const ids = rows.map((r) => r.id);
    await tx.$executeRaw`
      UPDATE outbox_events
      SET processed_at = now()
      WHERE id = ANY(${ids}::uuid[])
    `;
 
    return rows;
  });
 
  return events.length;
}
 
// Relay loop — run one per service replica
async function startRelay(): Promise<void> {
  while (true) {
    const processed = await processOutboxBatch();
    // Back off when idle to avoid hammering the DB unnecessarily
    await sleep(processed === 0 ? 500 : 50);
  }
}

FOR UPDATE SKIP LOCKED is the detail that makes horizontal scaling work. Multiple relay instances can run side by side — each grabs rows no other relay holds a lock on. You get safe concurrency without a distributed lock or a single-replica bottleneck.

Polling vs. Change Data Capture

Polling is the right starting point for most teams. With the partial index, a 200–500ms poll interval produces sub-second latency and negligible database load.

Change Data Capture reads directly from the PostgreSQL WAL stream, eliminating polling entirely. It is the right choice when you need sub-100ms latency at scale or when the outbox table is under extreme write pressure. For the vast majority of services, polling is the correct starting point and should stay the implementation until you have production data that justifies the operational overhead of Debezium.

Consumer Idempotency Is Not Optional

The outbox pattern guarantees at-least-once delivery — never exactly-once. If the relay publishes an event successfully but the processed_at commit fails before the transaction closes (network blip, process killed mid-flight), the same event will be published again on the next poll cycle. This is expected behavior, not a bug.

Every consumer must handle duplicates:

async function handleOrderCreated(event: OrderCreatedEvent): Promise<void> {
  const alreadyProcessed = await db.processedEvents.findUnique({
    where: { eventId: event.id },
  });
 
  if (alreadyProcessed) {
    logger.debug({ eventId: event.id }, "skipping duplicate event");
    return;
  }
 
  // Transactionally apply the effect and record the deduplication key
  await db.$transaction(async (tx) => {
    await tx.inventory.reserveItems(event.payload.lineItems);
    await tx.processedEvents.create({ data: { eventId: event.id } });
  });
}

The processedEvents table acts as a deduplication log. The check-then-write lives inside a transaction with a unique constraint on eventId — concurrent consumers racing on the same event will have one succeed and one receive a constraint violation, which is safe to swallow as a duplicate.

Retention and Cleanup

Processed outbox rows are dead weight. A scheduled cleanup job prevents unbounded table growth:

-- Run daily via pg_cron or your job scheduler
DELETE FROM outbox_events
WHERE processed_at IS NOT NULL
  AND processed_at < now() - INTERVAL '7 days';

Seven days of retention gives you a replay window for debugging incidents without an audit trail that grows forever. If your on-call rotation typically resolves issues within 24 hours, three days is sufficient. The partial index keeps query performance stable regardless of how much historical data you keep, but smaller tables mean faster vacuums and lower storage costs.

Key Takeaways

1. Never dual-write — writing to a database and then publishing to a broker is a consistency bug waiting to surface under the worst possible circumstances
2. The outbox row is your event — commit it atomically with your domain write; nothing else crosses the transaction boundary
3. FOR UPDATE SKIP LOCKED enables safe horizontal relay scaling — multiple instances coordinate through the database itself, no external locks needed
4. Start with polling, measure, then consider CDC — near-real-time latency rarely justifies the operational cost of Debezium early in a system's lifecycle
5. At-least-once delivery is a contract, not a bug — design every consumer to be idempotent from day one, not as a retrofit
6. Track oldest unprocessed event age — it is the single most useful metric for relay health, more informative than processed-per-second throughput