The notifier pattern for applications that use Postgres
brandur.org
brandur.org
An automatic stream processing pipeline for maintaining caches and listening to complex real-time events.
Quite underrated, it has so much promise. The concept is not new but it's still semi-stuck in Rust-land. It'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.
https://github.com/MaterializeInc/materialize
https://github.com/mit-pdos/noria
But to my mind, the main advantage for this postgres workflow is that you get something simple without adding a ton of stuff to your stack.
There are some efforts but still quite immature: https://github.com/sraoss/pg_ivm
But at least Materialize does have Postgres wire compatibility, and same for Noria and MySQL. It's a plug & play switch, it's not as complex as adopting other Pub-Sub, Job-Queue or KV-Caching solutions.
I've thought about this counterfactual a lot. (I'm a big part of the reason that Materialize was not built as a PostgreSQL extension.) There are two major technical reasons that we decided to build Materialize as a standalone product:
1. Determinism. For IVM to be correct, computations must be strictly deterministic. PostgreSQL is full of nondeterministic functions: things like random(), get_random_uuid(), pg_cancel_backend(), etc. You can see the whole list with `SELECT * FROM pg_proc WHERE provolatile <> 'i'`. And that's just scratching the surface. Query execution makes a number of arbitrary decisions (e.g., ordering or not) that can cause nondeterminism in results. Building an IVM extension within PostgreSQL would require hunting down every one of these nondeterministic moments and forcing determinism on them—a very long game of whack a mole.
2. Scale. PostgreSQL is fundamentally a single node system. But much of the reason you need to reach for Materialize is because your computation is exceeding the limit of what a single machine can handle. If Materialize were a PostgreSQL extension, IVM would be competing for resources (CPU, memory, disk, network) with the main OLTP engine. But since Materialize is a standalone system, you get to offload all that expensive IVM work to a dedicated cluster of machines, leaving your main PostgreSQL server free to spend all of its cycles on what it's uniquely good at: transaction concurrency control.
So while the decision to build Materialize as a separate system means there's a bit more friction to getting started, it also means that you don't need to have a plan for what happens when you exceed the limits of a single machine. You just scale up your Materialize cluster to distribute your workload across multiple machines.
One cool thing we're investigating is exposing Materialize via a PostgreSQL foreign data wrapper [0]. Your ops/data teams would still be managing two separate systems, but downstream consumers could be entirely oblivious to the existence of Materialize—they'd just query tables/views in PostgreSQL like normal, and some of those would be transparently served by Materialize under the hood.
[0]: https://www.postgresql.org/docs/current/postgres-fdw.html
> It'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.
Quick but important clarification: Materialize is source available, not open source. We've been licensed under the BSL [0] from the beginning. We feel that the BSL is the best way to ensure we can build a sustainable business to fund Materialize's development, while still contributing our research advances back to the scientific community.
> Quite underrated, it has so much promise.
I'm glad you think so. We think so too. One of the best parts of my job is watching the "aha" moment our prospects have when they realize how much of the complex code they've been writing is neatly expressed as a SUBSCRIBE over a SQL materialized view.
[0]: https://github.com/MaterializeInc/materialize/blob/main/LICE...
What I can say is that the research at the heart of Materialize (https://dl.acm.org/doi/10.1145/2517349.2522738) allows us to efficiently maintain computations that are more complex than what a lot of other IVM systems can handle.
Your best bet is to run your own benchmark of both systems using data that's representative of your workload. We offer a free seven day playground if you'd like to run such a benchmark: https://console.materialize.com/account/sign-up
We also have a community Slack where a number of Materialize employees hang out and answer questions: http://materialize.com/s/chat
For example, v0.28 was released on October 18, 2022, and becomes Apache 2.0 licensed four years after that date (i.e., 2.5 years from today), on October 18, 2026.
[0]: https://github.com/MaterializeInc/materialize/blob/76cb6647d...
Other large projects using the BSL include CockroachDB and (somewhat infamously) Terraform.
We're very glad to have been using the BSL for Materialize since our very first release. Relicensing an existing open source project under the BSL can be a painful transition.
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.
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.
That said, NOTIFY isn't really what this post is about. It concerns itself with the other half of listen/notify by describing a "notifier" pattern, one which listens via `LISTEN` statements and distributes them application subcomponents to help maximize economy around the use of Postgres connections.
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.
In the SKIP LOCKED topic-poller style pattern (for example, query a table for rows with state = 'ready' 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.
I find that the main thing they're useful for is notifying on particular changes so that components that care about them can decrease the time until they process those changes, and without sitting in a hot loop constantly polling tables.
For example, I wrote a piece here [1] describe how we use the notifier to listen for feature flag changes so that each running program can update its flag cache. Those programs could be sitting in loops reloading flags once a second looking for changes, but it's wasteful and puts unnecessary load on the database. Instead, each listens for notifications indicating that some flag state changed, then reloads its flag cache. They also reload every X seconds so that some periodic synchronization happens in case an update notification was missed (e.g. a notifier temporarily dropped offline).
Job queues are another example. You'll still be using `SKIP LOCKED` to select jobs to work, but listen/notify makes it faster to find out that a new job became available.
I think you can eliminate polling if you don't need to retry tasks, by simply processing pending tasks at startup and then just responding to LISTEN events. However, I'm curious if there are any alternatives to polling the queue in cases where you need to support retrying tasks at a given timestamp.
A pubsub pattern like pg_notify can definitely make sense depending on the requirements, but I wouldn't jump to it first. The few times I've used pubsub elsewhere, it was when subscribing to some other team's service, not via a shared DB.
I think the SKIP LOCKED part is really only useful to avoid contention between two workers querying for new work simultaneously.
Because there are no access controls on who can NOTIFY to what channel, you can't rely on the payload, so you really do have to look at a work queue. But if it's just one user, and all you're trying to do is broadcast date fast, then NOTIFY works great.
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.
Listen/notify is also super useful with triggers.
Shouldn't the channel rather block than discard if full?
Anyway, nice and relevant article for me as I'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
> Shouldn't the channel rather block than discard if full?
In Go, a blocking channel is one that's initialized without a size (see [1]). You could have a blocking channel where the sender uses a `select/default` to discard after it's full, but that leaves very little margin of error for the receiver. If it's still processing message 1, and then message 2 comes in and the notifier tries to send it, message 2 is gone.
IMO, better to use a buffered channel with some leeway in terms of size, and then write receivers in such a way that they clear incoming messages as soon as possible. i.e. If messages are expected to take time to process, the receiver spins up a goroutine to do so, or has another internal queue of its own where they're placed so that new messages from the notifier never get dropped.
---
So I don't see how notifications get discarded (meaning lost). But somehow that's what your text says?
messages := make(chan string)
select {
case messages <- "hi":
fmt.Println("sent message", msg)
default:
fmt.Println("no message sent")
}
Imagine a scaled out version of the notifier in which it's listening on hundreds of topics and receiving thousands of notifications. Each notification is received one-by-one using something like Pgx's `ListenForNotification`, and then distributed via channel to subscriptions that were listening for it.
In the case of a blocking send without `default`, one slow consumer that was taking too much time to receive and process its notifications would cause a build up of all other notifications the notifier's supposed to send, so one bad actor would have the effect of degrading the time-to-receive for all listening components.
With buffered channels, a poorly written consumer could still drop messages for itself, which isn't optimal (it should be fixed), but all other consumers will still receive theirs promptly. Overall preferable to the alternative.
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.
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.
So basically the notify keeps other processes from having the poll the table for updates, but the table itself still acts as the queue.
I'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/AF_LOCAL sockets that a process/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 "connected" to the database and LISTENing (i.e., having registered the pipe/socket).
I agree that that would be very useful.
A couple of things:
- LISTEN/NOTIFY lacks authorization on the channel. Indeed, channels are not even CREATEd. This means you cannot trust NOTIFY payloads if users you don't trust have access to the database.
- NOTIFYs are lost if no client is LISTENing.
In practice this means that NOTIFYs are only good for waking clients that then have to check what's up with a query.
That's what I was trying to convey in this blog post: you'll keep a fixed number of connections open for use with listen, but as few as possible by reusing a single connection per program to simultaneously listen on all channels that your application cares about (with the notifier distributing messages to each internal component that subscribed).
With your dedicated listen connections accounted for, the rest of the connection pool can operate normally, with programs checking connections in and out only as long as they need them.
So the net-net is that you have a handful of connections dedicated for listen, and the remaining ~hundreds are part of the connection pool for shared use.
Seems to work "ok" in practise. No real issues, apart from listen/notify not supporting 2 phase commit (2PC) which we'd been thinking of using but haven't investigated since.