This pattern falls down if you need to poll the database, because if you have 3 queues and 100 workers you're making 300 queries per poll interval.
The feature of postgres that makes this viable in comparison to most other databases is the "channel"
The feature of postgres that makes this viable in comparison to most other databases is the "channel"
INSERT INTO JOBS
INSERT INTO JOBS
redis-cli LPUSH job-queue job-id
1.) *They want to transactionally commit work along with the change that caused it
2.) They are already using Postgresql not using Redis
3.) Requiring users install yet another service(Redis) just for this one item isn't worth the costs
Though I agree on points 2 and 3, especially 3 since it adds complexity.
That's only true if they do not error—there is no "rollback" feature in Redis, and if you do error, anything you've done up to that point remains.
That's not transactional, it's more "you can, sometimes, execute a bunch of separate statement atomically".
If so if worker crashes you get a connection timeout on postgresql and it rolls back the transaction and releases row locks - then next select SKIP picks it up.
If you want fast response with safety listen and on restart do a poll always to pick up anything that's ready that you might have missed a pub for. If you want lots of coverage do a poll every 10s - postgresql can handle it, you still get immediate response by listening to notify.
If you need fancier approaches you can do a last_updated time on the job and or watch waiting on pg_stat_activity or similar etc - but all totally overkill I think - the simple approach get's pretty far (not an expert though at all).
Typically you would implement visibility timeouts and other such stuff. Depending on the use case you could make specific optimizations or keep it generic and have SQS like semantics or something.
There is a solution to every perceived problem.