CODEWITHSUNDEEP
postgresqlmessage-queue
dizzyf
dizzyf

Reputation: 3693

How to find the current length of a postgres NOTIFY/LISTEN message queue?

Question title says it all. We have TRIGGERS set up on a database table that are being consumed by an offsite worker. However, there are times that it appears the worker falls behind in updating records. Is there a SQL query or call to determine the existing length of the message queue? (without popping any of the items). I see numerous mentions of this queue in the postgres documentation and other stackoverflow questions, but can't find anything regarding actually determining the length. Any help is appreciated!

For those of you familiar with redis, I'm looking for the equivalent of an LLEN command for this postgres message queue.

Upvotes: 2

Views: 2516

Answers (1)

Nick Barnes
Nick Barnes

Reputation: 21346

I don't think there's anything which directly reports the number of queue entries.

The closest thing is probably pg_notification_queue_usage(), which tells you what fraction of the queue storage is currently used (out of 8GB in total in a standard installation, according to the NOTIFY docs).

The memory usage is going to depend a lot on the payload, of course, but if you can figure out your average notification size, you should be able to translate this to an approximate queue length.

Upvotes: 2

Related Questions