41–50 of 162 posts
At that point, people usually start looking at the connection pooling tools. Depending on how much work you need from the DB, connections pools can be a win. Anyone know how connection pooling works with listeners?
A
Earlier quoted context omitted.
I 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.
Obviously "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
Though I agree on points 2 and 3, especially 3 since it adds complexity.
Earlier quoted context omitted.
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"
You 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.
The architecture is to stick a list of your input shards in a Postgres table, have a state flag that goes PENDING->WORKING->FINISHED->(ERROR?), and then spin up a bunch of worker processes as EC2 spot instances that check for the next PENDING task, mark it as WORKING, pull it, process it, mark it as FINISHED, and repeat. They write their output back to the DB in a transaction; there's an assumption that aggregation can happen in-process and then get merged in a relatively cheap transaction. If the worker fails or gets pre-empted, it retries (or marks as ERROR) any shards it was previously working on.
Postgres basically functions as the MapReduce Master & Reducer, the worker functions as the Mapper and Combiner, and there's no need for a shuffle phase because output <<< input. Almost all the actual complexity in MapReduce/Hadoop is in the shuffle, so if you don't need that, the remaining stuff takes < 1 hour to implement and can be done without any frameworks.
Postgres generally has a fairly low maximum connections. If you're running your own servers, you can adjust this, but in the cloud you may not be able to. For example, Google CloudSQL maxes at 1000, Heroku at 500. At that point, people usually start looking at the connection pooling tools. Depending on how much work you need from the DB, connections pools can be a win. Anyone know how connection pooling works with li…
They don't. LISTEN is per connection and pgbouncer multiplexes many sessions onto a single one. The poller holds the connection and has no way to propagate back the notification while still maintaining multiplexed sessions as isolated.
Earlier quoted context omitted.
The 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.
at this point why not use something like rabbitmq and not reinvent it in postgres.
Used the "FOR UPDATE SKIP LOCKED LIMIT 1" trick to implement a job server in PG a few years ago for the first time. It's a great solution.
Earlier quoted context omitted.
The 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.
at this point why not use something like rabbitmq and not reinvent it in postgres.
Earlier quoted context omitted.
How is MySQL not OSS?
It is somewhat split. Not all MySQL features are open source (enterprise edition feature). Postgres is completely open source.
I believe Citus DB(?) is something similar.