Job Queues in SQL: SELECT ... FOR UPDATE SKIP LOCKED

Sometimes you don't want to drag Kafka or RabbitMQ into a project just to run a simple background queue: send an email, recompute a report, generate a PDF. If you already run PostgreSQL, it can be the queue itself — and do it reliably. The whole trick comes down to one clause: FOR UPDATE SKIP LOCKED. It lets ten workers pull tasks from a single table in parallel without stepping on each other and without piling up behind locks.

Let's walk through it with a jobs table tied to orders.

The problem: two workers grab the same task

Start with a queue table. Each row is one task, say "send the order confirmation".

CREATE TABLE jobs (
  id          bigserial PRIMARY KEY,
  order_id    bigint      NOT NULL,
  status      text        NOT NULL DEFAULT 'pending',  -- pending | processing | done | failed
  run_after   timestamptz NOT NULL DEFAULT now(),
  attempts    int         NOT NULL DEFAULT 0,
  locked_by   text,
  created_at  timestamptz NOT NULL DEFAULT now()
);

INSERT INTO jobs (order_id) VALUES (1001), (1002), (1003);

A naive worker does this:

-- ❌ broken with multiple workers
SELECT id FROM jobs WHERE status = 'pending' ORDER BY id LIMIT 1;
-- ...then a separate UPDATE ... SET status = 'processing'

Between the SELECT and the UPDATE, a second worker reads the same row. Result: two workers process order 1001 and the email goes out twice. This is the classic race condition.

The fix: FOR UPDATE SKIP LOCKED

PostgreSQL can take a row-level lock right inside the SELECT. Add FOR UPDATE and the row is locked until the transaction ends. Add SKIP LOCKED and a competing worker won't wait on a locked row — it simply skips it and grabs the next free one.

BEGIN;

SELECT id, order_id
FROM jobs
WHERE status = 'pending'
  AND run_after <= now()
ORDER BY id
FOR UPDATE SKIP LOCKED
LIMIT 1;

-- the worker sees, say, id = 1 and claims it
UPDATE jobs
SET status = 'processing', locked_by = 'worker-7', attempts = attempts + 1
WHERE id = 1;

COMMIT;

Step by step:

• FOR UPDATE locks the selected rows until COMMIT/ROLLBACK.
• SKIP LOCKED excludes rows already locked by other transactions, without waiting.
• LIMIT 1 takes exactly one task (you can grab a batch too — see below).
• ORDER BY id defines the order; without it the queue becomes unpredictable.

Run this block in two sessions at once: the first gets id = 1, the second instantly gets id = 2. Nobody waits on anybody.

Claim a batch in a single statement

The "SELECT then UPDATE" transaction works, but it's cleaner to do it in one statement with a CTE — and you can claim several tasks at once for a batch worker:

WITH picked AS (
  SELECT id
  FROM jobs
  WHERE status = 'pending'
    AND run_after <= now()
  ORDER BY id
  FOR UPDATE SKIP LOCKED
  LIMIT 10
)
UPDATE jobs j
SET status = 'processing',
    locked_by = 'worker-7',
    attempts  = j.attempts + 1
FROM picked
WHERE j.id = picked.id
RETURNING j.id, j.order_id;

RETURNING hands the worker the claimed tasks immediately — id and order_id to process. The lock lasts only for this transaction; after COMMIT the rows are already marked processing, so other workers skip them on the next poll anyway.

When the work is done, close the task out:

UPDATE jobs SET status = 'done', locked_by = NULL WHERE id = 1;

And if a task fails, requeue it with a delay so it retries later:

UPDATE jobs
SET status = 'pending',
    run_after = now() + interval '30 seconds'
WHERE id = 1 AND attempts < 5;

Indexes and performance

Without an index, SKIP LOCKED still scans the table and keeps bumping into locked rows. Add a partial index shaped exactly for the claim query:

CREATE INDEX idx_jobs_pending
ON jobs (run_after, id)
WHERE status = 'pending';

The index covers the filter (status = 'pending', run_after <= now()) and the sort (id), and being partial keeps it small — finished tasks never enter it.

Pitfalls

• SKIP LOCKED is meaningless without a transaction. The lock lives exactly until the transaction ends. Run SELECT ... FOR UPDATE in autocommit mode and the row is released immediately, so a parallel worker grabs it. Keep the claim and the processing mark in one transaction (or one CTE statement).
• Don't hold the transaction open during the work itself. If the worker spends a minute generating a PDF, don't leave BEGIN open the whole time. Claim the task, flip it to processing, COMMIT, and only then do the slow work. Otherwise one stuck transaction stalls VACUUM and accumulates locks.
• SKIP LOCKED weakens ordering. If the first task in line is locked, a worker takes the next one. Strict FIFO is not guaranteed — fine for a job queue, but don't rely on exact order.
• MySQL / other engines. SKIP LOCKED exists in MySQL 8.0+ (the FOR UPDATE SKIP LOCKED syntax is almost identical) and in Oracle. ClickHouse, however, is columnar and not built for row-locked queues — for an OLTP queue reach for PostgreSQL or MySQL, not an analytics engine.

That's enough to run a reliable queue handling hundreds of tasks per second with no separate broker. When you hit that ceiling, then start thinking about Kafka.