Using your Database as a Queue? Good or bad idea?

do you need a message broker or could you really just use your database as a queue well as in most things it depends let me talk about some of the trade-offs and things you should consider such as volume failures processing and more this video is brought to you by eventstore DB the stream database built from the ground up for event sourcing cqrs and event-driven microservices for mon event stored EB check out the link in the description this video is inspired by this blog post in which to be stated that the ditched rabbitmq and replaced it with the database they were already using postgres as their cue I'll have a link in the description to this blog post you can read in this entirety but the kind that too long didn't read is that they had issues with pre-fetching in rabbitmq because they run long running jobs and these jobs as they're processing messages can take potentially hours another note to this to this is that they're doing manual acknowledgments once they're done processing a message someone to illustrate some of the issues that we're having and some of the ways that most message brokers work so you can understand the differences here between this and what you'd be implementing with the database and what actual their troubles were so the first is understanding acknowledgments and the visibility timeout so when you have a consumer that's consuming a message from your queue and you're processing that message if you're manually acknowledging that you've completed that message that means that that message is there still on the broker it's just invisible to other consumers meaning no other consumers can actually consume and pull that message now once that message has been processed by your consumer if you're doing manual acknowledgments you then acknowledge back to the broker saying yes that message I processed it it's good you can now remove it from the queue now you may hear people say that you shouldn't be using a message broker for kind of long-running jobs like this and there's a variety of reasons why they say it maybe you should be having instead cues based on priority or SLA do you really know what the processing time is relative to your invisibility timeout is that going to be an issue are there's long-running jobs going to kill your throughput do you need throughput this is just a really a lot of considerations that to make this it's not really so black and white now I mentioned throughput because the competing consumers pattern is what allows us to scale horizontally and creates that throughput if we need to so as we have messages being produced we have two consumers they're not really consuming any messages yet the first one gets the first message and now we have another message we don't have to wait for that first consumer to finish because we have another consumer available and it can process that message so if we have an increase in messages inbound we can add more consumers potentially to increase our throughput or to maintain our throughput now the issue described in the blog posts with rabbitmq at least it has liver experiencing it and the problem that it caused is that let's say they had two messages available and then one of the consumers was processing that message it was actually prefix fetching two messages so this means that because they were doing manual acknowledgments if that first message took two hours and then they finally acknowledge it then they would start processing that same consumer the second message but no other consumer would be able to get that second message because it was actually being sent with prefetched with the first message so this was actually the underlying issue that they described and that they had and why they wanted to move away from rabbitmq now regardless if they were using rabbitmq ROM the configuration Etc that's besides the point they decided then to move to postgres the database that they were already using so how would you implement that using SQL this may not be exactly how they did it but I'm just kind of illustrate in here they said they had a single table to represent the queue that they were working in let's say I have a table here of Just The Columns of ID some data let's say it's Json as an example and some processing date time so we just record knowing when we actually start processing a message so the first thing you actually do from your consumer's point of view is you'd start a transaction so after we start a transaction then we would select from this table let's it was called Q we'd be ordering it by ID ascending and limiting it to the very first record and what we're doing here is this four update skip lock it's going to be then locking the record that we select skipping any records that haven't already been locked so in this case let's say that the first record is being locked already by another consumer because it has a processing date time in there that I've added so we would actually select and lock record for id2 then what we do is just for just the show that we're actually processing this let's update that processing column with now but because we're locked no other consumers are going to be able to process and select that row of id2 once we finish processing let's say it took two hours we can then delete that record from our table and then commit that transaction now if you want to do a comparison here really selecting that data with the lock is like our broker giving our messages to our queue and then a visibility tie note is actually very similar to The Wait timeout that our database might have if we don't actually complete a transaction so again you still kind of have this issue with what's your processing time and what's going to be that Timeout on your database if you don't complete a transaction or you have an idle connection potentially and then deleting is really like doing the acknowledgment that's what's going to be removing it from our queue once we commit our transaction so what's the issue with using a database as a queue rather than a full-blown message broker well in their case they were actually successful and so they were pretty happy with this and I think the criteria and the context around why is the illustration of why it works in this particular situation they had a single table as a queue and they were only using rabbitmq in kind of the simplest scenario so I can understand why having the overhead of separate infrastructure at surviving queue when they were familiar with postgres they were already using it it simplifies the complexity of potentially kind of their physical deployment or infrastructure it's simple for them it's a single table that represents the queue they can have really just the simplistic starting a transaction locking a record updating it potentially or deleting it after it's been consumed and this works for them because it's under a very simple scenario now where it can start to fall apart in using your database as a queue is something like volume if you are producing a lot of messages and then you need to consume a lot of messages so when I was talking about competing consumers how many consumers are you going to have because you're using a database you're going to need to be pulling I.E making select statements to your database on some interval again how important is processing time how important is latency Etc so you're going to be implementing this and that's the second part of this more and more features potentially if you start to get elderly simple scenarios so as you get into more complex scenarios that message Brokers provide out of the box as an example is also dead letter other cues when you have failures or don't acknowledge where's that message going are you just retrying it because it's staying in that table then you start implementing your own logic to move a record from there to some separate table that represents a dead letter q do you want to then start implementing different cues for different priorities because you have different slas you can see where I'm going with this once you start getting out of that simple scenario do you end up starting to build your own messaging Library abstraction for your specific use case for your application when you probably want to be using an actual message broker and some stable messaging Library potentially on top of that that provides a lot of functionality like the dead letter queuing retries different forms of handling your failures things like maybe you want to start doing the asynchronous request reply pattern with cues you start really opening the box when you get messaging where you're not going to want to implement this on your own against the database and you're going to want to be leveraging a message broker so it's just finding that line of are you really in that simple scenario and you can just leverage for update locks on a single table in their example sure that worked great but be careful really careful that kind of that slippery slope that you can go down we're going to implement more of your own common messaging patterns on top of the database and end up kind of building your own homegrown messaging Library if you found this video helpful or you're in this kind of scenario and you want to chat with other software developers about these topics and software architecture and design you can join my channel and get access to a private Discord server links in the description on how to join if you found this video helpful please give it a thumbs up if you have any other thoughts or questions make sure to leave a comment and please subscribe for more videos on software architecture and design thanks

