What is SKIP LOCKED in PostgreSQL? How to use it for task queues?

SKIP LOCKED is an option in PostgreSQL for the SELECT ... FOR UPDATE statement, which allows skipping locked rows during selection. This is useful for implementing task queues where multiple workers simultaneously pick tasks from a table.

When one process locks a row (task), other processes with SKIP LOCKED simply skip it and take the next available task, without waiting for the lock to be released.

Example of usage for a task queue:

BEGIN;
SELECT * FROM tasks
WHERE status = 'pending'
FOR UPDATE SKIP LOCKED
LIMIT 1;
-- update task status to 'processing'
UPDATE tasks SET status = 'processing' WHERE id = <selected_id>;
COMMIT;

This approach allows efficient distribution of tasks among multiple workers without conflicts and downtime.