From: | Grégoire de Turckheim <gdeturckheim(at)scaleway(dot)com> |
---|---|
To: | pgsql-performance(at)lists(dot)postgresql(dot)org |
Subject: | Notifications within triggers seem to compromise performance |
Date: | 2019-10-28 13:42:03 |
Message-ID: | 2b617759-5c59-de3c-1de0-bed20d9c0db1@scaleway.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2019-10-28 14:22:04 | Re: Notifications within triggers seem to compromise performance |
Previous Message | Pavel Stehule | 2019-10-22 12:39:19 | Re: max_connections |