In PostgreSQL, the log warns 'NOTIFY queue is 50% full' while pricing updates keep notifying; what is filling the queue, and what happens at 100%?
answer
- one queue for the whole server
- tail held by the slowest listener
- listener stuck in an open transaction
- failure lands on the sender's commit
- usage fraction, restart-only page cap
basics
~20 sThe queue frees an entry only after every listener reads past it, so one listener stuck in an open transaction pins it. When full, each notifying transaction fails at commit with 'too many notifications in the NOTIFY queue'.
solid answer
~50 sCommitted notifications sit in one server-wide queue of SLRU pages under `pg_notify/`, capped by `max_notify_queue_pages` (default 1048576 pages of 8 kB, about 8 GB, changeable only with a restart). An entry is freed only when **every** listening backend has read past it, and a listener reads only when it is idle between transactions. So a session that ran `LISTEN` and is now idle in transaction, or running a long query, holds the tail while pricing notifications pile up. From 50% the server logs `NOTIFY queue is N% full` at most every 5 seconds, with the PID of a process among those with the oldest transactions; `pg_notification_queue_usage()` returns the fraction in use. At 100%, a notifying transaction's commit fails with SQLSTATE `54000` and rolls back, price update included. Fix: end that session's transaction or `pg_terminate_backend()` it.
code
console · 5 linesWARNING: NOTIFY queue is 50% full
DETAIL: The server process with PID 41877 is among those with the oldest transactions.
HINT: The NOTIFY queue cannot be emptied until that process ends its current transaction.
...
ERROR: too many notifications in the NOTIFY queuego deeper
Recall that pending notifications sit in a bounded queue and that a full queue makes notifying transactions fail.
Explain why the slowest listener sets the queue tail, and name max_notify_queue_pages and pg_notification_queue_usage() with what each tells you.
Diagnose it end to end: the warning's PID, the listener idle in transaction, termination, an idle-transaction timeout and an alert on queue usage.
Weigh the blast radius: one misbehaving consumer can fail every notifying writer on the server, which argues for isolated listener connections.
## Where notifications wait Committed notifications go into **one queue for the whole server**, shared by every database. It is stored as SLRU pages in the `pg_notify` directory of the data directory, with recently used pages cached in shared memory (`notify_buffers`, default 16 blocks). The capacity is set by **`max_notify_queue_pages`**: - default `1048576` pages of 8 kB, about **8 GB** of disk; - it can only be set at server start, so a change needs a restart; - the queue is never WAL-logged and is emptied whenever the server starts. Each entry stays until **every** listening backend has read past it. Every listener keeps its own read position; the queue's tail can only advance to the oldest of them, and pages behind the tail are truncated periodically by notifying backends as they move the head forward. ## Why one listener can pin it 1. A listening backend reads the queue only when it is idle **between** transactions. 2. A listener that has executed `LISTEN` and then sits in an open transaction, such as idle in transaction after a forgotten `BEGIN`, or running a long report on the same connection, never reaches that point. 3. Its read position stops, so the tail stops, so every later notification from every database accumulates behind it. 4. Pricing updates keep notifying, and the queue grows towards its cap. The culprit can sit in a **different database** from the senders. Each entry is tagged with its database, but the queue is shared, so listeners everywhere must advance past every entry; the server even signals far-behind listeners in other databases to make them catch up. ## The signals before it fills | Signal | Where | Meaning | |---|---|---| | `pg_notification_queue_usage()` | SQL function returning `double precision` | fraction, 0 to 1, of the maximum size in use | | `WARNING: NOTIFY queue is 50% full` | server log and the notifying client | emitted at most every 5 seconds once usage reaches one half | | `DETAIL` naming a PID | same message | a server process among those with the oldest transactions | | `HINT` | same message | the queue cannot be emptied until that process ends its current transaction | An alert on `pg_notification_queue_usage()` set well below 0.5 gives far earlier warning than waiting for the log line. Because the cap is large, the queue fills slowly at ordinary notification rates. A queue that is half full almost always means a listener has been stuck for a long time, often a connection someone opened for debugging or a code path that issued `BEGIN` on the listener connection, rather than a sudden burst of pricing updates. That makes the PID in the `DETAIL` line the most useful piece of the message. ## What 100% does When a notifying transaction reaches pre-commit and finds no room, it raises `ERROR: too many notifications in the NOTIFY queue` with SQLSTATE **54000** (`program_limit_exceeded`). This happens before the commit record is written, so **the whole transaction rolls back**: the price update fails together with its notification. The failure lands on the writers, not on the stuck listener, and every writer that notifies keeps failing until space is freed. Transactions that do not notify commit normally. ## Fixing and preventing it - **Free the tail now.** Take the PID from the warning, confirm in `pg_stat_activity` that it is a listener sitting in a transaction, and end that transaction or call `pg_terminate_backend(pid)`. A terminated backend stops counting as a listener, and the tail can advance. - **Keep listener connections idle.** A listener connection should only `LISTEN` and wait; queries belong on other connections. - **Bound idle transactions.** Setting `idle_in_transaction_session_timeout` (default `0`, which disables it) for the listener role ends a forgotten `BEGIN` automatically. - **Do not just raise the cap.** A larger `max_notify_queue_pages` needs a restart, consumes more disk under `pg_notify/` and only delays the same failure. - **Monitor it.** Graph `pg_notification_queue_usage()` alongside the age of listener transactions. ## The interview angle The trap answers are *the queue drops old messages* and *the slow listener gets the error*. Neither is true. PostgreSQL protects delivery to current listeners by refusing new notifications, so the cost of one stuck listener is paid by every writer on the server that notifies, which is why a listener connection must never be used for anything else.
- Should you fix a filling PostgreSQL NOTIFY queue by raising max_notify_queue_pages?Rarely. It can only be set at server start, so it costs a restart, and it lets `pg_notify/` grow further on disk. The queue fills because a listener stopped reading, and a bigger cap only delays the same commit failures. Fix the stuck listener and alert on `pg_notification_queue_usage()`.
- In PostgreSQL, can a stuck listener in a different database fill the queue for this database's writers?Yes. There is one notification queue for the whole server; entries carry a database OID, but the tail can only advance past the oldest read position of every listener in every database. A listener idle in transaction in `reporting` can therefore make `shop`'s notifying commits fail.
- In PostgreSQL, what does the writing application see when the NOTIFY queue is full?Its `COMMIT`, or the autocommitted statement, fails with `too many notifications in the NOTIFY queue`, SQLSTATE `54000`, and the transaction is rolled back, data changes included. Retrying fails too until the stuck listener is cleared and the tail advances.
- A full NOTIFY queue makes PostgreSQL silently drop the oldest notifications.
- Only the slow listener's own session gets errors when the queue fills.
- max_notify_queue_pages can be raised with a configuration reload.
- The notification queue is per database, so other databases cannot fill ours.
- pg_notification_queue_usage() returns the number of pending notifications.