Dual Writes and the Outbox Pattern

await db.orders.insert(order);              // 1
await broker.publish('OrderCreated', order); // 2

Two lines that appear in nearly every service that has both a database and a message broker. They are also a data-consistency bug, and swapping them doesn't help.

The window

Between those two lines the process can crash, the broker can be unreachable, the network can drop. Whatever happens in that window leaves the two systems disagreeing:

Database first, publish fails. The order exists and nothing downstream hears about it: no confirmation email, no warehouse pick, no analytics row. Silent, and usually discovered by a customer.

Publish first, database fails. Consumers act on an order that doesn't exist. Now you have a confirmation email for a row nobody can find.

Retry the failed one. You either double-publish (the broker got it, the ack was lost) or you're retrying inside a request that has already returned. Retries change which failure you get, not whether you get one.

There's no ordering of two independent writes that makes this atomic, because atomicity across two systems is exactly what you don't have.

The outbox

Make it one write. The event goes into an ordinary table in the same transaction as the business data:

BEGIN;
  INSERT INTO orders (id, customer_id, total) VALUES (...);
  INSERT INTO outbox (id, topic, payload, created_at)
       VALUES (gen_random_uuid(), 'OrderCreated', $1, now());
COMMIT;

Either both rows land or neither does — that's the database's ordinary guarantee, and the whole point of the pattern is to rely on it rather than inventing a distributed one.

A separate relay then moves events from the outbox to the broker:

async function relay() {
  const rows = await db.query(
    `SELECT * FROM outbox WHERE sent_at IS NULL
     ORDER BY created_at LIMIT 100 FOR UPDATE SKIP LOCKED`   // safe with N relays
  );

  for (const row of rows) {
    await broker.publish(row.topic, row.payload);
    await db.query(`UPDATE outbox SET sent_at = now() WHERE id = $1`, [row.id]);
  }
}

If the broker is down, nothing is lost — the rows stay, and the next run picks them up. FOR UPDATE SKIP LOCKED is what lets several relay instances run without publishing the same row twice.

The table, properly

The outbox is a normal table, but a few columns and one index make the difference between a pattern that works in a demo and one that works at a few thousand events a second:

CREATE TABLE outbox (
  id            uuid PRIMARY KEY,
  aggregate_id  text        NOT NULL,   -- e.g. the order id; becomes the partition key
  topic         text        NOT NULL,
  payload       jsonb       NOT NULL,
  headers       jsonb       NOT NULL DEFAULT '{}',  -- trace id, schema version
  created_at    timestamptz NOT NULL DEFAULT now(),
  sent_at       timestamptz,
  attempts      int         NOT NULL DEFAULT 0,
  last_error    text
);

-- the relay only ever looks at unsent rows; keep that index tiny
CREATE INDEX outbox_unsent ON outbox (created_at) WHERE sent_at IS NULL;

The partial index is the important line. Sent rows vastly outnumber unsent ones, and an index over only the unsent rows stays a few pages in size no matter how large the table grows. Storing a trace ID in headers lets distributed tracing follow a request from the HTTP call, through the outbox, to every consumer — see distributed tracing.

Writing to it from application code

Wrap the pattern in one helper so nobody writes the two inserts by hand:

async function createOrder(input) {
  return db.tx(async (t) => {
    const order = await t.one(
      `INSERT INTO orders (id, customer_id, total)
       VALUES ($1, $2, $3) RETURNING *`,
      [uuid(), input.customerId, input.total]);

    await t.none(
      `INSERT INTO outbox (id, aggregate_id, topic, payload)
       VALUES ($1, $2, 'OrderCreated', $3)`,
      [uuid(), order.id, { eventId: uuid(), orderId: order.id, total: order.total }]);

    return order;
  });
}

The rule to enforce in review: no broker.publish inside request handlers. Every event goes through the outbox.

Every crash point, checked

The pattern is only convincing if every crash point lands somewhere safe:

No row in that table loses an event or publishes one for an order that doesn't exist. The one imperfection — a duplicate publish — is the one idempotent consumers already handle.

Two ways to run the relay

Polling — a loop like the one above, every second or so. Simple, no extra infrastructure, easy to reason about. Costs you a constant trickle of queries and adds latency equal to half the poll interval.

Change data capture — a tool like Debezium tails the database's write-ahead log and publishes outbox inserts as they're committed. No polling, lower latency, and it can't miss a row; the cost is a real piece of infrastructure to run and understand.

Start with polling. Move to CDC when the latency or the query load actually bothers you.

Cutting polling latency with LISTEN/NOTIFY

In Postgres there's a middle ground. Have the writing transaction send a notification, and have the relay wait for one instead of sleeping:

-- inside the same transaction as the outbox insert
NOTIFY outbox_new;

await listener.query('LISTEN outbox_new');
listener.on('notification', () => relayNow());   // wake immediately
setInterval(relayNow, 5000);                     // and poll as a safety net

Notifications are delivered only after commit, so the relay never wakes for a row it can't see yet. They aren't durable — a relay that's down misses them — which is why the slow poll stays as the backstop. Latency drops to milliseconds without running a CDC pipeline.

