To: pgsql-performance@lists.postgresql.org
From: =?UTF-8?Q?Gr=c3=a9goire_de_Turckheim?= <gdeturckheim@scaleway.com>
Subject: Notifications within triggers seem to compromise performance
Message-ID: <2b617759-5c59-de3c-1de0-bed20d9c0db1@scaleway.com>
Date: Mon, 28 Oct 2019 14:42:03 +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
Content-Type: multipart/alternative;
 boundary="------------CBB63DDE92E1FFB9036EB03C"
Content-Language: fr
Precedence: bulk

This is a multi-part message in MIME format.
--------------CBB63DDE92E1FFB9036EB03C
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: quoted-printable

Hi there!

I guess we stumbled upon a performance issue with notifications sent with=
in triggers (using PostgreSQL version 11.5)
and I'd like your opinion about this.

We want our app to maintain a data cache, so each instance of the app lis=
tens to some channels (one per table).
There are update triggers set up on the tables so each update yelds a not=
ification to the appropriate channel.

It works fine and we love the feature, but it seems to come with a perfor=
mance cost.
Since we set them up, we get query timeouts in our app (set to 200ms in t=
he app).

To try and understand this, we set deadlock_timeout to 100ms and enabled =
log_lock_waits to get the following warnings in the log: process XXXXX st=
ill waiting for AccessExclusiveLock on object 0 of class 1262 of database=
 0 after YYY.YYY ms
A row update transaction on table A is waiting for another row update tra=
nsaction on table B. Tables are only tied by an FK, the updated fields ar=
e not the ID or FK fields.

A quick google + source code search showed thePreCommit_Notify  <https://=
doxygen.postgresql.org/async_8c.html#a90945c51e67f5618a2003d672f1880cb>  =
function is trying to acquire this lock.
My educated guess of what happens during a COMMIT is the following :
- pre-commit actions are taken, the "notification lock" is taken
- commit actions are performed (can take some time)
- post-commit actions are taken, the notification is enqueued and "notifi=
cation lock" is released

Am I correct ?

Other transactions involving a notification are stuck waiting for previou=
s transactions to finish, this can be a performance issue.

I understand the need for lock to be taken pre-commit to ensure notificat=
ion order matches transaction order, but it in my case I don't really car=
e about the order and the performance penalty is high.

We could think of several options there :
- different locks for different channels (implies different notification =
queues I guess)
- an argument to NOTIFY query not to guarantee notifications order (and t=
hus take and release the lock in post-commit actions)

I believe the notify-in-trigger should be a pretty common usage pattern a=
nd so this finding may impact quite a few systems.

What do you think about this ?

Regards,

--=20
Gr=C3=A9goire de Turckheim


--------------CBB63DDE92E1FFB9036EB03C
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: quoted-printable

<html>
  <head>

    <meta http-equiv=3D"content-type" content=3D"text/html; charset=3DUTF=
-8">
  </head>
  <body text=3D"#000000" bgcolor=3D"#FFFFFF">
    <pre>Hi there!

I guess we stumbled upon a performance issue with notifications sent with=
in triggers (using PostgreSQL version 11.5)
and I'd like your opinion about this.

We want our app to maintain a data cache, so each instance of the app lis=
tens to some channels (one per table).
There are update triggers set up on the tables so each update yelds a not=
ification to the appropriate channel.

It works fine and we love the feature, but it seems to come with a perfor=
mance cost.
Since we set them up, we get query timeouts in our app (set to 200ms in t=
he app).

To try and understand this, we set deadlock_timeout to 100ms and enabled =
log_lock_waits to get the following warnings in the log: process XXXXX st=
ill waiting for AccessExclusiveLock on object 0 of class 1262 of database=
 0 after YYY.YYY ms
A row update transaction on table A is waiting for another row update tra=
nsaction on table B. Tables are only tied by an FK, the updated fields ar=
e not the ID or FK fields.

A quick google + source code search showed the <a class=3D"code" href=3D"=
https://doxygen.postgresql.org/async_8c.html#a90945c51e67f5618a2003d672f1=
880cb">PreCommit_Notify</a> function is trying to acquire this lock.
My educated guess of what happens during a COMMIT is the following :
- pre-commit actions are taken, the "notification lock" is taken
- commit actions are performed (can take some time)
- post-commit actions are taken, the notification is enqueued and "notifi=
cation lock" is released

Am I correct ?

Other transactions involving a notification are stuck waiting for previou=
s transactions to finish, this can be a performance issue.

I understand the need for lock to be taken pre-commit to ensure notificat=
ion order matches transaction order, but it in my case I don't really car=
e about the order and the performance penalty is high.

We could think of several options there :
- different locks for different channels (implies different notification =
queues I guess)
- an argument to NOTIFY query not to guarantee notifications order (and t=
hus take and release the lock in post-commit actions)

I believe the notify-in-trigger should be a pretty common usage pattern a=
nd so this finding may impact quite a few systems.

What do you think about this ?

Regards,
</pre>
    <pre class=3D"moz-signature" cols=3D"72">--=20
Gr=C3=A9goire de Turckheim
</pre>
  </body>
</html>

--------------CBB63DDE92E1FFB9036EB03C--