Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Making Postgres queues scale

dbos.dev

Post reply on HN
Print view View original

31–36 of 36 posts

Re: Making Postgres queues scale

#31

Post by dewey »

tomnipotent wrote: post #17
dewey wrote: post #15

Earlier quoted context omitted.

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.

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

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.

Re: Making Postgres queues scale

#32

Post by shakow »

rtpg wrote: post #23

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 co…

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

Re: Making Postgres queues scale

#33

Post by atombender »

sgt wrote: post #30
atombender wrote: post #22

Earlier quoted context omitted.

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.

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

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.

Re: Making Postgres queues scale

#34

Post by tomnipotent »

dewey wrote: post #31
tomnipotent wrote: post #17

Earlier quoted context omitted.

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

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.

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

Re: Making Postgres queues scale

#35

Post by 0x457 »

richwater wrote: post #4

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

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.

Re: Making Postgres queues scale

#36

Post by mattlong »

hmaxdml wrote: post #24
rtpg wrote: post #23

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 co…

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

Can you say more on how you support fair queueing with partitioned queues? Very top of mind for me right now!
Post reply on HN