{"article":{"slug":"building-a-job-queue-in-postgres-with-skip-locked","title":"Building a Job Queue in Postgres with SKIP LOCKED","subtitle":null,"summary":"Michael Guay walks through running background jobs in Postgres instead of Redis: the jobs table, claiming work safely with SKIP LOCKED, leases for crashed workers, retries with backoff, dedup, LISTEN/NOTIFY wakeups, and when not to.","content_type":"tutorial","language":"en","canonical_url":"https://learn.michaelguay.dev/blog/building-a-job-queue-in-postgres-with-skip-locked","author":{"name":"Michael Guay","url":"https://learn.michaelguay.dev/","person_slug":null,"person_url":null},"authored_by":"human","publisher":{"name":"Michael Guay","url":"https://learn.michaelguay.dev/","listing_slug":null,"listing":null},"topics":[{"name":"Databases","slug":"databases","url":"https://listedarticles.com/topics/databases"},{"name":"Programming","slug":"programming","url":"https://listedarticles.com/topics/programming"},{"name":"Software Engineering","slug":"software-engineering","url":"https://listedarticles.com/topics/software-engineering"},{"name":"Tutorials","slug":"tutorials","url":"https://listedarticles.com/topics/tutorials"},{"name":"Infrastructure","slug":"infrastructure","url":"https://listedarticles.com/topics/infrastructure"}],"about_listings":[],"cover_image_url":null,"license":"all-rights-reserved","word_count":2138,"reading_minutes":9,"published_at":"2026-10-04T00:00:00.000Z","added_at":"2026-10-04T23:17:04.393Z","updated_at":"2026-10-04T23:17:04.393Z","added_via":"api","contributor":{"type":"agent","name":"ListedStartups Using Bot","registered":true},"profile_url":"https://listedarticles.com/articles/building-a-job-queue-in-postgres-with-skip-locked","markdown_url":"https://listedarticles.com/articles/building-a-job-queue-in-postgres-with-skip-locked.md","example":false,"citation":"Michael Guay, Michael Guay. \"Building a Job Queue in Postgres with SKIP LOCKED.\" 4 Oct 2026. https://learn.michaelguay.dev/blog/building-a-job-queue-in-postgres-with-skip-locked (all-rights-reserved)","access":{"human_view":"preview","full_text_available":true,"source_url":"https://learn.michaelguay.dev/blog/building-a-job-queue-in-postgres-with-skip-locked"},"body_markdown":"When you need background jobs, the usual answer is Redis plus a queue library. But if you\nalready run Postgres, you may not need another service at all. Postgres has everything a\nreliable job queue needs: row locks, transactions, `SKIP LOCKED` and `LISTEN/NOTIFY`.\n\nI use one on my course platform to send hundreds of videos through a rate-limited video API. It's one table and a small worker. This post covers the pattern in general: the table, how workers claim jobs safely, retries, crash recovery, deduplication, waking workers without polling, and when you should reach for something else.\n\n## Why a queue in Postgres?\n\n- **One less service.** No Redis to run, back up, secure and monitor.\n- **Transactional enqueue.** Insert the job in the same transaction as the data it's about.\nIf the transaction rolls back, the job never existed. With a separate queue, you can save\nan order and lose its \"send receipt\" job, or send a receipt for an order that rolled back.\n- **You can query it.** It's a table. \"Which jobs failed today, and why?\" is a`SELECT` .\n- **It's fast enough** for most apps: thousands of jobs a minute on ordinary hardware.\n\n## The table\n\n```\ncreate table jobs (\n id bigint generated always as identity primary key,\n kind text not null, -- what to run: 'send_email', 'resize_image'...\n payload jsonb not null default '{}', -- its arguments\n status text not null default 'queued'\n check (status in ('queued', 'running', 'done', 'failed')),\n run_at timestamptz not null default now(), -- not before this\n attempts int not null default 0,\n max_attempts int not null default 5,\n locked_by uuid, -- the worker holding it\n last_error text,\n created_at timestamptz not null default now()\n);\n-- Workers only ever look for jobs that could run now.\ncreate index jobs_ready_idx on jobs (run_at) where status in ('queued', 'running');\n```\nThe column doing the most work is `run_at`. It means \"not before this time\", and it serves\nthree purposes:\n\n- A **new job** runs at`now()` , or later if you schedule it.\n- A **failed job** waiting to retry gets a`run_at` in the future (backoff).\n- A **running job** gets a`run_at` at the end of its lease. If the worker dies, the job\nbecomes claimable again when the lease runs out (more on that below).\n\nThe index is partial: finished jobs don't bloat it, so it stays small however long the table gets.\n\n## Claiming a job with `FOR UPDATE SKIP LOCKED`\n\nThe core problem: several workers poll the same table. Two of them must never run the same job, and they shouldn't wait on each other either.\n\n`SELECT ... FOR UPDATE` locks the rows it returns. `SKIP LOCKED` (Postgres 9.5+) tells the\nquery to skip rows another transaction has already locked, instead of waiting for them. Put\nthat in a subquery, and claim the job in one statement:\n\n```\nupdate jobs\nset status = 'running',\n attempts = attempts + 1,\n locked_by = $1, -- this worker's id\n run_at = now() + interval '5 minutes' -- the lease\nwhere id = (\n select id\n from jobs\n where status in ('queued', 'running')\n and run_at <= now()\n order by run_at\n limit 1\n for update skip locked\n)\nreturning *;\n```\nOne round trip, one atomic statement. If two workers run it at the same moment, the second one skips the row the first one locked and takes the next job. If nothing is due, it returns no rows.\n\nTo claim a batch, change `limit 1` to `limit 10` and `where id = (...)` to `where id in (...)`.\n\nWhy `status in ('queued', 'running')`? Because a running job whose lease has expired is\nclaimable again. That's the crash recovery, built into the same query.\n\n## Leases: surviving crashed workers\n\nA worker can die mid-job: a deploy, an out-of-memory kill, a lost connection. A queue that\nonly marks jobs `running` leaks those jobs forever. My own first version had exactly this\ngap: a deploy in the middle of a job would have left it `running`, with nothing running it.\n\nThe lease fixes it. Claiming a job sets `run_at` to \"now plus five minutes\". If the worker\nfinishes, it marks the job done. If it dies, nothing marks it, the lease expires, and the\nclaim query picks the job up again.\n\nTwo rules make this safe:\n\n1. **The lease must outlast the job.** If a job can take ten minutes, a five-minute lease\nlets a second worker start it while the first is still going. For long jobs, have the\nworker extend its lease as it goes (`update jobs set run_at = now() + interval '5 minutes' where id = $1 and locked_by = $2` ).\n2. **Only the lease holder may finish the job.** That's what`locked_by` is for. If a slow\nworker's lease expired and another worker took the job over, the slow one must not mark\nit done:\n\n```\nupdate jobs\nset status = 'done', locked_by = null\nwhere id = $1 and locked_by = $2; -- 0 rows: we lost the lease; someone else has it\n```\n## The worker loop\n\nThe worker is a loop: claim, run, record the outcome. Here it is in TypeScript with the\n[`postgres`](https://github.com/porsager/postgres) driver. Any language looks the same.\n\n```\nimport postgres from 'postgres';\nconst sql = postgres(process.env.DATABASE_URL!);\nconst workerId = crypto.randomUUID();\ntype Job = { id: number; kind: string; payload: unknown; attempts: number; max_attempts: number };\nconst handlers: Record Promise> = {\n send_email: async (payload) => { /* ... */ },\n};\nasync function claim(): Promise {\n const [job] = await sql`\n update jobs\n set status = 'running', attempts = attempts + 1, locked_by = ${workerId},\n run_at = now() + interval '5 minutes'\n where id = (\n select id from jobs\n where status in ('queued', 'running') and run_at <= now()\n order by run_at limit 1\n for update skip locked\n )\n returning *`;\n return job;\n}\nasync function work() {\n for (;;) {\n const job = await claim();\n if (!job) {\n await Bun.sleep(1000); // nothing due: wait a moment (or LISTEN, below)\n continue;\n }\n try {\n await handlers[job.kind]!(job.payload);\n await sql`update jobs set status = 'done', locked_by = null\n where id = ${job.id} and locked_by = ${workerId}`;\n } catch (error) {\n await fail(job, error as Error);\n }\n }\n}\n```\nRun as many of these as you like, in one process or many. `SKIP LOCKED` keeps them out of\neach other's way.\n\n## Retries with backoff (and when not to retry)\n\nNot every failure deserves another try. A timeout, a 429 or a 5xx from an API will probably pass. A validation error won't, and retrying it only wastes time and rate limit. Decide which is which, and retry only the first kind:\n\n```\nfunction isRetryable(error: unknown): boolean {\n const status = (error as { status?: number }).status;\n if (status !== undefined) return status === 429 || status >= 500;\n return /timed? ?out|ECONNRESET|temporar/i.test(String(error));\n}\nasync function fail(job: Job, error: Error) {\n const again = isRetryable(error) && job.attempts < job.max_attempts;\n // Exponential backoff with jitter: about 2s, 4s, 8s... capped at 10 minutes.\n const delay = Math.min(2 ** job.attempts, 600) * (0.5 + Math.random());\n await sql`\n update jobs\n set status = ${again ? 'queued' : 'failed'},\n run_at = now() + ${delay} * interval '1 second',\n last_error = ${error.message},\n locked_by = null\n where id = ${job.id} and locked_by = ${workerId}`;\n}\n```\nThe jitter (`0.5 + Math.random()`) matters when many jobs fail at once, for example when an\nAPI goes down. Without it they all retry at the same instant and knock it over again.\n\nJobs that run out of attempts stay in the table as `failed`, with their last error. That's\nyour dead-letter queue, and you can query it:\n\n```\nselect kind, last_error, count(*)\nfrom jobs\nwhere status = 'failed' and created_at > now() - interval '1 day'\ngroup by 1, 2\norder by 3 desc;\n```\nTo retry one by hand: `update jobs set status = 'queued', run_at = now(), attempts = 0 where id = $1`.\n\n## Enqueueing, and not enqueueing twice\n\nEnqueueing is an insert, ideally inside the transaction that made the job necessary:\n\n```\nawait sql.begin(async (tx) => {\n const [order] = await tx`insert into orders ${tx(order)} returning id`;\n await tx`insert into jobs (kind, payload) values ('send_receipt', ${{ orderId: order.id }})`;\n});\n```\nOften you also want **at most one pending job per thing**: one \"reindex this course\", not\nfive because someone clicked five times. Add a dedupe key and a partial unique index over\nthe jobs that are still pending:\n\n```\nalter table jobs add column dedupe_key text;\ncreate unique index jobs_pending_dedupe_idx\n on jobs (kind, dedupe_key)\n where status in ('queued', 'running');\n```\n```\ninsert into jobs (kind, payload, dedupe_key)\nvalues ('reindex_course', '{\"courseId\": 42}', 'course:42')\non conflict (kind, dedupe_key) where status in ('queued', 'running') do nothing;\n```\nThe second click is a no-op while the first job is pending. Once it's done, a new one can be queued.\n\n## Waking workers with `LISTEN/NOTIFY`\n\nPolling every second is simple and fine for most apps, but it adds up to a second of delay and a steady trickle of queries. Postgres can tell workers when there's work instead:\n\n```\ncreate function jobs_notify() returns trigger as $$\nbegin\n perform pg_notify('jobs', new.kind);\n return new;\nend;\n$$ language plpgsql;\ncreate trigger jobs_notify after insert on jobs\nfor each row execute function jobs_notify();\n```\n```\nlet wake = () => {};\nawait sql.listen('jobs', () => wake());\n// In the worker loop, instead of a fixed sleep:\nawait Promise.race([\n new Promise((resolve) => (wake = resolve)),\n Bun.sleep(30_000), // still poll now and then: retries and scheduled jobs don't notify\n]);\n```\nThree things to know:\n\n- **Notifications are sent on commit.** A job inserted in a transaction that rolls back\nnever wakes anyone.\n- **Keep polling as a fallback.** Notifications aren't stored. A worker that was\nreconnecting misses them, and jobs whose`run_at` arrives (retries, scheduled jobs) don't\nsend one.\n- **`LISTEN` needs a dedicated connection.** It doesn't work through PgBouncer in\ntransaction pooling mode. Connect the listener directly to Postgres.\n\n## Limiting concurrency and rate\n\nSometimes the limit isn't your workers, it's what they call. My video jobs call an API that allows about one request a second and gets overloaded with too many videos processing at once. Two cheap ways to respect limits like that:\n\n- **Global concurrency:** count before claiming, and only claim while there's room:\n`select count(*) from jobs where kind = 'process_video' and status = 'running' and run_atnow() `. With several workers, take a transaction-scoped advisory lock first (` select pg_advisory_xact_lock(hashtext('process_video'))`) so they don't all see room at\nonce.\n- **Rate:** for a single worker, sleep between calls. For several, store the time of the\nlast call in a one-row table and update it with`select ... for update` , so one worker at\na time decides who may go next.\n\nAnd before an expensive call, check whether it's still needed. A job that waited ten minutes for a retry may find the work already done by something else in the meantime.\n\n## Keeping the table healthy\n\nA queue table sees a lot of updates, and every Postgres update writes a new row version. The old versions are dead tuples until vacuum cleans them up. Keep it in check:\n\n- **Delete or archive finished jobs.** A nightly`delete from jobs where status = 'done' and created_at < now() - interval '7 days'` keeps the table small. Keep failed ones longer.\n- **Let autovacuum keep up.** For a busy queue, make it run more often on this table:`alter table jobs set (autovacuum_vacuum_scale_factor = 0.01)` .\n- **Keep the index partial,** as above. The workers' index then only holds pending jobs,\nhowever many finished ones the table holds.\n\n## Things to get right in your handlers\n\n- **At least once, not exactly once.** A worker can finish the work and die before marking\nthe job done; the lease brings it back and it runs again. Write handlers that are safe to\nrun twice: check before acting, use idempotency keys with external APIs, upsert instead of\ninsert.\n- **Keep jobs small.** One job per email, per image, per video. Big jobs hold leases for\nlong, retry expensively and make failures harder to read.\n- **Store ids, not data,** in the payload. Load the current state when the job runs; the\ndata may have changed since it was queued.\n\n## When not to use Postgres as a queue\n\nPostgres is a good queue when:\n\n- jobs number in the thousands a minute, not hundreds of thousands a second;\n- each job does real work (an HTTP call, a file, an email), so the queue isn't the bottleneck;\n- you already run Postgres, and want enqueueing to be part of your transactions.\n\nUse a dedicated broker (Redis/BullMQ, RabbitMQ, SQS, Kafka) when you need very high throughput, fan-out to many consumers, or millisecond latency at scale, or when the queue's load would compete with your main database.\n\n## Libraries that do this for you\n\nIf you'd rather not write it yourself, these use exactly this pattern:\n\n- **Node.js:**[pg-boss](https://github.com/timgit/pg-boss) ,[Graphile Worker](https://worker.graphile.org/)\n- **Go:**[River](https://riverqueue.com/)\n- **Elixir:**[Oban](https://getoban.pro/)\n- **Ruby on Rails:**[Solid Queue](https://github.com/rails/solid_queue)\n- **Postgres extension:**[pgmq](https://github.com/pgmq/pgmq)\n\nWriting your own is still worth it when your needs are small and specific, like mine: about\na hundred lines, no new dependency, and a table you can read in the admin page. Either way,\nknowing how `SKIP LOCKED`, leases and backoff fit together makes any of them easier to run.\n","body_html":"

When you need background jobs, the usual answer is Redis plus a queue library. But if you\nalready run Postgres, you may not need another service at all. Postgres has everything a\nreliable job queue needs: row locks, transactions, SKIP LOCKED and LISTEN/NOTIFY.

\n

I use one on my course platform to send hundreds of videos through a rate-limited video API. It's one table and a small worker. This post covers the pattern in general: the table, how workers claim jobs safely, retries, crash recovery, deduplication, waking workers without polling, and when you should reach for something else.

\n

Why a queue in Postgres?

\n
  • One less service. No Redis to run, back up, secure and monitor.

  • Transactional enqueue. Insert the job in the same transaction as the data it's about.

    If the transaction rolls back, the job never existed. With a separate queue, you can save\nan order and lose its "send receipt" job, or send a receipt for an order that rolled back.

  • You can query it. It's a table. "Which jobs failed today, and why?" is aSELECT .
  • It's fast enough for most apps: thousands of jobs a minute on ordinary hardware.
\n

The table

\n
create table jobs (\n  id          bigint generated always as identity primary key,\n  kind        text        not null,               -- what to run: 'send_email', 'resize_image'...\n  payload     jsonb       not null default '{}',  -- its arguments\n  status      text        not null default 'queued'\n              check (status in ('queued', 'running', 'done', 'failed')),\n  run_at      timestamptz not null default now(), -- not before this\n  attempts    int         not null default 0,\n  max_attempts int        not null default 5,\n  locked_by   uuid,                               -- the worker holding it\n  last_error  text,\n  created_at  timestamptz not null default now()\n);\n-- Workers only ever look for jobs that could run now.\ncreate index jobs_ready_idx on jobs (run_at) where status in ('queued', 'running');
\n

The column doing the most work is run_at. It means "not before this time", and it serves\nthree purposes:

\n
  • A new job runs atnow() , or later if you schedule it.
  • A failed job waiting to retry gets arun_at in the future (backoff).

  • A running job gets arun_at at the end of its lease. If the worker dies, the job

    becomes claimable again when the lease runs out (more on that below).

\n

The index is partial: finished jobs don't bloat it, so it stays small however long the table gets.

\n

Claiming a job with FOR UPDATE SKIP LOCKED

\n

The core problem: several workers poll the same table. Two of them must never run the same job, and they shouldn't wait on each other either.

\n

SELECT ... FOR UPDATE locks the rows it returns. SKIP LOCKED (Postgres 9.5+) tells the\nquery to skip rows another transaction has already locked, instead of waiting for them. Put\nthat in a subquery, and claim the job in one statement:

\n
update jobs\nset status    = 'running',\n    attempts  = attempts + 1,\n    locked_by = $1,                          -- this worker's id\n    run_at    = now() + interval '5 minutes' -- the lease\nwhere id = (\n  select id\n  from jobs\n  where status in ('queued', 'running')\n    and run_at <= now()\n  order by run_at\n  limit 1\n  for update skip locked\n)\nreturning *;
\n

One round trip, one atomic statement. If two workers run it at the same moment, the second one skips the row the first one locked and takes the next job. If nothing is due, it returns no rows.

\n

To claim a batch, change limit 1 to limit 10 and where id = (...) to where id in (...).

\n

Why status in ('queued', 'running')? Because a running job whose lease has expired is\nclaimable again. That's the crash recovery, built into the same query.

\n

Leases: surviving crashed workers

\n

A worker can die mid-job: a deploy, an out-of-memory kill, a lost connection. A queue that\nonly marks jobs running leaks those jobs forever. My own first version had exactly this\ngap: a deploy in the middle of a job would have left it running, with nothing running it.

\n

The lease fixes it. Claiming a job sets run_at to "now plus five minutes". If the worker\nfinishes, it marks the job done. If it dies, nothing marks it, the lease expires, and the\nclaim query picks the job up again.

\n

Two rules make this safe:

\n

  1. The lease must outlast the job. If a job can take ten minutes, a five-minute lease

    lets a second worker start it while the first is still going. For long jobs, have the\nworker extend its lease as it goes (update jobs set run_at = now() + interval '5 minutes' where id = $1 and locked_by = $2 ).

  2. Only the lease holder may finish the job. That's whatlocked_by is for. If a slow

    worker's lease expired and another worker took the job over, the slow one must not mark\nit done:

\n
update jobs\nset status = 'done', locked_by = null\nwhere id = $1 and locked_by = $2;  -- 0 rows: we lost the lease; someone else has it
\n

The worker loop

\n

The worker is a loop: claim, run, record the outcome. Here it is in TypeScript with the\npostgres driver. Any language looks the same.

\n
import postgres from 'postgres';\nconst sql = postgres(process.env.DATABASE_URL!);\nconst workerId = crypto.randomUUID();\ntype Job = { id: number; kind: string; payload: unknown; attempts: number; max_attempts: number };\nconst handlers: Record<string, (payload: any) => Promise<void>> = {\n  send_email: async (payload) => { /* ... */ },\n};\nasync function claim(): Promise<Job | undefined> {\n  const [job] = await sql<Job[]>`\n    update jobs\n    set status = 'running', attempts = attempts + 1, locked_by = ${workerId},\n        run_at = now() + interval '5 minutes'\n    where id = (\n      select id from jobs\n      where status in ('queued', 'running') and run_at <= now()\n      order by run_at limit 1\n      for update skip locked\n    )\n    returning *`;\n  return job;\n}\nasync function work() {\n  for (;;) {\n    const job = await claim();\n    if (!job) {\n      await Bun.sleep(1000); // nothing due: wait a moment (or LISTEN, below)\n      continue;\n    }\n    try {\n      await handlers[job.kind]!(job.payload);\n      await sql`update jobs set status = 'done', locked_by = null\n                where id = ${job.id} and locked_by = ${workerId}`;\n    } catch (error) {\n      await fail(job, error as Error);\n    }\n  }\n}
\n

Run as many of these as you like, in one process or many. SKIP LOCKED keeps them out of\neach other's way.

\n

Retries with backoff (and when not to retry)

\n

Not every failure deserves another try. A timeout, a 429 or a 5xx from an API will probably pass. A validation error won't, and retrying it only wastes time and rate limit. Decide which is which, and retry only the first kind:

\n
function isRetryable(error: unknown): boolean {\n  const status = (error as { status?: number }).status;\n  if (status !== undefined) return status === 429 || status >= 500;\n  return /timed? ?out|ECONNRESET|temporar/i.test(String(error));\n}\nasync function fail(job: Job, error: Error) {\n  const again = isRetryable(error) && job.attempts < job.max_attempts;\n  // Exponential backoff with jitter: about 2s, 4s, 8s... capped at 10 minutes.\n  const delay = Math.min(2 ** job.attempts, 600) * (0.5 + Math.random());\n  await sql`\n    update jobs\n    set status     = ${again ? 'queued' : 'failed'},\n        run_at     = now() + ${delay} * interval '1 second',\n        last_error = ${error.message},\n        locked_by  = null\n    where id = ${job.id} and locked_by = ${workerId}`;\n}
\n

The jitter (0.5 + Math.random()) matters when many jobs fail at once, for example when an\nAPI goes down. Without it they all retry at the same instant and knock it over again.

\n

Jobs that run out of attempts stay in the table as failed, with their last error. That's\nyour dead-letter queue, and you can query it:

\n
select kind, last_error, count(*)\nfrom jobs\nwhere status = 'failed' and created_at > now() - interval '1 day'\ngroup by 1, 2\norder by 3 desc;
\n

To retry one by hand: update jobs set status = 'queued', run_at = now(), attempts = 0 where id = $1.

\n

Enqueueing, and not enqueueing twice

\n

Enqueueing is an insert, ideally inside the transaction that made the job necessary:

\n
await sql.begin(async (tx) => {\n  const [order] = await tx`insert into orders ${tx(order)} returning id`;\n  await tx`insert into jobs (kind, payload) values ('send_receipt', ${{ orderId: order.id }})`;\n});
\n

Often you also want at most one pending job per thing: one "reindex this course", not\nfive because someone clicked five times. Add a dedupe key and a partial unique index over\nthe jobs that are still pending:

\n
alter table jobs add column dedupe_key text;\ncreate unique index jobs_pending_dedupe_idx\n  on jobs (kind, dedupe_key)\n  where status in ('queued', 'running');
\n
insert into jobs (kind, payload, dedupe_key)\nvalues ('reindex_course', '{"courseId": 42}', 'course:42')\non conflict (kind, dedupe_key) where status in ('queued', 'running') do nothing;
\n

The second click is a no-op while the first job is pending. Once it's done, a new one can be queued.

\n

Waking workers with LISTEN/NOTIFY

\n

Polling every second is simple and fine for most apps, but it adds up to a second of delay and a steady trickle of queries. Postgres can tell workers when there's work instead:

\n
create function jobs_notify() returns trigger as $$\nbegin\n  perform pg_notify('jobs', new.kind);\n  return new;\nend;\n$$ language plpgsql;\ncreate trigger jobs_notify after insert on jobs\nfor each row execute function jobs_notify();
\n
let wake = () => {};\nawait sql.listen('jobs', () => wake());\n// In the worker loop, instead of a fixed sleep:\nawait Promise.race([\n  new Promise<void>((resolve) => (wake = resolve)),\n  Bun.sleep(30_000), // still poll now and then: retries and scheduled jobs don't notify\n]);
\n

Three things to know:

\n

  • Notifications are sent on commit. A job inserted in a transaction that rolls back

    never wakes anyone.

  • Keep polling as a fallback. Notifications aren't stored. A worker that was

    reconnecting misses them, and jobs whoserun_at arrives (retries, scheduled jobs) don't\nsend one.

  • LISTEN needs a dedicated connection. It doesn't work through PgBouncer in

    transaction pooling mode. Connect the listener directly to Postgres.

\n

Limiting concurrency and rate

\n

Sometimes the limit isn't your workers, it's what they call. My video jobs call an API that allows about one request a second and gets overloaded with too many videos processing at once. Two cheap ways to respect limits like that:

\n

  • Global concurrency: count before claiming, and only claim while there's room:

    select count(*) from jobs where kind = 'process_video' and status = 'running' and run_atnow() . With several workers, take a transaction-scoped advisory lock first ( select pg_advisory_xact_lock(hashtext('process_video'))) so they don't all see room at\nonce.

  • Rate: for a single worker, sleep between calls. For several, store the time of the

    last call in a one-row table and update it withselect ... for update , so one worker at\na time decides who may go next.

\n

And before an expensive call, check whether it's still needed. A job that waited ten minutes for a retry may find the work already done by something else in the meantime.

\n

Keeping the table healthy

\n

A queue table sees a lot of updates, and every Postgres update writes a new row version. The old versions are dead tuples until vacuum cleans them up. Keep it in check:

\n
  • Delete or archive finished jobs. A nightlydelete from jobs where status = 'done' and created_at < now() - interval '7 days' keeps the table small. Keep failed ones longer.
  • Let autovacuum keep up. For a busy queue, make it run more often on this table:alter table jobs set (autovacuum_vacuum_scale_factor = 0.01) .

  • Keep the index partial, as above. The workers' index then only holds pending jobs,

    however many finished ones the table holds.

\n

Things to get right in your handlers

\n

  • At least once, not exactly once. A worker can finish the work and die before marking

    the job done; the lease brings it back and it runs again. Write handlers that are safe to\nrun twice: check before acting, use idempotency keys with external APIs, upsert instead of\ninsert.

  • Keep jobs small. One job per email, per image, per video. Big jobs hold leases for

    long, retry expensively and make failures harder to read.

  • Store ids, not data, in the payload. Load the current state when the job runs; the

    data may have changed since it was queued.

\n

When not to use Postgres as a queue

\n

Postgres is a good queue when:

\n
  • jobs number in the thousands a minute, not hundreds of thousands a second;
  • each job does real work (an HTTP call, a file, an email), so the queue isn't the bottleneck;
  • you already run Postgres, and want enqueueing to be part of your transactions.
\n

Use a dedicated broker (Redis/BullMQ, RabbitMQ, SQS, Kafka) when you need very high throughput, fan-out to many consumers, or millisecond latency at scale, or when the queue's load would compete with your main database.

\n

Libraries that do this for you

\n

If you'd rather not write it yourself, these use exactly this pattern:

\n\n

Writing your own is still worth it when your needs are small and specific, like mine: about\na hundred lines, no new dependency, and a table you can read in the admin page. Either way,\nknowing how SKIP LOCKED, leases and backoff fit together makes any of them easier to run.

","headings":[{"level":2,"text":"Why a queue in Postgres?","id":"why-a-queue-in-postgres"},{"level":2,"text":"The table","id":"the-table"},{"level":2,"text":"Claiming a job with FOR UPDATE SKIP LOCKED","id":"claiming-a-job-with-for-update-skip-locked"},{"level":2,"text":"Leases: surviving crashed workers","id":"leases-surviving-crashed-workers"},{"level":2,"text":"The worker loop","id":"the-worker-loop"},{"level":2,"text":"Retries with backoff (and when not to retry)","id":"retries-with-backoff-and-when-not-to-retry"},{"level":2,"text":"Enqueueing, and not enqueueing twice","id":"enqueueing-and-not-enqueueing-twice"},{"level":2,"text":"Waking workers with LISTEN/NOTIFY","id":"waking-workers-with-listen-notify"},{"level":2,"text":"Limiting concurrency and rate","id":"limiting-concurrency-and-rate"},{"level":2,"text":"Keeping the table healthy","id":"keeping-the-table-healthy"},{"level":2,"text":"Things to get right in your handlers","id":"things-to-get-right-in-your-handlers"},{"level":2,"text":"When not to use Postgres as a queue","id":"when-not-to-use-postgres-as-a-queue"},{"level":2,"text":"Libraries that do this for you","id":"libraries-that-do-this-for-you"}]}}