August 18th, 2015 by depesz | Tags: , , , , , | No comments »
Did it help? If yes - maybe you can help me?

On 17th of July, Robert Haas committed patch:

Add new function pg_notification_queue_usage.
This tells you what fraction of NOTIFY's queue is currently filled.
Brendan Jurd, reviewed by Merlin Moncure and Gurjeet Singh.  A few
further tweaks by me.

LISTEN / NOTIFY are very cool features, that don't get enough recognition. For completeness sake – they allow you to send asynchronous messages to external programs, and what's more – the messages are delivered after transaction has been committed, so it can be (and is) used for any kind of replication, refreshing materialized views, or anything like this.

It is possible that process listening for notification doesn't actually fetch them – for example, because it started some transaction (after doing LISTEN). In such case, the notifications are stored in queue.

The queue is rather large – if you didn't modify your sources, then it's limited to 8GB of space, so I can't really test it in reasonable time, but I assume that that it works.

The function is important for very simple reason – it's simple way to monitor if your system is handling notifications efficiently. That is – if you'd run:

$ select pg_notification_queue_usage();

and in return got value like 0.1 – then it means that you have ~ 800MB of unprocessed notifications. And something, somewhere, is not working properly.

Thanks guys – I don't have many apps that purposely use notifications (there are some replicated using triggers, which use notification, but that's internal to replication engine), but I definitely appreciate any work that makes PostgreSQL database that can be easily, and fully monitored.

Leave a comment