Message Queue. Database vs Dedicated MQ

Question

I am after advice regarding message queueing. We have requirements for "jobs" to be posted to a message queue.

The original suggestion was just to use a SQL Server instance and process messages from that. Everything I have read on the internet suggests that using a database for a Message Queue isn't a scalable solution. For this reason, the idea of using RabbitMQ or some other 3rd party MQ was suggested.

The other thing to take into account is that the requirement for "job processing" won't be any lower than 30 seconds, so the process that does the job will poll the database every 30 seconds. To me, this doesn't seem so bad and would probably work ok without adding a large load to the Database.

We already have a Database in place on our clients we could use for this so it won't add much extra support required to our clients, whereas if we added a 3rd party MQ there would be extra support for network configuration etc, which would be considerable given there is a lot of users.

The other option I was considering was allowing users to choose between either. If they are a small user then the Sql Server solution will be ok, but if they are a larger user then we allow them to configure a 3rd party MQ solution.

I'm not sold on any solution, I am wondering if anyone has anything I should consider or advice.

Answer 1

Everything I have read on the internet suggests that using a database for a Message Queue isn't a scalable solution.

The reality frequently ignored by the "don't use X because it doesn't scale" (link contains language some may find objectionable) crowd is that scale isn't always important. I'd go so far as to say that if you look at every application on the face of the planet in aggregate, scale is rarely important.

Your comment cites a rate of 20,000 messages daily, which means you'd be looking at needing to support an average rate of 0.23 messages per second (one every 4.3 seconds). If your project turns out to be two orders of magnitude more successful than you expected, your requirements jump to processing 23 messages per second, which is a task I'd be very comfortable giving to my four-year-old mobile phone or a Raspberry Pi. This still isn't a high-scale application even if you tack another couple of orders of magnitude on top of that.

I've watched (fortunately, from the sidelines) projects end badly because they either spent too much time too early obsessing over scale that wasn't going to happen or no time on it whatsoever and got crushed by scale that eventually did. Like everything else, there's a happy medium. If you think large scaling for your application is a realistic possibility, it shouldn't be difficult to make a business case for doing the small amount of extra work to build in enough abstraction around non-scalable parts that are inexpensive to deploy now. Doing this means that later, if a need for scale should arise, you have a way (and possibly the revenue) to do wholesale replacement of those parts without having to re-think the entire system.

While your application's message volume isn't going to make a database or a message queueing system break a sweat even on modest hardware, you probably have other requirements for how your message transactions are handled that make one or the other a better choice. Those requirements are what you should be evaluating.

Answer 2

Message Queues really come into their own when you have many of them and route messages between them, fanout to more than one consumer etc.

If you just have a single 'job queue' of stuff you want to process 'off line' then an SQL table will do just fine.

Don't forget to ensure you have some way of marking jobs in-progress, cleaning out old ones and alerting when the system stops. But for a single queue manually managing these things will be less work than maintaining a separate queuing solution.

Answer 3

As other have mentioned scale is probably not important here. The problem with using two different storage mechanisms is transactional integritety.

If going with a dedicated message queue you need to choose one of the following in case of failure.

1. Allow that data can be on mb but not in db
2. Allow that data can be in db but not in mb
3. Setup two phase commit or distributed transactions between db an mq which can be complicated

All these problems go away if you only save data in one place using a normal transaction. For this reason using db as a task queue is a perfectly fine solution.

Answer 4

For practicality, main reasons for me to use a message queue are:

• I didn't have to reinvent the wheel. Designing even the simplest message queue using database requires at least a couple of hours of thinking, a few hours of coding, and so on. Compared to implementing a message queue, the time required to configure and/or automate configuration is trivial
• Message queues have nice and clear interface for producer and consumer. This is probably the most important thing in writing application. Without the interface, message queues are basically just a collection of data
• Message queues give more features that might be needed in the future

Regarding allowing users to choose, that's really an implementation detail that users shouldn't care about. Users should get the same interface and there should be no difference to users if database or message queue is used. Once a single design is set, a probable choice for users is how many nodes need to be deployed to accommodate their needs.

Answer 5

I have created an Mssql message queue solution which can handle 20k operations per second, according to a performance test, and we need 10/sec most of the time. I think that the fact that you can actually have priority built in is a feature that the dedicated message queue lack. And this was a major requirement in my case.