Re: [PATCH] Improve performance of NOTIFY over many databases (issue blocking on AccessExclusiveLock on object 0 of class 1262 of database 0)

Martijn van Oosterhout <kleptog(at)gmail(dot)com> writes:
> On Tue, 23 Jul 2019 at 19:21, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Martijn van Oosterhout <kleptog(at)gmail(dot)com> writes:
>>> 2. Add a field to AsyncQueueEntry which points to the next listening
>>> backend. This would allow the loops over all listening backends to
>>> complete much faster, especially in the normal case where there are
>>> not many listeners relative to the number of backends. The downside is
>>> this requires an exclusive lock to remove listeners, but that doesn't
>>> seem a big problem.

>> I don't understand how that would work? The sending backend doesn't
>> know what the "next listening backend" is. Having to scan the whole
>> queue when a listener unlistens seems pretty awful too, especially
>> if you need exclusive lock while doing so.

> I mean tracking the listening backends specifically, so you can
> replace the loops:
> for (i=0; i < MaxBackends; i++)
> with
> for (i=QUEUE_FIRST_LISTENING_BACKEND; i; i = QUEUE_NEXT_LISTENING_BACKEND(i))

Ah ... but surely you would not put such info in AsyncQueueEntry,
where there'd be a separate copy for each message. I think you
meant to add the info to AsyncQueueControl.

It might be better to redefine the backends[] array as being mostly
contiguous (ie, a new backend takes the first free slot not the one
indexed by its own BackendId), at the price of needing to store
BackendId in each slot explicitly instead of assuming it's equal to
the array index. I suspect the existing data structure is putting too
much of a premium on making sizeof(QueueBackendStatus) a power of 2.

regards, tom lane