How to listen to database changes using Postgres triggers in elixir

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)

There -are- some big downsides to be aware of though.

1. You can't use PgBouncer w/ LISTEN/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'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's solvable (dedicated non-pgbouncer connection pool just for LISTEN/NOTIFY, for example), but painful to unwind.

2. The payload has a fixed size limit (8KB, IIRC). This has bitten us a few times!

Even though I really like pg_notify, I think that if I were starting over, I'd probably just use Redis Pub/Sub to accomplish the same thing. Tad bit more complex if you're not already running Redis, but without the downsides. (Of course, w/ Redis, you don't get the elegance of firing a notification via a pg trigger)

It reminds me of a very similar post I put out in 2018 https://by.ben.church/Get-notified-of-user-signups-and-plan-...

But I think Peter did a much better job going through the mechanics and providing a more modernized example.

For those that are curious there are pitfalls (that can be worked around)

1. If your DB goes down you may loose messages

2. If you have multiple backends behind a load balancer you may trigger additional events

3. There is a limit to the payload size you can send through these triggers

But for those that want to try this approach I do have a library here that does wraps everything Peter layed out: https://github.com/bnchrch/postgrex_pubsub

Also if you want something even better I recommend WalEx https://github.com/cpursley/walex

Which is based on WAL logs and doesnt have the same limitations.

> PERFORM pg_notify('appointments_canceled_changed', payload);

> 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.

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/processed events.

We're already tracking changes for the purposes of time travel queries and other auditing purposes using Temporal Tables (SQL:2011 feature). I'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.

Anyone see any problems with this approach?

FWIW, we're (estuary.dev) an open-source and fully managed tool for building CDC pipelines out of Postgres from the WAL.

High notes for inserts, low rumbles for reads or something. That could be pretty interesting actually.