Making Postgres queues scale

Posted by KraftyOne 4 days ago

Comments

Comment by atombender 3 days ago

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

Comment by KraftyOne 3 days ago

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

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.

Comment by danielheath 3 days ago

I suspect partitioned tables would be great for this - with a stored procedure to create partitions on-demand, you could split tasks up by date-range and type, and then drop the old tables once their time-range has passed and their jobs all processed.

Job workers could query the parent table, no need to modify them.

Comment by sgt 3 days ago

Not a problem unless your system has busy queue processing 24/7 though, which I bet is pretty rare for most companies.

Comment by atombender 3 days ago

Not true, unfortunately. The dead tuple build-up can happen in a very short amount of time. I speak from having had to deal with this in a production environment that used the SKIP LOCKED method used in the article.

Comment by sgt 3 days ago

What were the volumes involved, how many jobs per second and so on?

Comment by atombender 3 days ago

This is a while back, and we've since re-engineered it a bit, but we had database shards processing 400-500 rows/sec, maybe more. Vacuum was not able to keep up even with more aggressive autovacuum settings. (To be fair, the write activity wasn't only to the queue table.)

We were able to show that the dead tuples caused Postgres to use the wrong query plan because it misjudged the amount of real rows. We reported this problem on the Postgres mailing list, and it seemed like this was a known problem and that there was interest in making the planner more dead-tuple-aware.

Comment by dewey 4 days ago

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

Comment by mjfisher 3 days 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.

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.

I wonder if the two cases are conflated in a lot of online discussion.

Comment by dewey 3 days ago

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.

I know it's very hard to compare workloads, but famous recent example: https://openai.com/index/scaling-postgresql/

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

Comment by tomnipotent 3 days ago

That post is about how Postgres doesn't scale for write-heavy workloads and that they had to move those workloads to Cosmos DB. For the rest of the remaining mostly-read workload they have a single primary with 50 read replicas.

Comment by dewey 3 days ago

The point is that Postgres scales a very long way. Once you arrive at OpenAI / ChatGPT scale there's no shame in reaching for a dedicated queuing system.

Comment by tomnipotent 3 days ago

> scales a very long way

This ignores the fine print. It scales a very long way under specific circumstances with specific workloads. The more write-heavy your workload the less eloquently Postgres scales.

For OpenAI's use case you could swap Postgres with MySQL and it would scale just as well.

Comment by dewey 3 days ago

We are talking about Postgres as a queue here, which is heavy on tiny writes, reads and churning tables. The point is just that a RDMS like Postgres scales very far as a queue, it’s not a fight if Postgres or MySQL.

Nobody is arguing for using it for everything and forever but for most company sizes it’s perfectly fine to not reach for a dedicated queuing tool if you already have PG running.

Comment by tomnipotent 3 days ago

> on tiny writes

Postgres doesn't do "tiny writes" - it writes whole pages multiple times on every update even if you're only changing 4 bytes, combined with even more writes later on when vacuuming tables. This is one of the reasons why the historical advice was to not build high-volume queues on top of Postgres and why "oh look another post about queues on Postgres" keeps soliciting comments like this.

Comment by ballon_monkey 3 days ago

If your DB is write heavy, tune it for writes...

Comment by tomnipotent 3 days ago

You can't tune your way out of this constraint. The heap table and MVCC implementation put a hard ceiling on what can be accomplished without reaching for another tool.

Comment by bel8 3 days ago

I have the impression that SolidQueue uses whatever your rails app is using by default. That can be SQLite, PostgreSQL, MySQL, etc.

So I don't think they changed to PostgreSQL per se.

Comment by dewey 3 days ago

Correct, what I meant was that SolidQueue was the first database-backed default adapter over the usually common Redis + Sidekiq combo for ActiveJob.

Comment by sorentwo 3 days 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.

Comment by sebmellen 3 days ago

Very interesting, I’d never heard of Oban before, and that’s after a fair amount of research into background job queueing systems. Thanks for posting!

Comment by NightMKoder 3 days ago

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.

Comment by richwater 3 days ago

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

Comment by 0x457 2 days ago

And every time it is "FOR UPDATE SKIP LOCKED" or something else super obvious that should have been a starting point when you pick PG for your queue.

Comment by keeganpoppen 3 days ago

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.

Comment by mlnj 3 days ago

These days, most of them are from the DBOS folks. :)

Comment by tonyhb 3 days ago

indeed, every ~week with essentially the same thing: skip locked.

Comment by hiyer 3 days ago

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.

Comment by LtdJorge 3 days ago

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

Comment by rtpg 3 days ago

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

Comment by shakow 3 days ago

Running it at a couple dozens jobs per second, and we're happy with it now that we polished the cutting edges (virtually the same thing as in TFA).

Comment by hmaxdml 3 days ago

What's reasonable? DBOS has users running queues at millions of tasks per hour.

For fair queuing you can have partitioned queues where only active partitions consume resources

Comment by rcleveng 3 days ago

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

Comment by everfrustrated 3 days ago

[flagged]