Supabase Queues in Production: Dead-Letter Queues, Retries, and Poison Messages with pgmq

Failure Modes and Fixes

Failure Mode #1: The Infinite Retry Loop

Here's the scenario the docs never mention. Enqueue a message your worker can't handle:

-- template is null; the worker will throw on this every time
select pgmq.send('email_jobs', '{"template": null}'::jsonb);

Your worker reads it, throws, and doesn't archive it. Sixty seconds later the visibility timeout expires and the message is back. The worker reads it again. Throws again. Forever.

Watch it happen:

select msg_id, read_ct, enqueued_at from pgmq.q_email_jobs;

 msg_id | read_ct |          enqueued_at
--------+---------+-------------------------------
      2 |      14 | 2026-07-07 13:51:25.886937-05
(1 row)

Fourteen deliveries, fourteen failures, and no error surfaced by pgmq. This is called a poison message, and a single one consumes capacity on every worker run indefinitely. pgmq has no max_retries setting, so it will keep re-delivering the message until you intervene.

The Fix: Build a Dead-Letter Queue with read_ct

Does pgmq have a built-in dead-letter queue? No, and this isn't a workaround I invented. It's how pgmq's own creators run it in production: check read_ct on read, and when it exceeds a threshold, move the message to a second queue instead of processing it.

First, create the DLQ. It's just another queue:

select pgmq.create('email_jobs_dlq');

Then wrap pgmq.read() in a function that enforces your retry budget. This is the centerpiece of the whole setup:

create or replace function public.read_with_dlq(
  p_queue_name text,
  p_vt integer default 60,
  p_qty integer default 5,
  p_max_read_ct integer default 5
)
returns setof pgmq.message_record
language plpgsql
security definer
set search_path = ''
as $$
declare
  msg pgmq.message_record;
begin
  for msg in select * from pgmq.read(p_queue_name, p_vt, p_qty) loop
    if msg.read_ct > p_max_read_ct then
      -- Poison message: divert to the DLQ with context, then archive the original
      perform pgmq.send(
        queue_name => p_queue_name || '_dlq',
        msg => jsonb_build_object(
          'original_msg_id', msg.msg_id,
          'read_ct', msg.read_ct,
          'enqueued_at', msg.enqueued_at,
          'dead_lettered_at', now(),
          'payload', msg.message
        )
      );
      perform pgmq.archive(p_queue_name, msg.msg_id);
    else
      return next msg;
    end if;
  end loop;
end;
$$;

-- Only the backend worker should call this
revoke execute on function public.read_with_dlq(text, integer, integer, integer)
  from public, anon, authenticated;
grant execute on function public.read_with_dlq(text, integer, integer, integer)
  to service_role;

The retry math is worth spelling out, because read_ct includes the current read. With p_max_read_ct = 5: reads 1 through 5 hand the message to your worker (five real processing attempts). On read 6, read_ct = 6 > 5, and the message is diverted without being processed. So p_max_read_ct literally means "maximum processing attempts."

I verified this by sending the same poisoned payload through a fresh cycle: calls 1 through 5 to read_with_dlq() each returned the message, and call 6 returned zero rows: the message had been diverted. Here's what landed in the DLQ (expanded display):

-[ RECORD 1 ]---------------------------------------------------------------
msg_id  | 1
message | {"payload": {"template": null}, "read_ct": 6,
          "enqueued_at": "2026-07-07T18:51:39.838618+00:00",
          "original_msg_id": 3,
          "dead_lettered_at": "2026-07-07T18:51:40.175338+00:00"}

Two design notes. I wrap the original payload in an envelope with read_ct and timestamps so that each dead message carries its own history, which makes the DLQ far easier to investigate; the record above is exactly what that looks like. And I archive() the original rather than delete() it, so the main queue's archive table remains a complete record of everything that ever passed through.

Reprocessing dead letters after you fix the underlying bug is a two-liner per message:

-- Inspect what's dead
select msg_id, message from pgmq.q_email_jobs_dlq;

-- Re-enqueue the original payload, then archive the DLQ entry
select pgmq.send('email_jobs', (message -> 'payload')::jsonb)
  from pgmq.q_email_jobs_dlq
  where msg_id = 1;
select pgmq.archive('email_jobs_dlq', 1);

Note that the re-enqueued message gets a fresh msg_id and a fresh read_ct of zero, so its retry budget resets. That matters for idempotency, which we'll hit in failure mode #3.

