Subject: Re: Notifications within triggers seem to compromise performance
To: pgsql-performance@lists.postgresql.org
References: <2b617759-5c59-de3c-1de0-bed20d9c0db1@scaleway.com>
 <15979.1572272524@sss.pgh.pa.us>
 <1c0c95b4-01c4-bbbf-0e6e-f5d530e63ba0@scaleway.com>
 <24269.1572279925@sss.pgh.pa.us>
From: =?UTF-8?Q?Gr=c3=a9goire_de_Turckheim?= <gdeturckheim@scaleway.com>
Message-ID: <fbafc656-ca6f-0b94-7dff-37eeb937df7f@scaleway.com>
Date: Mon, 28 Oct 2019 17:35:22 +0100
User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.12; rv:60.0)
 Gecko/20100101 Thunderbird/60.9.0
MIME-Version: 1.0
In-Reply-To: <24269.1572279925@sss.pgh.pa.us>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Language: fr
Content-Transfer-Encoding: quoted-printable
Precedence: bulk

Le 28/10/2019 =C3=A0 17:25, Tom Lane a =C3=A9crit=C2=A0:
> =3D?UTF-8?Q?Gr=3Dc3=3Da9goire_de_Turckheim?=3D <gdeturckheim@scaleway.c=
om> writes:
>> Le 28/10/2019 =C3=A0 15:22, Tom Lane a =C3=A9crit=C2=A0:
>>> 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 mentione=
d
> 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.
Let's do it this way, I'll give it a try (might be long, this isn't=20
something we can easily upgrade) and get back to you.

Thanks!

--=20
Gr=C3=A9goire de Turckheim