The outbox pattern for WebSocket events #

A user moves a card on a kanban board. The API handler updates the database, then publishes a card.moved event to Redis so every other viewer’s board updates over WebSockets. Most of the time this works. Occasionally the process crashes, or Redis times out, between the two steps: the card has moved in the database but nobody else’s screen shows it until they reload. Worse, sometimes the publish succeeds and the database transaction then rolls back, and everyone sees a move that never happened. This is the dual-write problem: two systems, two writes, no transaction spanning both. The transactional outbox fixes it by making the event part of the database transaction and moving publication to a separate relay that retries until it succeeds.

Root cause #

A handler that writes to a database and then publishes to a message broker performs two independent operations. Whatever order you choose, a failure between them leaves the systems inconsistent. Publish after commit, and a crash or broker error in between loses the event: the data changed, but real-time clients never hear about it. Publish before commit, and a rollback invents the event: clients are told about a change that does not exist. Retrying the publish in the handler helps with transient broker errors but not with a process that dies mid-request, which is exactly what deploys, OOM kills and node failures do.

Real-time UIs make the inconsistency visible. A lost event means some users see stale state until they reload; an invented event means some users see state that the database will never agree with, and a later snapshot silently reverts it.

The dual-write failure The API handler commits the card update to the database, then the process is killed during a deploy before publishing the event, so viewers' boards stay stale until they reload. The dual-write failure API handler Database Redis Viewers UPDATE card, COMMIT process killed (deploy) PUBLISH card.moved never runs boards stale until reload No retry inside the handler can help — the handler is gone
Two writes without a shared transaction will eventually disagree.

Resolution #

Write the event to an outbox table in the same database transaction as the change. The event now commits if and only if the change commits. A separate relay reads unpublished outbox rows in order, publishes them to the fan-out layer, and marks them published. If the relay crashes, it resumes from the first unpublished row; if a publish is retried, downstream consumers deduplicate by event id. The outbox row id doubles as the stream’s sequence number, which gives WebSocket clients ordering and gap detection for free.

CREATE TABLE outbox (
  id          bigserial PRIMARY KEY,          -- global order; also the event's sequence
  stream      text        NOT NULL,           -- e.g. board:17
  type        text        NOT NULL,           -- e.g. card.moved
  payload     jsonb       NOT NULL,
  created_at  timestamptz NOT NULL DEFAULT now(),
  published_at timestamptz                    -- NULL until the relay has published it
);
CREATE INDEX outbox_unpublished ON outbox (id) WHERE published_at IS NULL;
import type { Pool, PoolClient } from 'pg';
import { createClient } from 'redis';

// 1. The handler: change + event in ONE transaction.
export async function moveCard(db: Pool, cardId: string, toList: string, boardId: string) {
  const client = await db.connect();
  try {
    await client.query('BEGIN');
    await client.query('UPDATE cards SET list_id = $1 WHERE id = $2', [toList, cardId]);
    await client.query(
      'INSERT INTO outbox (stream, type, payload) VALUES ($1, $2, $3)',
      [`board:${boardId}`, 'card.moved', { cardId, toList }],
    );
    await client.query('COMMIT');               // both or neither
  } catch (e) {
    await client.query('ROLLBACK');
    throw e;
  } finally {
    client.release();
  }
}

// 2. The relay: publish in id order, mark published, repeat. Run one active instance.
const BATCH = 200;
const IDLE_POLL_MS = 100;
const redis = createClient(); await redis.connect();

export async function relayLoop(db: Pool) {
  for (;;) {
    const client: PoolClient = await db.connect();
    let published = 0;
    try {
      await client.query('BEGIN');
      // Lock a batch; SKIP LOCKED lets a standby relay take over without double-publishing.
      const { rows } = await client.query(
        `SELECT id, stream, type, payload FROM outbox
         WHERE published_at IS NULL ORDER BY id LIMIT $1 FOR UPDATE SKIP LOCKED`, [BATCH]);
      for (const r of rows) {
        const event = JSON.stringify({ id: r.id, seq: Number(r.id), stream: r.stream, type: r.type, data: r.payload });
        await redis.publish(`stream:${r.stream}`, event);  // may be retried on crash: consumers dedupe by id
      }
      if (rows.length) {
        await client.query('UPDATE outbox SET published_at = now() WHERE id = ANY($1)', [rows.map((r) => r.id)]);
      }
      await client.query('COMMIT');
      published = rows.length;
    } catch (e) {
      await client.query('ROLLBACK');
      console.error('outbox relay error', e);
    } finally {
      client.release();
    }
    if (published === 0) await new Promise((r) => setTimeout(r, IDLE_POLL_MS));
  }
}

