What advantages does this have over RabbitMQ?
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.
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.
IMO 90% of software which uses external queues is buggy when it comes to edge cases.
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.
https://microservices.io/patterns/data/transactional-outbox....
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).
There's definitely use cases where PGMQ wont compare to RabbitMQ, or Kafka, though.
I'd be curious to know more about which sorts of use cases fall into this category.
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;`
For example, lets say the task is something like:
Run a SELECT query on database FOO, returning the results