zlacker

Strong disagree on using a database as a message queue. This article[0] covers many of the reasons why. Summary: additional application complexity and doesn't scale well with workers.

0. https://www.cloudamqp.com/blog/why-is-a-database-not-the-rig...

EDIT>> I am not suggesting people build their own rabbitmq infrastructure. Use a cloud service. The article is informational only.

>>daenz+(OP)
well rabbitmq is really really hard to setup correctly and stuff like priority, time based scheduling are not that much easier than rabbitmq. in fact a queue adds more complexity and it is not necessary until you outscale your database. not saying that rabbitmq might be a better fit, it's just not a good fit to start with. if you have a small team < 8 it's better to stay with as few things as possible and especially with things you know (well).

>>merb+t2
I wouldn't recommend setting up your own message queue infrastructure either. The cloudamqp link was more about the content than the product. All cloud providers come with extremely simple, scalable, and inexpensive message queue services with bindings to most languages.

A message queue is one of those things that is easy enough and worth the effort to do "right" early on, because it is not something you want to rip out and rewrite when you hit your scaling bottlenecks, given how critical it is and how many things it will end up touching.

>>daenz+b3
well most cloud queues do not support priorities you can only create multiple subscriptions and prefer the messages from the higher one. so in the end you would built a system on a system anyway. also these queues lock you in quite hardly (and do not work on premise)

Edit: also keep in mind most queues do not like "slow consumers" i.e. if your workload is bursty with long processing times, a database might be a better fit (i.e. rabbitmq does not like it)

Edit2: we implemented a queue with postgres since we need acid and having 10k inserts per second is highly unlikely since a customer upload takes longer than a second (we deal with files) we mostly have burst workloads short period of high volume followed by long pauses (i.e. nobody uploads stuff at night)

>>daenz+(OP)
The scalability problem is way overblown. Setting the correct isolation level and locking mode isn't that hard and a modern cloud-hosted PG/MySQL can push 10s of thousands of inserts/s no problem.

IMO, the downsides of hosting a queue inside your primary relational DB are very much outweighed by the downsides of 1) having to run a new piece of infra like rabbit and 2) having to coordinate consistency between your message queue and your relational DB(s)

>>daenz+(OP)
In the end, engineering is just whether the thing works with minimal maintenance. And ultimately, I've had great experiences using DBaaQ for low volume data movement. It works as a persistent queue with easy to do retries etc.

For high throughput (we had ad tech servers with 1E7 hits/s) we used a home-built low-latency queue that supported real time and persisted data. But for low throughput stuff, the DBaaQ worked fine.

And ultimately, maybe it was a lack of imagination on our part since Segment was successful with a mid-throughput DBaaQ https://segment.com/blog/introducing-centrifuge/

>>daenz+(OP)
It seems notable that this is a blog post from the perspective of rabbitmq authors, or at least the author of a book about it. It talks very vaguely about one potential implementation of a queue on postgres.

>>daenz+(OP)
I'm increasingly of the opinion that relational databases are absolutely the right way to build queue systems for most projects.

One of the biggest advantages comes when you start thinking about them in terms of transactions. Transactional guarantees are really useful here: guarantee that a message will be written to the queue if the transaction commits successfully, and guarantee that a message will NOT be written to the queue otherwise.

https://brandur.org/job-drain describes a great pattern for achieving that using PostgreSQL transactions.

>>simonw+m5
The transaction feature seems nice but how often is your application dropping queue messages because something happened between tx.commit() and queue.send(msg)? My experience has been that this is not an issue.

>>merb+44
>well most cloud queues do not support priorities you can only create multiple subscriptions

At least on GCP PubSub, a subscription is a separate concept from a topic/queue. If you want different priorities, you create multiple topics. You create multiple subscriptions when you want to fan out a single message to multiple workers. As far as I know, multiple subscriptions have nothing to do with priorities. Can you explain?

>>daenz+c7
If you're big enough to worry about the scalability of Postgres, you're big enough to experience this failure fairly often IMO.