If the relay publishes a batch and crashes before marking it, the next run publishes those events again. That is the at-least-once trade-off, and it is safe as long as WebSocket nodes and clients deduplicate by event id — the technique in idempotent WebSocket message processing. Because ids are monotonic, the client-side ordering logic from ordering WebSocket messages with sequence numbers drops duplicates automatically. Note that a global bigserial gives a global order with gaps per stream; for gap detection per room, have clients compare the previous id for their stream, which the relay can include, or maintain a per-stream counter in the transaction.

Polling adds up to one idle interval of latency. For lower latency, have the handler issue NOTIFY outbox after commit and the relay LISTEN for it, falling back to polling — or use change data capture (Debezium or logical replication) to stream outbox inserts without polling at all.

Ways to publish real-time events Publishing after commit can lose events on crash and publishing before commit can produce phantom events on rollback, while the transactional outbox and change data capture avoid both and provide ordering. Ways to publish real-time events Lost events Phantom events Ordering Publish after commit on crash no publisher-dependent Publish before commit no on rollback publisher-dependent Transactional outbox no no outbox id CDC on the table no no commit order Outbox and CDC both make the database the single source of truth for what happened
Only approaches that derive events from the commit are consistent.

Edge cases #

Outbox growth. Published rows accumulate. Delete or archive rows older than your replay window on a schedule; the partial index on unpublished rows keeps the relay’s query fast regardless of table size.

Relay throughput. One relay publishing in id order is simple and ordered, and it can move thousands of events per second. If you need more, partition the relay by stream (hash of stream name) and give each partition its own ordered relay; ordering then holds per stream, which is all clients need.

Commit order vs id order. bigserial ids are assigned at insert time, not commit time, so a transaction that inserts id 100 and commits after one that inserted id 101 creates a moment where 101 is visible and 100 is not. The relay can publish 101 first. Clients must tolerate small reorderings (the ordered-stream buffer does), or the relay can wait briefly before publishing ids beyond a gap.

Verification #

Chaos-test the exact failure the pattern exists for. Run a load test of card moves, kill the API process and the relay process at random intervals (kill -9), and afterwards compare: every committed change must have exactly one outbox row, every outbox row must eventually be marked published, and every WebSocket client’s final board must match the database.

-- Unpublished backlog and its age: should be near zero and seconds old.
SELECT count(*) AS pending, now() - min(created_at) AS oldest
FROM outbox WHERE published_at IS NULL;

Alert on the age of the oldest unpublished row, not the count. A growing age means the relay is stuck, and every real-time client is quietly falling behind.

A relay crash, recovered Events flow until the relay is killed mid-batch at ten seconds; events queue safely in the outbox; a standby relay takes over at twelve seconds, re-publishes the unmarked batch, and clients drop the duplicates. A relay crash, recovered events queue in outbox, none lost events flowing (0 s) relay killed mid-batch (10 s) standby relay takes lock (12 s) re-publishes last batch (12.2 s) clients drop duplicates (12.3 s) The outage costs two seconds of latency and zero events
A crashed relay delays events; it never loses them.

Operational checklist #

FAQ #

What is the dual-write problem? #

Writing to two systems — a database and a message broker — without a transaction that spans both. A failure between the writes leaves them inconsistent: either an event is lost after the data changed, or an event is published for a change that rolled back.

Doesn’t the outbox add latency to real-time updates? #

A little: the relay’s poll interval, typically tens to a hundred milliseconds, or less with LISTEN/NOTIFY or CDC. For user-facing real-time updates, that is usually imperceptible compared with the consistency it buys.

Can the relay publish an event twice? #

Yes, if it crashes after publishing but before marking the rows. That is why every consumer must deduplicate by event id; the outbox provides at-least-once delivery, and deduplication turns it into effectively-once processing.

Should I use change data capture instead? #

CDC (Debezium, logical replication) streams inserts from the database log with no polling and preserves commit order, which is excellent at scale. It adds infrastructure; an outbox relay is simpler to start with and can later be replaced by CDC reading the same table.

Back to Message Delivery Guarantees.