The Practical Developer

The Outbox Pattern: How To Stop Losing Events When Postgres And Kafka Disagree

November 25, 2022 by The Practical Developer

Whenever your code does “write to the database, then publish to Kafka,” there is a window where one succeeds and the other does not. The outbox pattern closes that window with a single extra table and 60 lines of dispatcher code. Here is how it works and why every alternative ends up reinventing it.

database

kafka

reliability

distributed-systems

A network rack with cables — the right metaphor for two systems that have to stay in agreement

The handler updates the order to paid in Postgres, then publishes an OrderPaid event to Kafka so the email service and the analytics pipeline pick it up. Most days, both succeed. Once a week, the database write commits and the Kafka publish times out — leaving the order paid in the database and no event for the rest of the system. The customer never gets a receipt. The dashboard undercounts revenue. The on-call gets paged Wednesday.

This is the classic dual-write problem: two systems, no common transaction. There is no clever ordering that fixes it — “publish first, then write” has the symmetrical bug. The fix is the outbox pattern: write the event into the same database transaction as the state change, and publish it from there asynchronously. About 60 lines of code. Solves the problem permanently.

The pattern in one diagram

[ HTTP POST /pay ]
     │
     ▼
   BEGIN
     ├── UPDATE orders SET status='paid' WHERE id=42
     └── INSERT INTO outbox (topic, payload) VALUES ('OrderPaid', {...})
   COMMIT             ← atomic. Both rows committed or neither.

   ───── async, separate process ─────

  [ outbox dispatcher ]
     │
     ├── SELECT * FROM outbox WHERE published_at IS NULL ORDER BY id LIMIT 100
     ├── for each row: publish to Kafka
     └── UPDATE outbox SET published_at=now() WHERE id IN (...)

The state change and the intent to emit an event are in the same database transaction. They commit together. They roll back together. The dispatcher is a separate process that polls the outbox and publishes to Kafka. If the dispatcher crashes, events sit in the outbox until it comes back. If Kafka is down, events sit in the outbox until Kafka returns. There is no way for the database to commit a state change without the corresponding event being eventually delivered.

The schema

CREATE TABLE outbox (
  id            bigserial PRIMARY KEY,
  topic         text       NOT NULL,
  key           text,
  payload       jsonb      NOT NULL,
  published_at  timestamptz,
  attempts      int        NOT NULL DEFAULT 0,
  last_error    text,
  created_at    timestamptz NOT NULL DEFAULT now()
);

-- Partial index — the only rows we care about are unpublished.
CREATE INDEX outbox_unpublished_idx ON outbox (id)
  WHERE published_at IS NULL;

topic is the Kafka topic. key is the partition key (lets ordered consumers process events for the same entity in order). payload is the event body. published_at is the only “state” — null means not yet sent, non-null means sent.

The partial index keeps the dispatcher query fast even after millions of historical events live in the table. After a row is published, it is invisible to the index.

The producer side

This is the only thing application code has to remember: when you change state, also insert into outbox, in the same transaction.

async function markOrderPaid(orderId: string) {
  await db.transaction(async (tx) => {
    await tx.query(
      `UPDATE orders SET status = 'paid', paid_at = now() WHERE id = $1`,
      [orderId],
    );
    await tx.query(
      `INSERT INTO outbox (topic, key, payload) VALUES ($1, $2, $3)`,
      ['orders.paid', orderId, JSON.stringify({ orderId, paidAt: new Date() })],
    );
  });
}

That is the application’s entire responsibility. No fancy framework, no special “event” abstraction. A row in the outbox is a promise to publish — the dispatcher fulfills the promise.

To prevent the same kind of bug elsewhere, wrap the pattern in a thin helper:

async function withOutbox<T>(
  events: { topic: string; key?: string; payload: unknown }[],
  fn: (tx: Tx) => Promise<T>,
): Promise<T> {
  return db.transaction(async (tx) => {
    const result = await fn(tx);
    for (const e of events) {
      await tx.query(
        `INSERT INTO outbox (topic, key, payload) VALUES ($1, $2, $3)`,
        [e.topic, e.key ?? null, JSON.stringify(e.payload)],
      );
    }
    return result;
  });
}

