From: Grégoire de Turckheim <gdeturckheim@scaleway.com>
To: pgsql-performance@lists.postgresql.org
Subject: Notifications within triggers seem to compromise performance
Date: Mon, 28 Oct 2019 14:42:03 +0100
Message-ID: <2b617759-5c59-de3c-1de0-bed20d9c0db1@scaleway.com> (raw)
Hi there!
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 want our app to maintain a data cache, so each instance of the app listens to some channels (one per table).
There are update triggers set up on the tables so each update yelds a notification to the appropriate channel.
It works fine and we love the feature, but it seems to come with a performance cost.
Since we set them up, we get query timeouts in our app (set to 200ms in the 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 still 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 transaction on table B. Tables are only tied by an FK, the updated fields are 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 "notification lock" is released
Am I correct ?
Other transactions involving a notification are stuck waiting for previous transactions to finish, this can be a performance issue.
I understand the need for lock to be taken pre-commit to ensure notification order matches transaction order, but it in my case I don't really care 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 thus take and release the lock in post-commit actions)
I believe the notify-in-trigger should be a pretty common usage pattern and so this finding may impact quite a few systems.
What do you think about this ?
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: <2b617759-5c59-de3c-1de0-bed20d9c0db1@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