Building Resilient Event-Driven Architectures with Postgres and Serverless Queues

The Dual-Write Problem Is Where Event-Driven Systems Go to Die

Your order service commits a row to orders, then publishes order.created to a queue. Between those two statements lives an entire class of production incidents: the process is OOM-killed, the pod is evicted during a rolling deploy, the queue returns a 503, the lambda times out. The order exists. The event does not. Downstream billing never charges, the warehouse never ships, and the reconciliation job you wrote six months later becomes load-bearing infrastructure.

Flip the order and you get the inverse pathology: the event is delivered, the transaction rolls back, and a phantom order is broadcast to every consumer. There is no ordering of those two operations that is safe, because they touch two independent systems with independent failure domains.

This article builds the pattern that actually solves it in production: a transactional outbox in Postgres, drained by a lease-based relay worker, delivered through a serverless queue (QStash), and consumed by idempotent handlers. Every piece has a specific job and a specific failure mode.

Why "Just Publish After Commit" Is Not a Strategy

The three failure windows

1. Process death between COMMIT and publish() — event lost forever.
2. Ambiguous network failure — the broker accepted the message but the response timed out, so you retry and duplicate it.
3. Partial consumer failure — your handler writes to the database, then crashes before acknowledging, so the queue redelivers.

Window 1 demands durability tied to the transaction. Windows 2 and 3 demand at-least-once delivery plus idempotency — because exactly-once across independent systems is not achievable without distributed transactions, and XA/2PC is not available with serverless queues (or desirable given the coordinator's latency and availability profile).

The correct mental model

Stop trying to prevent duplicates. Guarantee at-least-once delivery, then make every consumer harmless under replay. That is the entire resilience contract, and everything below implements it.

┌──────────────┐   same TX    ┌──────────────┐
│  business    │─────────────▶│   outbox     │
│  tables      │              │  (durable)   │
└──────────────┘              └──────┬───────┘
                                     │ claim (SKIP LOCKED + lease)
                              ┌──────▼───────┐
                              │    relay     │  idempotent publish
                              └──────┬───────┘
                                     │ HTTPS
                              ┌──────▼───────┐
                              │   QStash     │  retries, dedupe, DLQ
                              └──────┬───────┘
                                     │ signed webhook
                              ┌──────▼───────┐
                              │  consumer    │  dedupe table + work in one TX
                              └──────────────┘

The Transactional Outbox, Precisely

The outbox is a plain table in the same database as your business data. You insert your event into it inside the same transaction that mutates business state. Postgres gives you atomicity for free: either both rows commit or neither does. No message broker is involved in the commit path, so there is no dual write.

A schema that survives production

-- migrations/0001_outbox.sql
CREATE TABLE outbox (
  id               bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  topic            text        NOT NULL,
  partition_key    text        NOT NULL,
  payload          jsonb       NOT NULL,
  headers          jsonb       NOT NULL DEFAULT '{}'::jsonb,
  created_at       timestamptz NOT NULL DEFAULT now(),
  available_at     timestamptz NOT NULL DEFAULT now(),
  dispatched_at    timestamptz,
  dead_lettered_at timestamptz,
  attempts         integer     NOT NULL DEFAULT 0,
  locked_until     timestamptz,
  last_error       text
);

-- Partial index: only rows the relay can ever care about.
CREATE INDEX outbox_ready_idx
  ON outbox (available_at, id)
  WHERE dispatched_at IS NULL AND dead_lettered_at IS NULL;

-- Observability helper for slow consumers.
CREATE INDEX outbox_pending_age_idx
  ON outbox (created_at)
  WHERE dispatched_at IS NULL AND dead_lettered_at IS NULL;

Notes that matter in production:

• `headers` as `jsonb` carries correlation IDs, trace parents, and schema versions without schema churn.
• `available_at` encodes backoff. The relay never sleeps; it just skips rows that aren't ready.
• `locked_until` is a lease, not a lock. It is how you survive a relay crash mid-publish without a stuck row.
• `bigint` identity gives you a monotonic tiebreaker for stable ordering within a partition key.

Retention, partitioning, and vacuum

An outbox is an append-heavy table with a delete-shaped lifecycle. At meaningful volume, either delete aggressively (DELETE FROM outbox WHERE dispatched_at < now() - interval '7 days' in pg_cron) or partition by `created_at` and detach whole partitions — the latter keeps VACUUM bounded and lets you drop a month of history in milliseconds. Never let the outbox grow unbounded "just in case"; it becomes a table-scan tax on every relay poll.

The Relay: Claim, Publish, Mark — In That Order

Claiming work without holding locks across the network

The classic mistake is wrapping SELECT ... FOR UPDATE SKIP LOCKED and the HTTP publish in one transaction. You then hold row locks for the duration of a network round trip — hundreds of milliseconds, sometimes seconds — blocking vacuum and exhausting connections.

Instead, claim with a lease in a single atomic statement, then publish outside any transaction:

CREATE OR REPLACE FUNCTION claim_outbox(batch_size int, lease interval)
RETURNS TABLE (
  id bigint, topic text, partition_key text,
  payload jsonb, headers jsonb, attempts int
)
LANGUAGE sql AS $$
  UPDATE outbox o
     SET locked_until = now() + lease,
         attempts     = o.attempts + 1
   WHERE o.id IN (
     SELECT id FROM outbox
      WHERE dispatched_at IS NULL
        AND dead_lettered_at IS NULL
        AND available_at <= now()
        AND (locked_until IS NULL OR locked_until < now())
      ORDER BY available_at, id
      FOR UPDATE SKIP LOCKED
      LIMIT batch_size
   )
  RETURNING o.id, o.topic, o.partition_key, o.payload, o.headers, o.attempts;
$$;

If the relay dies after claiming, the lease expires and the row is reclaimable. The only consequence is a possible duplicate publish — which the idempotency layer absorbs.

The TypeScript relay worker

import { Pool } from 'pg';
import { Client as QStashClient } from '@upstash/qstash';

interface OutboxRow {
  id: string; // pg returns bigint as string — keep it that way
  topic: string;
  partition_key: string;
  payload: Record<string, unknown>;
  headers: Record<string, string>;
  attempts: number;
}

const pool = new Pool({ connectionString: process.env.DATABASE_URL, max: 10 });
const qstash = new QStashClient({ token: process.env.QSTASH_TOKEN! });
const BASE_URL = process.env.PUBLIC_BASE_URL!; // e.g. https://api.example.com

const MAX_ATTEMPTS = 12;
const LEASE_SECONDS = 30;

/** Exponential backoff with full jitter — see AWS Builders' Library. */
function backoffSeconds(attempt: number): number {
  const ceiling = Math.min(30 * 2 ** attempt, 3600);
  return Math.max(1, Math.floor(Math.random() * ceiling));
}

async function relayBatch(batchSize = 50): Promise<number> {
  const { rows } = await pool.query<OutboxRow>(
    'SELECT * FROM claim_outbox($1, $2::interval)',
    [batchSize, `${LEASE_SECONDS} seconds`],
  );

  await Promise.allSettled(
    rows.map(async (row) => {
      try {
        await qstash.publishJSON({
          url: `${BASE_URL}/events/${row.topic}`,
          body: row.payload,
          headers: { ...row.headers, 'x-outbox-id': row.id },
          retries: 5,
          deduplicationId: `outbox-${row.id}`, // collapses relay retries
          flowControl: { key: row.partition_key, parallelism: 1 },
        });

        await pool.query(
          'UPDATE outbox SET dispatched_at = now(), locked_until = NULL WHERE id = $1',
          [row.id],
        );
      } catch (err) {
        const message = err instanceof Error ? err.message : String(err);

        if (row.attempts >= MAX_ATTEMPTS) {
          await pool.query(
            'UPDATE outbox SET dead_lettered_at = now(), locked_until = NULL, last_error = $2 WHERE id = $1',
            [row.id, message],
          );
          console.error(JSON.stringify({ level: 'error', evt: 'outbox.dead_letter', id: row.id, message }));
          return;
        }

        await pool.query(
          `UPDATE outbox
              SET locked_until = NULL,
                  available_at = now() + ($2 || ' seconds')::interval,
                  last_error   = $3
            WHERE id = $1`,
          [row.id, String(backoffSeconds(row.attempts)), message],
        );
      }
    }),
  );

  return rows.length;
}

Run this on a schedule (pg_cron + pg_net, a Kubernetes CronJob, or a Vercel Cron) every few seconds and drain until the batch returns fewer rows than the batch size.

Polling vs. LISTEN/NOTIFY

The pragmatic answer is both. NOTIFY gives sub-second latency, but notifications are delivered only to currently-connected listeners, are not transactional with your read, and are silently dropped if the connection is idle-reaped. Use it as a wake-up hint; keep the 1–5 second poll as the safety net that guarantees liveness. Design for the poll to be correct, and treat NOTIFY purely as latency optimization.

Handing Off to a Serverless Queue

Why QStash fits this architecture

QStash is an HTTP-based message broker: you publish a JSON body to a URL, and it delivers a signed webhook with configurable retries, delays, deduplication, and flow control. Because the destination is an HTTP endpoint, there is no persistent consumer connection to manage — the relay is stateless and horizontally scalable, and it degrades gracefully if your consumer fleet scales to zero.

The three features that make it a natural partner for an outbox:

• `deduplicationId` — QStash collapses publishes with the same ID within a deduplication window, so a relay crash-and-reclaim cannot double-deliver.
• `flowControl: { key, parallelism }` — serializes deliveries per partition key, giving you per-entity ordering without a global log.
• `retries` + `failureCallback` — a managed retry ladder with a final hook into your dead-letter store.

Verifying inbound signatures

Never trust an unsigned webhook. QStash signs each request; verify with the Receiver before parsing a single byte:

import { Receiver } from '@upstash/qstash';

const receiver = new Receiver({
  currentSigningKey: process.env.QSTASH_CURRENT_SIGNING_KEY!,
  nextSigningKey: process.env.QSTASH_NEXT_SIGNING_KEY!,
});

export async function POST(req: Request): Promise<Response> {
  const signature = req.headers.get('upstash-signature');
  if (!signature) return new Response('missing signature', { status: 401 });

  const rawBody = await req.text(); // verify the RAW bytes, never re-serialized JSON

  const valid = await receiver
    .verify({ signature, body: rawBody, url: req.url })
    .catch(() => false);

  if (!valid) return new Response('invalid signature', { status: 401 });

  // ... dispatch to idempotent handler
  return new Response(null, { status: 200 });
}

Pitfall: verify compares the URL. Behind a proxy or on Vercel preview deployments, req.url may differ from the public destination URL you published to. Normalize using the forwarded host, or verification fails intermittently and only in production.

Idempotent Consumers: The Only Guarantee You Actually Get

A dedupe table that fits in one transaction

The consumer's dedupe record and its side effect must commit atomically. Otherwise you have rebuilt the dual write at the far end of the pipeline.

CREATE TABLE processed_messages (
  consumer     text        NOT NULL,
  message_id   text        NOT NULL,
  processed_at timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (consumer, message_id)
);

import type { Pool, PoolClient } from 'pg';

type Outcome = 'processed' | 'duplicate';

async function handleOnce(
  pool: Pool,
  consumer: string,
  messageId: string,
  work: (tx: PoolClient) => Promise<void>,
): Promise<Outcome> {
  const client = await pool.connect();
  try {
    await client.query('BEGIN');

    const claimed = await client.query(
      `INSERT INTO processed_messages (consumer, message_id)
       VALUES ($1, $2)
       ON CONFLICT DO NOTHING
       RETURNING message_id`,
      [consumer, messageId],
    );

    if (claimed.rowCount === 0) {
      await client.query('ROLLBACK');
      return 'duplicate'; // already handled — ack and move on
    }

    await work(client); // side effects in the SAME transaction
    await client.query('COMMIT');
    return 'processed';
  } catch (err) {
    await client.query('ROLLBACK').catch(() => undefined);
    throw err; // non-2xx → QStash retries with backoff
  } finally {
    client.release();
  }
}

Two rules make this work at scale. First, message_id must be stable across retries — use the outbox id, propagated via x-outbox-id, not a fresh UUID. Second, prune processed_messages on a TTL slightly longer than the queue's maximum retry horizon; an unbounded dedupe table is just a slow-motion outage.

Ordering and partitioning

Global ordering in an event-driven system is a scalability trap. Partition instead: partition_key (e.g. order:1234) plus flowControl.parallelism: 1 gives you per-key FIFO while allowing unbounded cross-key concurrency. Consumers must still tolerate out-of-order arrival across keys — encode that assumption in your schema, not your hopes.

Poison Messages, DLQs, and the Retry Budget

A poison message is one that will never succeed: malformed payload, a referenced entity that no longer exists, a schema version you dropped. Retrying it 5 times at the queue layer and 12 times at the relay layer is 60 wasted invocations and a growing backlog.

Guard it with three rules:

1. Classify errors. 4xx-shaped validation failures should dead-letter immediately; 5xx and timeouts deserve retries. Returning 200 for a malformed payload that you logged is a silent data-loss bug — return a non-retryable status and route to the DLQ.
2. Cap the retry budget at both layers and make the numbers explicit in config, not scattered across handlers.
3. Make the DLQ queryable, not a log line. A dead_lettered_at column on the outbox (as above) plus a failureCallback endpoint gives you one SELECT to see everything stuck, with the original payload and the exact error.

Observability: The Queries That Actually Page You

-- 1. Backlog depth and age — the two numbers that predict incidents.
SELECT count(*)                                        AS pending,
       max(now() - created_at)                         AS oldest_age,
       count(*) FILTER (WHERE attempts > 3)            AS flapping
  FROM outbox
 WHERE dispatched_at IS NULL AND dead_lettered_at IS NULL;

-- 2. Throughput in 1-minute buckets for lag dashboards.
SELECT date_trunc('minute', dispatched_at) AS minute, count(*)
  FROM outbox
 WHERE dispatched_at > now() - interval '1 hour'
 GROUP BY 1 ORDER BY 1;

-- 3. Stuck leases — a relay that died without releasing.
SELECT id, topic, attempts, locked_until
  FROM outbox
 WHERE locked_until < now() AND dispatched_at IS NULL
 ORDER BY locked_until LIMIT 20;

Alert on oldest pending age, not depth: 10,000 pending messages draining in ten seconds is healthy; 3 pending messages that are twenty minutes old means your relay is dead.

Production Readiness Checklist

• Outbox inserted inside the business transaction — verified by a test that rolls back and asserts no event exists.
• Relay claims with a lease; no HTTP call inside a FOR UPDATE transaction.
• deduplicationId set to the outbox primary key on every publish.
• Webhook signatures verified against raw request bytes.
• Consumer side effects and dedupe row commit in a single transaction.
• Retry budgets capped at both the relay and the queue; backoff uses jitter.
• DLQ is a table with payload + error, queryable in one statement.
• Retention job or partition detach scheduled; VACUUM never starves.
• Alerting on oldest pending age and on dead-letter arrival, not just error rate.

Sources & Technical References

• PostgreSQL Documentation — Explicit Locking (row-level locks, FOR UPDATE SKIP LOCKED): https://www.postgresql.org/docs/current/explicit-locking.html
• PostgreSQL Documentation — `NOTIFY` / `LISTEN`: https://www.postgresql.org/docs/current/sql-notify.html
• PostgreSQL Documentation — Table Partitioning: https://www.postgresql.org/docs/current/ddl-partitioning.html
• PostgreSQL Documentation — `INSERT ... ON CONFLICT`: https://www.postgresql.org/docs/current/sql-insert.html
• microservices.io — Pattern: Transactional Outbox (Chris Richardson): https://microservices.io/patterns/data/transactional-outbox.html
• Upstash QStash — Getting Started: https://upstash.com/docs/qstash/overall/getstarted
• Upstash QStash — Verifying Signatures: https://upstash.com/docs/qstash/howto/signature
• Upstash QStash — Deduplication: https://upstash.com/docs/qstash/features/deduplication
• Upstash QStash — Flow Control: https://upstash.com/docs/qstash/features/flow-control
• AWS Builders' Library — Timeouts, Retries, and Backoff with Jitter: https://aws.amazon.com/builders-library/timeouts-retries-and-backoff-with-jitter/
• Google SRE Book — Handling Overload and Cascading Failures: https://sre.google/sre-book/handling-overload/
• RFC 9110 — HTTP Semantics (idempotent methods, retry semantics): https://www.rfc-editor.org/rfc/rfc9110.html
• pg_cron — Job Scheduler for PostgreSQL: https://github.com/citusdata/pg_cron
• *Kleppmann, M. — Designing Data-Intensive Applications, Ch. 11 (Stream Processing)*: https://dataintensive.net/