Debug Postgres 'too many notifications in the NOTIFY queue'

Question

I am using a Postgres table which gets 2000-3000 updates per second. I am using for update this table queries generated with the update helper of pg-promise library.

Each update triggers a notify with pg_notify() function. Some nodejs scripts are handling these notifications. For some reason in Postgres logs keep appearing 'too many notifications in the NOTIFY queue' messages and also indication about the notify queue size which keep increasing up to 100%. I read some posts like: https://postgrespro.com/list/thread-id/1557124 or https://github.com/hasura/graphql-engine/issues/6263 but I cannot find a way to debug this issue. Which would be a good way to approach this situation?

Answer 1

Your listener doesn't seem to be consuming the notices fast enough, or possibly not at all. So the first step would be something like logging the processing of each notice from your app code, to figure out what is actually going on.

Answer 2

This might be because there is a long-running transaction that is blocking the release of older messages from the buffer. The process is explained in the manuals and is somewhat analoguous to vacuuming - old transactions need to finish in order to clean up old data.

A gotcha here is that any long-running query can hold up the cleanup; for me it was the process that was running the Listen - it was designed to just keep running forever. PG server log has a backend PID that might be the culprit, so you can look it up in pg_stat_activity and proceed from there.