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

[@adrian_franczak]

Yeah there is really complex queue system in my current project (hopefully not for long) on top of sqlserver and learning curve is very steep

[@CodeOpinion]

[sarcasm]Of course there's great documentation for the home grown queue system built on top of SQL Server! [/sarcasm] I should of mentioned how this is never the situation. Thanks for the comment.

[@adrian_franczak]

@@CodeOpinion unfortunately in my project there is no documentation and tests xD

[@CodeOpinion]

Ya, pretty typical unfortunately.

[@EmilioRodo]

Every company I've worked at had at some point implemented a "concurrent task execution" framework of some sort, based on a db. I've yet to see anyone who likes them, other than the person that wrote them:)

[@jirehla-ab1671]

@@CodeOpinion if I were to add a mesage que for real time OLTP database workloads, would that also induce latency which makes the OLTP database workload not in real time anymore? if so then whats the point of message queues then if its going to be used in real time OLTP database workloads?

[@sirg7573]

I am implementing this exact same approach in my project currently. Removing RabbitMQ and replacing it with a table-based queue in Postgres. And our volumes aren't so high that I need to worry about running into problems. Two motivations for this: 1. Less complexity. 2. Saving infra costs on RabbitMQ.

[@raghuveerdendukuri1762]

I used MySQL/MariaDB as message queue, dead letter queue, failure handling & retry, while ensuring ordered processing on it for years.

[@vlakarados]

There's a lot more to consider than what meets the eye at the first glance: push/pull model, DLQ, priorities, throughput, latency, multiple consumers for one queue, producing messages with fan-outs etc., not even talking about the guarantees of delivery, failovers and keeping full history necessities. There is no silver bullet. We actually chose Postgres for a system that needed the specifics from the mentioned: pull model, priorities, DLQ, failovers and did implement the queue logic in pl/sql due to the fact it's also a transport for messages and not a single service queue. Best way to go IMO is to try different solutions and see their shortcomings in a POC environment, built SPECIFICALLY to your project needs and not as a general solution. General solutions fail most of the time when you're working on projects that are medium-large in size or projects that weren't built AROUND the queue. Get a right tool for the job and reduce the marketing/hype noise "the X tool is much better than Y because it does Z" - do you really need Z? Is Z more important than all the things you lose by switching from Y? Very good video on an important topic, I see it as a continuation on "why best practices are bad" (or something along the lines) video. Context is KING.

[@jonthoroddsen6467]

I would love a video or a series where you focus on how you use EventStoreDB in your architecture. Also maybe when to use a message broker vs events.

[@CatMeowMeow]

I have a personal project that I use postgres for a queue system with. It takes about 1k operations a day & those operations can be anywhere from 500ms - 3 minutes. Personally I have 2 tables, a queue and a dispatches table. I issue a queue item by selecting an item in the queue that doesn't have a matching dispatch row from up to 3 minutes ago. Once a queue item has 3 matching dispatch rows, the queue item is considered failed. It was pretty easy to implement. Not sure if I did it in the optimal way or not, I'm a complete noob when it comes to postgres (and sql in general) It's a nice system so far. The dispatches table has a "fail reason" column which makes it pretty easy to figure out why things are failing when they are.

[@buildingphase9712]

What l see is the most benefit is for people using a small vps and don't want to install a message broker with a 1gig ram requirement off the bet, this strategy would really work well until you need advanced features.

[@belmirp]

Without transaction, you can do: UPDATE messages SET status = processing WHERE status = pending RETURNING id So you reserve records for processing and do whatever you want with them. If you finish processing successfully, change it to the next status, otherwise return it to the previous one.

[@pdgiddie]

You just have to be careful here that some process is in place to sweep for stale entries caused by crashes etc... The advantage of the transaction approach is that the DB can release the lock automatically if the connection dies or processing takes too long.

[@allinvanguard]

Somehow the solution used in this case feels like a workaround for a misconfigured setup in the first place though. I've only ever used DB's as queues for simplicity and accessibility, but with growing complexity, it feels like the feature set DB queues come with can't quite keep up

[@CodeOpinion]

Yes, I'd agree feels like something was misconfigured and they do state that in the blog post. They didn't provide a ton of details but that's just the sense I got as well.

[@user-qr4jf4tv2x]

postgres is ones you master it you mostly don't need anything else. i'd argue most infrastructure are overly complicated

[@guai9632]

there is a pgq extension to pg.

[@dabbas8443]

I put this same article on my reading list, you saved me from reaing it 😅.

[@quincymitchell2203]

