The notifier pattern for applications that use Postgres
1–10 of 56 posts
Re: The notifier pattern for applications that use Postgres
#2I thought that listen was bad because it holds connections unnecessarily and defeats connection pooling.
Re: The notifier pattern for applications that use Postgres
#3I thought that listen was bad because it holds connections unnecessarily and defeats connection pooling.
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.
Re: The notifier pattern for applications that use Postgres
#4I thought that listen was bad because it holds connections unnecessarily and defeats connection pooling.
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.
Re: The notifier pattern for applications that use Postgres
#5Great 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.
Re: The notifier pattern for applications that use Postgres
#6This 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.
Re: The notifier pattern for applications that use Postgres
#7"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
Re: The notifier pattern for applications that use Postgres
#8This 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 no…
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.
Re: The notifier pattern for applications that use Postgres
#9"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
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.
---
Re: The notifier pattern for applications that use Postgres
#10Other 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.