Nobody asked for max_notify_queue_pages. It exists because a limit that used to enforce itself stopped doing so, and something had to take its place.
LISTEN/NOTIFY runs on a single queue for the whole cluster, stored as SLRU pages under pg_notify/ in the data directory. Through PostgreSQL 16 that queue topped out at 8GB, and the number was an accident of plumbing: segment files had four-hex-digit names and held 32 pages each, and the SLRU code would conclude the queue had wrapped around if more than half of that range was in use. Half of 2,097,152 pages at 8kB apiece is 8GB. PostgreSQL 17 gave SLRUs 64-bit page numbers, pg_notify got fifteen-digit file names, and the wraparound went away, along with the only thing capping the queue. The commit that did it added this parameter with a default of 1048576 pages, which is exactly the old limit. The PostgreSQL 17 release notes don’t mention it.
The value is a bare count of pages, not a memory size, so '512kB' is rejected. The minimum is 64. The maximum is 2147483647, which is 16TB at the default block size, should you be feeling ambitious. Context is postmaster. It allocates nothing (the queue’s shared memory is notify_buffers, a different parameter); the server consults it in exactly two places, the check for a full queue and the denominator of pg_notification_queue_usage().
Three ways to fill 8GB
Notifications are appended to the queue at commit. Each listening backend keeps its own read position, and the tail can advance only as far as the slowest of them. A backend reads the queue when it is idle and outside a transaction, so one listening session that never gets there holds the tail in place for everyone while the head keeps moving.
At 50% full, the notifying session gets a WARNING (also logged, at most once every five seconds) with the PID of the laggard. At 100%, every transaction that issued a NOTIFY fails at COMMIT with too many notifications in the NOTIFY queue, SQLSTATE 54000. The whole transaction rolls back, notification and everything else: if the NOTIFY lives in a trigger on orders, you can no longer write to orders. There is one queue per cluster, so this happens in every database, including ones with no listeners at all. (Notifying commits are also serialized cluster-wide by a heavyweight lock, which is the more famous complaint about NOTIFY at scale. This is the less famous one.)
I set the parameter to 64 on 18.6 and got there three ways. The documented one: a session runs LISTEN, then sits idle in transaction. The variant nobody pictures when reading that: the listening session is active in a long statement, no BEGIN required. And the one I didn’t expect: a listening session that is plain idle, no transaction of any kind, whose client has stopped reading its socket. The backend fills the socket buffer, blocks with a wait_event of ClientWrite, and pins the tail like the others. A pooled connection that ran LISTEN once and went back into the pool without an UNLISTEN * is one way to build such a thing. The warning still advises that the queue can’t be emptied until that process “ends its current transaction.” It doesn’t have one.
Through 18, the stuck listener doesn’t even have to care about your notifications. A session in another database, listening on a channel nobody ever notifies, pinned the queue just the same. PostgreSQL 19 reworks this so that a notifier can move an uninterested listener’s position forward itself. On 19 beta 3 that session no longer pinned anything, right up until I sent a single notification on its own channel, after which it held up all traffic behind it exactly as before.
The knob is not the fix
The NOTIFY documentation says the queue “should be sufficiently sized for almost every use case,” and it’s right, which is the problem. At 1,000 notifications a second with a short channel name and a UUID payload (60 bytes per entry), 8GB lasts about forty hours. The first warning arrives at twenty. A session that has been idle in transaction for twenty hours has done worse things to you by then than fill a queue: it’s holding locks, and if it has written anything it’s holding back vacuum too.
So don’t wait for the warning. I pushed 200 page-sized notifications through that 64-page queue past a listener that was keeping up; nothing failed, and pg_notification_queue_usage() read zero at the end of it. Healthy is zero, so alert on 0.01, which at the default size is about 80MB of backlog and, at the rate above, 24 minutes of stall. There is no SQL-visible list of which backends are listening; the PID in the warning’s DETAIL is what you get, and pg_stat_activity will tell you which of the three cases you have. pg_terminate_backend() works on all three, and the queue drains at once: usage went to zero and the segment files were gone by the next check.
To keep it from recurring, idle_in_transaction_session_timeout covers the first case, and transaction_timeout (17 and later) covers the first two. The third is harder. idle_session_timeout doesn’t fire on a backend blocked in ClientWrite (I tried), and it would be the wrong tool anyway, since sitting idle for days is a listener’s entire job. What does work, for TCP connections, is tcp_user_timeout. With it at 10s, the kernel dropped a stalled client’s connection within about ten seconds, the backend exited, and not one NOTIFY failed; at the default of 0, the backend sat in ClientWrite until I killed it. It needs a platform with TCP_USER_TIMEOUT (Linux has it, Windows doesn’t), it is ignored on Unix-socket connections, and it can be set on just the role that listens. It’s a backstop. Fix the client.
Leave the parameter alone. Raising it buys you a later alarm. Lowering it makes sense in one situation: a data volume where 8GB of unplanned files in pg_notify/ would itself be the outage, and you’d prefer the smaller one. (The minimum of 64 has one other use, on a staging box: it lets you find out in an afternoon what your application does when COMMIT starts returning 54000, which before 17 took a recompile.) Either way you can’t do it during the incident, because changing it takes a restart, and a restart empties the queue (it isn’t WAL-logged and doesn’t survive one) and disconnects whoever was blocking it. The restart fixes the problem. The new value is just along for the ride.