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
