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.<p>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.<p>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.<p>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.<p>Previously discussed on HN here [2].<p>[1] <a href="https://github.com/NikolayS/pgque" rel="nofollow">https://github.com/NikolayS/pgque</a><p>[2] <a href="https://news.ycombinator.com/item?id=47817349">https://news.ycombinator.com/item?id=47817349</a>
This is something we mention in the third section of the article: dead tuples and autovacuum caused real performance hits, which we (partially) mitigated through index optimization (minimizing the number and size of indexes, and making indexes partial).<p>PgQue is a really interesting system! However, its semantics are quite a bit more similar to Kafka than to a job queue, which is good for some workloads and not for others. For example a truncation-based deletion system is fast but inflexible, and not suitable for a job queue system because a single long-running job (and DBOS supports workflows that run for months) can block truncation.
Not a problem unless your system has busy queue processing 24/7 though, which I bet is pretty rare for most companies.
> The conventional wisdom around Postgres-backed queues is that they don't scale.<p>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.
I see a lot of back and forth about postgres' suitability as a queuing system. I wonder if there's a couple of separable problems here. Postgres backed queues - even very scalable ones - might work well for background jobs in a monolithic app backed by a single DB. Things like backgrounding sending an email etc.<p>But usually when I reach for a queuing system, it's because I want to decouple a part of the architecture. And in that case, it's probably better to use a dedicated queueing system instead of postgres.<p>I wonder if the two cases are conflated in a lot of online discussion.
We are very heavily using Postgres as a queuing system in production for many years already, not just some quiet background tasks but with millions of tasks in the queue at any given moment. I have yet so see any issues with that so I'm always a bit suspicious when people say they had to reach for something else unless you are at a crazy scale.<p>I know it's very hard to compare workloads, but famous recent example: <a href="https://openai.com/index/scaling-postgresql/" rel="nofollow">https://openai.com/index/scaling-postgresql/</a><p>> It may sound surprising that a single-primary architecture can meet the demands of OpenAI’s scale; however, making this work in practice isn’t simple.
I have the impression that SolidQueue uses whatever your rails app is using by default. That can be SQLite, PostgreSQL, MySQL, etc.<p>So I don't think they changed to PostgreSQL per se.
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).<p>I ended up finding a good number of HN comments like "we were doing this and regretting it".<p>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
Are these posted manually or is there a scheduled claude co-work task that does it?
They certainly do, and I don't think it's a controversial take at this point.<p>Shameless link to an older article about throughput with Oban (<a href="https://oban.pro/articles/one-million-jobs-a-minute-with-oban" rel="nofollow">https://oban.pro/articles/one-million-jobs-a-minute-with-oba...</a>), 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
everyone sure acts like postgres is some sort of thing that we discovered next to the antikythera mechanism on the sunken isle of atlantis. the "just get one big machine to do all of the stuff" scaling strategy remains undefeated, though. even if it is presented like it is anything but.
These days, most of them are from the DBOS folks. :)
[flagged]