The Transactional Outbox Pattern in Postgres and NestJS: How We Ended Dual Writes Between the Database and the Queue

Key takeaways

• A dual write is one operation that writes to two systems with no shared transaction, such as a Postgres row plus a Redis, RabbitMQ or Kafka message; a crash between the two writes leaves the systems disagreeing.

• The transactional outbox pattern stores each event as a row in the same Postgres transaction as the state change, so the commit makes both durable or neither.

• A relay process claims unpublished outbox rows with SELECT … FOR UPDATE SKIP LOCKED, publishes them, and marks them published; several relay instances can run without claiming the same row twice.

• The outbox gives at-least-once delivery, not exactly-once, so every consumer records processed event IDs in a table with a unique constraint and skips repeats.

• Polling is the simplest relay; change data capture with Debezium removes polling latency but adds Kafka Connect and a replication slot to operate.

What is the dual-write problem, and why don't retries fix it?

The dual-write problem is that two independent writes cannot be made atomic from application code, so any failure between them produces an inconsistency that no retry can see. Many NestJS services, including early versions of ours, create an order in Postgres and enqueue an order.created job on the next line. Tests pass because tests never kill the process between two awaits; production does, through deploys that send SIGTERM mid-request, out-of-memory kills, and broker timeouts.

The dual write fails in two directions. A lost event happens when Postgres commits and the process dies before the publish: the order exists, but no confirmation email, invoice or stock reservation ever follows. A phantom event happens when the message is published first, or inside the transaction before COMMIT, and the transaction then rolls back: a consumer acts on an order that does not exist.

A retry loop cannot fix the dual write, because the process that would retry is the process that crashed. Moving the publish inside the transaction only swaps lost events for phantom events and holds a database transaction open across a network call. Two-phase commit (XA) is the textbook answer, but Redis, Kafka and most managed brokers cannot join a Postgres transaction that way.

How does the transactional outbox pattern work?

The transactional outbox pattern turns publishing a message into inserting a row, which Postgres commits atomically with the business change. A separate relay reads the committed rows later and performs the actual publish.

Our outbox table has a uuid primary key, the aggregate type and aggregate ID (for example order and the order's ID), the event type, a jsonb payload, created_at, published_at, an attempts counter, a next_attempt_at timestamp for retry backoff, and the last error. A partial index on rows where published_at IS NULL keeps the relay's query cheap as the table grows.

On the write side, the NestJS service opens one Drizzle transaction, inserts the order, and inserts the outbox row through the same transaction client. Prisma's interactive prisma.$transaction and TypeORM's dataSource.transaction provide the same kind of transaction-scoped client. We put a version field and a snapshot of the fields consumers need into every payload, because the outbox row outlives the code that wrote it.

How does the relay publish outbox rows safely with several workers?

The relay claims a batch of pending rows with SELECT … FOR UPDATE SKIP LOCKED, publishes each one with the outbox row ID attached as the event ID, and marks the successes as published before committing. FOR UPDATE locks the claimed rows, and SKIP LOCKED makes a second relay instance skip rows the first one holds instead of waiting, so the relay can run in every API replica. In NestJS we start the loop in onApplicationBootstrap and stop it in onApplicationShutdown, with app.enableShutdownHooks() enabled.

Per-row error handling matters most. If one failing publish throws, the whole batch rolls back and the same poison row is retried forever, blocking everything behind it. Our relay catches each failure, increments attempts, and pushes next_attempt_at forward with exponential backoff capped at five minutes, so one bad row waits its turn while the rest flow, and a broker outage delays delivery without discarding anything.

The claim transaction stays open while the relay talks to the broker, so publishes need a short timeout. For slow targets such as third-party webhooks, we use a lease instead: claim rows by setting a locked_until timestamp, commit, publish outside the transaction, then mark the rows published.

Ordering needs explicit design. Postgres now() returns the transaction's start time, so a transaction that started first can commit last and its event can be published after a newer one. The relay never skips that late row, because it re-queries every pending row instead of reading past a high-water mark, but several relays remove global ordering entirely. When consumers need per-aggregate order, we run a single relay or shard relays by aggregate ID, and consumers use a version number to discard stale events.

Why is outbox delivery at-least-once, and how do consumers deduplicate?

Outbox delivery is at-least-once because the relay can crash after the broker accepts a message but before the published_at update commits; the row is still pending, so the next run publishes it again. Deduplication therefore lives in the consumer.

Each consumer keeps a processed_events table with the event ID as its primary key. Inside the same transaction as its own work, the consumer runs INSERT … ON CONFLICT DO NOTHING RETURNING on that table; when no row comes back, the event was already handled and the handler returns without repeating the side effect.

For side effects outside the consumer's database, such as charging a card, we pass the event ID as the provider's idempotency key when the API supports one; Stripe's Idempotency-Key header is the standard example.

Should the relay poll, use LISTEN/NOTIFY, or use change data capture?

Polling is the right default for most teams; change data capture is worth its infrastructure when a team already runs Kafka or needs sub-second latency at high volume. This is how we compare the three relay strategies:

• Polling with SKIP LOCKED: latency of up to one poll interval, no infrastructure beyond a worker, and a constant small query load even when idle.

• Polling plus LISTEN/NOTIFY wake-ups: near-immediate latency and one dedicated session connection. Notifications are not durable, so the poll stays as a fallback, and transaction-mode PgBouncer does not support LISTEN.

• Change data capture with Debezium's outbox event router: near-immediate delivery in commit order, but it needs Kafka Connect or Debezium Server, wal_level=logical and a replication slot. An abandoned replication slot retains WAL until the disk fills.

Change data capture (CDC) means reading row changes from the Postgres write-ahead log through logical replication instead of querying the table. NOTIFY is transactional: a notification sent inside a transaction is delivered only if that transaction commits, which makes it a safe wake-up signal for a poller.

How do we keep the outbox table healthy in production?

We keep the outbox small and alert on its lag. A scheduled job deletes rows whose published_at is older than a few days, in batches, to avoid long locks. At higher volumes, partitioning the table by created_at and dropping old partitions is cheaper than deleting rows.

The alert we would not ship without is the age of the oldest pending row, together with the highest attempts value. A growing oldest-pending age means the relay is stuck, the broker is down, or a poison row keeps failing. None of these is visible from the API, which keeps returning 201 because the order itself was saved correctly.

FAQ

Q: Do we still need an outbox if the queue is BullMQ on Redis?

A: Yes. Postgres and Redis are two systems, so writing a row and enqueuing a BullMQ job is a dual write. The alternative is a Postgres-backed queue such as Graphile Worker, whose graphile_worker.add_job() SQL function enqueues a job inside your own transaction, which makes the job table itself the outbox.

Q: Does the transactional outbox give exactly-once delivery?

A: No. The transactional outbox gives at-least-once delivery; exactly-once processing comes from consumers that deduplicate by event ID.

Q: Can a service write to the broker first and to the database second?

A: No. Reversing the order still leaves two independent writes and turns lost events into phantom events for rows that never commit.

Q: Should the outbox payload contain the full entity or only its ID?

A: A versioned snapshot of the fields consumers need. An ID-only event forces every consumer to call back to the producer, and by then the entity may already be in a newer state.

Q: Does the outbox pattern work with Prisma or TypeORM instead of Drizzle?

A: Yes. The outbox pattern only needs a database transaction that covers two inserts, and Prisma's interactive transactions and TypeORM's dataSource.transaction() both provide one.