Use SKIP LOCKED for Non-Blocking Queue Processing

Why This Matters

FOR UPDATE SKIP LOCKED allows multiple workers to process queue items concurrently without blocking each other. When a row is locked by one worker, other workers simply skip it and pick the next available item.

This is perfect for:

• Job queues with multiple workers
• Email sending queues
• Background task processing
• Any scenario where multiple processes need to consume work items

The Problem

Without SKIP LOCKED, workers block waiting for locks, reducing parallelism:

-- Worker 1: Gets job 1, locks it
BEGIN;
SELECT * FROM jobs 
WHERE status = 'pending' 
ORDER BY created_at LIMIT 1
FOR UPDATE;
-- Processing job 1...

-- Worker 2: Waits for Worker 1's lock to release
BEGIN;
SELECT * FROM jobs 
WHERE status = 'pending' 
ORDER BY created_at LIMIT 1
FOR UPDATE;
-- BLOCKED! Waiting for Worker 1...

-- Worker 3: Also blocked waiting...
-- Only one worker processes at a time!

Workers wait in line instead of processing different jobs simultaneously, wasting resources and slowing down queue processing.

The Solution

Use FOR UPDATE SKIP LOCKED to skip locked rows and pick the next available item:

-- Worker 1: Gets job 1, locks it
BEGIN;
SELECT * FROM jobs 
WHERE status = 'pending' 
ORDER BY created_at LIMIT 1
FOR UPDATE SKIP LOCKED;
-- Returns job 1, processing...

-- Worker 2: Skips locked job 1, gets job 2
BEGIN;
SELECT * FROM jobs 
WHERE status = 'pending' 
ORDER BY created_at LIMIT 1
FOR UPDATE SKIP LOCKED;
-- Returns job 2 immediately! Processing concurrently...

-- Worker 3: Gets job 3, also processing concurrently
-- All workers process different jobs simultaneously!

Complete queue worker pattern:

-- Queue table
CREATE TABLE jobs (
  id BIGSERIAL PRIMARY KEY,
  status TEXT NOT NULL DEFAULT 'pending',
  payload JSONB,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  CHECK (status IN ('pending', 'processing', 'completed', 'failed'))
);

CREATE INDEX jobs_status_created_idx ON jobs (status, created_at)
WHERE status = 'pending';

-- Worker function
CREATE OR REPLACE FUNCTION process_next_job()
RETURNS TABLE(job_id BIGINT, job_payload JSONB) AS $$
DECLARE
  job_record jobs%ROWTYPE;
BEGIN
  -- Get and lock next available job
  SELECT * INTO job_record
  FROM jobs
  WHERE status = 'pending'
  ORDER BY created_at
  LIMIT 1
  FOR UPDATE SKIP LOCKED;
  
  IF job_record.id IS NULL THEN
    -- No jobs available
    RETURN;
  END IF;
  
  -- Mark as processing
  UPDATE jobs SET status = 'processing' WHERE id = job_record.id;
  
  -- Return job details
  RETURN QUERY SELECT job_record.id, job_record.payload;
END;
$$ LANGUAGE plpgsql;

Best Practices

• Always use an index: SKIP LOCKED still needs to scan rows. Index on (status, created_at) makes it fast.
• Use partial indexes: CREATE INDEX ... WHERE status = 'pending' keeps the index small and fast.
• Handle empty results: When no jobs are available, SKIP LOCKED returns no rows. Workers should sleep briefly before retrying.
• Update status atomically: Mark jobs as 'processing' in the same transaction that locks them to prevent double-processing.
• Use NOWAIT for timeouts: Combine with FOR UPDATE SKIP LOCKED NOWAIT to fail immediately if you can't get a lock (rarely needed).

Example: Multi-Worker Email Queue

Complete example with multiple workers processing emails:

-- Email queue table
CREATE TABLE email_queue (
  id BIGSERIAL PRIMARY KEY,
  to_email TEXT NOT NULL,
  subject TEXT NOT NULL,
  body TEXT NOT NULL,
  status TEXT NOT NULL DEFAULT 'pending',
  priority INTEGER NOT NULL DEFAULT 0,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Index for efficient queue processing
CREATE INDEX email_queue_pending_idx ON email_queue (priority DESC, created_at)
WHERE status = 'pending';

-- Worker: Get next email to send
BEGIN;
SELECT * FROM email_queue
WHERE status = 'pending'
ORDER BY priority DESC, created_at
LIMIT 1
FOR UPDATE SKIP LOCKED;

-- If row found, mark as processing and send
UPDATE email_queue SET status = 'processing' WHERE id = -- job id;
COMMIT;

-- Send email (outside transaction!)
-- send_email(to_email, subject, body)

-- Mark as completed
UPDATE email_queue SET status = 'completed' WHERE id = -- job id;