SKIP LOCKED: a job queue in your database, and its limits
A background job table looks trivial until two workers grab the same row. The next thing people reach for is a broker. For many workloads, a single clause in the database is enough.
The problem it solves
With plain SELECT ... FOR UPDATE, the second worker blocks until the first one commits, and then it usually gets the same job's neighbour anyway. Workers spend their time waiting for each other on the first row of the queue. SKIP LOCKED tells the query to ignore rows that another transaction has locked and move on to the next one. PostgreSQL supports it since 9.5, and MySQL since 8.0.
The pattern (PostgreSQL syntax)
UPDATE jobs
SET status = 'running', started_at = now()
WHERE id = (
SELECT id FROM jobs
WHERE status = 'queued'
ORDER BY id
FOR UPDATE SKIP LOCKED
LIMIT 1
)
RETURNING *;The worker runs this, processes the job, and updates the status to done or failed. Because the claim and the status change happen in one statement, a crash between them cannot leave a job half-claimed without a trace. Add a partial index on (id) WHERE status = 'queued' so the lookup stays cheap however many finished jobs the table accumulates.

Details that need decisions
- Crashed workers. A job marked
runningby a worker that died stays running forever. Storestarted_at(or a heartbeat) and have a sweeper put stale jobs back toqueuedafter a timeout, which means jobs must be safe to run twice. - Ordering is best effort. Workers skip locked rows, so jobs do not finish in strict order. If order matters within one customer, you need something more, such as one queue per key.
- Retries and backoff. Keep an
attemptscount and arun_attimestamp, and select only rows whererun_at <= now(), so failed jobs wait before returning. - Keep transactions short. If the worker holds the transaction open while doing the slow work, the row stays locked and a connection is used up. Claim, commit, do the work outside the transaction, and record the result in a second short transaction.
- Cleanup. Delete or archive completed jobs regularly, or the table becomes the dead-row problem from another post.
Where it stops working
A table used as a queue produces constant inserts, updates and deletes, and that load competes with your real data for I/O and vacuum. Very high job rates, fan-out to many consumers, delayed messages at scale, or the need for streaming and replay are the signs to move to a broker such as RabbitMQ, Redis Streams or Kafka. Below that threshold, having jobs in the same transaction as your data (insert the order and its email job atomically) is a real advantage a separate broker does not give you.
Waking workers instead of polling for them
The pattern above still needs something to decide when a worker checks the table again, and the simplest answer, a short sleep in a loop, either wastes cycles polling an empty queue or adds latency equal to the sleep interval before a new job is even noticed. PostgreSQL's LISTEN and NOTIFY remove that trade-off for a SKIP LOCKED queue specifically: a trigger on insert sends a lightweight notification, and idle workers block on LISTEN instead of polling, waking immediately when a job arrives and falling back to a longer poll interval only as a safety net in case a notification is ever missed (connection drops between the notify and a worker's listen do not queue the message for it).
CREATE OR REPLACE FUNCTION notify_new_job() RETURNS trigger AS $$
BEGIN
PERFORM pg_notify('new_job', NEW.id::text);
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER jobs_notify AFTER INSERT ON jobs
FOR EACH ROW EXECUTE FUNCTION notify_new_job();Because LISTEN needs its own persistent connection per listening process, and PgBouncer's transaction pooling mode does not support it (covered in the pooling article), a queue that combines SKIP LOCKED with LISTEN/NOTIFY needs a direct connection for the listening side, separate from the pooled connections the workers use to actually claim and run jobs.