Postgres Pro
Downloads
Home   >   mailing lists

Re: bad COPY performance with NOTIFY in a trigger - Mailing list pgsql-performance

From Filip Rembiałkowski
Subject Re: bad COPY performance with NOTIFY in a trigger
Date
Msg-id CAP_rwwmQRA6UmofTu_HxZn369UjNUQvXOSWGGY4-nAmkP11cKA@mail.gmail.com
Whole thread Raw
In response to Re: bad COPY performance with NOTIFY in a trigger  (Tom Lane <tgl@sss.pgh.pa.us>)
Responses Re: bad COPY performance with NOTIFY in a trigger  (Filip Rembiałkowski <filip.rembialkowski@gmail.com>)
List pgsql-performance
Tree view
On Thu, Feb 4, 2016 at 11:41 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Filip Rembiałkowski <filip.rembialkowski@gmail.com> writes:
> A table has a trigger.
> The trigger sends a NOTIFY.
> Test with COPY FROM shows non-linear correlation between number of inserted
> rows and COPY duration.

No surprise, see AsyncExistsPendingNotify.  You would have a lot of other
performance issues with sending hundreds of thousands of distinct notify
events from one transaction anyway, so I can't get terribly excited about
this.


What kind of issues? Do you mean, problems in postgres or problems in client?

Is there an additional non-linear cost on COMMIT (extra to the cost I already showed)? 

The 8GB internal queue (referenced in a Note at http://www.postgresql.org/docs/current/static/sql-notify.html) should be able to keep ~ 1E8 such notifications (assumed one notification will fit in 80 bytes).

On client side, this seems legit - the LISTENer deamon will collect these notifications and process them in line. 
There might be no LISTENer running at all. 

Still, the main problem I get with this approach is quadratic cost on big insert transactions. 
I wonder if this behavior is possible to change in future postgres versions. And how much programming work does it require.

Is duplicate-elimination a fundamental, non-negotiable requirement?



Thank you,
Filip

pgsql-performance by date:

Previous
From: Marc Mamin
Date:
Subject: gin performance issue.
Next
From: Tom Lane
Date:
Subject: Re: gin performance issue.