NOTIFY and pg_notify performance when deduplicating notifications

Hi,

Back in 2016 a patch was proposed to fix the O(N^2) performance on transactions that generate many notifications. The performance issue is caused by the check for duplicate notifications.

I have a feature built around LISTEN / NOTIFY that works perfectly well, except for the enormous performance impact to transactions that emit large numbers of notifications. It’s very hard to work around the problem on the application side and transactions that could take just a few seconds end up taking over 30 minutes.

The patch that was proposed was nearly finalized, but ended up being abandoned. The latest patch is here: https://www.postgresql.org/message-id/CAP_rwwmKjO_p3kYB4jYceqcvcyRYjBQdji1GSCyqvLK%3D5nZzWQ%40mail.gmail.com .

I understand that the only work left to be done on the patch was to address comments made on the proposed syntax. I’m attaching an updated patch that changes the syntax to allow for a variable number of modes. The new syntax would be NOTIFY channel [ , payload [ , collapse_mode ] ] ; where collapse_mode can be 'never' or 'maybe'.

I hope this patch can be reviewed and included in PostgreSQL.

Best regards.

--
Julien Demoor