Postgres as a Queue: The Locking Hotspot Pattern

Postgres as a Queue: The Locking Hotspot Pattern

Using PostgreSQL as a job queue is a common architectural shortcut. It avoids the operational overhead of running a dedicated message broker, keeps the job state transactionally consistent with application data, and works well enough at modest throughput. The typical implementation looks simple: a `jobs` table, a `status` column, and a worker loop that runs something like `SELECT * FROM jobs WHERE status = 'pending' ORDER BY id LIMIT 1 FOR UPDATE`. This pattern holds up fine at low volume. It starts to fall apart, often silently, once job throughput climbs past a few hundred per second.

Why FOR UPDATE Becomes a Hotspot

`SELECT ... FOR UPDATE` works by taking a row-level lock on the candidate rows before they're returned to the caller. When multiple workers poll concurrently, they're all racing to lock rows near the front of the same index scan. Postgres resolves this by making later transactions wait on the lock held by whichever worker got there first. As worker count and polling frequency increase, more transactions pile up waiting on the same small set of "next available" rows.

This isn't a deadlock in the strict sense — Postgres will detect true deadlocks and abort one of the transactions automatically. What actually happens is worse in practice: a convoy effect. Workers queue up behind each other waiting for row locks, transaction duration increases, connections stay open longer, and throughput degrades non-linearly. At low concurrency the wait is imperceptible. Past a few hundred transactions per second, the lock wait queue becomes the bottleneck, and p99 latency on job pickup can spike from milliseconds to seconds without any error being logged. It looks like the database is "hanging" even though no query is technically broken.

The Missing SKIP LOCKED

The most common root cause is a missing or misused `SKIP LOCKED` clause. Without it, a worker that hits a locked row simply waits for it to unlock rather than moving on to the next candidate. `SELECT ... FOR UPDATE SKIP LOCKED` tells Postgres to skip any row currently locked by another transaction and move to the next match, which is the behavior almost every queue implementation actually wants. Teams that add this clause late, after the queue is already in production, often see an immediate and dramatic drop in contention — but by then the underlying index and query shape may already be poorly suited to concurrent access, and `SKIP LOCKED` alone doesn't fix an unindexed status scan or a table bloated with completed jobs.

Index Design and Table Bloat

A queue table under heavy churn needs a partial index on the pending-state rows specifically, not a full index on `status`, since the planner otherwise has to scan through rows it will never return. Equally important is aggressive vacuuming: completed or failed jobs that aren't archived or deleted accumulate as dead tuples, inflating the table and slowing every scan the queue workers run. This is where query performance problems and index misconfiguration compound each other — a bloated table with a weak index makes lock contention worse because workers spend longer holding locks while scanning.

When to Stop Using Postgres as a Queue

Past a certain throughput, the pragmatic answer isn't a cleverer query — it's recognizing that a relational table was never designed to be a high-concurrency work queue. Batching job claims, reducing polling frequency, and monitoring slow queries tied to the jobs table can extend its runway considerably. But teams consistently pushing well beyond