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

Choose Postgres queue technology

adriano.fyi

Post reply on HN
Print view View original

101–110 of 369 posts

Re: Choose Postgres queue technology

#101

Post by what-no-tests »

beachy wrote: post #92
what-no-tests wrote: post #88

Really unimpressed with so many people thinking it's OK to shoehorn their database as a message queue. Use the right tool for the job.

Our industry is full of shysters pushing their own technology. Time and again, it turns out that a RDBMS will handle that job just fine. That's really the premise of this article. So please, go on and back up your bold statements with some specifics. Why specifically is it not OK to use a database as a message queue?

No time at the moment to break it all down, but here's a previous discussion thread on a similar topic.

https://news.ycombinator.com/item?id=27483003

Re: Choose Postgres queue technology

#102

Post by MattIPv4 »

djbusby wrote: post #90
MattIPv4 wrote: post #81

We process around 1 million events a day using a queue like this in Postgres, and have processed over 400 million events since the system this is used in went live. Only issue we've had was slow queries due to the table size, as we keep an archive of all the events processed, but some scheduled vacuums every so often kept that under control.

Active Queue table and then archive jobs to a JobDone table? I do that. Queue table is small but archive goes back many months

We just have a single table, with a column indicating if the job has been taken by a worker or not. Probably could get a bit more performance out of it by splitting into two tables, but it works as it is for now.

Re: Choose Postgres queue technology

#103

Post by vore »

nsonha wrote: post #76
vore wrote: post #71

Earlier quoted context omitted.

It seems strictly worse to use ORDER BY in this case, since if you're using SKIP LOCKED you should be doing parallel processing anyway, and if you're doing parallel processing, ordering is already going out the window.

Parallel or not, the order is of importance in any queue system.

You have no ordering guarantees, so how can order be important? If 4 work items are scheduled on 4 independent workers, you have no guarantee which will start first or finish first.

Re: Choose Postgres queue technology

#104

Post by paulddraper »

djbusby wrote: post #90
MattIPv4 wrote: post #81

We process around 1 million events a day using a queue like this in Postgres, and have processed over 400 million events since the system this is used in went live. Only issue we've had was slow queries due to the table size, as we keep an archive of all the events processed, but some scheduled vacuums every so often kept that under control.

Active Queue table and then archive jobs to a JobDone table? I do that. Queue table is small but archive goes back many months

In modern PG you can use partitioned table for a similar effect.

Re: Choose Postgres queue technology

#105

Post by andrewstuart »

MattIPv4 wrote: post #81

We process around 1 million events a day using a queue like this in Postgres, and have processed over 400 million events since the system this is used in went live. Only issue we've had was slow queries due to the table size, as we keep an archive of all the events processed, but some scheduled vacuums every so often kept that under control.

Partial indexes might help.

Re: Choose Postgres queue technology

#106

Post by sarchertech »

nsonha wrote: post #76
vore wrote: post #71

Earlier quoted context omitted.

It seems strictly worse to use ORDER BY in this case, since if you're using SKIP LOCKED you should be doing parallel processing anyway, and if you're doing parallel processing, ordering is already going out the window.

Parallel or not, the order is of importance in any queue system.

Unless you can guarantee that the processing time of each job is exactly the same, if you have multiple workers processing the same queue, you can’t order anything except the start time.

You can use locks to effectively break the queue into sub queues so that each sub queue is only being processed by 1 worker. Then you can order that sub queue.

Re: Choose Postgres queue technology

#108

Post by Xeoncross »

> As an industry, we’ve become absolutely obsessed with “scale”.

I wish the industry was even half as concerned with efficiency as it was with scale. Bitcoin? Electron? 5MB web pages? 5/10/25GB downloads to run 20yr old CD-sized games on modern software?

Re: Choose Postgres queue technology

#109

Post by dminor »

mbb70 wrote: post #43
matsemann wrote: post #3

Earlier quoted context omitted.

I've done even simpler without locks (as no transaction logic), where I select a row, and then try to update a field about it being taken. If 1 row is affected, it's mine. If 0, someone else did it before me and I select a new row. I've used this for tasks at big organizations without issue. No need for any special deployments or new infra. Just spin up a few worker threads in your app. Perhaps a thread to reset aban…

I've done the same with MongoDB with findOneAndModify, simple and solid

Agenda uses this, and we found the hard way on mongo 4 that it can lead to mongo spinning the CPU at 100% if it gets too many at once. No idea if they've fixed it in later versions.

Re: Choose Postgres queue technology

#110

Post by thom »

surprisetalk wrote: post #55
aduffy wrote: post #2

For several projects I’ve opted for the even dumber approach, that works out of the box with every ORM/Query DSL framework in every language: using a normal table with SELECT FOR UPDATE SKIP LOCKED https://www.pgcasts.com/episodes/the-skip-locked-feature-in-... It’s not “web scale” but it easily extends to several thousand background jobs in my experience

I recently published a manifesto and code snippets for exactly this in Postgres! delete from task where task_id in ( select task_id from task order by random() -- use tablesample for better performance for update skip locked limit 1 ) returning task_id, task_type, params::jsonb as params [1] https://taylor.town/pg-task

Presumably it's okay that this loses work if your task runner has an error?
Post reply on HN