Failure Mode #2: The Visibility Timeout Is a Bet, and You Can Lose It Both Ways

Every read() requires a vt, and that number is a bet on how long processing takes. Bet too low and a slow job's message becomes visible again while the first worker is still processing it. A second worker picks it up, and now the same job is running twice concurrently. If the job sends an email, two emails go out. This isn't a crash (nothing errors), which makes it miserable to debug.

Bet too high and a genuinely crashed worker's message stays invisible for the full window. With vt => 3600, a job that died at second 5 waits 59 more minutes before anything retries it.

The rule of thumb: set vt to your worst realistic processing time (p99, not average) plus a small buffer. For a batch worker, remember the clock starts at read time, so a message processed last in a batch of ten needs the timeout to cover the nine jobs ahead of it.

For jobs with rare-but-legitimate long runs, don't inflate the global timeout; extend it per message while you work. pgmq.set_vt pushes a specific message's visibility further into the future:

-- "I'm still working on message 42; give me 120 more seconds"
select pgmq.set_vt('email_jobs', 42, 120);

Call it from your worker as a heartbeat during long operations, and you get short timeouts (fast crash recovery) and safe long jobs.

Failure Mode #3: "Exactly-Once" Doesn't Mean What You Think

Supabase's marketing says messages are delivered exactly once, but read the fine print: exactly once within a customizable visibility window. Across a message's lifetime, pgmq is an at-least-once system. A worker that crashes after doing the work but before archiving the message guarantees a redelivery. So does a too-short vt. So does re-enqueueing from the DLQ.

The consequence: your job handler must be idempotent, safe to run twice with the same input. For jobs that write to your own database, idempotency is often free (insert ... on conflict do update is naturally re-runnable). For jobs with external side effects (emails, payment API calls, push notifications), you need a claim check.

Postgres makes this trivially cheap:

create table public.processed_jobs (
  idempotency_key text primary key,
  processed_at timestamptz not null default now()
);

create or replace function public.claim_job(p_key text)
returns boolean
language sql
security definer
set search_path = ''
as $$
  insert into public.processed_jobs(idempotency_key)
  values (p_key)
  on conflict (idempotency_key) do nothing
  returning true;
$$;

revoke execute on function public.claim_job(text)
  from public, anon, authenticated;
grant execute on function public.claim_job(text)
  to service_role;

The returning true only fires when the insert actually happens. On a duplicate, the function returns null, which is falsy in your worker's JavaScript, so the check reads naturally: if (claimed) { doTheWork(); }.

One subtlety: put the idempotency key in the message payload at send time (something business-meaningful like email:welcome:8f3c2a10:2026-07-07), not derived from msg_id. DLQ reprocessing assigns a new msg_id, and a key based on it would happily let the side effect fire twice.

And an honest tradeoff: claim-before-work means a crash between claiming and finishing leaves that job permanently done-but-not-done. For emails, that's the right failure direction: at-most-once beats double-sending. For jobs where a miss is worse than a duplicate, claim after the work instead and treat the table purely as dedup.

Failure Mode #4: Overlapping Workers (and the Advisory-Lock Trap)

The standard Supabase architecture is pg_cron invoking an Edge Function worker every minute. What happens when a run takes longer than a minute? The next tick fires and now two workers are draining the queue concurrently.

Often, that's fine. The visibility timeout already guarantees two workers can't read the same message at the same time, so overlap just means the queue drains faster. If your jobs are idempotent (see above) and your downstream can take the parallelism, let it happen.

If you genuinely need single-flight (say the job hammers a rate-limited third-party API), the tempting answer is pg_try_advisory_lock(). Don't use it through the Data API. Advisory locks are session-scoped, and when your worker calls it via PostgREST/RPC, the "session" is a pooled connection that goes back into the pool still holding the lock. Your queue silently stops processing and nothing in any log tells you why.

The pooler-safe alternative is a lease row claimed with one atomic statement:

create table public.worker_leases (
  worker_name text primary key,
  lease_until timestamptz not null
);

create or replace function public.claim_worker_lease(
  p_worker text,
  p_seconds integer default 55
)
returns boolean
language sql
security definer
set search_path = ''
as $$
  insert into public.worker_leases(worker_name, lease_until)
  values (p_worker, now() + make_interval(secs => p_seconds))
  on conflict (worker_name) do update
  set lease_until = excluded.lease_until
  where public.worker_leases.lease_until < now()
  returning true;
$$;