Notifications within triggers seem to compromise performance

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux