que

I think for me the problem with every single new PG queue is that it seems like everyone and their mother thinks they need to reinvent this specific wheel for some reason and the flavor of the day doesn’t often bring much new to the space. Probably because it's 1. Pretty easy to understand and grok the problem space 2. Scratching the programmer itch of wanting something super generic that you can reuse all over the place 3. Doable with a modest effort over a reasonable scope of time 4. Built on rock solid internals (Postgres) with specific guarantees that you can lean on Here's 7 of them just right quick: - https://github.com/timgit/pg-boss - https://github.com/queueclassic/queue_classic - https://github.com/florentx/pgqueue - https://github.com/mbreit/pg_jobs - https://github.com/graphile/worker - https://github.com/pgq/pgq - https://git

Oban is fantastic and has been a huge source of inspiration for us, showing what is possible in this space. In fact I think during my time at Distru we were one of Parker's first customers with Oban Web / Pro :) We've also had a lot of experience with with other libraries like Que ( https://github.com/que-rb/que ) and Sidekiq (https://sidekiq.org/) which have certainly influenced us over the years.

> Can you define "low throughput"? IDK maybe <1000 messages per minute Not saying SKIP LOCKED can't work with that many. But you'll probably want to do something with lower overhead. FWIW, Que uses advisory locks [1] [1] https://github.com/que-rb/que

slower to run, but when you keep the postgres connection open you will know that the job is still running, while with for update skip locked you would need to have a status and a job_timeout basically. so pg_try_advisory_lock/pg_advisory_unlock can lock over transactions while for update skip locked can't, thus you would either need to keep a transaction open or use status+job_timeout (and in postgres you should not use long transactions) basically we use c#, but we looked into https://github.com/que-rb/que which uses advisory_locks, since our jobs take like 1 min to 2 hours it was a no-brainer to use advisory_locks. it's just not the best thing if you have thousands of fast jobs per second, but for a more moderate queue where you have like 10000 jobs per minute/10 minutes/30 minutes and they take like 1 min to 2 hours its fine. we also d

Few things. 1. The main downside to using PostgreSQL as a pub/sub bus with LISTEN/NOTIFY is that LISTEN is a session feature, making it incompatible with statement level connection pooling. 2. If you are going to do this use advisory locks [0]. Other forms of explicit locking put more pressure on the database while advisory locks are deliberately very lightweight. My favorite example implementation is que [1] which is ported to several languages. [0] https://www.postgresql.org/docs/current/explicit-locking.htm... [1] https://github.com/que-rb/que

(not sure why this comment was dead, I vouched for it) There are a lot of ways to implement a queue in an RDBMS and a lot of those ways are naive to locking behavior. That said, with PostgreSQL specifically, there are some techniques that result in an efficient queue without locking problems. The article doesn't really talk about their implementation so we can't know what they did, but one open source example is Que[1]. Que uses a combination of advisory locking rather than row-level locks and notification channels to great effect, as you can read in the README. [1]: https://github.com/que-rb/que

https://github.com/que-rb/que This one seems to be the most performant. By a lot too, from my understanding (haven't ran any benchmark myself, but the readme shows some good postgres knowledge)

Implementations that use advisory locks like https://github.com/que-rb/que are much more efficient (atleast when I last tested) and will easily reach 10k job/s on even very modest hardware. There is a Go port of Que but you can also easily port it to any language you like. I have a currently non-OSS implementation in Rust that I might OSS someday when I have time to clean it up.

It’s also possible to use advisory locks to implement a job queue in Postgres. See e.g. Que[1]. Note there are a fair number of corner cases, so studying Que is wise if trying to implement something like this, as well as some (a bit older) elaboration[2]. We implemented a similar design to Que for a specific use case in our application that has a known low volume of jobs and for a variety of reasons benefits from this design over other solutions. [1]: https://github.com/que-rb/que [2]: https://brandur.org/postgres-queues

Hopefully one day projects like que[1][2] will become stable and battle-tested enough to use in a production environment. Until then I'll be using something like sidekiq (if you're going for a client-side job queue, eg: the clients don't really know about each other and only have rate-limiting, not true throttling). With Postgres you also need to worry about high churn, especially since you are creating/locking/deleting rows constantly. This can be alleviated through a variety of means, of which personally I would use per-day table partitioning and truncate older partitions on a cron, not to mention the sharp increase in database connections to the host now required. Ignoring the literal elephant in the room of synced writes to the store. Redis can be used quite effectively in a blocking manner with RPOPLPUSH/LMOVE(6.2+) for a reliable qu

For rails I’ve used que in the past, which is a job queue adapter for Postgres. It supports activejob. https://github.com/que-rb/que

I used Que [0] in a number of projects in the last few years that had stronger consistency demands. It can plug into ActiveJob. Those projects are still humming along without trouble today. [0]: https://github.com/que-rb/que

As a Kafka alternative, has anyone attempted to use PostgreSQL logical replication with table partitioning for async service communication? Proof of concept (with diagrams in the comments): https://gist.github.com/shuber/8e53d42d0de40e90edaf4fb182b59... Services would commit messages to their own databases along with the rest of their data (with the same transactional guarantees) and then messages are "realtime" replicated (with all of its features and guarantees) to the receiving service's database where their workers (e.g. https://github.com/que-rb/que, skip locked polling, etc) are waiting to respond by inserting messages into their database to be replicated back. Throw in a trigger to automatically acknowledge/cleanup/notify messages and I think we've got something that resembles a queue? Maybe make that same trigger match incoming me

It helps to reference Que's schema [1] and source code to explain this further. But I'm also going from memory so it's possible I will miss some details :) * If the entire worker process dies, then it will lose its Postgres connection which is holding an advisory lock on the jobs being worked. This releases those jobs to be worked by another worker. I don't recall how the built-in retry & back-off mechanisms work in this scenario. This advisory lock is indeed held for the entire time the jobs are being worked on, but only from a single supervisor connection (rather than one connection per job). * If the job thread crashes, the worker supervisor catches this and the job is marked for a retry at a later time. [1]: https://github.com/que-rb/que/blob/master/lib/que/migrations...

Is this what you’re talking about? https://github.com/que-rb/que