> Never use your primary DB as a queue. Using a separate Postgres instance can work if you over-provision capacity and don't need to maximize throughput, and a Redis-based solution can work if you don't need high availability and can tolerate some messages lost if something goes wrong.
That is a broad generalization that assumes most applications are operating at mega scale. The benefits of simplified dependencies (a single database instance), transactional guarantees (a single database instance) and persistence (not using Redis) far outweigh the eventual possibility that the queue will place too large a load on your database.
As the author of Oban[0] (an PG backed persistent queue in Elixir) I'm definitely biased. However, the level of adoption in the Elixir community seems to signal that a lot of companies favor simplicity and safety over a possible scale issue down the road. The primary application I work on processes ~500k-1m jobs a day and the queue overhead is virtually invisible.
You actually bring up another point that in my mind is yet another argument against using your primary DB as a queue, so I'm sure I am also biased from being burned in the past :)
But in a past company I worked at, the company started out thinking sure just throw the queue in the primary db for simplicity. Eventually our slow query logs and db performance monitoring tools were showing that ~40% of the db load was due to the queue inserts and queries. It may be that it was doing something incredibly inefficient and unnecessary in the particular library we were using but we did look into it pretty thoroughly, this was a few years ago though so I don't recall all of the details. And that was at normal operation, then we ran into an issue that basically brought our site down when queues backed up.
At that point it was definitely time to split out the queue, and when we did it we realized that we had implicitly been depending on transactional consistency between the queue and the app data in a few places, which was then extra work to track down and fix these types of issues. This is IMO a code smell as well in general - your data and your infrastructure should ideally not be so tightly coupled.
Managed databases are so easy to set up these days, I would still definitely recommend a separate instance for the queue vs the primary db from day 1 in any new app I build. If you do want to combine them to save money on infra, use a separate logical db and separate connection pool and everything so that it's easy to split out in the future.
There's also another benefit around using of as a queue, you can just publish your messages from within the same transaction you are using on the request, and that's nice, all or nothing..
I will dig on your elixir queue, just build my own some days ago (mostly for fun) but also for solving some limitations in rabbit (mostly time based scheduling at short periods of time.. and control the throughput (to solve some rate limits).
The main feature is that it's built with pl/pgsql and allows to integrate so well with the rest of server backend (publishing jobs from triggers... ) Also listening on results with pg_notify
That is a broad generalization that assumes most applications are operating at mega scale. The benefits of simplified dependencies (a single database instance), transactional guarantees (a single database instance) and persistence (not using Redis) far outweigh the eventual possibility that the queue will place too large a load on your database.
As the author of Oban[0] (an PG backed persistent queue in Elixir) I'm definitely biased. However, the level of adoption in the Elixir community seems to signal that a lot of companies favor simplicity and safety over a possible scale issue down the road. The primary application I work on processes ~500k-1m jobs a day and the queue overhead is virtually invisible.
[0] https://github.com/sorentwo/oban