A hung worker turns queue throughput into disk
Here's the incident. Your job queue's table is many times the size of the rows it holds: a few thousand live jobs, gigabytes on disk. Autovacuum is running, on schedule, no errors in the log. The monitoring dashboard is green. And the table keeps growing.
The long transactions lesson proved the mechanism behind this: one open transaction holds the vacuum horizon back, and VACUUM reclaims nothing behind it. What that lesson didn't do is put a meter on the cost. A queue is a churn engine. Every job is a claim with FOR UPDATE SKIP LOCKED, a completion UPDATE, and a COMMIT, so one worker that hangs mid-job turns the whole queue's throughput into growth nobody can reclaim until it lets go. The bill isn't set by how many jobs are stuck. It's set by how many drained past the one that is.
Watch the meter climb
A is a worker that claims a job and then hangs: the connection stays open and the transaction never commits. This is the queue's own flavor of "idle in transaction."
A> BEGIN;
BEGIN
A> SELECT id, task FROM jobs WHERE state = 'queued'
ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED;
id | task
----+--------------------
1 | send welcome email
(1 row)The queue is 1200 rows in 9 pages, and it keeps humming while A sits there. B is a healthy worker running the exact chapter 5 loop. Watch one cycle produce one dead version: B skips A's locked job 1 and claims job 2.
B> SELECT (pg_relation_size('jobs') / 8192)::int AS pages;
pages
-------
9
(1 row)
B> BEGIN;
BEGIN
B> SELECT id, task FROM jobs WHERE state = 'queued'
ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED; -- job 1 is claimed by A, skipped without waiting
id | task
----+--------------------
2 | send welcome email
(1 row)
B> UPDATE jobs SET state = 'done' WHERE id = 2;
UPDATE 1
B> COMMIT;
COMMITThat cycle left exactly one dead version: the old 'queued' tuple of job 2. Now B runs the same body a thousand more times. drain(n) is that cycle in a procedure — claim, mark done, COMMIT, repeat — so the per-job COMMIT is why CALL runs outside a transaction block. The SQL a reader trusts is the SQL they just watched.
B> CALL drain(250); -- 250 more claim/complete cycles
CALL
B> SELECT (pg_relation_size('jobs') / 8192)::int AS pages;
pages
-------
11
(1 row)
B> CALL drain(750); -- 750 more
CALL
B> SELECT (pg_relation_size('jobs') / 8192)::int AS pages;
pages
-------
17
(1 row)The table is still 1200 live rows and has nearly doubled on disk. Count the line pointers across every page: 1200 live plus 1001 dead, none of the dead ones removable while A's snapshot might still need the 'queued' versions they replaced.
B> SELECT count(*)::int AS occupied
FROM generate_series(0, (pg_relation_size('jobs') / 8192)::int - 1) AS p,
heap_page_items(get_raw_page('jobs', p::int))
WHERE lp_flags = 1;
occupied
----------
2201
(1 row)
B> VACUUM jobs;
VACUUMVACUUM ran, reported success, and reclaimed nothing. Same silent no-op as the long transactions lesson, now with a price tag: the line-pointer count has not moved.
B> SELECT count(*)::int AS occupied
FROM generate_series(0, (pg_relation_size('jobs') / 8192)::int - 1) AS p,
heap_page_items(get_raw_page('jobs', p::int))
WHERE lp_flags = 1;
occupied
----------
2201
(1 row)
A> COMMIT;
COMMITThe instant A's snapshot is gone, the identical VACUUM can remove all 1001 dead versions. The occupied count falls to the 1200 live rows.
B> VACUUM jobs;
VACUUM
B> SELECT count(*)::int AS occupied
FROM generate_series(0, (pg_relation_size('jobs') / 8192)::int - 1) AS p,
heap_page_items(get_raw_page('jobs', p::int))
WHERE lp_flags = 1;
occupied
----------
1200
(1 row)But the file never shrank. VACUUM freed the space inside the 17 pages for the queue to reuse; it did not hand it back to the OS, and only VACUUM FULL would, under a table lock. Page count is why you can't measure this bug with pg_relation_size alone: the disk you bought during the hang stays bought.
B> SELECT (pg_relation_size('jobs') / 8192)::int AS pages;
pages
-------
17
(1 row)Verified against PostgreSQL 18.4 · Run it yourself · Scenario source
Session A is the stuck worker: it claims job 1 and never commits. A slow HTTP call, a debugger breakpoint, a BEGIN an ORM forgot to close — the cause doesn't matter, only that the transaction stays open. B is every other worker, healthy, running the taught loop. The first cycle shows the pattern in the open: B skips A's locked job 1 (that's SKIP LOCKED earning its keep), claims job 2, marks it done, commits. One dead row version left behind, the old 'queued' tuple of job 2.
Then B runs that same body a thousand more times through drain, a procedure holding the identical three statements. The page count is the meter: 9 pages before, 11 after 250 cycles, 17 after 750 more. The table hasn't gained a single live row — it's still 1200 jobs — but every completed cycle left a dead version A's snapshot might still need, and not one can go while A holds the horizon. Count the occupied line pointers across the pages and there they are: 1200 live plus 1001 dead, 2201 in all.
VACUUM jobs runs, reports success, and frees none of it. The line-pointer count doesn't budge. That's the failure mode's cruelest trick: the vacuum worked, which is exactly why the dashboard stays green while the disk fills. VACUUM is obeying its one rule, and the manual states it plainly: "The standard form of VACUUM removes dead row versions in tables and indexes and marks the space available for future reuse." It removes dead versions, and A's snapshot is what keeps these ones alive. The instant A commits, the identical VACUUM jobs reclaims all 1001 and the count drops back to 1200. The whole freeze was one worker's grip on a snapshot, and ending the transaction ends it.
The file stays big anyway
One number never moved through any of this: the page count. Seventeen pages after the drain, seventeen after the reclaiming VACUUM. VACUUM marked the dead space reusable inside the file; it didn't return it to the operating system. The only thing that does is VACUUM FULL, which rewrites the whole table under an ACCESS EXCLUSIVE lock, and the manual itself counsels against reaching for it here: "Although VACUUM FULL can be used to shrink a table back to its minimum size and return the disk space to the operating system, there is not much point in this if the table will just grow again in the future." A queue will. So the disk you bought during the hang stays bought, held in reserve for the next batch of jobs. This is also why pg_relation_size alone can't diagnose the bug: it reads the same 17 whether those pages are dead weight or free space waiting for reuse. The line-pointer count is what tells the two apart.
The fix is upstream
You don't fix this with vacuum tuning. You fix it by not holding the transaction. The job queue lesson already names the trade: for jobs that run longer than a few seconds, drop the claim-is-the-lease design and claim by state instead, with UPDATE ... SET state = 'running', claimed_at = now() in one short transaction plus a reaper that requeues stale claims. That caps how long any worker can pin the horizon, at the price of the automatic crash safety the lease handed you for free.
And catch it long before 3am: chapter 8's bloat & vacuum health turns the line-pointer gap you just watched into a graph and an alert, so a queue drifting under a stuck worker shows up as a slope on a dashboard instead of a disk-full page at midnight.