A Postgres Outbox That Survives Two Pods (No Message Broker)

· 5 min read · Jo4 Team
java springboot postgres architecture

Here's a deceptively simple requirement: when something important happens, post a message to a Slack channel. Exactly once. Durably — if the process crashes mid-send, retry it. And it has to be correct when the app runs on more than one instance.

The naive version (slackClient.post(msg) inline) fails three ways: it's lost on a crash, it double-posts on a retry, and under load it hammers Slack. You could reach for Kafka. You could also just use the database you already have. Here's the outbox we landed on — and the concurrency bug that took two rewrites to actually kill.

TL;DR

  • Persist the message first (an "outbox" row), deliver separately. Decouples the write from the flaky HTTP call.
  • To make multiple pods safe, claim a row atomically before delivering it: FOR UPDATE SKIP LOCKED + a lease token.
  • Enqueue idempotently with INSERT ... ON CONFLICT DO NOTHING.
  • Increment the attempt counter at claim time, not at completion, or a crash-loop never dead-letters.

The table

CREATE TABLE alert_outbox (
  id               BIGSERIAL PRIMARY KEY,
  dedup_key        VARCHAR(64) NOT NULL,
  message          TEXT NOT NULL,
  status           VARCHAR(20) NOT NULL,   -- PENDING, IN_PROGRESS, SENT, FAILED
  attempts         INT NOT NULL DEFAULT 0,
  lease_token      VARCHAR(64),
  lease_expires_at BIGINT,
  created_at       BIGINT NOT NULL,
  updated_at       BIGINT NOT NULL
);
CREATE UNIQUE INDEX uq_alert_outbox_dedup ON alert_outbox (dedup_key);

Enqueue: idempotent by construction

The producer shouldn't care whether this event was already recorded. A unique dedup_key plus ON CONFLICT DO NOTHING makes a double-enqueue a no-op:

INSERT INTO alert_outbox (dedup_key, message, status, attempts, created_at, updated_at)
VALUES (:key, :msg, 'PENDING', 0, :now, :now)
ON CONFLICT (dedup_key) DO NOTHING
RETURNING id;

If it returns a row, you created it. If it returns nothing, someone beat you to it. No try/catch, no race.

The claim: this is where multi-pod correctness lives

A relay scheduler runs on every pod. If two of them SELECT the pending rows and both deliver, you double-post. The fix is to make claiming a row atomic, so only one relay can own it:

WITH candidates AS (
  SELECT id FROM alert_outbox
  WHERE attempts < :maxAttempts
    AND (status IN ('PENDING','FAILED')
         OR (status = 'IN_PROGRESS' AND lease_expires_at < :now))
  ORDER BY created_at
  FOR UPDATE SKIP LOCKED
  LIMIT :batch
)
UPDATE alert_outbox r
   SET status = 'IN_PROGRESS',
       lease_token = :token,
       lease_expires_at = :leaseUntil,
       attempts = attempts + 1,
       updated_at = :now
  FROM candidates c
 WHERE r.id = c.id
RETURNING r.*;

Two things are doing the heavy lifting:

  • FOR UPDATE SKIP LOCKED — each pod row-locks a disjoint set of candidates and skips whatever the other pod already locked. No blocking, no overlap.
  • The lease (lease_token + lease_expires_at) — the claimer stamps a random token and an expiry. If it crashes mid-delivery, another relay reclaims the row after the lease expires (status = 'IN_PROGRESS' AND lease_expires_at < now). Crash recovery for free.

Delivery then happens outside any transaction — you never want to hold a DB lock across an HTTP call — and the terminal update is token-guarded:

UPDATE alert_outbox
   SET status = 'SENT', updated_at = :now,
       lease_token = NULL, lease_expires_at = NULL
 WHERE id = :id AND status = 'IN_PROGRESS' AND lease_token = :token;

That lease_token = :token clause is the zombie guard. If your delivery ran long, the lease expired, and another pod already reclaimed and re-sent the row, your late markSent matches zero rows — it can't clobber the new owner's state.

The bug that took two tries: crash-loops that never die

First version incremented attempts on the terminal update (markSent/markFailed). Looks fine — until a row crashes the process after being claimed but before the terminal update. Its attempts never increments, the lease eventually expires, it gets reclaimed… forever. A poison message that quietly retries for eternity.

The fix is a one-line move: increment attempts in the claim, not the completion. Now every claim burns an attempt whether or not delivery finishes, so a poison row hits maxAttempts and dead-letters (loud ERROR log, stops retrying) instead of looping.

One more gotcha: don't lock on a shared key space

To serialize a couple of operations on the same entity we reached for a Postgres advisory lock — pg_advisory_xact_lock(entityId). Then we noticed every scheduler in the codebase also uses single-argument advisory locks with their own constant IDs, all sharing one 64-bit key space. An entity id colliding with a scheduler's lock id is astronomically unlikely, but it's a latent smell. The fix is the two-argument form — pg_advisory_xact_lock(namespace, id) — which lives in a key space fully separate from every single-arg lock.

Lessons learned

  • Your database is a decent queue for exactly-once-ish, low-throughput work. Reach for a broker when the volume or fan-out actually demands it.
  • FOR UPDATE SKIP LOCKED is the multi-consumer primitive. It's how you turn a table into a work queue without a coordinator.
  • A lease turns crashes into retries — but only if the terminal write is token-guarded, or a slow-then-reclaimed row corrupts state.
  • Count attempts at claim time. Otherwise "at least once" quietly becomes "forever."

Do you use a DB-backed outbox, or go straight to a broker? Where's your cutover point? Would love to compare notes.

Building jo4.io — a URL shortener with analytics for developers who'd rather not run Kafka for three messages a minute.