// Usage
await withOutbox(
  [{ topic: 'orders.paid', key: orderId, payload: { orderId } }],
  async (tx) => {
    await tx.query(`UPDATE orders SET status = 'paid' WHERE id = $1`, [orderId]);
  },
);

This eliminates the chance of a developer “forgetting” to insert into outbox: every state change goes through withOutbox, and the helper makes the event mandatory.

The dispatcher

import { Pool, PoolClient } from 'pg';
import { Kafka } from 'kafkajs';

const pool = new Pool({ connectionString: process.env.DATABASE_URL });
const kafka = new Kafka({ brokers: process.env.KAFKA_BROKERS!.split(',') });
const producer = kafka.producer({ idempotent: true, maxInFlightRequests: 1 });

const POLL_INTERVAL_MS = 500;
const BATCH_SIZE       = 100;

export async function startDispatcher() {
  await producer.connect();
  let stopped = false;

  async function loop() {
    while (!stopped) {
      const sent = await tick();
      if (sent === 0) await sleep(POLL_INTERVAL_MS);
    }
  }
  loop().catch((e) => console.error('[outbox] crashed', e));
  return () => { stopped = true; };
}

async function tick(): Promise<number> {
  const client = await pool.connect();
  try {
    await client.query('BEGIN');

    // Lock the next batch so a second dispatcher instance never picks the same rows.
    const { rows } = await client.query(
      `SELECT id, topic, key, payload
         FROM outbox
        WHERE published_at IS NULL
        ORDER BY id
        FOR UPDATE SKIP LOCKED
        LIMIT $1`,
      [BATCH_SIZE],
    );
    if (rows.length === 0) {
      await client.query('COMMIT');
      return 0;
    }

    // Publish to Kafka. Idempotent producer ensures Kafka does not duplicate
    // even if we retry the same message.
    await producer.send({
      topic: rows[0].topic, // assume one batch = one topic; or send per-row.
      messages: rows.map(r => ({
        key: r.key,
        value: JSON.stringify(r.payload),
        headers: { outbox_id: String(r.id) },
      })),
    });

    // Mark sent inside the same DB transaction we used to claim them.
    await client.query(
      `UPDATE outbox SET published_at = now() WHERE id = ANY($1)`,
      [rows.map(r => r.id)],
    );

    await client.query('COMMIT');
    return rows.length;
  } catch (err) {
    await client.query('ROLLBACK').catch(() => {});
    throw err;
  } finally {
    client.release();
  }
}

const sleep = (ms: number) => new Promise((r) => setTimeout(r, ms));

The key construct is FOR UPDATE SKIP LOCKED on the SELECT — the same trick the SKIP LOCKED job queue post uses. It lets multiple dispatcher instances run in parallel without coordinating, and without ever picking the same row.

A few non-obvious decisions baked in:

Idempotent producer. idempotent: true and maxInFlightRequests: 1 make Kafka deduplicate retries from the same producer instance — so if the network drops between producer.send and the database update, the next attempt does not produce a duplicate message in Kafka. Combined with the database update, you get effectively-once delivery into Kafka.

Batch publish, then update. The producer.send with multiple messages reduces round-trips. The follow-up UPDATE marks the whole batch published in one statement. If the producer fails, the transaction rolls back and the rows are unclaimed — the next tick retries.

Transactional outbox insertion + marking. The BEGIN ... COMMIT around the whole dispatcher tick is what makes the system safe against dispatcher crashes mid-publish. If the process dies after producer.send but before UPDATE, the next dispatcher instance will pick the same rows and re-send. Idempotency on the consumer side handles the duplicate.

Idempotency on the consumer

Outbox guarantees at-least-once delivery, not exactly-once. Consumers must be idempotent. The standard pattern: every event has an ID (the outbox.id); the consumer keeps a record of processed IDs and ignores duplicates.

async function handleOrderPaid(event: { id: bigint; orderId: string; ... }) {
  await db.transaction(async (tx) => {
    const existing = await tx.query(
      `INSERT INTO processed_events (id) VALUES ($1) ON CONFLICT DO NOTHING`,
      [event.id],
    );
    if (existing.rowCount === 0) return; // already processed

    await sendReceiptEmail(event.orderId);
  });
}

