Putting it here because it isn't well known and bit me once.
NOTIFY payloads are deduped over the course of a transaction, with a O(n^2) algorithm if all payloads are different.
If you do a NOTIFY on delete or update and hit a few million rows, the query can go from seconds to hours.
The solution would be to write your payloads to a temp table and send them in batch when the transaction ends. Notifying in chunks of 100 will make the process 10'000 faster.
NOTIFY payloads are deduped over the course of a transaction, with a O(n^2) algorithm if all payloads are different.
If you do a NOTIFY on delete or update and hit a few million rows, the query can go from seconds to hours.
The solution would be to write your payloads to a temp table and send them in batch when the transaction ends. Notifying in chunks of 100 will make the process 10'000 faster.