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

21–30 of 36 posts

Re: Making Postgres queues scale

#21

Post by KraftyOne »

atombender wrote: post #12

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

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.

Re: Making Postgres queues scale

#22

Post by atombender »

sgt wrote: post #16
atombender wrote: post #12

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

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

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.

Re: Making Postgres queues scale

#23

Post by 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

Re: Making Postgres queues scale

#24

Post by hmaxdml »

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

Re: Making Postgres queues scale

#25

Post by danielheath »

KraftyOne wrote: post #21
atombender wrote: post #12

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

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

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.

Re: Making Postgres queues scale

#26

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

Re: Making Postgres queues scale

#27

Post by sebmellen »

sorentwo wrote: post #3

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.

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!

Re: Making Postgres queues scale

#28

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

Re: Making Postgres queues scale

#29

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

Re: Making Postgres queues scale

#30

Post by sgt »

atombender wrote: post #22
sgt wrote: post #16

Earlier quoted context omitted.

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

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?
Post reply on HN