How to listen to database changes using Postgres triggers in elixir

I&#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).<p>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)<p>There -are- some big downsides to be aware of though.<p>1. You can&#x27;t use PgBouncer w&#x2F; LISTEN&#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&#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&#x27;s solvable (dedicated non-pgbouncer connection pool just for LISTEN&#x2F;NOTIFY, for example), but painful to unwind.<p>2. The payload has a fixed size limit (8KB, IIRC). This has bitten us a few times!<p>Even though I really like pg_notify, I think that if I were starting over, I&#x27;d probably just use Redis Pub&#x2F;Sub to accomplish the same thing. Tad bit more complex if you&#x27;re not already running Redis, but without the downsides. (Of course, w&#x2F; Redis, you don&#x27;t get the elegance of firing a notification via a pg trigger)

This was a fun read!<p>It reminds me of a very similar post I put out in 2018 <a href="https:&#x2F;&#x2F;by.ben.church&#x2F;Get-notified-of-user-signups-and-plan-changes-automatically-using-Postgres-and-Phoenix-PubSub&#x2F;" rel="nofollow noreferrer">https:&#x2F;&#x2F;by.ben.church&#x2F;Get-notified-of-user-signups-and-plan-...</a><p>But I think Peter did a much better job going through the mechanics and providing a more modernized example.<p>For those that are curious there are pitfalls (that can be worked around)<p>1. If your DB goes down you may loose messages<p>2. If you have multiple backends behind a load balancer you may trigger additional events<p>3. There is a limit to the payload size you can send through these triggers<p>But for those that want to try this approach I do have a library here that does wraps everything Peter layed out: <a href="https:&#x2F;&#x2F;github.com&#x2F;bnchrch&#x2F;postgrex_pubsub">https:&#x2F;&#x2F;github.com&#x2F;bnchrch&#x2F;postgrex_pubsub</a><p>Also if you want something even better I recommend WalEx <a href="https:&#x2F;&#x2F;github.com&#x2F;cpursley&#x2F;walex">https:&#x2F;&#x2F;github.com&#x2F;cpursley&#x2F;walex</a><p>Which is based on WAL logs and doesnt have the same limitations.

&gt; Postgres offers quick and simple Notifications that can help you react to changes in your database without much overhead. They are particularly interesting if you can’t use Phoenix’s PubSub, for example, if another non-Elixir application also makes changes to your database.<p>&gt; PERFORM pg_notify(&#x27;appointments_canceled_changed&#x27;, payload);<p>&gt; Be aware that this listener can easily become a bottleneck if you have lots of messages. If you can’t handle the messages quickly enough, the message queue will fill up and crash your application. If you’re worried about this case, you could create one listener per channel or use a PartitionSupervisor to start more handlers and spread out the work.<p>Why not insert into an events table instead of pg_notify? That way the events are recorded within the database itself, can be processed by any component, the state of processing can be saved in the table so even if the component dies, it can resume (and can even fan out the actual processing to workers). Further, you have the record of all events alongwith the flexibility of interacting with the event information with SQL and with partitioning, you can have a clean way to manage performance + ability to easily archive past&#x2F;processed events.

If you want to listen to database changes, check out Debezium. Instead of triggers it takes advantage of the more recent CDC functionality that most SQL Servers has implemented today. The difference is that triggers works on every transaction while CDC works on a redo log file. This makes it possible to transfer database changes with minimal performance impact.

Listening to your database — is there a way to create an audio stream so you can actually &quot;listen to&quot; reads (a low hum), writes (tinkling wind chimes), and failed transactions (BANG! CRASH! BOOM!)? That would be a useful ambient sound for a DBA&#x27;s office, wouldn&#x27;t it?

I thought this was going to be about using audio to literally listen to the database along the lines of &quot;What different sorting algorithms sound like&quot; <a href="https:&#x2F;&#x2F;www.youtube.com&#x2F;watch?v=t8g-iYGHpEA">https:&#x2F;&#x2F;www.youtube.com&#x2F;watch?v=t8g-iYGHpEA</a>

I actually have this exact problem right now with SQL Server on AWS RDS. Unless I want to pay for standard+ editions in my dev&#x2F;stage&#x2F;qa&#x2F;etc. environments, I can&#x27;t use the baked in CDC features. And because of the minimum instance sizes for Standard+ edition, it costs ~1700 bucks per month per database. This is fine for production, because I need features like High Availability, but paying a <i>significant</i> premium over web&#x2F;express in those environments seems like lighting money on fire.<p>We&#x27;re already tracking changes for the purposes of time travel queries and other auditing purposes using Temporal Tables (SQL:2011 feature). I&#x27;m thinking a cron job triggering a lambda every minute should be sufficient to read from the history tables and publish out change data events over a bus.<p>Anyone see any problems with this approach?

Debezium is a cool project, though as a fair warning does come with a fair amount of on-going maintenance. And you should prolly be using Kafka and comfortable with the JVM generally.<p>FWIW, we&#x27;re (estuary.dev) an open-source and fully managed tool for building CDC pipelines out of Postgres from the WAL.

Pretty cool! not the same but for flyway + java I have a simple github action that on push to develop checks the diff for any *.sql files and mails them to the DA team, as they dont have access to our repo

I was hoping for some sort of audio stream to listen to my database changes.<p>High notes for inserts, low rumbles for reads or something. That could be pretty interesting actually.

This is all fun and games but how do you catch-up after a disconnect? Why choose this over logical replication?

boo! cows don&#x27;t have &quot;hooves instead of feet&quot; and they don&#x27;t lack toes either!

Great article, thank you! Please submit it to <a href="https:&#x2F;&#x2F;elixirstatus.com&#x2F;" rel="nofollow noreferrer">https:&#x2F;&#x2F;elixirstatus.com&#x2F;</a> as well!