PostgreSQL 17 replaces NOTIFY's accidental limit with an explicit parameter

Up through PostgreSQL 16, the LISTEN/NOTIFY queue had an 8GB ceiling that nobody designed on purpose. It was a consequence of how the queue's segment files were named: four-digit hexadecimal names, 32 pages per file, and an SLRU (Simple LRU) logic that concluded the queue had wrapped around completely if more than half of that space was occupied. Half of 2,097,152 pages of 8kB each comes out to exactly 8GB. The number was never a capacity decision: it was the point where the wraparound arithmetic stopped working.

Christophe Pettus's post on Planet PostgreSQL, part of the "All Your GUCs in a Row" series, reconstructs this history to explain why PostgreSQL 17 had to introduce the max_notify_queue_pages GUC. Version 17 gave SLRUs 64-bit page numbers, files in pg_notify/ started having fifteen-digit names, and the wraparound that defined the old limit disappeared. Except it had unintentionally been the only thing keeping the queue from growing indefinitely. The commit that fixed the wraparound had to put something in its place: a parameter with a default value of 1,048,576 pages, exactly the old 8GB limit. PostgreSQL 17's release notes don't mention the change.

A GUC that stores nothing

It's worth noting what max_notify_queue_pages does and doesn't do, because the name suggests memory allocation, and that's not it. The value is a pure page count, not a size: writing '512kB' is rejected by the parser. The minimum is 64 pages, the maximum is 2147483647, which comes out to 16TB at the default block size. The context is postmaster, meaning changing the value requires a server restart. And the parameter doesn't allocate any memory at all: the queue's shared memory comes from notify_buffers, a separate GUC. The server checks max_notify_queue_pages in exactly two places: in the full-queue check and in the denominator of pg_notification_queue_usage().

How the queue actually fills up

Notifications are appended to the queue at commit. Each backend that has issued LISTEN keeps its own read position, and the queue's tail only advances as far as the slowest listener has already read. A backend only reads the queue when it's idle and outside a transaction. This means a single listening session that never reaches that state pins the tail for everyone, while the head keeps advancing.

At 50% occupancy, the notifying session receives a WARNING (also logged, at most once every five seconds) with the PID of the stuck session. At 100%, every transaction that issued NOTIFY fails at COMMIT with the error too many notifications in the NOTIFY queue, SQLSTATE 54000. The entire transaction is rolled back, notification and everything else along with it: if the NOTIFY is in a trigger on the orders table, the application simply can no longer write to orders. And since there is a single queue per cluster, this happens across every database, including those with no listeners at all. Pettus also notes that notifying commits are already serialized by a heavyweight cluster-level lock, the better-known problem with NOTIFY at scale. Queue overflow is the lesser-known problem.

The three ways to stall the queue

Testing with the parameter reduced to 64 pages on PostgreSQL 18.6, Pettus reproduced three distinct stall scenarios:

• The documented one: a session runs LISTEN and sits idle inside a transaction (idle in transaction).
• The variant nobody expects: the listening session is active inside a long-running statement, with no BEGIN involved at all.
• The unexpected case: a listening session that's completely idle, with no transaction at all, whose client stopped reading the socket. The backend fills the socket buffer, blocks with wait_event equal to ClientWrite, and pins the tail exactly like the other two cases. A pooled connection that ran LISTEN once and went back to the pool without an UNLISTEN * is a common way to create this situation. And the WARNING text still recommends waiting for the process to "finish its current transaction," even when it doesn't have one.

Through version 18, the stuck backend doesn't even need to care about the notifications in question: a session in a different database, listening on a channel that nobody ever notifies, pins the queue just the same. PostgreSQL 19 reworks this behavior so that the notifying backend can push forward the position of an uninterested listener on its own. In Pettus's tests with version 19 beta 3, this worked until he sent a single notification on that session's own channel: from then on, it went back to stalling everything behind it, as before.

The parameter doesn't solve the problem

The NOTIFY documentation says the queue "should be large enough for almost any use case," and Pettus agrees: that's exactly the problem. At 1,000 notifications per second, with a short channel name and a UUID payload (60 bytes per entry), the default 8GB lasts about 40 hours. The first WARNING arrives at 20 hours. A session that has been idle in transaction for 20 hours has already caused damage worse than filling a queue by that point: it's holding locks and, if it wrote anything, it's blocking vacuum too.

The practical recommendation, tested by Pettus, is not to wait for the 50% warning. He pushed 200 full-page-sized notifications through a 64-page queue with a listener that stayed caught up, and nothing failed: pg_notification_queue_usage() read zero at the end. A healthy reading is zero, and the alert should trigger at 0.01, which at the default queue size equals about 80MB of accumulated lag and, at the rate in the example 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's available, and pg_stat_activity helps identify which of the three scenarios is at play. pg_terminate_backend() works in all three cases and drains the queue at once.

What prevents recurrence

For the first scenario, idle_in_transaction_session_timeout solves it. For the first and second, transaction_timeout (available starting in PostgreSQL 17) covers both. The third case is the hardest: idle_session_timeout doesn't fire on a backend blocked in ClientWrite, as Pettus confirmed by testing, and even if it did fire it would be the wrong tool, since sitting idle for days is literally a listener's job.

What works, for TCP connections, is tcp_user_timeout. With the value set to 10 seconds, the kernel dropped a stuck client's connection in about ten seconds, the backend exited, and no NOTIFY failed; with the default of 0, the backend stayed stuck in ClientWrite until killed manually. The caveat is that the parameter depends on platform support (TCP_USER_TIMEOUT exists on Linux, not on Windows), is ignored on Unix socket connections, and can be set for a specific role that does LISTEN. It's a backstop, not a fix: the right move is to fix the client that stopped reading the socket.

When (not) to change the value

Pettus's conclusion is direct: leave the parameter alone. Increasing max_notify_queue_pages only buys a later alarm for the same structural problem. Lowering it makes sense in one specific scenario: a data volume where an unexpected 8GB of files in pg_notify/ would, by itself, cause an outage, in which case the smaller outage is preferable. The minimum value of 64 has yet another use, in a staging environment: it lets you discover in an afternoon what the application does when COMMIT starts returning 54000, something that before PostgreSQL 17 required recompiling the binary to test.

Either way, you can't change the parameter during the incident, because the change requires a server restart, and the restart by itself empties the queue (it isn't logged to WAL and doesn't survive a restart) and disconnects whoever was causing the stall. The restart solves the problem. The new GUC value is just along for the ride.