Did anyone ever figure out if it’s possible to disable the prefetch function of rabbitMQ? Because it might be a situation where they reached for a DB queue because it’s something they can control, but rabbitMQ wasn’t.

[@amgadtawfik7534]

Hi Derek in the trade offs you mentioned processing time and latency. I am sorry I did not understand why exactly would using a database affect processing time and latency?

[@CodeOpinion]

Processing time I shouldn't of just said, just overall latency because you'd be polling the database on some interval rather than it being pushed to you by an existing connecting of your broker.

[@buildingphase9712]

@@CodeOpinion you can have some form of trigger on insert as well.

[@appology9102]

@@CodeOpinion What about using NOTIFY and LISTEN?

[@LawZist]

In my current company we use combination of both. Db as queue for long async jobs and rabbitmq for the microservices communication which provide us good reliability (previous company used http == a lot of failures and lost messages) I wonder if there is a place for the outbox pattern in this kind of architecture

[@everydreamai]

I've used a bit of both. Reporting engine with low throughput and high processing time using SQL to track jobs, but MSMQ for smaller/faster transactions in the same system, yet other systems using event driven architecture would have different topics for state change tracking. Many different solutions for different problems. Great overview as usual! No one size fits all.

[@MegaMage79]

With this approach you can't really update the row until after you have processed the entire message, right ? So you wouldn't be able to see how many messages are being processed for example.

[@CodeOpinion]

Ya, good point, that processing column date/time is pointless. Good call.

[@GabrielSoldani]

You can query `pg_locks` to check how many rows are locked: this is exactly how many messages are being processed. It’s not as simple as SELECT COUNT(processing_time) FROM Queue, but it does the job. An alternative that is more understandable but less performant, but would do the job: use the same FOR UPDATE SKIP LOCKED but without the LIMIT, then immediately roll back. The returned row count is exactly how many messages haven’t been picked up yet, and you can subtract that from the total row count.

[@mikhailbo8589]

we can commit the transaction right after the first status updating...

[@GabrielSoldani]

@@mikhailbo8589 The proposed approach works because it leverages the transaction to keep the row locked while the consumer is processing it, but once the consumer is unresponsive, the connection drops, the transaction rolls back, processing_time is back to NULL, and the database releases the lock, allowing another consumer to take it. If you commit, you release the lock. Now if a consumer fails, the message will be forever stuck in the “processing” state. Unless you implement a timeout to deliver the message again to a different consumer. But now the first consumer might just be taking its time and actually finish processing: you end up with 2 deliveries. By now you need to implement a way for the first consumer to know it timed out, the simple queue solution isn’t simple anymore and you’d be better off with a message broker.

[@lizard450]

I've done the DB queue approach because our pre-existing solution was failing miserably. It works for us, but the simple example proposed here didn't seem to work for us. We have an outdated mysql DB and it's not exactly standard. Transactions work a bit differently there than what I was use to with some other DBs. Anyway a little more complicated than this scenario, but not much. It's been rock solid thus far and I'm going to be looking to replacing it with a "proper queue" eventually, but this way we have a rock solid backup in case we have some issues with our queue implementation like we did before. One of the few times in my career where my concurrent programming from my CS degree really came into play. Love it.

[@Jacek2048]

Great vid. One of the two major PHP frameworks, Symfony, has a Messenger component, which is an abstraction for message queues. It has bridges for a lot of messages brokers - RabbitMQ, Amazon SQS, and many others. But there's also a feature-complete database bridge, with DLQs, multi-queue support, and retries. So a few of the arguments you're presented here would probably not apply. Of course, the question of throughput and how convenient it will be for other systems (especially non-Symfony apps) to interact with is another matter.

[@Samuel.Mwangi]

I read the referenced blog post last week. Thank you for your comprehensive summary on the pros and cons of both approaches. Makes one respect frameworks that ship with a great queue mechanism out of the box e.g Ruby on Rails & Laravel

[@CodeOpinion]

Ya, I read it last week as well which is what gave me the idea. For any type of messaging, queues and/or pubsub, it's not something I generally want to implement but would rather reach for a library/framework. Once you go down the messaging path, it's hard to escape.

[@Rotem_shwartz]

Are you sure the problem is correct? If prefetch X every consumer (pod/container) should handle X messages simultaneously..

[@CodeOpinion]

I don't understand their underlying issue of prefetching 1 message. Pre-fetching isn't handling concurrently however.

[@Rotem_shwartz]

@@CodeOpinion when using rabbiq with nodejs for example, messages are handled concurrently. I think the problem here misuse in the application layer.