>>peterh+f9
Scalability was the second of two concerns I listed. The first was additional application complexity that real message queues hide from you by virtue of being a system built for that usage pattern.

>you're big enough to experience this failure fairly often IMO

Please explain how? You would either have to suffer from frequent network connectivity issues that affects only your db and not your queue, or your process must be mysteriously dying in the microseconds between those 2 operations. Either of those cases are not something I would consider things that happen "fairly often," even if you were processing trillions of messages per day.

In my experience, the vast majority of message processing failures happen at the worker level.

>>daenz+(OP)
Basically all of those reasons are solved by using LISTEN/NOTIFY and FOR UPDATE SKIP LOCKED, which every queue built on pg will use.

>>daenz+c7
Oh that happens fairly often. In fact, some message will be lost every time your queue server reboots due to a power outage, PSU failure, kernel panic, OOM, etc. (Unless it spends almost all of its time idle in which case I guess no messages will be in flight)

You’re guaranteed to break the invariant sooner or later so you end up with all the usual complexity of keeping stuff in sync.

>>NavinF+ve
Your queue server rebooting is completely orthogonal to whether the application submitting the message can do so atomically or not. Use a cloud service if you care about durability.

Edit>> I see you edited your post after I responded. None of those scenarios qualify as "fairly often."

>>SahAss+zc
>all of those reasons are solved

How does it solve the additional code complexity problem?

>>daenz+c7
I am SUPER worried about this when it affects something really important, like getting the client/customer/merchant/cardholder their money. It seems like the world has moved on without me —- “ohh, three nines is enough”… “hrmmm maybe?…”

>>daenz+bi
Which complexity? Of running a SQL query on the same database you're already using, vs writing code to support some other new system?

>>KptMar+Ok
>Of running a SQL query on the same database you're already using

Go back and read OPs link. They create new SQL types, tables, triggers, and functions, with non-trivial and very unforgiving atomic logic. And every system that needs to read or write from this "db queue" needs to leverage specific queries. That's the complexity.

>vs writing code to support some other new system

You mean using a stable well maintained library with a clean and sensible interface to a queueing system? Yes, that is far more simple.

>>daenz+(OP)
Basically every piece of this article's criticism is wrong as applied to the source / link above.

- No need to poll the database table

- No table-level locks and manual handling: row locks used for handling the work in progress

- "Manual cleanup" -- uhhh

Etc.

>>daenz+1m
Not necessarily. Many languages like Java already have queuing libraries that operate on rdbms through JPA. So not even a single additional line needs to be written for this to work. We got ours working in a day and it works great. I don't know if other languages have these libraries but I'm inclined to believe they do (at least nodejs has).

>>option+Uw
Do you have to set up the triggers, tables, and procedures beforehand or how does that work?

>>daenz+f8
ah yeah topics... I basically meant topics. But having multiple topics for priority is still way harder than lets say the rabbitmq priority stuff or the postgres stuff.

>>daenz+2y
The libraries create their own tables, triggers etc during initialization.

>>daenz+5f
Wish we would stop saying "use a cloud service" for everything. People can and do operate their own hardware, manage their own databases, and build and maintain their own application stacks. I don't know how we got to this point of learned helplessness where now we just have to use cloud providers for everything.

>>daenz+J9
If the queue goes down you end up updating the db without enqueuing a job and now an engineer needs to go in and re enqueue the missing jobs manually.

>>VWWHFS+zl1
It's not learned helplessness to avoid re-inventing the wheel. At the end of the day, the goal is to deliver value to customers, not invent a queueing system, unless your company's product is queueing systems.

>>option+1e1
So your application needs privileged access (to create tables, triggers, etc) to the database in order to run? That's an anti-pattern. Your deployed application should only need least privileges possible. If you need to do extra things to your database, it should be done in migrations, which should be more privileged, but now you've decoupled the creation of these extra db objects from the library itself, meaning if the library changes, your migrations will not be in sync.

>>daenz+eq4
No... JPA writes out a file with the necessary DDL and the administrator runs it.

If this is insufficient for more complicated migrations, there's tooling to support it. e.g. Flyway.