Controversial database topics are a guaranteed success on reddit, because everyone has an opinion on those topics. More importantly, many people have a dogmatic opinion, which always triggers more debate than pragmatism.
So, recently, I posted a link to an older article titled The Database As Queue Anti-Pattern by Mike Hadlow, and it got decent results on /r/programming:
.
Mike’s post was pretty much against all sorts of queueing in the database, which matches the opinions I have heard from a couple of JavaZone speakers in a recent discussion, who all agreed that messaging in the database is “evil.”
… and I’m saying: No. Messaging in the database is not an anti-pattern, it is (can be) totally OK. Let’s consider why:
KISS and YAGNI
First off, if you don’t plan on deploying thousands of message types and millions of messages per hour, then you might have a very simple messaging problem. Since you’re already using an RDBMS, using that RDBMS also for messaging is definitely an option.
Obviously, many of you will now think:
If all you have is a hammer, everything looks like a nail
… and you’re right! But guess what, the reasons for only having a hammer can be any of:
- You don’t have the time to equip with a sophisticated tool box
- You don’t have the money to equip with a sophisticated tool box
- You actually don’t really need the sophisticated tool box
Think about these arguments. Yes, the solution might not be perfect, or even ugly…
But we’re engineers and as such, we’re not here to debate perfection. We’re here to deliver value to our customers and if we can get the job done with the hammer, why not just forget our vanity and simply use that hammer to get the job done.
Transactional queues
In a perfect world, queues are transactional and guarantee (to the extent allowed by underlying theory) atomic message delivery or failure – something that we’ve been taking for granted with JMS forever.
At GeeCON Krakow, I had a very interesting discussion with Konrad Malawski regarding his talk about Akka Persistence. If we remove all the hype and buzzwords (e.g. reactive programming, etc.) from Akka, we can see that Akka is just a proprietary alternative to JMS, which looks more modern but is lacking tons of features that we’re used to having in JMS (e.g. like transactional queue persistence).
One of the interesting aspects of that discussion with Konrad Malawski is the fact that a 100% message delivery guarantee is a myth (details here). Which leads to the conclusion:
Messaging is really hard
It is, indeed! So if you really think you need to embed a sophisticated MQ system, beware of the fact that you will have to learn how it really works and how to correctly operate it.
If you’re using RDBMS-backed queues, you can get rid of this additional transactional complexity, because your queue operations participate in the transactions that you already have with your database. You get ACID for free!
No additional operations efforts
What developers very often underestimate (we can’t say this enough) are the costs incurring to your operations team when you add new external systems to yours.
Having just one simple RDBMS (and your own application) is a very very lean and simple architecture. Having an RDBMS, an MQ, and your application is already more complex.
There are a lot of excellent DBA out there who know what they’re doing when operating productive databases. Finding excellent “MQA” is much harder.
If you’re using Oracle: Use Oracle AQ
Oracle has a very sophisticated built-in queueing API called Oracle AQ, which can interoperate with JMS.
Queues in AQ are essentially just tables that contain a serialised version of your message type. If you’re using jOOQ, we’ve blogged about how to integrate Oracle AQ with jOOQ, recently.
RDBMS-centric applications can be much easier
We’ve blogged about that before as well: Why Your Boring Data Will Outlast Your Sexy New Technology.
Your data might just survive your application. Consider Paypal replacing Java with JavaScript (it could also have gone the other way round). In the end, however, do you think that Paypal also replaced all their databases? I don’t. Migrating from Oracle to DB2 (different vendor), or from Oracle to MongoDB (different DBMS type) is mostly motivated by political decisions rather than technical ones. Specifically, people don’t migrate from RDBMS to NoSQL databases entirely. They usually just implement a specific domain with NoSQL (e.g. document storage, or graph traversal)
Assuming that the above really applies to you (it may, of course, not apply): If your RDBMS is in the middle of your system, then running queues in your RDBMS to communicate between system components is quite an obvious choice, isn’t it? All system parts are already connected to the database. Why not keep it that way?
Conclusion
The arguments listed here are all pretty obvious and pragmatic. At some point, they no longer hold true, as your messaging demands are really big enough to justify the integration with a sophisticated MQ system.
But many people have strong opinions about the “hammer / nail” argument. Those opinions may be correct but premature. Very often in software engineering, it is entirely acceptable and sufficient to work with just one tool. The hammer of software: The RDBMS.
I am glad you addressed this topic. I’ve seen this use case on multiple projects and all were facing the same challenges:
1. Messaging queues have discarding policy and throttling. If the producing rate exceeds the consuming one then the message table will grow larger and larger.
2. A table is either optimized for read or for write. You can’t have both. The more indexes you add, the better the read the worse the write/delete. With messaging queues you don’t have to balance the read/write operations.
3. Processing one item at a time is going to be resource-intensive. Once you start processing an item you have to reserve it, so other concurrent batch processors won’t try to process the same rows. But transactions are isolated so somehow you want others to see you reserved a row, while you want Acid for the actual processing result.
4. Large batch require longer transactions. The longer the transaction the more the time the locks are held.
There are so many quality messaging queueing systems:
– JMS (ActiveMQ, OpenMQ)
– AMQP (RabbitMQ)
– Low-level-queuing technologies (ZeroMQ)
– even High frequency trading queues (Java Chronicle)
While ZeroMQ/Java Chronicle are niche technologies, JMS and AMQP are straight forward, it’s even easier to use them than any RDBMS.
In conclusion, RDBMS is great for storing/querying data but it was never conceived to work as a persisted queue. It’s optimized for relation algebra not for the queueing theory challenges.
You’re right, there are some caveats to doing the kind of locking required for queue implementations. Oracle AQ uses a vendor-specific extension to the
FOR UPDATEclause: TheFOR UPDATE SKIP LOCKEDclause. This allows for non-blocking pessimistic locking of records – quite an interesting feature with subtle implications, because it has a similar effect like theWHEREclause filtering out records, but it is semantically applied only after theORDER BYclause.Now that you say so, I remember having experienced a heavy productive issue with contention and concurrency related to AQ running out of control in Oracle 11g, which was fixed only in 11gR2 (if I’m not mistaken).
Anyway, as always, why not reply to this post with an answer-post :-) I’m particularly interested in the implications of queueing theory challenges on such RDBMS-backed implementations.
We developers are very optimistic persons. I’ve seen PhD architects advancing such proposals and right before launching to production, they kinda started to agree with my non-RDBMS queueing warning.
I’ve been on that road myself. I also said that it cannot be that bad and I can surely optimize it later, but after failing and several refactoring sessions, I got the chance of doing it right on a 2.0 product version.
The second time, the DB was replaced by JMS. Most of the time, this kind of tasks are asynchronous by nature, so adding concurrent consumers was a breeze. We even auto-scaled it in the cloud, but that’s a different story.
While I was doing this refactoring, some other remote team started using RDBMS for queueing for some other task, and when I hat the chance to review that part we were already in pre-prodoction mode.
This subject requires more than one post. A book chapter is more appropriate.
Meanwhile I stumbled on this blog post. It’s a very well written post and I totally agree with it.
Well, one post would already be very interesting! :-) I mean, just an experience report would be awesome
Spring Integration offers a few interesting features to use the database as your queue’s message store.
For instance, it is possible to define a channel with bounded queue that stores its messages in a message store, where the message store is any kind of database.
And Spring integration can handle all the complexity of reading and writing into the queue for you.
I gave it a try at the company I work for, although at the end we decided to use RabbitMQ as our persistence queue.
Very interesting. Will need to read up on that!