What CDC looks like

With Debezium, the relay is configuration rather than code: a connector reads the write-ahead log, and an outbox "event router" turns each inserted outbox row into a message on the topic named in the row, keyed by aggregate_id. The outbox rows can even be deleted right after insert — the log already captured them — so the table never grows. The trade is operating Kafka Connect and a replication slot, and making sure that slot doesn't fall behind and fill the database's disk with retained WAL.

The inbox: the consumer's half

The idempotent consumer above is sometimes formalised as an inbox: the consumer writes each incoming event into an inbox table (with a unique event ID) in the same transaction as its own state changes. It's the mirror image of the outbox — one local transaction on each side, with the broker in between — and together they give end-to-end effectively-once processing out of at-least-once parts. A worker can also process the inbox asynchronously, which decouples receiving an event from acting on it.

At-least-once, not exactly-once

The relay can crash after publishing and before marking the row sent. On restart it publishes again. That is unavoidable — the publish and the mark are themselves two writes to two systems, which is the original problem one level down.

So the outbox gives you at-least-once delivery with no lost events, and consumers must be idempotent. Include a stable event id in the payload and have consumers record what they've processed:

async function onOrderCreated(event) {
  const inserted = await db.query(
    `INSERT INTO processed_events (event_id) VALUES ($1)
     ON CONFLICT DO NOTHING RETURNING event_id`,
    [event.id]
  );
  if (inserted.rowCount === 0) return;    // already handled
  await sendConfirmationEmail(event);
}

This is the same conclusion as message queues versus event logs: idempotent consumers are cheaper to build than exactly-once delivery is to guarantee.

Operational details that matter

Clean up the table. An outbox that's never pruned becomes the largest table in the database. Delete sent rows after a retention window, or partition by day and drop old partitions.

Watch the lag. The age of the oldest unsent row is the single best alert: it goes up when the broker is down, when the relay has crashed, or when a poison message is blocking the loop.

Handle poison messages. One row that always fails to publish will stall everything behind it if you process strictly in order. Track attempts and move repeat offenders aside after a threshold.

Order is per-key, not global. If consumers depend on ordering, publish with a partition key (the order id, the customer id) and let the broker keep order within that key. Global ordering through a single relay is a throughput ceiling you don't want.

Batch the publishes. The relay above publishes and updates one row at a time — two round trips per event. At volume, publish the whole batch of 100 in one producer call, wait for all acks, then mark them sent with a single UPDATE outbox SET sent_at = now() WHERE id = ANY($1). Throughput goes up by an order of magnitude, and the failure behaviour is unchanged: a crash mid-batch just republishes the batch.

Keep the transaction short. Don't hold the FOR UPDATE lock while waiting on a slow broker for seconds; select and lock the batch, publish with a timeout, mark, commit. If the broker is slow, fail fast and let the next run retry.

Alternatives, and why they usually lose

• Distributed transactions (XA / 2PC) across the database and the broker would make the two writes atomic, but most brokers don't support it, and those that do pay heavily in latency and availability — see sagas vs two-phase commit.
• Publish first, then have the service consume its own event to write the database ("listen to yourself"). Atomic in the sense that the log is the single source of truth, but the service can't read its own write immediately, and every request becomes asynchronous.
• Event sourcing: make the events themselves the database. Then there's only one write by construction. Powerful, and a much bigger commitment than an outbox table — see event sourcing and CQRS.

For a service that already has a relational database, the outbox is the smallest change that removes the bug.

Events are an API

Once events leave the outbox, other teams depend on their shape, so treat the payload like a public API:

• Version it. Put a schema version in the headers or the event type (OrderCreated.v2). Consumers can then handle old and new shapes side by side during a rollout.
• Only add, never repurpose. Adding an optional field is safe; renaming or changing the meaning of one breaks every consumer that hasn't been redeployed. If a change is incompatible, publish a new event type and run both until consumers have moved.
• Carry enough to act on, not the whole row. An event with only an ID forces every consumer to call back into your service (and hit a replica that may not have the row yet). An event with the entire row couples consumers to your table layout. Include the fields that describe what happened — "order 42 was placed by customer 7 for ₹1,250" — and nothing internal.
• Use a schema registry once several teams publish and consume. It rejects incompatible changes at publish time instead of at 3 a.m. in a consumer.

Because the outbox stores the payload exactly as it will be published, it's also the natural place to validate it: check the event against its schema before inserting, and an invalid event fails the business transaction instead of poisoning the relay later.

When you don't need it

The outbox is worth its complexity when events drive other systems' state. It's overkill when:

• The event is advisory — a metric, a cache invalidation, a nice-to-have notification where a rare miss costs nothing.
• The consumer can derive the state by reading the database directly.
• You can restructure so there's only one write. A consumer that polls a status column doesn't need an event at all.

That last one is worth sitting with before building anything. A surprising number of dual-write problems disappear when the second system stops needing to be told and starts being able to look.