1

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?

Dani Mesejo
  • 61,499
  • 6
  • 49
  • 76
Arian Mareș
  • 46
  • 8
  • 1
    PostgreSQL documents it - see [Notes](https://www.postgresql.org/docs/13/sql-notify.html#id-1.9.3.157.7). – vitaly-t Dec 12 '20 at 10:48
  • Yes, I read this but it's not very clear for me what means by '_not yet processed by all listening sessions -_'. I am using the 'notification' event of pg-node implementation, my understanding is that each time this event is triggered, the notification is processed. – Arian Mareș Dec 12 '20 at 10:54
  • Your issue is not with the library or even NodeJS environment, it is strictly with the server, and so you need to approach this accordingly - either by changing notifications strategy, or update/volume strategy, or even monitor the size of the queue using the function in documentation. – vitaly-t Dec 12 '20 at 12:11

2 Answers2

2

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.

jjanes
  • 37,812
  • 5
  • 27
  • 34
  • what do you mean by consuming? I use the notification event of pg-node library so each time a notification is triggered, it trigger a callback where I do stuff. – Arian Mareș Dec 12 '20 at 17:48
  • The pg_notify is called in a table trigger on Update. You suggest that I have to log somewhere each pg_notify call and compare this log with one generated by notification events callbacks. OK. Will do that? And what to do if some notifications are missing? – Arian Mareș Dec 12 '20 at 17:52
  • "so each time a notification is triggered, it trigger a callback where I do stuff" Yes, that is what you want to happen. Is it working? How do you know? – jjanes Dec 12 '20 at 18:48
  • Mostly of them are working. I couldn't know for sure because the whole thing looks to work as expected. The only annoyance is this queue size increasing uncontrollable. You say that some notification events are not triggered? What would be the cause? I will check this by logging both notifications and triggered notification events. – Arian Mareș Dec 12 '20 at 18:55
0

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.

stox
  • 91
  • 1
  • 3