From: Tom Lane <tgl@sss.pgh.pa.us>
To: =?UTF-8?Q?Gr=c3=a9goire_de_Turckheim?= <gdeturckheim@scaleway.com>
cc: pgsql-performance@lists.postgresql.org
Subject: Re: Notifications within triggers seem to compromise performance
In-reply-to: <1c0c95b4-01c4-bbbf-0e6e-f5d530e63ba0@scaleway.com>
References: <2b617759-5c59-de3c-1de0-bed20d9c0db1@scaleway.com>
 <15979.1572272524@sss.pgh.pa.us>
 <1c0c95b4-01c4-bbbf-0e6e-f5d530e63ba0@scaleway.com>
Comments: In-reply-to =?UTF-8?Q?Gr=c3=a9goire_de_Turckheim?=
 <gdeturckheim@scaleway.com>
	message dated "Mon, 28 Oct 2019 16:39:30 +0100"
MIME-Version: 1.0
Content-Type: text/plain; charset="UTF-8"
Content-ID: <24268.1572279925.1@sss.pgh.pa.us>
Content-Transfer-Encoding: 8bit
Date: Mon, 28 Oct 2019 12:25:25 -0400
Message-ID: <24269.1572279925@sss.pgh.pa.us>
Precedence: bulk

=?UTF-8?Q?Gr=c3=a9goire_de_Turckheim?= <gdeturckheim@scaleway.com> writes:
> Le 28/10/2019 à 15:22, Tom Lane a écrit :
>> 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.

> 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.

Right, but all commits are single-threaded at some granularity.
The big problem with NOTIFY is that it sits for a long time holding
that lock, if you have a lot of notify traffic.  The commits I mentioned
should improve that.

Anyway, as I said, it would be good to find out whether the already
finished fixes are enough to solve your problem, before we debate
whether more needs to be done.

			regards, tom lane