The notifier pattern for applications that use Postgres

Take a look at Materialize, Noria and the family of Differential&#x2F;Timely Dataflow technologies. It&#x27;s the same concept on steroids, you can subscribe to arbitrary queries and efficiently receive any changes to that view. You can also efficiently maintain any materialized view for extremely fast reads for known queries.<p>An automatic stream processing pipeline for maintaining caches and listening to complex real-time events.<p>Quite underrated, it has so much promise. The concept is not new but it&#x27;s still semi-stuck in Rust-land. It&#x27;s becoming more mainstream with Materialize, which is technically open-source, but they are quite aggressive with pushing their expensive cloud and offuscating on-prem usage.<p><a href="https:&#x2F;&#x2F;github.com&#x2F;MaterializeInc&#x2F;materialize">https:&#x2F;&#x2F;github.com&#x2F;MaterializeInc&#x2F;materialize</a><p><a href="https:&#x2F;&#x2F;github.com&#x2F;mit-pdos&#x2F;noria">https:&#x2F;&#x2F;github.com&#x2F;mit-pdos&#x2F;noria</a><p><a href="https:&#x2F;&#x2F;timelydataflow.github.io&#x2F;differential-dataflow&#x2F;" rel="nofollow">https:&#x2F;&#x2F;timelydataflow.github.io&#x2F;differential-dataflow&#x2F;</a><p><a href="https:&#x2F;&#x2F;timelydataflow.github.io&#x2F;timely-dataflow&#x2F;" rel="nofollow">https:&#x2F;&#x2F;timelydataflow.github.io&#x2F;timely-dataflow&#x2F;</a>

This post misses the most important part of LISTEN &#x2F; NOTIFY: transactions<p>The notify piece respects your current transactional state. So of you issue a notify within a transaction, it’s only delivered if the transaction commits. If the transaction rolls back, the notification is discarded.<p>This leads to the common pattern of combining the notification with insertion into a work queue. That solves the “listener was not listening yet” problem too.

I was recently very annoyed by my immich server on my NAS doing constant writes to risk. I discovered that this was due to the use of the postgres notify backed socket.io plugin. It turns out while the notify itself does not use the WAL for any information, one needs to trigger a WAL flush for the notify to propagate. In my case this lead toa lot of unnecessary empty WAL writes. If you scale up applications or anyways have constant writes this won&#x27;t matter. But for a home lab server this is suboptimal.

Other than the space for past notifications and&#x2F;or having to issue a DELETE, are there significant reasons to prefer this over the typical table-based approach with SKIP LOCKED queries to poll the queue?<p>It seems to me that if the listener dies, notifications in the meantime will be dropped until a listener resubscribes, right? That seems prone to data loss.<p>In the SKIP LOCKED topic-poller style pattern (for example, query a table for rows with state = &#x27;ready&#x27; on some interval and use SKIP LOCKED), you can have arbitrary readers and if they all die, inserts into the table still go through and the backlog can later be processed.

Great post!<p>Postgres.js does this implicitly through a simple API[1] mimicking the postgres way, thereby using only a single dedicated connection for listening per process.<p>Listen&#x2F;notify is also super useful with triggers.<p>[1] <a href="https:&#x2F;&#x2F;github.com&#x2F;porsager&#x2F;postgres#listen--notify">https:&#x2F;&#x2F;github.com&#x2F;porsager&#x2F;postgres#listen--notify</a>

&quot;Instead, a received notification is immediately sent into buffered channel, which means it’s discarded if the channel is full&quot;<p>Shouldn&#x27;t the channel rather block than discard if full?<p>Anyway, nice and relevant article for me as I&#x27;ve recently added a few listeners to my app. I chose the naive approach since I only have two topics and a surplus of connections

I built a service using LISTEN&#x2F;NOTIFY a while ago, but the fact that Postgres will instantly drop all messages to a given channel if there are no registered listeners made me nervous.<p>This felt like a big caveat to me, taking what could be an extremely well-guaranteed approach (e.g. a db trigger that writes a row to an event table, which is being polled by an application process) into an ops risk, where transient network issues could result in data loss. You’d want idempotent tasks and zero-downtime deployments, but it feels like the benefits don’t really justify the risk. If you want a topic exchange just have the polling process forward to a real message broker.<p>I like the concept of sending messages straight from the db, and we did run my service in production (for non-critical stuff), but unless something has changed with the way this works I would still be wary.

&gt; [...] was somewhat shocked recently looking into MySQL and SQLite to learn that even in 2024, no equivalent exists.<p>I&#x27;m not sure how one would make `NOTIFY` work on SQLite3. Maybe one could have a system table (`sqlite_temp_notifies`?) that would store notifies, and then have a directory associated with the database where one can create pipes&#x2F;AF_LOCAL sockets that a process&#x2F;thread running `NOTIFY` would attempt to write to in a non-blocking manner, then delete the notifies from that system table. The system table would only be needed to make notifies reliable while &quot;connected&quot; to the database and LISTENing (i.e., having registered the pipe&#x2F;socket).<p>I agree that that would be very useful.<p>A couple of things:<p>- LISTEN&#x2F;NOTIFY lacks authorization on the channel. Indeed, channels are not even CREATEd. This means you cannot trust NOTIFY payloads if users you don&#x27;t trust have access to the database.<p>- NOTIFYs are lost if no client is LISTENing.<p>In practice this means that NOTIFYs are only good for waking clients that then have to check what&#x27;s up with a query.

Any post from brandur is a must read. Love the post

As someone who came into a project that uses postgres notify, it was pretty confusing, as your app would connect to other apps db to listen for changes. There also was no documented nor enforced data format, so it was very annoying to deal with in practice.

Isnt this pattern already implemented in javascript by pg-promise? <a href="https:&#x2F;&#x2F;github.com&#x2F;vitaly-t&#x2F;pg-promise&#x2F;wiki&#x2F;Robust-Listeners">https:&#x2F;&#x2F;github.com&#x2F;vitaly-t&#x2F;pg-promise&#x2F;wiki&#x2F;Robust-Listeners</a>

There’s also the payload size limit for notification messages, requiring consumers to do a fetch in order to get the complete message. There’s still the potential there, if your message payloads are large, to have consumers contend for connections with the main application.

I thought that listen was bad because it holds connections unnecessarily and defeats connection pooling.

I looked into using LISTEN&#x2F;NOTIFY recently and I must be misunderstanding. How is it different to polling a table? It seemed like it had all the same machinery behind it.