SELECT FOR UPDATE SKIP LOCKED, revisited

Five years ago I wrote a short note claiming SELECT FOR UPDATE SKIP LOCKED was the best-kept secret in postgres. Every few months since, someone has asked me the same three questions about it. This is an attempt to answer all of them at once.

The problem it solves

Imagine you have a jobs table. Multiple worker processes need to pull jobs from it concurrently. The naive approach — SELECT ... WHERE status = 'pending' LIMIT 1 followed by an UPDATE — has a race condition. Two workers can grab the same row. So you add a lock: SELECT ... FOR UPDATE. Now workers block each other. Throughput tanks.

This is the exact scenario where SKIP LOCKED shines. It tells postgres: if a row is already locked by another transaction, don't wait — skip it and move to the next one.

BEGIN;
SELECT id, payload
FROM jobs
WHERE status = 'pending'
ORDER BY created_at
LIMIT 1
FOR UPDATE SKIP LOCKED;

-- got a row? update it, commit
UPDATE jobs SET status = 'running' WHERE id = ?;
COMMIT;

No redis. No rabbit. No zookeeper. Just postgres doing what postgres already does, with one clause most people have never read.

Why it works so well

Under the hood, postgres maintains a lock table. When you add SKIP LOCKED to a FOR UPDATE clause, the executor simply skips rows that appear in that lock table instead of waiting for the transaction holding the lock to complete. This means zero contention between consumers — they naturally distribute work across available rows.

The ordering guarantee comes from the index scan. If you have an index on (status, created_at), postgres walks the index in order, skipping locked rows, and returns the first unlocked one it finds. The result is near-perfect work distribution with minimal overhead.

When it falls over

I used to say "never." That was wrong. At around 5k enqueues per second on a single table, you start seeing xid wraparound pressure if you're not careful with vacuum settings. At 20k/s, the index on status becomes the bottleneck and you need partial indexes to keep the planner honest.

Past that, you're probably ready for a real message queue. But most systems never get there — and the ones that do were probably wrong to start with something more complex anyway.

Three things I had to learn the hard way

1. Always set a lock timeout. If your workers crash after selecting but before committing, the lock holds until the connection times out. Set lock_timeout to something reasonable — 5 seconds is usually enough.

2. Use advisory locks for deduplication. If you need to ensure a job is only processed once even across restarts, pair SKIP LOCKED with pg_try_advisory_lock(id). If you can't acquire the advisory lock, someone else already claimed it.

3. Vacuum matters more than you think. A high-churn jobs table needs aggressive autovacuum. Tune autovacuum_vacuum_scale_factor down to 0.05 or lower, and consider partitioning by time if the table grows fast.

You don't need kafka. You need to write better postgres.