<story><title>How to listen to database changes using Postgres triggers in elixir</title><url>https://www.peterullrich.com/listen-to-database-changes-with-postgres-triggers-and-elixir</url></story><parent_chain><item><author>stevencorona</author><text>I&amp;#x27;ve been using Elixir for the past 5-6 years for my startup. We use pg_notify extensively to broadcast changes between running nodes (basically, use Phoenix.PubSub locally in our apps, with a GenServer to subscribe+re-broadcast using pg_notify).&lt;p&gt;This has been a really elegant and low-complexity way to get distributed pubsub without the complexity of running a distributed erlang cluster (which seems a lil bit painful in a K8S+Continuous Deploy world)&lt;p&gt;There -are- some big downsides to be aware of though.&lt;p&gt;1. You can&amp;#x27;t use PgBouncer w&amp;#x2F; LISTEN&amp;#x2F;NOTIFY. This has been really painful because of the high memory overhead of a pgsql connection + elixir keeping a pool of open pgsql connections. The tried and true method of scaling here is to just use PgBouncer. We&amp;#x27;ve kicked the can on this by vastly over-provisioning our pg instance, but this has cost $10s of thousands on the cloud. Of course, it&amp;#x27;s solvable (dedicated non-pgbouncer connection pool just for LISTEN&amp;#x2F;NOTIFY, for example), but painful to unwind.&lt;p&gt;2. The payload has a fixed size limit (8KB, IIRC). This has bitten us a few times!&lt;p&gt;Even though I really like pg_notify, I think that if I were starting over, I&amp;#x27;d probably just use Redis Pub&amp;#x2F;Sub to accomplish the same thing. Tad bit more complex if you&amp;#x27;re not already running Redis, but without the downsides. (Of course, w&amp;#x2F; Redis, you don&amp;#x27;t get the elegance of firing a notification via a pg trigger)</text></item></parent_chain><comment><author>colinchartier</author><text>We got around this at my company by just pooling all of the LISTEN&amp;#x2F;NOTIFY streams into a single database connection in software, here&amp;#x27;s a sample implementation:&lt;p&gt;function software_listen(channel, callback):&lt;p&gt;&lt;pre&gt;&lt;code&gt;  if not channel_listened(channel):

    sql(&amp;quot;LISTEN &amp;quot; + channel)

  listeners[channel].append(callback)

&lt;/code&gt;&lt;/pre&gt;
function on_message(channel, data):&lt;p&gt;&lt;pre&gt;&lt;code&gt;  for listener in listeners[channel]

    listener(channel, data)

&lt;/code&gt;&lt;/pre&gt;
function unlisten(channel, listener):&lt;p&gt;&lt;pre&gt;&lt;code&gt;  listeners[channel].remove(listener)

  if len(listeners[channel]) == 0:

    sql(&amp;quot;UNLISTEN &amp;quot; + channel)

&lt;/code&gt;&lt;/pre&gt;
Here&amp;#x27;s the actual go implementation we use:&lt;p&gt;&lt;a href=&quot;https:&amp;#x2F;&amp;#x2F;gist.github.com&amp;#x2F;ColinChartier&amp;#x2F;59633c1006407478168b52e2318da8e8&quot; rel=&quot;nofollow noreferrer&quot;&gt;https:&amp;#x2F;&amp;#x2F;gist.github.com&amp;#x2F;ColinChartier&amp;#x2F;59633c1006407478168b52...&lt;/a&gt;</text></comment>
<story><title>How to listen to database changes using Postgres triggers in elixir</title><url>https://www.peterullrich.com/listen-to-database-changes-with-postgres-triggers-and-elixir</url></story><parent_chain><item><author>stevencorona</author><text>I&amp;#x27;ve been using Elixir for the past 5-6 years for my startup. We use pg_notify extensively to broadcast changes between running nodes (basically, use Phoenix.PubSub locally in our apps, with a GenServer to subscribe+re-broadcast using pg_notify).&lt;p&gt;This has been a really elegant and low-complexity way to get distributed pubsub without the complexity of running a distributed erlang cluster (which seems a lil bit painful in a K8S+Continuous Deploy world)&lt;p&gt;There -are- some big downsides to be aware of though.&lt;p&gt;1. You can&amp;#x27;t use PgBouncer w&amp;#x2F; LISTEN&amp;#x2F;NOTIFY. This has been really painful because of the high memory overhead of a pgsql connection + elixir keeping a pool of open pgsql connections. The tried and true method of scaling here is to just use PgBouncer. We&amp;#x27;ve kicked the can on this by vastly over-provisioning our pg instance, but this has cost $10s of thousands on the cloud. Of course, it&amp;#x27;s solvable (dedicated non-pgbouncer connection pool just for LISTEN&amp;#x2F;NOTIFY, for example), but painful to unwind.&lt;p&gt;2. The payload has a fixed size limit (8KB, IIRC). This has bitten us a few times!&lt;p&gt;Even though I really like pg_notify, I think that if I were starting over, I&amp;#x27;d probably just use Redis Pub&amp;#x2F;Sub to accomplish the same thing. Tad bit more complex if you&amp;#x27;re not already running Redis, but without the downsides. (Of course, w&amp;#x2F; Redis, you don&amp;#x27;t get the elegance of firing a notification via a pg trigger)</text></item></parent_chain><comment><author>the_angry_angel</author><text>For #1 I&amp;#x27;ve been keeping a keen eye on pgcat [1], in particular the &lt;a href=&quot;https:&amp;#x2F;&amp;#x2F;github.com&amp;#x2F;postgresml&amp;#x2F;pgcat&amp;#x2F;issues&amp;#x2F;303&quot;&gt;https:&amp;#x2F;&amp;#x2F;github.com&amp;#x2F;postgresml&amp;#x2F;pgcat&amp;#x2F;issues&amp;#x2F;303&lt;/a&gt; which 
implies that it should be possible to add support for transaction mode LISTEN&amp;#x2F;NOTIFY support.&lt;p&gt;[1] &lt;a href=&quot;https:&amp;#x2F;&amp;#x2F;github.com&amp;#x2F;postgresml&amp;#x2F;pgcat&quot;&gt;https:&amp;#x2F;&amp;#x2F;github.com&amp;#x2F;postgresml&amp;#x2F;pgcat&lt;/a&gt;</text></comment>