A hung worker turns queue throughput into disk
This is a measured local schedule, not a production incident or an autovacuum test. On PostgreSQL 18.6 with 8 KiB heap pages, one worker holds job 1 while another completes 1001 jobs. The table still has 1200 rows, but its heap grows from 9 to 17 pages. Manual VACUUM leaves the occupied tuple-slot count unchanged until the first worker commits and another VACUUM runs.
The job queue supplies the selection and completion pattern. Task names are stored strings: this Scenario sends no email and tests no process crash, workload rate, or external delivery guarantee.
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)
B> SELECT backend_xid IS NOT NULL AS has_xid
FROM pg_stat_activity WHERE application_name = 'A'
-- A retains an assigned xid; READ COMMITTED does not establish a transaction-wide RR snapshot
has_xid
---------
t
(1 row)The queue is 1200 rows in 9 pages, and it keeps humming while A sits there. B is a worker using the chapter 5 selection predicate and completion update. 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;
COMMITB runs the same selection and completion body a thousand more times. drain(n) commits each cycle, so CALL runs outside a transaction block. Task names are data: no email is sent in this Scenario.
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)
B> SELECT count(*)::int AS jobs,
count(*) FILTER (WHERE state = 'done')::int AS done,
count(*) FILTER (WHERE state = 'queued')::int AS queued
FROM jobs;
jobs | done | queued
------+------+--------
1200 | 1001 | 199
(1 row)There are 1200 current rows and 1001 additional occupied tuple slots. † The horizon explanation is an inference from PostgreSQL 18's old-transaction vacuum guidance, linked in the lesson. READ COMMITTED does not give A a transaction-wide RR snapshot or access to every old version.
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 completed, but the occupied line-pointer count did not fall. This measurement does not prove that VACUUM did no other maintenance.
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;
COMMITAfter A commits, a second VACUUM reduces occupied tuple slots to 1200. Commit alone does not perform that cleanup.
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)The heap file stays at 17 pages in this run. Ordinary VACUUM can truncate entirely empty trailing pages under suitable locking conditions, but does not compact live rows. VACUUM FULL and other table rewrites are separate operations, not executed here. File size alone does not measure reusable space.
B> SELECT (pg_relation_size('jobs') / 8192)::int AS pages;
pages
-------
17
(1 row)Verified against PostgreSQL 18.6 · Run it yourself · Scenario source
A begins at READ COMMITTED and locks job 1. The monitoring query asserts an assigned backend_xid. This does not establish the transaction-wide REPEATABLE READ snapshot in the long-transactions lesson. An old assigned xid can also constrain the removal horizon; it does not mean that A can read every intermediate version. The Scenario does not assert a null backend_xmin: client-protocol snapshot lifetime is not the same as the isolation-level contract.
B skips locked job 1, selects job 2, updates it, and commits. The drain procedure repeats the selection, update, and per-job commit 250 times, then 750 times. The heap sizes are asserted as 9, 11, and 17 pages. Another query asserts 1200 current rows: 1001 done and 199 queued. These numbers depend on this layout and schedule; they do not predict production bytes per job.
The page-inspection query counts 2201 normal occupied line pointers. The 1001 extra tuple slots remain after the first VACUUM. This does not prove that VACUUM did no other maintenance, that autovacuum ran, or that every table's cleanup stopped. After A commits, the second VACUUM reduces the count to 1200. Commit alone does not reclaim those slots.
† The horizon explanation is an Entailed inference from PostgreSQL 18's old-transaction cleanup guidance and transaction identifiers. The asserted counts demonstrate this schedule, not every possible hung-worker schedule. A retained snapshot and an assigned xid are different diagnostics.
The file stays big anyway
The final asserted heap size is still 17 pages. PostgreSQL's space-recovery contract says ordinary VACUUM "marks the space available for future reuse". It can also truncate entirely empty trailing pages when the required exclusive lock is obtainable. Thus, unchanged size here is not a rule that ordinary VACUUM can never return space to the operating system.
VACUUM FULL, CLUSTER, and other table rewrites can compact a table, with locking and temporary-space costs. None runs here. File size alone cannot distinguish occupied tuple slots from reusable space, and an occupied-slot count is not a measurement of free bytes or index bloat.
The fix is upstream
Operational advice: investigate the transaction holding the old horizon and reduce its lifetime. Vacuum tuning does not remove that transaction's visibility requirement. A short claimed-state transaction with stale-claim recovery is a different queue design; this site does not implement or verify its reaper, fencing, or duplicate-effect handling. Choose those policies before moving external work outside the transaction.
The vacuum dashboard uses estimated statistics, not this page-inspection count. Combine those estimates with transaction horizons and file-size trends. No alert threshold or guaranteed early warning is established by this run.