One big advantage of using queue inside DB is that you can actually use queue operations in the same transaction as your data operations. It makes everything incredibly simpler when it comes to failure modes.
IMO 90% of software which uses external queues is buggy when it comes to edge cases.
A fair point. If you do need an external queue for any reason (legacy/already have one, advanced routing semantics, integrations for external stream processors, etc.) the "Transactional Outbox" pattern provides a way to have your cake and eat it too here--but only for produce operations.
In this pattern, publishers write to an RDBMS table on publish, and then best-effort publish to the message broker after RDBMS transaction commit, deleting the row on publish success (optionally doing this in a failure-swallowing/background-threaded way). An external scheduled job polls the "publishes" table and republishes any rows that failed to make it to the message broker later on. When coupled with inbound message deduplication (a feature many message brokers now support to some degree) and/or consumer idempotency, this is a pretty robust way to reduce the reliability hit of an external message broker being in your transaction processing path.
It's not a panacea, in that it doesn't help with transactional processing/consumption and imposes some extra DB load, but is fairly easy to adopt in an ad-hoc/don't-have-to-rewrite-the-whole-app way.
Sure, not having to spin up a separate server is nice, but this aspect is underappreciated, imo. We eliminated a whole class of errors and edge cases at my day job just by switching the event enqueue to the same DB transaction as the things that triggered them. It does create a bottleneck at the DB, but as others have commented, you probably aren't going to need the scalability as much as you think you do.
The advantage of using your DB as a queue is that a traditional DB is easier to interact with (for example using SQL queries to view or edit the state of your queue).
In most business applications the message payload is a "job_id" pointing to a DB table so you always need and have to go back to the database to do something useful anyway. With this setup it's one less thing to worry about and you can take full advantage of SQL and traditional database features.
The only downside and bottleneck of having your DB act as a queue is if the workers processes are hitting the DB too frequently to reserve their next job.
Most applications will not reach the level of scale for that to be a problem.
However if it does become a problem there is an elegant solution where you can continously populate a real queue by querying the DB and putting items in it ("feeder process"). Now you can let the workers reserve their jobs from the real queue so the DB is not being hit as frequently.
The workers will still interact with the DB as part of doing their work of course. However they will not ask their "give me my next job ID" question to the DB. They get it from the real queue which is more efficient for that kind of QPOP operation.
This solution has the best of both worlds you get the best features of something like Postgres to be the storage backend for your jobs without the downside of hammering the DB to get the next available job (but in general the DB alone can scale quite well for 95% of the businesses out there).
Simplicity is one of the reasons we started this project. IMO, far less maintenance overhead to running Postgres compared to RabbitMQ, especially if you are already running Postgres in your application stack. If PGMQ fits your requirements, then you do not need to introduce a new technically.
There's definitely use cases where PGMQ wont compare to RabbitMQ, or Kafka, though.
PGMQ doesn't give you a way to deliver the same message to concurrent consumers the same way that you can with Kafka via consumer groups.
To get this with PGMQ, you'd need to do something like creating multiple queues, then send messages to all the queues within a transaction. e.g. `begin; pgmq.send('queue_a'...); pgmq.send('queue_b'...); commit;`
PGMQ doesn't seem to have functionality for returning a payload to the task submitter.
For example, lets say the task is something like:
Run a SELECT query on database FOO, returning the results
There would be workarounds (ie store the results in a "results" table or similar) but just being able to directly return the result in a message to the caller is conceptually simpler.
My experience is Postgres queuing makes sense you must extract or persist in the same Postgres instance.
Otherwise, there’s no advantage over standard MQ systems.
Is there something I don’t know.