We use exactly this for windmill (OSS Retool alternative + modern airflow) and run benchmarks everyday. On a modest github CI instance where one windmill worker and postgres run as containers, our benchmarks run at 1200jobs/s. Workers can be added and it will scale gracefully up to 5000jobs/s. We are exploring using Citus to cross the barrier of 5000j/s on our multi-tenant instance.
Choose Postgres queue technology
41–50 of 369 posts
Re: Choose Postgres queue technology
#42I'm always surprised that when I see people talk about queues I never see anyone mention beanstalkd. I've been using it for basically everything for 10 years and it's solid as a rock, incredibly simple and requires basically no maintenance. It Just Works™
Re: Choose Postgres queue technology
#43For 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'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
Re: Choose Postgres queue technology
#44I've implemented queues with tables in RDBMSs a few times and it's always great and usually all you need. Worried about future scale? Make a class to wrapper the queue with a decent interface and swap it for RabbitMQ or whatever you want down the road. Implementation stays opaque and you have an easy upgrade path later on.
Re: Choose Postgres queue technology
#45Temporal, which AFAIK was made by the Uber Cadence team, which was also involved in SQS, uses postgres as a backend.
I used it for a web automation system for an accounting client (automatically read files from a network share, lookup the clients on a database, submit the documents to government websites, using headless browsers, and put the resulting files in the directory). It allows for completely effortless deterministic programs that call workers that run the non deterministic code, with built in configurable retries (react to certain exception type, exponential back off) so you can write code that works almost like there were no issues with api connections, filesystem, etc.
This code has been running for 5 or more years, with barely any maintenance, with 0 issues so far. It keeps everything in postgres, so even full reboots and crashes have no impact, it will just move the work back to the queue and it will run when there's an available worker.
Re: Choose Postgres queue technology
#46USE. 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.)
Pretty common advice for scaling Postgres is to deploy pgbouncer in transaction mode in front of it to handle connection pooling.
Advisory locks don’t work in this setup (and will start behaving in strange ways if you do try to use them.) Something to consider if you go this route.
Re: Choose Postgres queue technology
#47You don't even need a database to make a message queue. The Linux file system makes a perfectly good basis for a message queue since file moves are atomic. My guess is that many people are implementing queuing mechanisms just for sending email. You can see how this works in Arnie SMTP buffer server, a super simple queue just for emails, no database at all, just the file system. https://github.com/bootrino/arniesmtpbu…
This is true, and I’ve worked on systems that use this, but it’s a lot more work than just a rename.
I’d recommend that, if you have a Postgres database already, definitely use that instead. Your queues will be transactional and they will get backed up when the rest of your database does.
Re: Choose Postgres queue technology
#48there's an important dimension of scalability that I think gets overlooked in a lot of these discussions about database-as-a-queue vs queue-system-as-a-queue: are you queuing jobs , or are you queuing messages ? that's a fuzzy distinction, so somewhat equivalently, what's the expected time it takes for a worker to process a given queue item? at one end, an item on the queue may take several seconds to a minute or lon…
I’ve used PG as a message queue, actually it was used as a transactional front end to Kafka; we’d push messages to a PG table during a transaction, which would then be snarfed up to Kafka by a separate process after the transaction completed.
I’ve seen very high transaction rates from this arrangement, more than 20k messages/second.
Re: Choose Postgres queue technology
#49You don't even need a database to make a message queue. The Linux file system makes a perfectly good basis for a message queue since file moves are atomic. My guess is that many people are implementing queuing mechanisms just for sending email. You can see how this works in Arnie SMTP buffer server, a super simple queue just for emails, no database at all, just the file system. https://github.com/bootrino/arniesmtpbu…
This is true, and I’ve worked on systems that use this, but it’s a lot more work than just a rename. I’d recommend that, if you have a Postgres database already, definitely use that instead. Your queues will be transactional and they will get backed up when the rest of your database does.
>> but it’s a lot more work than just a rename
Such as?
Re: Choose Postgres queue technology
#50For 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
As I understand, with SKIP LOCKED rows would no longer be processed in-order?
article says he also uses "order by" clause, but I am wondering if it will severely limit throughput since all messages will need to be sorted on each lookup, but this probably can be solved by introducing index.