How does FOR UPDATE SKIP LOCKED help build a job queue in PostgreSQL?
SKIP LOCKED makes a query ignore rows another transaction has already locked instead of waiting. Several workers can each grab different pending jobs at the same time without blocking or double-processing.
Explain it simply
At a college fest food counter with five servers, each server takes the next unserved token. If one server is already handling token 17, the next server does not stand around waiting; they take token 18. SKIP LOCKED gives each worker that behaviour: rows locked by someone else are treated as not there.
Without it, every worker would queue on the same first row and process one job at a time.
Example
CREATE TABLE jobs (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
status text NOT NULL DEFAULT 'pending',
payload jsonb
);
-- each worker runs this
WITH next_job AS (
SELECT id FROM jobs
WHERE status = 'pending'
ORDER BY id
LIMIT 1
FOR UPDATE SKIP LOCKED
)
UPDATE jobs SET status = 'running'
FROM next_job WHERE jobs.id = next_job.id
RETURNING jobs.id, jobs.payload;Two workers running this at once receive different jobs. A partial index on (id) WHERE status = 'pending' keeps the lookup fast.
The result is not a strict global order, since skipped rows may be taken later, so use it for work queues, not for ordered consistent reads.
What to say in the interview
"FOR UPDATE SKIP LOCKED lets concurrent workers claim different rows without waiting on each other, which makes a simple reliable job queue. It gives up strict ordering, so it suits independent tasks."
Common mistakes
- Forgetting to mark the job done or failed, so crashed work is never retried.