Horton

Re: Optimize LISTEN/NOTIFY

Tom Lane <tgl@sss.pgh.pa.us>

View thread
From: Tom Lane <tgl@sss.pgh.pa.us>
To: "Joel Jacobson" <joel@compiler.org>
Cc: "Chao Li" <li.evan.chao@gmail.com>, pgsql-hackers <pgsql-hackers@postgresql.org>
Date: 2025-10-16T20:16:48Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Optimize LISTEN/NOTIFY via shared channel map and direct advancement.

  2. Fix incorrect logic for caching ResultRelInfos for triggers

"Joel Jacobson" <joel@compiler.org> writes:
> On Thu, Oct 16, 2025, at 20:16, Joel Jacobson wrote:
>> Building pendingNotifyChannels is O(N^2) yes, but how large N is
>> realistic here?

> I agree this looks like a real problem, since I guess it's not
> completely unthinkable someone might have
> some kind of trigger on a table, that could fire off NOTIFY
> for each row, possibly causing hundreds of thousands of
> notifies in the same db txn.

We already de-duplicate identical NOTIFY operations for exactly that
reason (cf. AsyncExistsPendingNotify).  However, non-identical NOTIFYs
obviously can't be merged.

I wonder whether we could adapt that de-duplication logic so that
it produces a list of unique channel names in addition to a list
of unique NOTIFY events.  One way could be a list/hashtable of
channels used, and for each one a list/hashtable of unique payloads,
rather than the existing single-level list/hashtable.

			regards, tom lane