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 adding infrastructure like Kafka or RabbitMQ, keeps transactional guarantees between application data and job state, and works well for low-to-moderate throughput. The usual pattern is straightforward: 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 SKIP LOCKED;

```

This works fine at low volume. It falls apart, often silently, once throughput climbs past a few hundred jobs per second. Understanding why requires looking at how Postgres actually handles row locks and index scans under contention.

Why FOR UPDATE Becomes a Hotspot

`SELECT ... FOR UPDATE` acquires a row-level lock, but the path to that row usually runs through an index on `status` (or `status, id`). When many workers poll simultaneously, they all traverse the same narrow set of index pages looking for the same small set of "pending" rows. Even with `SKIP LOCKED` correctly avoiding already-locked rows, the workers still contend on the same B-tree pages and buffer locks to determine what's available.

As concurrency increases, this turns into a hotspot: every worker is fighting over the same leading edge of the index. Lock waits multiply, CPU spent on lock manager bookkeeping grows, and query latency becomes unpredictable. The failure mode isn't a clean deadlock in the classic two-transactions-blocking-each-other sense — it's usually worse: queries queue up behind spinlocks and buffer pins, throughput craters, and connections pile up waiting on locks that never resolve within a reasonable time. From the outside it looks like a stall or a soft deadlock, even though `pg_locks` may not show a textbook deadlock cycle.

The 500 Jobs/Sec Ceiling

The exact threshold varies with hardware, row size, and index layout, but a repeatable pattern shows up around a few hundred transactions per second when many workers target the same status value. The number itself is less important than the mechanism: contention on a small, hot region of an index scales badly with worker count, not job volume. Adding more workers to "keep up" makes it worse, because each new worker adds another contender for the same page locks. Reviewing slow query patterns around this workload usually reveals lock wait time dominating total query duration, even though the query plan itself looks efficient in isolation.

Fixing the Hotspot

A few changes consistently help:

- Partition or shard the pending queue by hash or worker group so polling queries hit different index ranges instead of the same leading edge.

- Use `SKIP LOCKED` correctly and confirm it's actually in the query — omitting it is a common regression during refactors.

- Batch dequeues instead of one-row-at-a-time claims, reducing the number of lock acquisitions per unit of work.

- Add a partial index on `status = 'pending'` to shrink the scanned region and reduce page contention.

- Move to `LISTEN/NOTIFY` or advisory locks for coordination instead of relying purely on row locks for polling.

Monitoring lock wait time specifically, not just query duration, is critical here — tools that surface lock contention over time make this pattern visible before it becomes an outage. Querk's monitoring can help correlate rising lock waits with job throughput to catch