This is unfortunately the status quo in the slightly less ideal universe than “seemed like a good idea at the time”
Use SQS or RabbitMQ or something.
Edit: Also if you’re using something SQL for queues you’re going to have to build out your monitoring stack yourself based on your implementation rather than suck up a metrics endpoint with queue stats or pull from cloudwatch.
Unless we’re talking about a high load, there should be no problem doing this.
Having the queue on its own means you have to handle exceptions like "job is done but queue didn't get updated" or get into distributed transaction which is very different challenge.
Also, operationally having one inherently stateful component (db) versus two (db, queue) makes things a lot simpler to manage, deploy, update, etc.
Not claiming this is a perfect solution, just that the tradeoff isn't obvious.
Is contention with the application your only objection? That’s pretty weak.
What’s the real compelling reason not to use a database? You haven’t said.
Not to mention numerous database administrators crying into their cups of coffee.
Enough?
Like I said 20 years of people fucking this up is my experience.
By putting it in the same transactional store with the same transactional boundaries you’re instantly shooting the whole point.
Not only that, most queues define boundaries between different logical systems where transactional boundaries do exist. At which point your database’s transaction scope is extremely limited in capability.
In the real world of messaging transactions span more than just the database engine.
It’s just bad architecture. Full stop.
Databases actually work fine as a queue but emotionally you don’t like it. That’s fine it’s just not real strong objections.
What you have not said is “it physically does not work”, and that’s because it does work fine.
I think if I were going to argue against using DBs as queues it would be around: heavy parallel write use cases, latency concerns of both reads/writes and scaling to millions of events per second.
If you don’t have those concerns using a properly normalized and protected schema (which you are doing anyway right? Cause if not you are already shooting your toes off) for queues goes a very long way and removes a very big operational burden and tons of failure modes.
How is this done in Redis automatically? At some point you are writing queries (albeit Redis ones) to pull metrics. Other queueing systems may expose an API but there is always some level of integration. With Postgres/other db I would write SQL which is their API and a powerful one at that.
I can couple events with triggers to auto-generate other events etc, have built in audit capability, roll up reporting etc, all with standard SQL. (all in the context of monitoring and reporting)
Going from a single database to a (database + queue) means two server processes to manage, maintain, observe, test etc.
I actually start with SQLite to reduce as much distributed state as possible, then move to something else once it’s proven it will not work.
As for triggers, reporting, audit, I'm laughing now because you miss the point. That's another bunch of IO in your monolithic black box, and you're still building it.
Start here: https://www.rabbitmq.com/prometheus.html
The point of queues in any particular application is some subset of that; the calculus of implementation approaches that make sense depends not on the abstract point of queues in general but on the concrete point of queues in your use case.
To clarify we just moved this entire problem to SQS.
That's... nothing. Databases handle billions of rows effortlessly using a b-tree index. So not really sure what point you're trying to make?
I've thought about using rabbit mq, but have a few questions: - it appears that when a job is consumed, it's gone. How would I maintain a record of the jobs & status of the job in rabbitmq? If I wanted to display job updates how would I handle that? I didn't think you could update a message once it's already in the queue
Or am I missing the mark? Do I want to separate the business entity "job" that maintains a status and updates and such from the "thing that dispatches jobs to workers"? And when a worker Has the job it just updates the business entity in the database?
If you ever really need to that's one way to do it but consider I'm imagining 50-100k state updates per second as being reasonable depending on how the data and queries are laid out. You can always use NOTIFY as discussed to cut down on the amount of worker queries for new work before having to completely push signaling to a separate system(which would likely still require occasional polling and/or a background process to re-signal work that hasn't been picked up).
It's interesting to consider that AWS Step Functions charges(or did charge) by the state transition.