From: | Filip Rembiałkowski <filip(dot)rembialkowski(at)gmail(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: bad COPY performance with NOTIFY in a trigger |
Date: | 2016-02-05 12:45:10 |
Message-ID: | CAP_rwwmQRA6UmofTu_HxZn369UjNUQvXOSWGGY4-nAmkP11cKA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
On Thu, Feb 4, 2016 at 11:41 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> =?UTF-8?Q?Filip_Rembia=C5=82kowski?= <filip(dot)rembialkowski(at)gmail(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2016-02-05 15:07:17 | Re: gin performance issue. |
Previous Message | Marc Mamin | 2016-02-05 11:28:08 | gin performance issue. |