Using Postgres as a Job Queue (SKIP LOCKED and pgmq)

Postgres works well as a job queue. The core technique is SELECT ... FOR UPDATE SKIP LOCKED, which lets many workers each grab different rows from a jobs table without blocking each other. If you would rather not write the queue logic yourself, the pgmq extension gives you an SQS-style queue in Postgres with send, read with a visibility timeout, archive and delete. Both keep jobs in the same transactional database as your data, so a job is enqueued if and only if the transaction that created it commits.

This article builds the queue three ways, from simplest to most complete, then covers retries, dead letters, workers, and when you should use a dedicated broker instead.

#Why a Postgres queue at all

The usual alternative is Redis, RabbitMQ, SQS or Kafka next to your database. That works, but introduces one subtle bug class: the dual write. Your app commits an order in Postgres, then publishes "send confirmation e-mail" to a broker. If the process dies between the two, the order exists and the e-mail never goes out. Or the publish succeeds and the transaction rolls back, and you e-mail a customer about an order that does not exist.

With the queue in Postgres, the job insert is part of the same transaction:

begin;
insert into public.orders (id, customer_id, total) values ($1, $2, $3);
insert into public.jobs (kind, payload) values ('send_receipt', jsonb_build_object('order_id', $1));
commit;

Either both rows exist or neither does. For most apps that guarantee is worth more than raw broker throughput.

#Version 1: a jobs table with SKIP LOCKED

create table public.jobs (
  id bigint generated always as identity primary key,
  kind text not null,
  payload jsonb not null default '{}',
  status text not null default 'pending'
    check (status in ('pending', 'running', 'done', 'failed')),
  run_at timestamptz not null default now(),
  attempts int not null default 0,
  max_attempts int not null default 5,
  locked_at timestamptz,
  last_error text,
  created_at timestamptz not null default now()
);

-- workers only look at pending jobs that are due
create index jobs_pending_idx on public.jobs (run_at) where status = 'pending';

alter table public.jobs enable row level security;  -- no policies: hidden from the public API

#Claim a batch

with next as (
  select id
  from public.jobs
  where status = 'pending' and run_at <= now()
  order by run_at
  limit 10
  for update skip locked
)
update public.jobs j
set status = 'running', locked_at = now(), attempts = j.attempts + 1
from next
where j.id = next.id
returning j.id, j.kind, j.payload, j.attempts;

What happens here:

1. The inner select finds due jobs and tries to lock them.
2. skip locked skips any row another worker has already locked instead of waiting for it.
3. The update marks the claimed rows as running and returns them.

Ten workers can run this at the same moment and each gets a different set of jobs. Without skip locked, they would queue up behind the first worker's locks. The PostgreSQL docs describe SKIP LOCKED as giving an inconsistent view of the data, unsuitable for general queries but exactly right for queue-like tables with multiple consumers (SELECT locking clause).

#Finish or fail

-- success
update public.jobs set status = 'done' where id = $1;

-- failure: retry with exponential backoff, or give up
update public.jobs
set status = case when attempts >= max_attempts then 'failed' else 'pending' end,
    run_at = now() + (interval '1 second' * power(2, attempts)),
    last_error = $2,
    locked_at = null
where id = $1;

#Recover from crashed workers

If a worker dies after claiming jobs, those rows stay running forever. A reaper puts them back:

update public.jobs
set status = 'pending', locked_at = null
where status = 'running' and locked_at < now() - interval '10 minutes';

Schedule it with pg_cron every few minutes (see Postgres cron jobs). Pick a timeout longer than your slowest job.

#Keep the table small

Finished jobs pile up. Delete or archive them regularly, because a jobs table with millions of dead rows makes every claim query slower:

delete from public.jobs where status = 'done' and created_at < now() - interval '7 days';

#Version 2: claim and finish in one transaction

Version 1 commits the claim, then does the work, then commits again. An alternative holds the row lock for the whole job:

begin;
select id, kind, payload
from public.jobs
where status = 'pending' and run_at <= now()
order by run_at
limit 1
for update skip locked;

-- ... do the work in your app ...

update public.jobs set status = 'done' where id = $1;
commit;

If the worker crashes, the transaction rolls back, the lock is released and the job is available again. No reaper needed. The trade-off is that each in-flight job holds a database connection and an open transaction, so it suits short jobs (well under a second or two) and a small number of workers. Long transactions also hold back vacuum. For anything slow, use Version 1 or pgmq.

#Version 3: pgmq

pgmq packages the pattern as an extension with an API modeled on AWS SQS (pgmq on GitHub). Each queue is a table; reading a message hides it for a visibility timeout instead of locking it.

create extension if not exists pgmq;

select pgmq.create('emails');

-- producer
select pgmq.send('emails', '{"to": "[email protected]", "template": "welcome"}');

-- delayed by 60 seconds
select pgmq.send('emails', '{"to": "[email protected]", "template": "tips"}', 60);

