Making Postgres queues scale

A performance pitfall that isn't addressed in the DBOS article at all is the bloat problem: If you update or delete rows that you consume, dead tuples start to accumulate due to Postgres' way of doing MVCC.

This is a serious problem because it affects the planner's ability to make good choices. Dead tuples are still indexed and the need to skip them isn't accounted for by the query planner, so a table with lots of dead tuples may perform really badly. The autovacuum process will be constantly chasing dead tuples, and you'll want to set the autovacuum settings to be very aggressive to be able to keep up.

My team has been starting to use PgQue [1] for a new application, and it seems really well-designed. PgQue is explicitly designed to solve the bloat problem, by avoiding tuple deletion. Instead of deleting processed tuples, it will periodically TRUNCATE the entire table. It uses two tables that it "flips" between so TRUNCATE can run on the inactive table while the active on is used for queuing. PgQue also uses a snapshot approach to avoid row-level locks.

PgQue also stands out in that its queue model is position-based, so it can implement nice features like collaborative consumers, fan-out, atomic batches, and "recover from last good" behaviour. It makes some compromises (no priority support, slightly higher latency), but they're fine for most use cases.

Previously discussed on HN here [2].

[1] https://github.com/NikolayS/pgque

[2] https://news.ycombinator.com/item?id=47817349

> The conventional wisdom around Postgres-backed queues is that they don't scale.

That might have been the case 10 years ago. In the past years there have been many Postgres powered queueing systems and even Rails switched to Postgres powered queues by default (SolidQueue) more than 3 years ago.

They certainly do, and I don't think it's a controversial take at this point.

Shameless link to an older article about throughput with Oban (https://oban.pro/articles/one-million-jobs-a-minute-with-oba...), and in follow-up research we've sustained 12k/s with a p99 under ~100ms.

Reading Postgres queuing posts always seem like deja vu. People love to write about them

[flagged]