The first thing people tell you when you say “I’m using Postgres as a queue” is that you shouldn’t. The second thing, usually, is a suggestion to add RabbitMQ.

I did the opposite for NewsCrawl’s URL frontier, and after a year of running it I would make the same call again. Not because message brokers are bad, but because the thing I actually needed was not message delivery. It was coordinated claims over a mutable, prioritised work set - and that is a database problem wearing a queue costume.

What a frontier actually has to do

A crawl frontier is not a stream of independent messages. It is a set of URLs where:

  • Priority changes after insertion. A breaking-news source jumps the line.
  • The same URL is scheduled repeatedly, on a cadence per source.
  • Failures need exponential backoff, not immediate redelivery.
  • You need to answer questions like “how many URLs are pending for source 12?” - a query, not a queue operation.
  • A worker crash must return its claims, not lose them.

Model that in a broker and you end up rebuilding half a database on top of it: a side table for state, a separate store for scheduling, and a reconciliation job to keep them agreeing. Model it in Postgres and it’s one table.

The claim

Everything rests on one statement:

UPDATE frontier
SET    status       = 'leased',
       lease_owner  = $1,
       lease_expiry = now() + $2::interval,
       attempts     = attempts + 1
WHERE  id IN (
  SELECT id
  FROM   frontier
  WHERE  status = 'ready'
    AND  next_attempt_at <= now()
  ORDER  BY priority DESC, next_attempt_at ASC
  FOR UPDATE SKIP LOCKED
  LIMIT  $3
)
RETURNING id, url, source_id, attempts;

FOR UPDATE SKIP LOCKED is the entire trick. Without it, twenty workers running this concurrently serialise behind each other - each one blocking on rows the others have locked, so throughput collapses to single-worker speed under contention. With it, a worker that finds a row locked simply steps over it and takes the next candidate.

Two properties fall out of that:

No worker ever waits for another worker. Contention costs you a skipped row, not a blocked transaction.

No two workers ever get the same row. The lock is held for the duration of the UPDATE’s transaction, and by the time it releases, status is leased, so the row no longer matches the inner SELECT.

One subtlety that bites people: the SKIP LOCKED must be in the subquery that selects candidates, and the LIMIT must be there too. If you write it as a plain UPDATE ... WHERE status = 'ready' LIMIT, Postgres has no such form, and the naive rewrites re-introduce the blocking you were trying to avoid.

Why a lease, and not a lock

A held lock dies with its connection. That sounds like exactly what you want for crash safety - until you realise the work takes ninety seconds and you do not want a ninety-second transaction, or a connection held open for the duration of an HTTP fetch that might hang.

So the claim transaction is short: mark the rows, commit, get out. What the worker holds afterwards is not a database lock but a lease - a row-level assertion that says “this is mine until lease_expiry.”

The consequence is that a crashed worker releases nothing. Its rows sit in leased with an expiry in the past, invisible to every other worker, forever.

Which is why the reaper is not optional.

The reaper

UPDATE frontier
SET    status          = 'ready',
       lease_owner     = NULL,
       lease_expiry    = NULL,
       next_attempt_at = now() + (interval '1 second' * least(pow(2, attempts) * 30, 21600))
WHERE  status = 'leased'
  AND  lease_expiry < now()
RETURNING id;

A periodic sweep returns expired leases to ready and pushes their next attempt out by an exponentially growing delay, capped at six hours. Three things worth noting:

The backoff is applied by the reaper, not the worker. A worker that crashed did not get the chance to record a failure. If backoff only happened on the explicit-failure path, a source that reliably kills workers would be retried in a tight loop forever.

attempts was already incremented at claim time. Not at completion. This is deliberate: an increment at completion never runs for a worker that dies, so a URL that crashes workers would have an attempt count of zero forever and never age out. Counting attempts at claim time means “how many times have we tried this” includes the tries that ended badly.

The cap matters. Uncapped exponential backoff schedules a retry after the heat death of the sun. Six hours means a source that was down for a morning recovers the same day.

Set the lease interval to comfortably exceed your slowest legitimate task - I use roughly three times the p99 processing time. Too short and you get duplicate work from slow-but-healthy workers; too long and crash recovery drags.

Priority scheduling for free

Because the frontier is a table, priority is a column, and changing it is an UPDATE. That sounds trivial and it is exactly the thing brokers make hard.

UPDATE frontier
SET    priority = 100
WHERE  source_id = $1 AND status = 'ready';

Boost a source and the very next claim reflects it. In a broker you would be republishing to a different queue, or maintaining priority queues you must poll in order.

The index that makes this fast is the one that matches the claim’s ORDER BY, restricted to the rows that can actually be claimed:

CREATE INDEX frontier_claimable
  ON frontier (priority DESC, next_attempt_at ASC)
  WHERE status = 'ready';

A partial index is the difference between scanning claimable work and scanning history. It also keeps the index small: completed rows, which dominate the table over time, are not in it at all.

The three failure modes I actually hit

Dead tuple accumulation. Every claim and every completion is an UPDATE, which in Postgres means a new row version. A high-throughput frontier generates dead tuples fast, and the default autovacuum thresholds - scaled to a fraction of table size - get lazier as the table grows. Set an aggressive per-table autovacuum_vacuum_scale_factor on this table specifically. I use 0.02.

Long-running transactions blocking cleanup. Vacuum cannot remove tuples still visible to an open transaction. One analytics query left open in a session pins the horizon and the frontier bloats. This is where the “don’t use Postgres as a queue” advice originates - and it’s really “don’t leave transactions open,” which was already true.

The lease interval as a hidden coupling. When I added Playwright rendering, task duration tripled and the reaper started stealing work from healthy workers, which then finished and wrote results for URLs another worker was also processing. Fix: make the lease interval a property of the task class, not a global constant. Fast HTTP fetches keep a short lease; browser renders get a long one.

When I would use a broker instead

I am not arguing Postgres wins every time. Reach for a real broker when:

  • You need fan-out to multiple independent consumer groups - that is genuinely what streams are for, and it is why NewsCrawl’s processing plane is Redis Streams even though its frontier is Postgres.
  • Throughput is beyond roughly a few thousand claims per second, where UPDATE churn becomes the bottleneck.
  • Producers and consumers are separate services that should not share a database.

But when the work set is mutable, prioritised, queryable, and needs to survive a worker being killed mid-task - the database you already run, already back up, and already monitor is a genuinely good answer.

The best part is what you don’t get: no broker to operate, no second durability model, no reconciliation job that exists only because two systems disagree about what has been done.