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 the PreCommit_Notify 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
