Re: Monitoring and insight into NOTIFY queue

I just ran into monitoring this and came up with the following 1 liner for monitoring this in releases < 9.6 through
theSQL layer.  Once you are at 9.6 Jeff Janes solution is correct. 

It does make the assumption that the queue size is 8GB.  It can misjudge the queue usage by up to one file segment
size. If you are using this for nagios monitoring, however, that error just disappears as a couple thousandths of a
percentof noise. 

SELECT sum((pg_stat_file('pg_notify/' || pg_ls_dir)).size)/(8 * pow(1024, 3)) * 100 as notify_queue_usage_percent FROM
pg_ls_dir('pg_notify');