Re: NOTIFY performance

On Fri, Aug 24, 2012 at 4:11 PM, Artur Zając <azajac(at)ang(dot)com(dot)pl> wrote:
>>> I would like to create some application using triggers and
>>> LISTEN/NOTIFY framework. I've tested it, and I noticed that
>>> performance of NOTIFY significally decreases with increasing number of
>>> distinct NOTIFIES in transaction.
>>> I found that function AsyncExistsPendingNotify is responsibe for it. I
>>> think that complexivity of searching duplicates there is O(N^2). Would
>>> be possible to improve performance of it? Maybe by using list for
>>> elements precedence and binary search tree for searching duplicates -
>>> with complexivity of O(Nlog2(N)).
>>>
>>> I'v tested with 50000 of NOTICES. Updating table with 20000 NOTICES
>>> when searching is not performed took 1,5 second. With searching it
>>> took 28 seconds.
>>
>>I've confirmed the n^2 behavior on 9.2:
>>postgres=# select pg_notify(v::text, null) from generate_series(1,10000) v;
>>Time: 281.000 ms
>>postgres=# select pg_notify(v::text, null) from generate_series(1,50000) v;
>>Time: 7148.000 ms
>>
>>...but i'm curious if you're going about things the right way...typically I'd imagine you'd write out actionable items to a table and issue a much broader NOTIFY which taps listeners on the table to search the action queue. Could you describe your problem in >a little more detail?
>
> When there was only NOTIFY option with simple channel name there was no need to send so many messages - creating 50000 channels would be really stupid. NOTIFY to channel might only mean that there is sth new in table or sth similar. But with payload option it would be possible to make simple system for notify other database clients (or self notify - when changes are made by triggers) that some single record has changed and it should be invalidated in client cache. I would made (and I already made) that system (similar to streaming replication :) but more more simple), but unfortunately even not big update on table would kill my system with complexivity O(N^2). In general , I know that this system would be not efficient, but for my application it would simply solve my many problems.

Yeah -- my take is that you're pushing too much information through
the notify. If I was in your shoes, I'd be notifying the client to
come and check and invalidation queue which would be updated through
some sort of trigger. The payload options is great in that it can
save you a round trip in some latency sensitive cases but it's not a
replacement for a proper queue.

merlin