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?

9 comments

#ecto #postgres #postgrex #pubsub

1 1855 9

2022-11-07 21:00:39 UTC

Most Liked

al2o3cr

What about a hybrid approach?

Keep the sourcing as-is
Use a centralized collection of processes that do Postgrex.Notifications.listen and 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.

Post #1

benwilson512

Author of Craft GraphQL APIs in Elixir with Absinthe

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.

Post #2

benwilson512

Author of Craft GraphQL APIs in Elixir with Absinthe

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.20.2 to spawn a fixed number of listener processes and then have each client pid use one of those.

Post #9