Wednesday, May 13, 2015

Why Database is not suitable for a Queue

There are many applications which use database as a queue in order to orchestrate a long running business process. While it is enticing and easy to do so here are a few reasons about why the developer should avoid doing it...

1) Work distribution : As the number of messages flowing through the system increases the work distribution has to be managed by using locks or by using a Z axis scaling i.e. partition the message table and let different processes handle it. It becomes difficult to manage the failure cases e.g. if the process responsible to manage a particular partition goes down how do I bring up another process to pick up that partition. This is a problem which can be easily solved by event driven queues with horizontal scaling. 
2) Database loads : As the messages passing through the system increase the load on the database starts increasing. This means that the database needs a scaling which is much more costly then scaling a queue bases solution. 
3) Deleting and archiving the queue related tables is expensive as they are indexed. 
4) Database has to be polled to see the pending work items while an event driven queue can simply call the worker process when a new message pops into the queue. 

So while it may be enticing and easy to use database to orchestrate queue related workflows one should refrain from taking this technical debt as it would be really hard to pay it back in the future...

Abhishek