Hacker News Reader

Comment by balfirevic | Hacker News Reader

I know what you meant by saying you use DB _instead_ of message queue but I think it kind of points to a broader point - message queue is a concept and the technology used to implement it is just implementation detail. It just turns out that databases have all kinds of useful and powerful operations that make implementing message queues relatively easy.

View on HN

If you don't need high throughput and otherwise don't have a reason to add a separate technology to the stack just to have a queue, SQL can make a great, simple and reliable queue!

* Postgresql and Oracle with skip locked

* MS SQL Server with readpast

* DB2 with skip locked data

If you use MySQL, it's going to be a bit more difficult.

I hadn't heard of skip locked before, and very much enjoyed this article walking through it for postgres: https://blog.2ndquadrant.com/what-is-select-skip-locked-for-...

Love the headline "Most work queue implementations in SQL are wrong". That kind of stuff makes me read an article...

This short screencast was also very helpful https://www.pgcasts.com/episodes/7/skip-locked/

Using the database as a queue is an anti-pattern. There used to be an entire blog series about why this is the case. However, I can't find it at the moment.

With MySQL, there's a fairly simple trick you can do to build a queue. Set a temp var to null, run an update with a select subquery to update state of next row(s), then select that var to get your queue row pk's.

Having been on the receiving end of more than a few apps where the devs used a database as a queue, I say "please don't".

Yes, Oracle's AQ product is built on top of an RDBMS. And yes, early versions of Microsoft's MSMQ used SQL Server. But most dev teams use the database as a shortcut and don't invest the effort in making their hacktastic DB queue work like a real product.

I have used DB as queues very successfully in multiple projects (as well as "real" queues in others), so your "please don't" is not very persuasive. Some actual argument, instead of content-free "hacktastic" vs. "real product" designations would be appreciated.

And you use "shortcut" like some kind of negative attribute. Of course they use DB as a shortcut, and they should - it works well and even has additional features (transactions with your primary store) that you wouldn't get with separate queue product.

Monitoring and administration are more difficult until you write them yourself, especially if you are splitting monolithic code. Many of the message queue products you download include these. Can your DB message queue also support different queue concepts and message routing?

In my case it supported multiple queues (also, a concept of hierarchical queues), but no message routing. As for administration and monitoring - you are correct, you don't get it out of the box. Well, you do get some basic administration for free from whoever administers your database :)

On the other hand, you can grow solution for those organically over time to fit your needs.

In a previous life I was on a team that moved a ecomm backend-Email Service Provider(ESP) from a postgres based queue to rabbitMQ. The motivation was voiced in language similar to yours, "hacktasic" was actually used more than once as a reason to replace the DB with rabbitMQ.

In the end, I'd say they were equally reliable, but from a data analyst's perspective I very much missed the postgres based queue. I think the mechanics inherent to a database solution prompted the original implementers to keep messages around over time vs the mechanics inherent to an MQ solution where they became ephemeral (on the senders side.) Having access to those messages was a treasure trove for troubleshooting and for analytics. That and from a pure cost benefit stance, sending 10-20k messages a day to the ESP definitely didn't necessitate the expensive rearchitecture, as the DB solution was more than capable of handling the load on cheap hardware.

Yeah, at 10-20k it definitely sounds like overkill. Some of our past systems sent a couple million emails per day using a MySQL backed email queue.

The reason to use message queues rather than RDBMS for queue behaviors isn't because queues do stuff databases don't, but rather because of connection cost. There's a lot of complicated handling noise around the database connection, whereas queues tend to be very inexpensive.

As an alternate anecdote, I have had far more issues with MQ connections than with RDBMS connections. I don't think there is a clear advantage to messaging from that standpoint.

Do you mean development/complexity cost or performance cost? If it is the former, then I don't really see it. If it is the latter then yes - RDBMS backed queue is probably going to hit scaling limits earlier then dedicated product.

Reply on news.ycombinator.com