-- consumer: read up to 5 messages, hidden from other readers for 30 seconds
select msg_id, read_ct, message from pgmq.read('emails', 30, 5);

-- done: archive (kept for history) or delete
select pgmq.archive('emails', 1);
select pgmq.delete('emails', 2);

-- queue depth and age
select * from pgmq.metrics('emails');

Semantics to know:

• pgmq.read(queue, vt, qty) hides messages for vt seconds. If they are not deleted or archived in time, they become visible again. That is your crash recovery.
• read_ct counts how often a message has been read, which you use for retry limits.
• pgmq.pop(queue) reads and deletes in one step, for at-most-once handling.
• pgmq.create_unlogged(queue) is faster but loses messages on a crash.
• Archived messages are kept in a separate archive table per queue.

#Dead letters with pgmq

Use read_ct to stop retrying poison messages:

select pgmq.create('emails_dead');

create or replace function public.process_email_queue()
returns int language plpgsql as $$
declare
  m record;
  handled int := 0;
begin
  for m in select * from pgmq.read('emails', 60, 20) loop
    if m.read_ct > 5 then
      perform pgmq.send('emails_dead', m.message);
      perform pgmq.archive('emails', m.msg_id);
      continue;
    end if;

    -- hand the message to something that sends mail, e.g. an outbox table or pg_net
    insert into public.email_outbox (payload) values (m.message);
    perform pgmq.archive('emails', m.msg_id);
    handled := handled + 1;
  end loop;
  return handled;
end $$;

#Workers: who reads the queue?

You have three options:

1. An app worker (Node, Python, Go) that loops: claim, process, finish, sleep briefly when empty. Use a session-mode or direct connection for long-running workers.
2. pg_cron calling a SQL function every few seconds. Good when the work itself is SQL or an HTTP call through pg_net.
3. A serverless function triggered on a schedule that drains a batch per call.

Polling every second or two is fine for most apps. If you want lower latency, add LISTEN/NOTIFY: producers notify jobs_channel after insert, and the worker wakes up on the notification instead of waiting for the next poll. Keep polling as a fallback, because notifications are not delivered to a worker that is disconnected.

#Choosing between the three

#When not to use Postgres as a queue

Be honest about the limits:

• Very high throughput. Thousands of messages per second sustained, with many consumers, is what Kafka, RabbitMQ or SQS are designed for. Postgres can do a lot, but every message is a row write with WAL and vacuum cost.
• Fan-out and streaming. Many independent consumers replaying the same event stream is a log (Kafka), not a queue.
• Cross-system messaging. If producers and consumers do not share your database, a broker is the natural boundary.

For a typical SaaS or mobile backend sending e-mails, generating PDFs, syncing to Stripe and resizing images, a Postgres queue keeps the stack small and the data consistent.

#Queues on StitchDB

StitchDB has pgmq as a platform integration (Queues docs):

1. In Studio, enable pgmq under Database, then Extensions. Your project role owns the queues.
2. Under Integrations, then Queues, create logged or unlogged queues, send messages with a delay, read, archive, delete and see queue metrics.
3. Process a queue on a schedule with Cron jobs (down to every few seconds), or expose a public function that reads it and call it through RPC.

From an app or Edge Function, call a wrapper function through the SDK:

const { data: handled, error } = await stitchdb.rpc('process_email_queue')

A few connection details matter for workers (connection pooling): LISTEN/NOTIFY and session advisory locks need session mode on port 5432; in transaction mode (port 6543) session state does not carry over between transactions. The SKIP LOCKED claim query itself works in either mode because it is a single transaction.

For multi-step jobs with retries, sleeps and run history, StitchDB also has Background jobs built on Edge Functions, which you may prefer over writing a worker loop.

#FAQ

#Is Postgres good for a job queue?

Yes, for most app workloads. FOR UPDATE SKIP LOCKED lets many workers pull jobs concurrently, and enqueueing in the same transaction as your data removes dual-write bugs. Very high throughput or streaming workloads are better served by a dedicated broker.

#What does SKIP LOCKED do in Postgres?

It makes SELECT ... FOR UPDATE skip rows that another transaction has locked, instead of waiting for them. Each worker gets rows nobody else is processing.

#What is pgmq?

An extension that implements a lightweight message queue on Postgres, with an API similar to AWS SQS: send, read with a visibility timeout, pop, archive, delete and metrics.

#How do I retry failed jobs?

In a jobs table, set the row back to pending with a later run_at and count attempts. In pgmq, simply do not delete the message; it reappears after the visibility timeout, and read_ct tells you how many times it has been tried.

#Can I use LISTEN/NOTIFY through a connection pooler?

Only in session mode. Transaction-mode pooling hands a different connection to each transaction, so a listener would miss notifications. On StitchDB, use port 5432 for listeners.

Build your queue next to your data: enable pgmq from the queues docs on a free project.