Job queues

Postgres makes a perfectly good job queue, and "just use Postgres" is the right answer far more often than not. The failure mode is worth understanding before you get there, because it is not the one people expect.

The shape that works

CREATE TABLE jobs (
  id          BIGINT      GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  run_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
  status      TEXT        NOT NULL DEFAULT 'pending',
  attempts    INT         NOT NULL DEFAULT 0,
  payload     JSONB       NOT NULL
);

-- Partial index: only pending rows are in it, so it stays small no matter
-- how many jobs have been processed.
CREATE INDEX jobs_pending ON jobs (run_at) WHERE status = 'pending';

Claiming work:

BEGIN;

SELECT id, payload
FROM jobs
WHERE status = 'pending' AND run_at <= now()
ORDER BY run_at
LIMIT 10
FOR UPDATE SKIP LOCKED;

-- ... process ...

DELETE FROM jobs WHERE id = ANY($1);
COMMIT;

Three things are doing the work there:

• FOR UPDATE SKIP LOCKED is what makes concurrent workers possible. Each worker locks the rows it claims; other workers skip straight past locked rows instead of blocking behind them. Without it, N workers serialise into one.
• LIMIT 10 rather than LIMIT 1 amortises the index scan across ten jobs. At any real throughput this matters more than it looks.
• The partial index keeps the structure the claim query walks proportional to the backlog, not to the table's history.

Keep the transaction short. Everything below is a consequence of that one rule.

Why the queue slows down

A queue table is the highest-churn table you will ever own: every job is inserted, updated once or twice, and deleted. Every one of those operations leaves a dead row version behind, and the index accumulates entries pointing at them.

That is fine, because VACUUM cleans it up continuously - as long as it is allowed to. It is not allowed to reclaim anything newer than the oldest open transaction in the database (Long-running transactions).

So the failure mode is this: your queue gets slow because of something that is not your queue. A reporting query, a nightly export, an ORM holding a transaction across an HTTP call - anything that holds the horizon open blocks cleanup on the queue table specifically, because the queue is where the churn is. The claim query starts walking index entries that point at rows no longer visible, each costing a page read that produces nothing, and claim latency climbs from single-digit milliseconds into the hundreds.

It is worse than a single long query, too. Three overlapping 40-second reports, staggered 20 seconds apart, hold the horizon continuously even though no individual query is long enough to look suspicious.

Keeping it healthy

Vacuum the queue table harder than the default. Autovacuum's default trigger is a percentage of the table, which is the wrong shape for a queue where the row count is small but the churn is enormous:

ALTER TABLE jobs SET (
  autovacuum_vacuum_scale_factor  = 0.0,    -- ignore table size
  autovacuum_vacuum_threshold     = 1000,   -- vacuum every 1000 dead rows
  autovacuum_analyze_scale_factor = 0.0,
  autovacuum_analyze_threshold    = 1000
);

Lower the fillfactor so updates can stay on the same page (a HOT update, which does not have to touch the index at all):

ALTER TABLE jobs SET (fillfactor = 70);

Separate the workloads by role, so a report cannot hold the horizon indefinitely:

ALTER ROLE app_reports SET statement_timeout = '5min';
ALTER ROLE app_reports SET idle_in_transaction_session_timeout = '1min';

Delete completed jobs, do not accumulate them. If you need history, move it: DELETE ... RETURNING into an archive table, or write the archive row at completion and delete the job. A queue table that also serves as an audit log is two tables wearing one trenchcoat, and the query patterns fight.

When the queue is genuinely large

If you retain completed jobs, partition by time and drop old partitions instead of deleting rows. DROP TABLE on a partition is a file unlink - it costs the same whether the partition holds one row or a hundred million, and it produces no dead rows to clean up. DELETE of the same data costs proportionally and leaves cleanup work behind. See Large tables.

What to watch

The Vacuum needed and Long-running transaction advisories in your dashboard are both relevant here, and if you see them together, the second one is the cause.

Directly:

SELECT n_live_tup, n_dead_tup, last_autovacuum, autovacuum_count
FROM pg_stat_user_tables
WHERE relname = 'jobs';

A n_dead_tup that climbs while last_autovacuum keeps updating is the signature: vacuum is running, and being told it may not remove anything. That is a horizon problem, not a vacuum-tuning problem, and no amount of autovacuum tuning will fix it.