gangstead
Replace pg_notify with PubSub.broadcast
I’m trying to rework a project that set up insert/update triggers that call pg_notify and then has Postgrex.Notifications.listen listeners around the code base to receive those events.
This has caused a proliferation of db connections that is causing performance issues. There are 40 triggers across 26 tables. For listeners there are a few dozen places in the code, but since these are in GenServers and we have a multi node deployment we end up using 100’s of db connections at a time.
I’d like to stop using postgres as an event bus and replace all the database notify/listens with PubSub events. Updating the consumers to do PubSub.subscribe instead of Postgrex.Notifications.listen is pretty straight forward but I’m trying to find a clean way to replace the event producing triggers with Elixir code.
I suspect the “right” way to do it is adding a Broadcast in the app at every point that I insert / update any of these 26 schemas, but I’m not sure of a way to prove that I’ve found every place in the code base that each model has been inserted/updated. Is there any way in the Ecto schema definition to write something that will serve as sort of a application side trigger? That way whenever MyRepo.insert or MyRepo.update is passed one of the watched schemas I could add in a Broadcast?
Most Liked
al2o3cr
What about a hybrid approach?
- Keep the sourcing as-is
- Use a centralized collection of processes that do
Postgrex.Notifications.listenand then push notifications to PubSub - Listeners subscribe to PubSub
This could also make a smooth transition easier; once the pool of listeners is in place, individual listeners can be switched over to PubSub one at a time.
benwilson512
I think @al2o3cr is dead on with this recommendation. The other critical benefit is that it preserves the same transactional semantics of your current solution, which should help you minimize bugs or corner cases introduced by the migration.
Specifically what I mean is that if pg_notify() occurs inside of a transaction that is later rolled back, no actual notification occurs. If you however change your pg_notify to a broadcast! call then not only does this happen whether or not the transaction rollsback, it also happens immediately. This means if you broadcast!(topic, %{record_id: record_inserted_in_this_transaction.id}) and the broadcast occurs before the transaction has committed, and other processes may try to fetch that record and it won’t be there yet.
benwilson512
Yes, instead of having each cache call start_notification_listener you should start a single one in your supervision tree somewhere with a name, and have everyone use that.
If you are concerned about that being overloaded you could use a PartitionSupervisor — Elixir v1.14.1 to spawn a fixed number of listener processes and then have each client pid use one of those.
al2o3cr
TBH I’m not sure, which I why I handwaved at “a collection of processes” without specifying how many or how they are divided 
Popular in Questions
Other popular topics
Latest on Elixir Forum
Sponsor Spotlight
Supporting innovation across the BEAM ecosystem.
Categories:
Sub Categories:
Forums
Our Sponsors
Build Elixir applications with speed and confidence.
Supporting innovation across the BEAM ecosystem.
We build reliable cloud platforms for business-critical systems.
Catch errors, track performance, monitor hosts and more.
Practical resources that improve the lives of professional developers.
Producing high quality Elixir screencasts since 2017.
Enabling companies to succeed by building software people love.
Courses that'll move you from confusion to "Aha, now I get it!"