[@LawZist]

@@Rotem_shwartz if the job is doing synchronized work, then it will not be concurrently. I guess we need more information here

[@ilijanl]

You made some errors in the queries you show for PostgreSQL. First query you also need to filter with where processTime is null, secondly when you update the process time column, you can commit. Thus you don't need a long running transaction. Otherwise the scalability of the system is bad

[@GabrielSoldani]

If you UPDATE the processing_time and COMMIT, if a consumer fails after COMMITting but before DELETEing the message, the message will never be picked up by a different consumer - you can’t differentiate between long-running messages and failure states. You don’t need to filter on processing_time IS NULL because the consumer will hold a lock preventing a different consumer from SELECTing it. And only when you’re done processing you COMMIT, releasing the lock. If the consumer dies, the transaction rolls back and processing_time never gets set to a non-null value. In the example given, a non-null processing_time can only be observable by the consumer processing the message, since it DELETEs the message before COMMITting the change. So you don’t need processing_time at all. I’d opt not to have it so its clearer that the queue guarantees exactly-once delivery with competing consumers.

[@GabrielSoldani]

@@ilijanl How does the background job know if the message is stale or if it’s simply long-running? I don’t see how this guarantees at-most-once delivery.

[@ilijanl]

@@GabrielSoldani you need to define some job timeout, this is different per use case. The same holds for other message brokers like rabbitmq. To be more specific, you normally have more columns in the job table, such as amount of retries and expire date etc

[@penaplaster]

I refused using a message broker for a simple scenario in my previous project. Runs on a database table and polling consumers for several years now with zero issues and maintenance. We should introduce complexity only when no suitable simple solutions left.

[@LawZist]

Can you share from your own experience when you would use message broker in a project?

[@penaplaster]

@@LawZist Like always, context it the key. In that particular case, we didn’t have a message broker and to implement the feature we had to add the broker to the solution. That part of the system wasn’t under a heavy load, there was no need for integrating it with external services. Thus, adding a message broker looked as an overkill. As for the general guidance for when to use the message broker, the answer will be too big for the comment on KZbin :) I suggest you looking up guides for cloud native applications. All major cloud vendors provide lots of resources on the topic. There, look for service-based, event-driven architectures and micro services. Also, have a look at application integration patterns.

[@marna_li]

Had to come back and say that MassTransit will get support for SQL Databases as a "transport" soon. So there is no need to create your own abstraction for queues using database. And you will get all the goodies from using that framework.

[@kevinlloyd9507]

Just saw this myself. Outside of volume, the other trade-offs mainly including implementing your own abstraction for other features. I wonder if his opinion changes if someone else has already done that for me.

[@arandaid465]

You do know that you can still use a real message broker but not use prefetch if you build your own custom logic..

[@dimitriborgers9800]

Would not deleting the rows at the end of the transaction cause significant performance issues? I'd like to keep the rows after they are finished processing to show the status in an admin dashboard Also, would polling multiple rows (10 to 100) at once, cause issues?

[@MorkorOuzor]

for fast processing context, how about the money cost of consistently querying the database each 2 seconds in many processes ?

[@bobbycrosby9765]

We do have our own messaging library built on top of MySQL and/or Redis. We've debated moving to a real message queue, but, we're a small company and production is Hard - way harder than it is to just slap a random piece of tech into your project, especially when you know what hits the fan, and we'd rather minimize the number of techs we have in production.

[@nitreus1]

When working as a beginner developer, I implemented similar queue on an Oracle database, without really understanding what I'm doing. Later when I learned about real message brokers I assumed that using database for that was a crazy idea. Fun to see today that it could actually made sense after all :)

[@mateuszszczuka2311]

In my opinion, it is always good to look for dedicated solutions instead of creating your own. RabbitMq is not the only solution available on the market. Apache Kafka is a much better solution when it comes to large-scale applications. Thanks for bringing up the topic 👍👨‍💻

[@croncoder862]

This constant comparison is really useless. Rabbitmq is a beast if you know how to use it in different use cases and trust me Kafka does not cater to all these scenarios.

[@CodeOpinion]

I've got a video about this. It's an apples or oranges comparison yet people use Kafka often times actually just needing a queue or topic/exchange.

[@mateuszszczuka2311]

I think we misunderstood each other. I didn't mean to compare the competence of RabbitMq or Kafka or any other tool and decide which one is better. The main point is to choose mature technologies that meet our expectations. If RabbitMQ didn't work out then you should look for other solutions, such as Azure Service Bus or find a specialist to improve the queue configuration. As for me, the worst possible solution is to produce your own libraries, maintain them and develop them. Unless you have a really strong argument why you should go this path.

