Why a database is not always the right tool for a queue based system

When is the right time to use a message queue and why is a database rarely the best tool for a queue based problem?

Imagine that you have a web application for customers to upload a text document, which then converts it into a PDF, and then emails it back to the customer. This site receives a lot of PDF creation requests every second and every request takes a second to process. An orderly, organized queue is needed to be able to handle all requests in a timely manner.

User scenario

  1. Customer uploads a text document
  2. Your application converts the text document into a PDF
  3. Your application emails the PDF back to the customer

Database for a queue based system

Database for a queue based system

In this kind of situation, you might consider using a database for your PDF job queue. Typically you would create a database table that has a queue with records representing PDF requests. You would then place a flag in the table representing which state the task is in and whether the task is completed or not.

INSERT INTO pdf_job_queue (name, status, email) VALUES ("White paper", "NEW", "myemail@example.com");

You need to write code to insert the new requests into the database. Code that takes an input from the database, perhaps change a status column, with values such as "NEW" and "PROCESSING", code that handles the request, more code that again updates the database status field to "FINISHED", and more code to remove the request from the queue.

Database vs message queue

A message queue is built to handle this kind of scenario, making it easy to queue or dequeue messages.

Another thing that you should keep in mind when you build a solution where you let your database act as a queue, is that your application has to ask the server over and over again for the latest queued requests. Messages that have already been consumed need to be filtered out. The number of results received might need to be limited.

SELECT * FROM pdf_job_queue WHERE queue = 'resize_queue' AND handled = false ORDER BY date_recived limit 1;

To operate effectively, you might need to poll the database quickly and frequently. Of course, this adds a significant load to the database.

Frequent DB queries

The database table is both read from and written to. Usually, it is a quick operation to add, update or query data from a table, but now you will have to deal with all of these happening in the same table. Queuing/dequeuing will impact each other under heavy loads, causing lock conditions, transaction deadlocks, IO timeouts, etc.

Note: As long as you don't have very high-performance throughput requirements and if you do not have to communicate with any other systems then a database will probably work fine.

Scaling

Now imagine that your web application is getting used by more and more customers. You have to queue many PDF creation requests in your database and you want to be able to process more requests per second on a concurrent basis. You can connect more workers (processes that handle the requests) or you can scale your solution by adding more machines into your pool of resources. You can even add more power (CPU, RAM) to your existing machine to be able to handle the high load. You decide to connect more workers to your database.

Lock on messages and manual handling of the complexity

When a second worker is added, it’s possible that the two workers will try to handle the same request at the same time. You need to implement some kind of row lock on the queue so that the same request never gets picked up by concurrent receivers.

transactions

Each worker needs to acquire some sort of a lock on the request. Once the exclusive lock has been acquired for the row the system needs to handle the update (e.g. update a status to "PROCESSING"), and after that, the lock will need to be released (e.g. by committing the transaction) as soon as possible so that other workers can access the queue too.

You can also imagine what happens when one of your workers gets a network issue and can not process the request. The request has to be added back to the queue, the status has to be changed back somehow or you have to use transactions and lock it until you have processed the document - meaning that you will lock the queue for other workers.

All this complexity will have to be managed by the programmer so even more code has to be written. A decent message queue will take care of a lot of this automatically and you don't have to worry about any deadlocks or race conditions (when your system attempts to perform two or more operations at the same time).

Manual cleanup

If you don’t clean up your database table regularly or delete requests as soon as they have been handled, your database will keep growing until the performance of your database takes a hit. Eventually, it will become a serious operation problem for you.

The locking challenges and the scan process to find more work will make you run into scalability hang-ups and it will typically also slow down your database.

Message queues

We have now talked a lot of the limitations that you might notice if you use a database for a queue based system (if the system is heavily used or when the system most likely needs to scale up when it is growing). We have talked about the constant polling of messages from the queue, the manual cleanups of messages, deadlocks, race conditions and failed processing that has to be handled manually. All these makes it fairly clear that a database table is not the right tool for a queue based system. It's time to understand when and why the message queue shines!

A message queue only needs to ensure that a message is stored and successfully processed, it doesn't need to bother with locking and writing transactions. You can receive your PDF processing request and let the worker do all the processing, the worker can then move on to the next message of the queue. Message queue scaling is much easier than scaling databases; all that needs to be done is to simply add another worker when the queue starts to fill up. If you get too many connections or some other operation that requires a lot of CPU, simply add more CPU power to your message queue server. In a message queue, you don’t need to worry about any race conditions or locks keeping you out of the system.

Pushed in real-time

Messages from a message queue are pushed in real-time instead of periodically polled from a database. A significantly higher volume of concurrent messages can be supported efficiently using a message queue. Messages in a message queue are automatically cleaned up after being received.

Acknowledgment

You may wonder what happens if one of the workers starts to handle a request and dies when it is partly finished. If a worker dies, the task should be delivered to another worker or held in the queue until the worker is ready to receive the request again. In order to make sure a message is never lost, a good message queue supports message acknowledgments. An acknowledgment is sent back from the worker to tell the message queue that a particular message has been received and processed and that the message queue is free to delete it. If a worker dies without sending an acknowledgment, the message queue will understand that a message wasn't processed fully and will redeliver it to the queue and to another worker. That way you can be sure that no message is lost.

Databases are great for storing information, like product information on a webshop. It is great when you need to ask questions about the data or sort it in different categories. Using a message queue is highly recommended when you need to process a high-volume of asynchronous messages.

A message queue is perfect to use when you want a high performance, highly concurrent and scalable system that can process thousands of messages per second concurrently across many servers/processes.

If you feel that it's about time to get started with your first service using a message queue, check out our article: RabbitMQ for beginners, what is RabbitMQ?

Please email us at if you have any suggestions or feedback.

CloudAMQP - industry leading RabbitMQ as a service

Start your managed cluster today. CloudAMQP is 100% free to try.

13,000+ users including these smart companies