8 ms·
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
by colinchartier 7y ago
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"
- gigatexal 7y agoThe caveat to this is in the first paragraph: all of the data is stored in memory id each row is ~1kb and the table size has some sort of TTL you’ll be good.
- colinchartier 7y agoYeah, but even without tuning, you can have hundreds of "sparse" queues with this pattern - you don't have to care about the frequency of insertions or deletions or the size of the rows.
- gigatexal 7y agoI mean I agree I think with what I think you’re saying: as much as I love Postgres and SQL I’d much rather use Redis for this.
- colinchartier 7y agoI actually used redis pub/sub before switching to triggers - it is significantly easier to just: INSERT INTO JOBS versus INSERT INTO JOBS redis-cli LPUSH job-queue job-id It's also easier to configure, redis has BRPOPLPUSH for acked-queues, but it's much easier to just query jobs with a select statement from the database.
- Rapzid 7y agoObviously "it depends" but here are a few reasons one may want to use PostgreSQL instead: 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
- gigatexal 7y agoRedis modifications are transactional: https://redis.io/topics/transactions https://redis.io/topics/transactions Though I agree on points 2 and 3, especially 3 since it adds complexity.
- Rapzid 7y agoInternally but if the change causing the work is in PostgreSQL Redis would need to support 2-phase commits to commit the change and the work together atomically(and durably).
- erichocean 7y ago> Redis modifications are transactional 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".
- gigatexal 7y agoI’ll admit I don’t know the internals of Redis as well as I do relational databases. I’ve learned much from this thread.
- MuffinFlavored 7y agoWhat if your listeners crashed/were down at the time of the `PUB` message? Does this mean the message falls into oblivion (since it will never receive a reply/ACK/get worked on)?
- colinchartier 7y agoThe pattern I follow is that workers immediately poll once when they start - this means that when your workers restart after crashing, they'll pick up any missing jobs.
- pm90 7y agoI'm not seeing how this recovers from the failure scenario where a worker grabbed an event and then immediately crashed.
- tjungblut 7y agoYou need some timeout and/or leasing mechanism to make it work in this scenario.
- Rapzid 7y agoThe comment you are replying to wasn't addressing that scenario. 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.
- cuddlecake 7y agoYou can have 3 queues and 3 dispatchers, arrange some backpressure, a push-based consumer-producer model and you will have 3 queues, 300 queries and n polls per interval. There is a solution to every perceived problem.
- schwarzmx 7y agoAt what point does solving these "new" problems become reinventing the wheel?
- Ericson2314 7y agoYou should never need to poll. Polling is bad.