hirosystems
switch `PgNotifier` from NOTIFY/LISTEN to pg realtime subscription
We've been having some replication problems recently which are reported by postgres as "deadlocks" on certain queries. In order for us to be able to try other replication modes, we need to remove the current dependency between primary and replicas that exists through LISTEN/NOTIFY messages for PgNotifier.
We should switch this to realtime subscriptions on postgres so we can emit these messages directly from replicas when they have data available.
@CharlieC3 I was looking at this yesterday and I'm afraid we won't be able to achieve what we want with realtime updates. Realtime updates need us to create a PUBLICATION on postgres so we can then subscribe to it, however, from pg docs:
A publication can be defined on any physical replication primary
Which means we can't subscribe to replicas i.e. we're right back where we started.
Another option would be to make the realtime update queries (once the NOTIFY message is received) be against the primary instead of the replicas. Would this be acceptable? To be clear, though, any read-only API would query the primary before sending any websocket event.