Channel: CodeOpinion

Views: 9,888

Keywords: Database as a Queue, software architecture, software design, software architect, .net, message queues, kafka, event bus, event driven architecture, azure service bus, rabbitmq, distributed transactions, service bus, message queue, message queuing, database as a queue antipattern, activemq, postgresql, postgres, postgres queue, mysql queue, oracle queue, sqlite queue, sqs, postgresql queue, queue for update, postgres queueing

Id: DOaDpHh1FsQ

Length: 9min 31sec (571 seconds)

Published: Thu Apr 20 2023

Reddit Comments

[u/KamikazeHamster]

I've read a lot of software books and I can tell you, without having watched the video yet, the only answer to every architecture video that ever comes to this sub with a question as the title...

It depends.

[u/daedalus_structure]

Good idea for low volume with few concurrent writes and infrequent polling.

Bad idea for anything where you aren't in control of those variables and their scale.

Edit:

One situation where this pattern shines is when you have an asynchronous workflow where a user kicks off a job and a process needs to call several APIs, some which may be intermittently unreachable, records are created in another system, but the next part of the process can't happen until actions are taken on the other end.

You may have 20 queue items per week and since it's all table backed it's easy to build management interfaces to the queue to restart jobs and see current status and process details.

[u/burglar_bill]

2 cases I can think of where a DB table is preferable to a broker: 1 you need to be able to query what is in the queue at any point. 2 outboxing. Ok that’s only 1 and a half cases, outbox uses both.