8 ms·

The notifier pattern for applications that use Postgres

• dajtxx 2y ago
  I looked into using LISTEN/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.
  • orangeisthe 2y ago
    well it's the same difference between polling an api vs setting up a webhook.
• John23832 2y ago
  I thought that listen was bad because it holds connections unnecessarily and defeats connection pooling.
  • brandur 2y ago
    Listen does hold a connection, but that doesn't mean it defeats connection pooling. 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.
    • John23832 2y ago
      Thank you. The more you know.
  • justinclift 2y ago
    My application tends to open a single, extra connection to the database server (not through the main pool) just for receiving notifys. That connection then feeds them to whatever part of the application needs them. 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.
• porsager 2y ago
  Great post! 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. [1] https://github.com/porsager/postgres#listen--notify https://github.com/porsager/postgres#listen--notify
• koolba 2y ago
  This post misses the most important part of LISTEN / NOTIFY: transactions 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.
  • brandur 2y ago
    Author here. The behavior of notify with respect to transactions is indeed notable, and definitely a great feature that makes them distinct from pub/sub in other systems. Notifies fire only when data is ready after the transaction is committed, and they're also deduplicated based on payload so listeners don't have to react to many of the same message unnecessarily. 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.
    • koolba 2y ago
      Ha! I just realized that the “notifier” in the post title refers to the handling on the client side.
  • cryptonector 2y ago
    Also, because there are no authorization controls on who can `NOTIFY` on a given channel (indeed, channels aren't even `CREATE`d objects) you can't really rely on the notification payload, so you really do have to go query a work queue.
• salojoo 2y ago
  "Instead, a received notification is immediately sent into buffered channel, which means it’s discarded if the channel is full" 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
  • brandur 2y ago
    Author here. The Go channel send behavior could certainly be altered depending on the particular semantics of the application, but the reason I chose to use a non-blocking buffered channel is so that no particular subcomponent can slow down the distribution of notifications for everybody. > 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. --- [1] https://gobyexample.com/channels https://gobyexample.com/channels
    • salojoo 2y ago
      We probably both understand the underlying concepts correctly. But just in case: also buffered channels will block if full. So I don't see how notifications get discarded (meaning lost). But somehow that's what your text says?
      • brandur 2y ago
        They'll get lost when using a non-blocking send with select/default: messages := make(chan string) select { case messages <- "hi": fmt.Println("sent message", msg) default: fmt.Println("no message sent") } The reason you'd use a non-blocking send is to make sure that in the event of one slow consumer that the entire system doesn't slow down. 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.
• mcqueenjordan 2y ago
  Other than the space for past notifications and/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? 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.
  • brandur 2y ago
    In Postgres listen/notify are inherently lossy channels — if a notification goes out while a listener wasn't around to receive it, it's gone, so they should never be relied upon in cases where data consistency is at stake. 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. [1] https://brandur.org/fragments/instant-feature-flags https://brandur.org/fragments/instant-feature-flags
    • mcqueenjordan 2y ago
      Got it, thanks for the reply. The feature flag cache reload use case seems like reasonable one to me.
  • thom 2y ago
• vivzkestrel 2y ago
  Isnt this pattern already implemented in javascript by pg-promise? https://github.com/vitaly-t/pg-promise/wiki/Robust-Listeners https://github.com/vitaly-t/pg-promise/wiki/Robust-Listeners
• shayonj 2y ago
  Any post from brandur is a must read. Love the post
  • brandur 2y ago
    Thanks Shayon!!! Very kind of you :)
• macNchz 2y ago
  I built a service using LISTEN/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. 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.
  • bouncing 2y ago
    When I've used it, I've used it in conjunction with a table that maintains state. So basically the notify keeps other processes from having the poll the table for updates, but the table itself still acts as the queue.
• oersted 2y ago
  Take a look at Materialize, Noria and the family of Differential/Timely Dataflow technologies. It'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. 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/MaterializeInc/materialize https://github.com/mit-pdos/noria https://github.com/mit-pdos/noria https://timelydataflow.github.io/differential-dataflow/ https://timelydataflow.github.io/differential-dataflow/ https://timelydataflow.github.io/timely-dataflow/ https://timelydataflow.github.io/timely-dataflow/
  • bouncing 2y ago
    All cool stuff. 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.
    • oersted 2y ago
      Indeed! It would be so much better if this were a Postgres extension instead. There are some efforts but still quite immature: https://github.com/sraoss/pg_ivm 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.
      • benesch 2y ago
        > It would be so much better if this were a Postgres extension instead. 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 https://www.postgresql.org/docs/current/postgres-fdw.html
• jakjak123 2y ago
  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.
• riedel 2y ago
  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't matter. But for a home lab server this is suboptimal.
  • zorgmonkey 2y ago
    I'm not an expert in postgres performance, but I would think that excessive WAL flushing would also slow down workloads with constant writes.
• agentultra 2y ago
  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.
• cryptonector 2y ago
  > [...] was somewhat shocked recently looking into MySQL and SQLite to learn that even in 2024, no equivalent exists. 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.