A jobs table that claims work in 3 ms at 300 jobs a second can still claim it in 6 ms at 4,000. What drags it to 400 ms is a report somebody else started.
Those figures come from a load test for a company that doesn’t exist. I invented both, with the numbers in the proportions the mechanism produces, because the mechanism is what I want on the page. Measure your own hardware before you quote any of it.
My position going in: a jobs table with skip-locked workers is a good queue for much longer than most teams expect. The argument about whether it’s a “real” queue is a waste of a meeting. What you need is a short list of measurements that say when to leave.
I made the case for a jobs table over a broker in the post on hand-offs between stages. This one is about the day it stops holding.
The table, the claim and the index
CREATE TABLE jobs (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
kind text NOT NULL,
payload jsonb NOT NULL,
status text NOT NULL DEFAULT 'ready'
CHECK (status IN ('ready', 'running', 'dead')),
attempts int NOT NULL DEFAULT 0,
max_attempts int NOT NULL DEFAULT 8,
run_at timestamptz NOT NULL DEFAULT now(),
locked_at timestamptz,
last_error text,
created_at timestamptz NOT NULL DEFAULT now()
);
-- Only ready rows are indexed, so the index stays small.
CREATE INDEX jobs_ready_idx ON jobs (run_at, id) WHERE status = 'ready';
-- Claim up to 10 jobs. Other workers skip the rows this one locked.
WITH next AS (
SELECT id
FROM jobs
WHERE status = 'ready' AND run_at <= now()
ORDER BY run_at, id
LIMIT 10
FOR UPDATE SKIP LOCKED
)
UPDATE jobs j
SET status = 'running', locked_at = now(), attempts = j.attempts + 1
FROM next
WHERE j.id = next.id
RETURNING j.id, j.kind, j.payload, j.attempts;
FOR UPDATE SKIP LOCKED has been in Postgres since 9.5. A worker locks the rows it picks, and every other worker steps over them instead of waiting. Twenty workers can run that statement at once without handing out a job twice or queuing behind each other.
The claim commits immediately and the job runs outside a transaction. When the job finishes, the worker deletes the row. A failed job goes back to ready with a later run_at, or to dead once attempts reaches max_attempts, and a sweeper returns rows a crashed worker left in running. Keep the two writes every job makes in mind, the update to running and the delete. They’re where the trouble starts.
I poll, a few times a second per worker. LISTEN/NOTIFY is fine as a wake-up hint at low volume, but NOTIFY takes a global lock at commit, and I don’t want that on a busy write path.
The test at Harrowgate Rail
Harrowgate Rail is my invented seat-reservation service for intercity trains. Its jobs table sits in the main Postgres database and carries booking confirmations, e-tickets and seat-hold expiries. A normal day is 300 jobs a second. The morning the holiday timetable opens for booking, it’s 4,000 a second for about twenty minutes.
| Run | Jobs/s | Workers | Long transaction | Claim time | Table and indexes |
|---|---|---|---|---|---|
| Normal day | 300 | 16 | none | 3 ms | 0.3 GB |
| Holiday release | 4,000 | 64 | none | 6 ms | 1 GB |
| Release, finance report running | 4,000 | 64 | 20 min | 400 ms | 16 GB |
| Same, workers doubled | 4,000 | 128 | 20 min | 310 ms | 16 GB |
| Same, report cancelled | 4,000 | 64 | none | 6 ms | 16 GB |
The second row is the one people don’t expect. Thirteen times the load and the claim barely moved.
What the 400 ms was made of
Every job leaves two dead row versions behind: one from the update to running, one from the delete. Harrowgate’s payloads run about a kilobyte and a half of booking data, small enough that Postgres keeps them inside the row, so every version carries a full copy. At 4,000 jobs a second that’s 8,000 dead versions a second. Autovacuum normally clears them about as fast as they appear.
Vacuum can only remove a row version that no open transaction could still see. The finance report took its snapshot at minute zero and held it for twenty minutes, so 9.6 million dead versions had to stay. Their index entries stayed too. Each claim walked through a growing pile of entries for rows that were already gone, checking every one against the table, before it reached a job that was actually ready. The 400 ms is the median for the run, and it was still climbing when the report finished. At that speed 64 workers taking ten jobs a claim clear about 1,600 a second, so the backlog grew the whole time too.
Doubling the workers put twice as many sessions through the same pile. Cancelling the report let the next autovacuum pass clear it, and claim time fell back to 6 ms within a few minutes. The files stayed at 16 GB. Postgres reuses that space but won’t hand it back to the operating system without a rewrite.
Three numbers on the dashboard
-- 1. Lag: how long the oldest ready job has been waiting.
SELECT count(*) AS ready, now() - min(run_at) AS oldest_ready
FROM jobs
WHERE status = 'ready' AND run_at <= now();
-- 2. Bloat: dead row versions in the jobs table, and when vacuum last ran.
SELECT n_live_tup, n_dead_tup, last_autovacuum,
pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM pg_stat_user_tables
WHERE relname = 'jobs';
-- 3. The usual cause: the longest open transactions.
SELECT pid, usename, application_name, state,
now() - xact_start AS open_for,
left(query, 60) AS query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL AND pid <> pg_backend_pid()
ORDER BY xact_start
LIMIT 5;
I alert on the first when it passes the delay users will tolerate. On the second, dead versions at many times the live count with a recent last_autovacuum means vacuum is running and getting nowhere, which sends me to the third. There, anything open longer than a few minutes on a database that hosts a queue gets a statement timeout or a new home. Moving the report to a replica only helps if the replica isn’t feeding its snapshot back to the primary through hot_standby_feedback. Abandoned replication slots and prepared transactions hold vacuum back the same way and don’t show up in that query.
Churn vacuum can’t clear, and readers who each want everything
If dead versions keep climbing with no long transaction in sight, and per-table autovacuum settings are already aggressive, the churn has outgrown what vacuum can clear on that machine. Partitioning by time and dropping old partitions buys a while. After that it’s a broker.
The other exit is several consumers that each want every event at their own pace, with replay. The hand-offs post covers why that calls for a log and not a table.
Even then I’d keep the jobs table as the outbox. The job still commits with the business row, and a relay publishes it to the broker.
Harrowgate kept its table. Reporting got a five-minute statement timeout, and the third query went on the on-call dashboard.