You've written this code. Everyone has:
async function placeOrder(input: OrderInput) {
const order = await db.orders.insert(input); // 1. write to the database
await broker.publish('order.placed', { id: order.id }); // 2. tell the rest of the system
return order;
}
It looks correct, and it works for months. Then a deploy lands at the wrong moment, the broker has a hiccup, or the process runs out of memory, and the system quietly ends up in a state that makes no sense:
- The order exists, but no email was sent, no inventory reserved, no invoice created
- Or the event was published for an order that was rolled back and doesn't exist
There is no exception in your logs, just two systems that disagree. This is the dual-write problem, and this post explains why you can't fix it with better error handling, then implements the standard solution, the transactional outbox, in Node.js and PostgreSQL.
Why two writes can't be made atomic
A database transaction gives you atomicity within one database. Your broker (Kafka, RabbitMQ, SQS, a webhook) is a different system with its own failure modes, and there's no transaction that spans both. Whichever order you choose, there's a window where one write succeeded and the other didn't.
Order A: write the database first, then publish
BEGIN; INSERT order; COMMIT; ✅ committed
publish event 💥 process crashes / broker down / network timeout
Result: the order exists, but the event is lost forever. Downstream services never find out.
Order B: publish first, then write the database
publish event ✅ delivered; consumers start working
BEGIN; INSERT order; COMMIT; 💥 fails (constraint violation, crash, deadlock)
Result: a phantom event for an order that doesn't exist.
Order C: publish inside the transaction, before the commit
BEGIN
INSERT order
publish event ✅ delivered
COMMIT 💥 fails, or the process dies first
Same phantom-event problem as B, plus you've held a transaction (and a pooled connection) open across a network call.
"Just retry" doesn't solve it
- Retrying the publish helps only if the process is still alive. If it crashed after the commit, there's nothing left to retry, because the intent to publish lived only in memory.
- A retry after an ambiguous failure (a timeout where the broker did receive the message) produces duplicates.
- Wrapping it in
try/catchand "compensating" with a delete creates another failing write.
The fundamental issue is that the intent to publish isn't stored durably alongside the data change. The outbox pattern fixes exactly that.
The outbox idea
Instead of publishing directly, write the event into a table in the same database, in the same transaction as the business change. Then a separate process (the relay) reads that table and publishes to the broker.
┌────────────── one local transaction ──────────────┐
│ INSERT INTO orders ... │
│ INSERT INTO outbox (type, payload) ... │ ← event is durable and atomic with the order
└───────────────────────────────────────────────────┘
│
▼
Relay: read unpublished rows ──▶ broker ──▶ consumers
│
▼
mark rows as published
Now the two writes that must succeed or fail together are in one database transaction, which databases are excellent at:
- Transaction commits → the order and its event both exist. The relay will eventually publish it, even across crashes and restarts.
- Transaction rolls back → neither exists. No phantom events.
What you give up: delivery becomes at-least-once. The relay can crash after publishing but before marking the row as published, so the same event may be published twice. That's the price, and it's manageable: consumers must be idempotent (covered below).
Step 1: The schema
CREATE TABLE outbox (
id BIGSERIAL PRIMARY KEY,
event_id UUID NOT NULL DEFAULT gen_random_uuid(), -- stable id for consumer-side dedupe
aggregate_type TEXT NOT NULL, -- e.g. 'order'
aggregate_id TEXT NOT NULL, -- e.g. the order id; used as the partition/ordering key
type TEXT NOT NULL, -- e.g. 'order.placed'
payload JSONB NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
published_at TIMESTAMPTZ, -- NULL = not yet published
attempts INTEGER NOT NULL DEFAULT 0,
locked_until TIMESTAMPTZ -- lease so multiple relays don't double-claim
);
-- The relay only ever looks at unpublished rows, so index just those
CREATE INDEX outbox_unpublished_idx ON outbox (id) WHERE published_at IS NULL;
Notes:
-
gen_random_uuid()is built in from PostgreSQL 13 (earlier versions need thepgcryptoextension). -
event_idis separate fromid.idis for the relay's ordering;event_idtravels with the message so consumers can recognize duplicates. - The partial index keeps the relay's lookups fast no matter how many published rows have piled up.
Step 2: Write the event in the same transaction
async function placeOrder(input: OrderInput) {
return withTransaction(async (tx) => {
const { rows: [order] } = await tx.query(
`INSERT INTO orders (customer_id, total_cents, status)
VALUES ($1, $2, 'placed') RETURNING id, customer_id, total_cents`,
[input.customerId, input.totalCents],
);
await tx.query(
`INSERT INTO outbox (aggregate_type, aggregate_id, type, payload)
VALUES ('order', $1, 'order.placed', $2)`,
[String(order.id), JSON.stringify({
orderId: order.id,
customerId: order.customer_id,
totalCents: order.total_cents,
})],
);
return order;
});
}
Notice what's not here: no broker call, no network I/O inside the transaction. Writing a row is fast, local, and atomic with the order. (withTransaction is the helper from the earlier posts in this series.)
A few payload design tips:
- Include what consumers need (a snapshot) so they don't have to call back for it, but keep payloads small. Don't put whole entities with sensitive fields in an event log.
-
Version your event types (
order.placed.v1) or include a schema version, since consumers will outlive your current code.
Step 3: The relay
The relay is a small worker that repeatedly: claims a batch of unpublished events, publishes them, and marks them done.
Don't hold a transaction open while publishing
The tempting implementation is BEGIN; SELECT ... FOR UPDATE SKIP LOCKED; publish; UPDATE; COMMIT. It's correct, but it holds a transaction and a pooled connection open across broker calls, the pattern we warned against in the pool starvation post. A slow broker would pin connections and row locks.
A better design is a lease: claim rows with a short, quick UPDATE, publish outside any transaction, then mark them published. If the relay dies mid-way, the lease expires and another pass picks the rows up.
// relay.ts
const BATCH = 100;
const LEASE_SECONDS = 30;
const MAX_ATTEMPTS = 10;
const CLAIM_SQL = `
UPDATE outbox
SET locked_until = now() + make_interval(secs => $2),
attempts = attempts + 1
WHERE id IN (
SELECT id FROM outbox
WHERE published_at IS NULL
AND attempts < $3
AND (locked_until IS NULL OR locked_until < now())
ORDER BY id
LIMIT $1
FOR UPDATE SKIP LOCKED
)
RETURNING id, event_id, aggregate_type, aggregate_id, type, payload, created_at`;
export async function relayOnce(publisher: Publisher): Promise<number> {
const { rows } = await pool.query(CLAIM_SQL, [BATCH, LEASE_SECONDS, MAX_ATTEMPTS]);
if (rows.length === 0) return 0;
rows.sort((a, b) => Number(BigInt(a.id) - BigInt(b.id))); // RETURNING order isn't guaranteed
const published: string[] = [];
let failed: { id: string; err: unknown } | undefined;
for (const ev of rows) {
try {
await publisher.publish(ev);
published.push(ev.id);
} catch (err) {
failed = { id: ev.id, err };
break; // stop at first failure to preserve order within the batch
}
}
if (published.length) {
await pool.query(
'UPDATE outbox SET published_at = now() WHERE id = ANY($1::bigint[])',
[published],
);
}
if (failed) {
log.warn({ err: failed.err, outboxId: failed.id }, 'outbox publish failed');
// Exponential backoff via the lease: retry in 2^attempts seconds, capped at 5 minutes
await pool.query(
`UPDATE outbox SET locked_until = now() + make_interval(secs => least(300, 2 ^ attempts))
WHERE id = $1`,
[failed.id],
);
// Release the rest of the batch so they can be retried immediately
const rest = rows.map((r) => r.id).filter((id) => !published.includes(id) && id !== failed!.id);
if (rest.length) {
await pool.query('UPDATE outbox SET locked_until = NULL WHERE id = ANY($1::bigint[])', [rest]);
}
}
return published.length;
}
The FOR UPDATE SKIP LOCKED inside the claim subquery lets several relay instances run concurrently without blocking each other or claiming the same rows. And because the claim is a single quick statement, no transaction is held during publishing.
The publisher is a thin adapter over your broker. Here's one for Kafka using kafkajs:
interface Publisher { publish(ev: OutboxRow): Promise<void> }
export class KafkaPublisher implements Publisher {
constructor(private producer: Producer) {}
async publish(ev: OutboxRow) {
await this.producer.send({
topic: `${ev.aggregate_type}-events`,
messages: [{
key: ev.aggregate_id, // same key => same partition => per-aggregate ordering
value: JSON.stringify(ev.payload),
headers: { 'event-id': ev.event_id, 'event-type': ev.type },
}],
});
}
}
And the loop that drives it:
let running = true;
export async function runRelay(publisher: Publisher) {
while (running) {
try {
const n = await relayOnce(publisher);
if (n === 0) await sleep(500); // idle: poll gently
} catch (err) {
log.error({ err }, 'relay iteration failed');
await sleep(2_000); // database trouble: back off
}
}
}
// graceful shutdown: stop claiming; the current batch finishes before the loop exits
export const stopRelay = () => { running = false; };
The trap: ids are not commit order
Here's a bug that sinks many first implementations. It's tempting to poll like a log reader: "give me rows with id > last_seen_id."
-- ❌ Can permanently skip events
SELECT * FROM outbox WHERE id > $1 ORDER BY id LIMIT 100;
The problem: sequence values are assigned when the row is inserted, not when the transaction commits. Two concurrent transactions:
Tx A: INSERT outbox → id 101 ... (slow work) ... COMMIT ← commits LATE
Tx B: INSERT outbox → id 102 ... COMMIT ← commits first
If the relay polls between those commits, it sees 102 but not 101 (uncommitted, invisible). It advances last_seen_id to 102. When 101 commits, it's below the cursor, and it's never published.
The design above avoids this by tracking state per row (published_at IS NULL) rather than with a high-water mark. A late-committing row simply shows up as unpublished the next time the relay looks. If you ever build something that reads by position, such as a cursor on id, you need a different technique (for example, only advancing past ids older than your longest transaction, or reading the WAL directly, which is what CDC does).
Ordering: what you do and don't get
-
Within a batch from one relay, events publish in
idorder. - Across multiple relays, two events for the same aggregate could be claimed by different relays and published out of order.
- Also,
idorder isn't strictly commit order (see above), so "order by id" is a close approximation, not a guarantee.
How much this matters depends on your consumers. Options:
- Make consumers order-tolerant. Include a per-aggregate version number or timestamp in the payload and have consumers ignore anything older than what they've applied. This is the most robust approach.
-
Shard the relay. Run one relay per shard, with events routed by
hash(aggregate_id) % N, so a given aggregate is always handled by one relay. - Run a single relay instance if throughput allows. It's the simplest and often sufficient.
Using the aggregate id as the Kafka message key gives you per-aggregate ordering inside the broker once messages arrive, but it can't fix order lost before publishing.
Step 4: Idempotent consumers (the other half of the pattern)
Because delivery is at-least-once, the same event can arrive twice: the relay crashed between publishing and marking, or the broker redelivered. If your consumer sends an email or charges a card per event, duplicates are real bugs.
The standard fix is an inbox / processed-events table with a unique key, checked in the same transaction as the consumer's side effects:
CREATE TABLE processed_events (
event_id UUID PRIMARY KEY,
processed_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
async function handleOrderPlaced(eventId: string, payload: OrderPlaced) {
await withTransaction(async (tx) => {
const res = await tx.query(
'INSERT INTO processed_events (event_id) VALUES ($1) ON CONFLICT DO NOTHING',
[eventId],
);
if (res.rowCount === 0) return; // already handled: safe no-op
await tx.query(
'INSERT INTO invoices (order_id, amount_cents) VALUES ($1, $2)',
[payload.orderId, payload.totalCents],
);
});
}
The dedupe insert and the business write commit together, so either both happen or neither does. If the handler crashes after the commit but before acking the message, the redelivery hits the unique key and does nothing.
When the consumer's side effect is external (an email, a payment API), a local table can't make it atomic, so pass the event_id as an idempotency key to the external service where it supports one, or accept and design for the rare duplicate.
Alternatives to polling
Polling every few hundred milliseconds is simple and works well for most systems. Two ways to improve on it:
-
LISTEN/NOTIFYas a wake-up signal. Have the writer runNOTIFY outbox_new(delivered only when the transaction commits) and have the relayLISTEN, so it can poll immediately instead of waiting. Treat it purely as a hint: notifications are lost if no listener is connected, so keep the periodic poll as a fallback. Don't put the event itself in the notification. - Change data capture (CDC). Tools like Debezium read PostgreSQL's write-ahead log via logical decoding and publish outbox rows as they commit. It removes polling load and gives you true commit ordering, at the cost of more infrastructure (a replication slot to monitor, connectors to operate). It's a good fit when volume or latency requirements outgrow a poller.
Both patterns still write the outbox row in the same transaction. Only the relay changes.
Operating an outbox
Clean up, or it grows forever
Published rows are dead weight. Delete them on a schedule:
DELETE FROM outbox
WHERE published_at IS NOT NULL
AND published_at < now() - interval '7 days';
Delete in batches (a LIMIT via a subquery on id) so you don't create one giant transaction. A table that is constantly inserted, updated, and deleted generates bloat, so make sure autovacuum keeps up. For very high volume, consider time-partitioning the table and dropping old partitions, which is far cheaper than deleting rows.
Handle poison messages
An event that can never be published (a payload the broker rejects, say) would be retried forever. The attempts < MAX_ATTEMPTS guard in the claim query parks it instead of looping. Alert on rows that hit the limit, and fix or replay them deliberately rather than letting them silently stall.
Monitor lag and backlog
The metric that matters is how stale the oldest unpublished event is:
SELECT count(*) AS unpublished,
max(now() - created_at) AS oldest_age,
count(*) FILTER (WHERE attempts >= 10) AS stuck
FROM outbox
WHERE published_at IS NULL;
Export these and alert when oldest_age exceeds your tolerance (for example, 30 seconds), or when stuck > 0. A growing backlog means the relay is down, slow, or blocked on a bad broker.
Test the failure windows
The whole point of the pattern is surviving specific failures, so test those exact failures:
it('creates no event when the business transaction rolls back', async () => {
await expect(placeOrder({ ...validInput, totalCents: -1 })).rejects.toThrow(); // violates a CHECK
const { rows } = await pool.query('SELECT count(*)::int AS n FROM outbox');
expect(rows[0].n).toBe(0);
});
it('redelivers after a crash between publish and mark, and the consumer dedupes', async () => {
await placeOrder(validInput);
// Publisher succeeds, but "crash" before the row is marked published
const published: OutboxRow[] = [];
await relayOnceWithCrashAfterPublish((ev) => published.push(ev));
// After the lease expires, the relay runs again and republishes
await expireLeases();
await relayOnce({ publish: async (ev) => { published.push(ev); } });
expect(published).toHaveLength(2); // at-least-once: duplicate delivered
// The consumer applies it exactly once
for (const ev of published) await handleOrderPlaced(ev.event_id, ev.payload);
const { rows } = await pool.query('SELECT count(*)::int AS n FROM invoices');
expect(rows[0].n).toBe(1);
});
When not to use it
The outbox adds a table, a worker, and consumer-side dedupe. It's worth it when losing or inventing an event causes real harm (billing, inventory, notifications that matter). It's overkill when:
- There's a single system and no downstream consumers
- Losing an occasional event is genuinely acceptable (some analytics or cache invalidation)
- You can derive state from the database itself (consumers read the data directly)
Pattern summary
| Piece | Responsibility |
|---|---|
| Business transaction | Writes the data and the outbox row atomically |
| Outbox table | Durable record of "events that must be published" |
| Relay | Claims, publishes, marks; retries with backoff; safe to run multiple instances |
| Broker | Carries events, with a key for per-aggregate ordering |
| Consumer | Idempotent, deduping on event_id in the same transaction as its effects |
| Monitoring | Oldest-unpublished age, stuck rows, cleanup |
Checklist
- ✅ Never call the broker inside (or beside) the business transaction. Insert an outbox row instead
- ✅ Write the outbox row in the same transaction as the data change
- ✅ Track publish state per row, not with an
idhigh-water mark - ✅ Claim with a lease (
FOR UPDATE SKIP LOCKED) and publish outside any transaction - ✅ Retry with exponential backoff; cap attempts; alert on stuck rows
- ✅ Give every event a stable
event_id, and make consumers idempotent - ✅ Use the aggregate id as the ordering key, and decide how you handle ordering across relays
- ✅ Clean up published rows and watch table bloat
- ✅ Test the crash windows explicitly
Key takeaways
- Dual writes can't be made atomic. Any order of "write database, publish event" has a failure window that loses events or invents them.
- The outbox turns two writes into one. The event is saved in the same database transaction as the data, and a relay publishes it later.
-
Delivery is at-least-once, so consumers must be idempotent. Dedupe on a stable
event_idin the same transaction as the side effects. -
Don't poll by
idhigh-water mark. Sequence order isn't commit order, and late-committing rows get skipped. Track published state per row. - Claim with leases, not long transactions, so a slow broker can't pin your connection pool.
- Decide your ordering story (version numbers, sharded relays, or a single relay) rather than assuming it.
- Operate it: monitor the age of the oldest unpublished event, park poison messages, and clean up old rows.
Have you run an outbox in production, with polling or CDC? I'd like to hear what bit you, so share it in the comments.
Further reading: Chris Richardson's write-up of the transactional outbox pattern, the Debezium outbox event router docs, and the PostgreSQL docs on SELECT ... FOR UPDATE SKIP LOCKED and NOTIFY.
Top comments (0)