From: Grégoire de Turckheim <gdeturckheim@scaleway.com>
To: pgsql-performance@lists.postgresql.org
Subject: Re: Notifications within triggers seem to compromise performance
Date: Mon, 28 Oct 2019 16:39:30 +0100
Message-ID: <1c0c95b4-01c4-bbbf-0e6e-f5d530e63ba0@scaleway.com> (raw)
In-Reply-To: <15979.1572272524@sss.pgh.pa.us>
References: <2b617759-5c59-de3c-1de0-bed20d9c0db1@scaleway.com>
<15979.1572272524@sss.pgh.pa.us>
Le 28/10/2019 à 15:22, Tom Lane a écrit :
> =?UTF-8?Q?Gr=c3=a9goire_de_Turckheim?= <gdeturckheim@scaleway.com> writes:
>> I guess we stumbled upon a performance issue with notifications sent within triggers (using PostgreSQL version 11.5)
>> and I'd like your opinion about this.
> We made some performance improvements for NOTIFY just a couple months
> ago, cf commits b10f40bf0, bb5ae8f6c, bca6e6435, 51004c717. It would
> be interesting to know how much those changes helped your use-case.
Thanks for your quick reply!
If my understanding of the problem is correct, there is no performance
issue with the notification itself.
The problem is the following: a system-wide lock is taken pre-commit, so
any other transaction with a NOTIFY will have to wait for other
transactions to complete before it can leave its own pre-commit stage.
Is this wording better to clarify my explanation attempt ? :)
In my case, ~90% of the data is in tables with triggered notifications,
all of this data updates become "single threaded", whatever the table it
is in.
>
> I'm quite disinclined to reduce the correctness guarantees around
> NOTIFY for performance's sake. That's the sort of thing that sounds
> like a good idea until you find out that it subtly breaks your
> application, and then you've got nothing.
100% agreed, this is why my suggestion was to make it an option. From a
user perspective, it seems very complex to understand if this option is
to be used or not. I really don't know how to present such an option to
the user.
There also may be better ways to do it, I suggested different queues
(and thus locks) for different channels but I have no idea about the
cost of it.
Regards,
--
Grégoire de Turckheim
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: gdeturckheim@scaleway.com, pgsql-performance@lists.postgresql.org
Subject: Re: Notifications within triggers seem to compromise performance
In-Reply-To: <1c0c95b4-01c4-bbbf-0e6e-f5d530e63ba0@scaleway.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox