If you're working with Ruby I have had good experiences with Que[1], which implements a pattern similar to the OP using advisory locks.
System design hack: Postgres is a great pub/sub and job server
11–20 of 162 posts
Re: System design hack: Postgres is a great pub/sub and job server
#12If you're working with Ruby I have had good experiences with Que[1], which implements a pattern similar to the OP using advisory locks. [1]: https://github.com/que-rb/que
Is there a similar library in Python?
I really like the look of this approach, but don't want to build it myself if I can avoid it.
Re: System design hack: Postgres is a great pub/sub and job server
#13If you're working with Ruby I have had good experiences with Que[1], which implements a pattern similar to the OP using advisory locks. [1]: https://github.com/que-rb/que
Is there a similar library in Python? I really like the look of this approach, but don't want to build it myself if I can avoid it.
https://pypi.org/project/pq/ looks similar, although it looks like they use `SKIP LOCKED` instead of advisory locks. I'm not sure what the tradeoff is.
Re: System design hack: Postgres is a great pub/sub and job server
#14Can someone share their experience with scaling pg's NOTIFY and LISTEN?
The use case I have in mind has a lot of logically separate queues. Is it better for each queue to have its own channel (so subscribers can listen to only the queue they need) or have all queues notify a global channel (and have subscribers filter for messages relevant to them). I am mainly confused about whether I need a dedicated db connection per LISTEN query and also how many channels is too much.
Re: System design hack: Postgres is a great pub/sub and job server
#15Can someone share their experience with scaling pg's NOTIFY and LISTEN? The use case I have in mind has a lot of logically separate queues. Is it better for each queue to have its own channel (so subscribers can listen to only the queue they need) or have all queues notify a global channel (and have subscribers filter for messages relevant to them). I am mainly confused about whether I need a dedicated db connection…
You can listen multiple times per database connection.
I'm not sure what the maximum scale is, but I've never hit it.
Re: System design hack: Postgres is a great pub/sub and job server
#16If you're working with Ruby I have had good experiences with Que[1], which implements a pattern similar to the OP using advisory locks. [1]: https://github.com/que-rb/que
I'll second this recommendation. It's great because it just snaps onto your backups for your regular database and you can query on it if you need to.
Re: System design hack: Postgres is a great pub/sub and job server
#17If you're working with Ruby I have had good experiences with Que[1], which implements a pattern similar to the OP using advisory locks. [1]: https://github.com/que-rb/que
https://github.com/timgit/pg-boss for node, https://github.com/mbuhot/ecto_job for elixir.
I wish all these libraries agreed on a common schema >_<
Re: System design hack: Postgres is a great pub/sub and job server
#18Thanks, seeing that atomic fetch in action is very useful.
Re: System design hack: Postgres is a great pub/sub and job server
#19Used 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.
Can you show what your schema for your job table looked like?
Re: System design hack: Postgres is a great pub/sub and job server
#20Isn't hijacking a DB as a "distributed" message queue a pretty well trodden path? Enterprises have been doing this for decades.
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"
What 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)?