[@lizard450]

@@mateuszszczuka2311 You're not wrong. The don't reinvent the wheel rule is a good solid rule. What you need to understand is that when you're introducing a new tool into your application there's a learning curve and risk to factor in. In our real-world scenario our 3rd party solution just started shitting the bed in production. No one on the team had any familiarity with the solution and it had been intermittently problematic over the years but it was never a big deal. Well at this time it was a big deal because we weren't able to perform a function for a customer and the deal was for a big customer and a lot of money. Okay top priority.. That night we pushed a patch to retire the old solution. The quick patch was a very simple solution like what was posted above. Not perfect, but good enough. The next week I was able to get a slightly beefed up solution out there and beat the crap out of it in testing. It's been solid ever since. Do I want to update it and use a different 3rd party solution? Sure I do and I even know which one. I'm really proud of my homegrown solution and have 100% confidence in my solution, but currently on our small team I'm the only one who can work with it and that's not cool. However when you're relying on 3rd party solutions you need to evaluate them and beat the crap out of them in testing like I did to my solution and you never know when there are gotchas you're going to run into. Like you're using a "database" that doesn't guarantee primary key uniqueness. When you're dealing with a problem that needs a solution ASAP and cannot fail... there is value in reducing the solution to the most basic components and doing it on your own where you have the least uncontrolled variables.

[@JoseLopez-sg5pc]

Cool.

[@dariuszlenartowicz]

We have queue based on table. We have Topics and each topic have own worker to pooling messages. We have also VisibleFrom and ExpiresAt columns. Table is properly indexed but those indexes are not used by sql engine because rows quantity on that table is very variable and changes a lot so sql engine have no chance to have proper statistics - that leads to just scans. Scan is not a problem when there is 1k of rows but our clients sometimes puts/generates 200k messages at once (or more). Scan (to find Topic) on that number of rows is significant. Now we are on the path to ditch sql tables as queue and move to RabbitMQ :)

[@peterniks]

In rabbitmq we can't prioritize messages based on their timestamp. Any solution to this? Is it possible in rabbitmq or Kafka? Or is there another better solution? The job can be anywhere from 5 sec to 30mins potentially..

[@raticus79]

Redpanda's a good one for developers to get started with a log-based broker, to be ready when those tradeoffs start to matter more. Kafka-compatible but easier and faster. Dagster did a blog about using postgres as a queue too - it's one of those things that works fine at smaller scales, simple and has some advantages... then maybe one day it doesn't work any more. Good to think ahead to that point.

[@PersonOfBook]

I once used Sqlite as a message queue.

[@LawZist]

Great video!

[@dasdotnet1]

Is it Gud ?

[@ZadakLeader]

Hi, just stumbled upon your channel and I like the content. A small tip, however. The video recording seems to be blurry, you should invest in a better camera potentially, something like a DSLR/Mirrorless and use that to record videos? Either that or when processing add a higher bitrate

[@CodeOpinion]

Blurry? I'm using mirrorless (Canon M200) running at 60fps (some previous videos are at 30) I'm not seeing where it's blurry?

[@ZadakLeader]

​@@CodeOpinion Hmm i guess I am spoiled maybe by youtubers with an even better setup :( For me when you're on the whole window the details seem to be lost, it's like smudged, even at 1080p. Maybe it's the bitrate? Also maybe if it possible to upload it at 1440p? Maybe try it next time? :D

[@ZadakLeader]

@@CodeOpinion Also another tip! Make sure the white balance is the same, consistency in the coloring in the video makes for better videos (looking at some thumbnails, in some you are more blue in some more yellow, that's just a white balance issue). Unless this was an explicit decision you took to have it like that (then it's fine!) :)

[@CodeOpinion]

If anything, I think I need better lighting. A/V isn't my lane so I really just winging it. My camera I believe should be good I might just need to be configured different, and better lighting.

[@ZadakLeader]

@@CodeOpinion Yes, light does help too! What is the bitrate you export in?

[@cbrunnkvist]

Faced precisely that problem (in Ruby) a long long time ago the first time the team I was on interacted with RabbitMQ - had 4 workers, one of them took a thousand messages at a time and blew up all of the time, causing all the prefetched - some already processed - messages to go back on the queue, causing havoc. We couldn't figure out if we were doing something wrong of if it was just this counterintuitive by design, and I think we solved it only after setting max_prefetch=1 and manually .ack():ing each message. 🤔