記録 · Entry

Postgres as a job queue with SELECT FOR UPDATE SKIP LOCKED

You do not need a second system for background jobs. How SKIP LOCKED turns a table into a queue, and the details that decide whether it holds up.

10 min read

PostgreSQLDatabasesBackendQueuesReliability

The first time a project needs to send an email after a signup, somebody proposes adding Redis. Or RabbitMQ. Or SQS. A queue is a queue, the reasoning goes, and a database is for data.

Then you look at what the job actually contains. A user id. An order id. A reference to a row that was written a millisecond ago, in a transaction that has already committed, in the database you already run. The job's whole purpose is to do something with data that lives in Postgres, and the first thing every worker will do on picking it up is query Postgres.

Putting the queue in the same database is not a shortcut. It buys you the one property a separate broker cannot: the job and the row it refers to commit together, or not at all. Nothing is enqueued for an order that rolled back, and no order commits without its job. That is worth a great deal, and it costs one table and one query.

The query is the interesting part.

#The table

CREATE TABLE jobs (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  queue       text        NOT NULL DEFAULT 'default',
  payload     jsonb       NOT NULL,
  status      text        NOT NULL DEFAULT 'pending',
  run_at      timestamptz NOT NULL DEFAULT now(),
  attempts    int         NOT NULL DEFAULT 0,
  max_attempts int        NOT NULL DEFAULT 5,
  locked_at   timestamptz,
  locked_by   text,
  last_error  text,
  created_at  timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX jobs_ready_idx ON jobs (run_at, id) WHERE status = 'pending';

Enqueueing is an insert, and the whole point is that it goes in the same transaction as the business write:

await db.transaction(async (tx) => {
  const order = await tx.insertOrder(input)
  await tx.query(
    `INSERT INTO jobs (queue, payload) VALUES ($1, $2)`,
    ['email', { type: 'order_confirmation', orderId: order.id }]
  )
})

If the order insert fails, the job is never written. If the job insert fails, the order rolls back. There is no window between them, because there is no "between".

The partial index is not optional, and I will come back to why.

#Claiming a job without every worker fighting for the same row

The naive claim is a SELECT for the oldest pending job followed by an UPDATE to mark it running. With one worker that works. With two, both select the same row, both update it, and the job runs twice.

FOR UPDATE fixes the correctness problem by locking the selected row until the transaction ends. Now the second worker blocks on the first worker's lock, waits for it to commit, re-reads the row, sees it is no longer pending, and comes back with nothing. Correct, but every worker in the pool is now queued up behind a single row, and your throughput is that of one worker with extra steps.

SKIP LOCKED is the clause that makes this a queue. It tells the scan to step over any row that another transaction holds a lock on, rather than waiting for it:

UPDATE jobs
SET status = 'running',
    locked_at = now(),
    locked_by = $1,
    attempts = attempts + 1
WHERE id = (
  SELECT id
  FROM jobs
  WHERE status = 'pending'
    AND run_at <= now()
  ORDER BY run_at, id
  FOR UPDATE SKIP LOCKED
  LIMIT 1
)
RETURNING *;

Ten workers run this at once. The first locks the oldest row. The second reaches that row, finds it locked, skips it, and locks the next one. Each worker leaves with a different job and none of them waited on any of the others. The subselect is needed because UPDATE has no LIMIT of its own.

The lock is a row lock and it lives until the transaction that took it commits or rolls back. Which raises the question of how long that transaction should be.

#Do the work outside the claiming transaction

There are two ways to structure a worker, and the choice matters more than anything else in this post.

The first holds the transaction open for the duration of the job. Claim the row, do the work, mark it done, commit. It is attractive because a crashed worker releases its lock automatically and the row is immediately visible to the others again. No lease, no reaper, no stale state.

The cost is a long-running transaction, and Postgres does not like those. Every row version created while your transaction is open cannot be vacuumed, because your snapshot might still need it. A job that takes thirty seconds pins thirty seconds of garbage across every table in the database, not only the jobs table. Fifty workers doing that in parallel and autovacuum quietly stops making progress. It also holds a connection for the whole job, which is exactly the scarce resource you were trying to protect by moving the work off the request path.

The second structure claims the job in a short transaction, commits, does the work with no transaction open, and then runs a second short transaction to mark it done. The row is unlocked the moment the claim commits. What protects it from being picked up twice is the status = 'running' filter, and what protects you from a worker that dies mid-job is a lease.

That is what locked_at is for. A reaper runs every minute and returns anything held too long:

UPDATE jobs
SET status = 'pending', locked_at = NULL, locked_by = NULL
WHERE status = 'running'
  AND locked_at < now() - interval '5 minutes';

Choose the lease to comfortably exceed the slowest legitimate job. A job that legitimately runs longer than the lease will be handed to a second worker while the first is still on it, and then it runs twice.

Which is the moment to say plainly: jobs will sometimes run twice regardless. A worker can finish the work and die before it marks the row done. The reaper will hand that job out again. Make the work idempotent, because at-least-once is the only delivery guarantee any of this offers. If the job sends an email, record that the email for this order was sent and check before sending. If it charges a card, use an idempotency key on the charge. The queue cannot solve this for you, and no queue can.

#The partial index is what keeps the claim fast

The claim query wants the oldest pending job whose run_at has passed. It has to find that row without reading the rows that are not pending, because over the life of the table almost none of them will be.

A plain index on (run_at, id) covers every job ever written. The scan walks it from the oldest entry forward, checking the heap for each one to see whether it is still pending, and skipping the ones that are done. The table is a hundred thousand jobs deep and the first pending row is at the end of it, so every claim reads a hundred thousand index entries to return one.

WHERE status = 'pending' on the index definition means only pending rows are in it. When a job is marked done, its entry is removed. The index stays the size of the backlog, not the size of history, and the claim reads only as far as the first unlocked row.

The id as a second column gives the order a tiebreaker, so two jobs with the same run_at are always visited in the same sequence and SKIP LOCKED distributes them cleanly.

#Retries with backoff, and a dead letter state

When a job fails, do not put it straight back to pending. Push run_at out, and push it further each time:

UPDATE jobs
SET status = CASE WHEN attempts >= max_attempts THEN 'dead' ELSE 'pending' END,
    run_at = now() + (interval '10 seconds' * power(2, attempts))
                   + (random() * interval '5 seconds'),
    last_error = $2,
    locked_at = NULL,
    locked_by = NULL
WHERE id = $1;

The exponential part spreads a job's own retries out. The random part spreads different jobs' retries away from each other, so a batch of a thousand that all failed on the same downstream outage do not all come back in the same second. The reasoning is the same as for retry storms between services, and it applies with the same force here.

dead is a status that nothing picks up. It exists so that a job that has failed five times stops consuming worker time and starts showing up on a dashboard where a human can look at last_error. A queue without a dead letter state does not have failed jobs, it has jobs that fail forever.

#Done rows: delete them

The simplest completion is a delete. The job ran, the row is gone, the table stays small.

If you need a record, insert into a jobs_done table in the same transaction as the delete and let that table grow. Keeping finished rows in the working table with status = 'done' is the common choice and it is the wrong one: the partial index protects the claim query from them, but nothing protects VACUUM, backups, or the person who runs SELECT count(*) FROM jobs from a table that is ninety-nine percent history.

Either way the jobs table sees a high rate of updates and deletes on a small number of live rows, which is the workload autovacuum handles worst by default. Turn it up for this table specifically:

ALTER TABLE jobs SET (
  autovacuum_vacuum_scale_factor = 0.01,
  autovacuum_vacuum_cost_delay = 0
);

#Polling is fine, and NOTIFY only shortens the wait

Workers loop: claim a job, run it, claim again. When the claim returns nothing, sleep for a second and try again. That is a query per worker per second on an index that fits in cache, and it is not a cost worth optimising away.

What polling does cost is latency. A job enqueued just after a worker went to sleep waits out the rest of the sleep. If a second matters, NOTIFY on insert and have workers LISTEN, waking early when a notification arrives.

Keep the poll. Notifications are not durable, a worker that reconnects has missed everything sent while it was away, and a notification sent inside a transaction that then rolls back is simply discarded. Treat NOTIFY as a hint that shortens the wait, never as the thing that guarantees the job is seen. The poll is the guarantee.

#Where this stops being enough

A single Postgres instance will claim and complete several thousand jobs a second with this design, which is more than most products will ever enqueue. The ceiling is not throughput. It is one of three other things.

The first is fan-out. A queue hands each job to one worker. If you need every one of several services to see every event, that is a log, and you want Kafka or a stream, or an outbox that publishes to one.

The second is isolation. If job churn is generating enough vacuum work to hurt the application tables, the queue has become a noisy neighbour, and moving it to its own database is the fix. It can still be Postgres.

The third is when the jobs stop referring to your data. A queue of work that arrives from outside, is processed, and leaves again, with no transaction to join, gains nothing from living in the database and should go wherever is cheapest to operate.

Until one of those is true, the table is the queue. Do not build the second system on the day you need the first one.

#The one-line summary

FOR UPDATE SKIP LOCKED lets many workers claim different rows without waiting on each other. Claim in a short transaction, work outside it, lease the row, make the job idempotent, and index only the rows still waiting.

#References

Related entries