Dev News Daily ENDE

A SKIP LOCKED Postgres queue stalled at 375 jobs a second on one CPU; a pool lifted it to 650

A post on garbagecollected.io measures where the popular Postgres job-queue pattern, claiming rows with FOR UPDATE SKIP LOCKED, stops scaling. The setup was deliberately small: Postgres 18 in a container capped at one CPU and 1 GB of memory, default settings apart from 256 MB of shared buffers, a backlog of 500,000 pending jobs, and workers that each claim a job, wait 100 milliseconds to simulate work and mark it done, each worker on its own connection.

Doubling the workers from 1 to 128, throughput climbed until 32 workers, then stalled at about 375 jobs per second at 64 workers, before the default 100 connections were used up. At 128 workers it fell to about 200 jobs per second and 29 workers could not connect. Median query time rose from 8 to 45 milliseconds between 32 and 64 workers, and p99 went from 25 milliseconds to 393, then 1.6 seconds.

Memory was not the limit; the container peaked near 560 MB, mostly cache, at roughly 2 MB per connection. The CPU was at 86% with 64 workers and 100% with 128. The busiest waits were LWLock:WALWrite, because every job writes the log twice, and then LWLock:BufferContent, contention on the few pages at the head of the queue that every worker claims from.

With a shared client-side pool of 20 connections, 128 workers reached 652 jobs per second and p99 fell to 360 milliseconds, at the cost of higher p99 from 32 workers up and Postgres again near full CPU. The author notes the figures come from single runs, that the Python client also used a full core, and that a pool only shares connections within one process, which is where a server-side pooler such as PgBouncer comes in.

A SKIP LOCKED Postgres queue stalled at 375 jobs a second on one CPU; a pool lifted it to 650
A SKIP LOCKED Postgres queue stalled at 375 jobs a second on one CPU; a pool lifted it to 650 — Dev News Daily

What it means

The pattern holds up well, but its ceiling arrives at CPU and lock contention well before the connection limit most people watch. Pooling moves that ceiling rather than removing it.

Written by Victoria Shinder.