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

91–100 of 369 posts

Re: Choose Postgres queue technology

#91

Post by mianos »

Skype used postgres as queue with a small plugin to process all their CDR many years ago. I have no idea if it used these days but it was 'web scale', 10 years ago. Just working, while people on the internet argued about using a database as a queue is an anti-pattern.

Having transactions is quite handy.

https://wiki.postgresql.org/wiki/SkyTools

I did a few talks on this at Sydpy as I used it at work quite a bit. It's handy when you already have postgresql running well and supported.

This said, I'd use a dedicated queue these days. Anything but RabbitMQ.

Re: Choose Postgres queue technology

#92

Post by beachy »

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?

Re: Choose Postgres queue technology

#93

Post by pyrophane »

I don't have a problem with this approach at all, but I would argue that for many use cases a redis backed job library like Celery or Sidekiq might be even easier, especially if you are on a cloud provider that offers managed redis.

Re: Choose Postgres queue technology

#94

Post by [deleted] »

Scubabear68 wrote: post #14
pphysch wrote: post #9

Another point is that task queue technology is highly fungible. There's nothing stopping you from starting with cron, adding in Postgres/Redis, then graduating to Kafka or something as need arises. And running all three in parallel, with different jobs in each. I would be surprised if the average Kafka shop didn't also have a bunch of random cron jobs doing things that could be implemented on Kafka or vice versa. At…

I agree with all of this except for the part about “cron”. Cron jobs in my experience quickly become hard to manage and effectively invisible over time. Use almost anything else to manage job scheduling….

[deleted]

Re: Choose Postgres queue technology

#95

Post by ttfkam »

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

I think you mean findOneAndUpdate, and while simple, I wouldn't call it solid.

https://stackoverflow.com/a/76821755

Re: Choose Postgres queue technology

#96

Post by jgavris »

I wrote a hacky version of SKIP LOCKED using advisory locks and a recursive CTE before it was released for a job queue. It worked splendidly, along with the transactional semantics of a proper database. I’m surprised more systems don’t realize they need it.

Re: Choose Postgres queue technology

#97

Post by pphysch »

dharmab wrote: post #15
Scubabear68 wrote: post #14

Earlier quoted context omitted.

I agree with all of this except for the part about “cron”. Cron jobs in my experience quickly become hard to manage and effectively invisible over time. Use almost anything else to manage job scheduling….

I'm not sure if they literally mean crond, or something vaguely cron-like but easier to manage like systemd timers.

Yeah either or. I don't like how hard they are to manage compared to a proper queue, but they do exist on production systems.

Re: Choose Postgres queue technology

#98

Post by codeadict »

This is exactly what the Oban https://getoban.pro/ Elixir library uses and combining postgres plus actors for queues scales pretty great for 90% of the needs out there. I have used it at my last few jobs at pretty decent scale and would take it over 10 years using Celery to manage queues + supervisord, setting up RabbitMQ or Redis. Its so simple you only need Elixir and Postgres and not 3 or 4 infrastructure pieces to manage a queue.

Re: Choose Postgres queue technology

#99

Post by liminal »

My main issue with pretty much all queue approaches is that they don't work across platforms. They are built for one technology stack, be it Python/NodeJS/etc. This is fine if you've only got one stack, but in a microservices world it doesn't work where jobs can span multiple systems. You might be able to find some abandoned library that supports that queue tech on the other platforms you need, but now you've basically become a queue tech maintainer.

Re: Choose Postgres queue technology

#100

Post by paulddraper »

sorentwo wrote: post #63
paulddraper wrote: post #25

USE. ADVISORY. LOCKS. Do not use SKIP LOCKED unless it is a toy/low throughout. Row locks require transactions and disk writes. Advisory locks require neither. (However, you do have to stay inside the configurable memory budget.)

To do anything safe and interesting you’ll need transactions. Using SKIP LOCKED won’t be your bottleneck, your application will. Job queues are about side effects and the rest of your application needs to keep up. Oban is able to run over 1m jobs a minute, and the ultimate bottleneck is throttling in application code to prevent thrashing the database: https://getoban.pro/articles/one-million-jobs-a-minute-with-...

That's true.

However in the empty poll case, you can avoid a transaction.

Post reply on HN