Le 28/10/2019 à 15:22, Tom Lane a écrit :
=?UTF-8?Q?Gr=c3=a9goire_de_Turckheim?= <gdeturckheim@xxxxxxxxxxxx> writes:
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 made some performance improvements for NOTIFY just a couple months
ago, cf commits b10f40bf0, bb5ae8f6c, bca6e6435, 51004c717. It would
be interesting to know how much those changes helped your use-case.
Thanks for your quick reply!
If my understanding of the problem is correct, there is no performance
issue with the notification itself.
The problem is the following: a system-wide lock is taken pre-commit, so
any other transaction with a NOTIFY will have to wait for other
transactions to complete before it can leave its own pre-commit stage.
Is this wording better to clarify my explanation attempt ? :)
In my case, ~90% of the data is in tables with triggered notifications,
all of this data updates become "single threaded", whatever the table it
is in.
I'm quite disinclined to reduce the correctness guarantees around
NOTIFY for performance's sake. That's the sort of thing that sounds
like a good idea until you find out that it subtly breaks your
application, and then you've got nothing.
100% agreed, this is why my suggestion was to make it an option. From a
user perspective, it seems very complex to understand if this option is
to be used or not. I really don't know how to present such an option to
the user.
There also may be better ways to do it, I suggested different queues
(and thus locks) for different channels but I have no idea about the
cost of it.
Regards,
--
Grégoire de Turckheim