The ON CONFLICT DO NOTHING is the deduplication gate. If a duplicate event arrives, the insert is a no-op, the email is not sent, and the consumer commits. This pattern is identical to the idempotency-key approach for inbound webhooks — same problem, same shape, different layer.

Performance and operational notes

Outbox table grows fast. A high-throughput service writes one outbox row per event. After a year, that table is huge. Two strategies: a periodic cleanup job that deletes rows older than N days where published_at IS NOT NULL, or partitioning by week and dropping old partitions. Whichever you pick, do it from day one — the table is much harder to clean up after a year of inattention.

Dispatcher latency. The polling interval determines worst-case publish latency. 500ms is fine for most use cases. If you need lower (sub-100ms), use Postgres LISTEN / NOTIFY: the producer fires NOTIFY outbox_new after inserting, the dispatcher LISTENs and only polls on the wakeup. Latency drops to single-digit milliseconds and CPU spent polling drops to zero.

Multiple dispatchers. Run two or three for redundancy. Because of SKIP LOCKED, they will not collide. If you run dozens, you may want partitioning of the outbox table by hash so each dispatcher polls a partition.

Backlog alerts. A growing count(*) WHERE published_at IS NULL is the signal that something is wrong — either the dispatcher is down, Kafka is unhappy, or the rate of writes has outpaced publish capacity. Alert on it.

-- Dashboard query:
SELECT count(*) FROM outbox WHERE published_at IS NULL;
SELECT count(*) FROM outbox WHERE published_at IS NULL AND created_at < now() - interval '5 minutes';

The second query is the one to alert on. Anything older than five minutes unprocessed is a problem.

Why not Debezium / CDC?

Debezium reads Postgres’ write-ahead log (WAL) and turns row changes into Kafka events directly, no outbox table needed. It is a real alternative — and the right choice if you can run it. Two reasons it does not always fit:

Operational complexity. Debezium adds a Kafka Connect cluster, a managed plugin, and a deep dependency on the Postgres replication slot — which, if the connector fails, will pin WAL forever and fill your disk. Most teams discover this at 3 a.m. The outbox pattern requires no extra infrastructure.

Schema control. With Debezium, your Kafka events are derived from row changes — you cannot easily emit a “high-level” event (“OrderPaid”) that summarizes multiple row changes. Outbox lets you write whatever event shape your consumers want.

For teams already running Kafka Connect at scale, Debezium is great. For everyone else — especially anyone wanting their event schema decoupled from their database schema — outbox is simpler.

When you do not need the outbox

Two cases where the pattern is overkill:

Best-effort events. Notifications, analytics pings — events you can lose without a customer noticing. Direct publish-then-write or fire-and-forget is fine.

Synchronous downstream. If the downstream is the same database (a different table in the same Postgres), wrap both in one transaction. No queue needed.

For everything else where “this event must be delivered” — payment confirmations, fulfillment, audit logs, downstream search index updates — the outbox is the cheapest reliable choice.

The takeaway

The dual-write problem is permanent. It is not solved by ordering (“write first then publish”), retries (“just retry the publish”), or distributed transactions (XA / 2PC, which has its own catastrophic failure modes). It is solved by making the event part of the same database transaction as the state change, and publishing the event from the database to the message broker asynchronously.

Sixty lines of dispatcher code, one outbox table, idempotent consumers — that is the entire pattern. No team I have seen regrets adopting it. Several have regretted not adopting it, usually after a Saturday spent reconciling event streams against the database to find the events that got lost.

A note from Yojji

The kind of cross-system reliability work that prevents “the database said paid but no event went out” — outbox patterns, idempotent consumers, dispatcher monitoring — is the unglamorous backend work that decides whether your data and your event stream stay in agreement. It is the kind of engineering Yojji’s teams build into the production systems they ship.

Yojji is an international custom software development company founded in 2016, with teams across Europe, the US, and the UK. They specialize in the JavaScript ecosystem (React, Node.js, TypeScript), cloud platforms (AWS, Azure, GCP), and event-driven backends — including the message-flow plumbing that decides whether a system can be trusted with the customer’s money.