Making Postgres queues scale

dewey

> 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.

sorentwo

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.

richwater

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

atombender

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

rcleveng

Are these posted manually or is there a scheduled claude co-work task that does it?

rtpg

we had a whole discussion at work around whether to not to use pg for queues at a reasonable size (in particular for doing some notion of fair queueing distribution across tenants). I ended up finding a good number of HN comments like "we were doing this and regretting it". So here's my ask: anybody here use PG for queues at a system with reasonable throughput, without regretting it? Like where there might be some contention

hiyer

In a recent interview I was asked to design a job queue and I went with postgres with the first and third optimizations mentioned here. For the scale of the question - 1000 concurrent jobs - I argued that postgres would easily scale. But the interviewer - maybe because they were from aws - felt it wouldn't and wanted me to go with sqs instead.

LtdJorge

Isn’t "for no key update skip locked" better than "for update skip locked"? Most of the times there will be no improvement, but it’s a good option when you don’t need a stronger lock (e.g., for DELETE).

NightMKoder

You can go deeper and model a queue as a ring-esque buffer with a write head (can just be a serial id) and a read head. The read head starts at the same place as the write head and advances only up to the write head and no further via nextval(). The main benefit is you now remove the lock contention as many workers attempt to dequeue at once. The super advanced version of this is pgque - https://pgque.dev/ - but that’s more like Kafka in Postgres. I wouldn’t go there if you don’t know the Kafka model already and you want it.