PostgreSQL LISTEN/NOTIFY Queue Full: A 512 KiB Reproduction

PostgreSQL reports an error message stating "too many notifications in the NOTIFY queue." To reproduce this issue, the author utilized PostgreSQL version 17, Docker, and two separate psql sessions. Rather than attempting to exhaust PostgreSQL's default multi-gigabyte queue, the experiment was conducted with a queue capacity of only 512 KiB. This was achieved by setting the parameter max_notify_queue_pages to 64, with each page being 8 KiB in size.

In this setup, the LISTEN command was issued with the message "demo" followed by a BEGIN command, and the transaction remained idle. From a different connection, 100 large calls to the pg_notify() function were generated. The outcome was straightforward: out of the 100 sends, 64 were successful, while 36 failed, indicating that the queue was fully utilized at 100%.

Upon returning to the LISTENER, issuing a COMMIT command allowed for the delivery of the remaining pending notifications. Running the command SELECT pg_notification_queue_usage () confirmed that the queue usage had returned to 0.

The crucial insight gained from this experiment is that increasing the value of max_notify_queue_pages only provides additional space for notifications. However, it does not resolve the issue if a transaction is preventing the cleanup of notifications. The complete experiment, including the Docker Compose setup, SQL commands, queue measurements, inspection of pg_notify/, and verification of recovery, can be found at https://bodanthebackend.com/articles/en/postgresql-listen-notify-queue-full/.