A Postgres jobs table is a fine queue until one of three numbers moves

An invented load test of a skip-locked jobs table: fine at 300 jobs a second, slow at 4,000 for a reason unrelated to the workers, and three numbers to watch.

Producer Jobs table Skip-locked workers Retry schedule Dead-letter rows Bloat and lag monitor Broker business row + job row with backoff past the thresholds
A row and its job commit together; workers claim, retry or dead-letter, and a monitor on the table says when a broker takes over.
On this page
  1. The table, the claim and the index
  2. The test at Harrowgate Rail
  3. What the 400 ms was made of
  4. Three numbers on the dashboard
  5. Churn vacuum can’t clear, and readers who each want everything

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.