Re: Optimize LISTEN/NOTIFY

> On Oct 27, 2025, at 07:24, Joel Jacobson <joel@compiler.org> wrote:
>
> Write-up of changes since v20:
>
> Two new fields have been added to QueueBackendStatus:
> + QueuePosition advisoryPos; /* safe skip-ahead position */
> + bool advancingPos; /* backend is reading the queue */
>
> These are used SignalBackends and asyncQueueReadAllNotifications to
> handle the empheral state of the shared queue position, since we don't
> take a lock while advancing it in asyncQueueReadAllNotifications.
>
> In SignalBackends, we now don't signal laggers in other databases,
> instead we will signal any listening backend that could possibly be
> behind the old queue head, since we can't know if such backend is
> interested in the notifications before the old queue head.  Realistic
> benchmarks will be needed to determine if this happens often enough to
> warrant a more complex optimization, such as the ranges idea suggested
> by Arseniy Mukhin.
>
> In SignalBackends, if a backend that is uninterested in our
> notifications, has a shared pos that is at the old queue head, then we
> will check if it's not currently advancing its pos, in which case we can
> set its shared pos to the new queue head, i.e. "direct advance" it,
> otherwise, if it's currently advancing its pos, and if its advisory pos
> is behind our new queue head, we will update its advisory pos to our new
> queue head.
>
> In asyncQueueReadAllNotifications, we start by setting wakupPending to
> false and advisoryPos to true, to indicate that we've woken up, and that
> we will now start advancing the pos.  We also check if the pos is behind
> the advisory pos, and if so use the advisory pos to update the pos.
>
> In asyncQueueReadAllNotifications's PG_FINALLY block, we reset
> advancingPos to false, and detect if the advisoryPos was set by
> SignalBackends while we were processing messages on the queue, and if
> so, and if the advisoryPos is ahead of our pos, we update our shared pos
> with the advisoryPos, and otherwise update the shared pos with the new
> pos.
>
> /Joel<0001-optimize_listen_notify-v21.patch><0002-optimize_listen_notify-v21.patch>

I did a quick review on v21 only focusing on the “direct advancement” logic.

In v21, you added advisoryPos and advancingPos which is same as my proposed solution. But you missed an important point
frommine. 

Let’s say listener L1 is doing a slow advancing, because the last notifier pushed a bunch of notifications and L1 is
interestingin them, say current QUEUE_HEAD is QH1. So, L1 is reading till reaching QH1. 

Now notifier N1 comes. To N1, posBeforeWrite is QH1, and say posAfterWrite is QH2. In this case, as L1 is reading, if
N1knows that L1 will read till QH1, then N1 can still set L1’s advisoryPos to QH2, right? From this perspective, we
needto add a new field adviancingTillPos to QueueBackendStatus. (This field was also missing from my proposed patch). 

Then notifier N2 comes after N1. To N2, posBeforeWrite is QH2, and say posAfterWrite is QH3. As L1 is still reading,
andit’s advisoryPos is QH2, so N2 can also advance L1’s advisoryPos to QH3. 

Finally, L1 finished reading and reached QH1. Now it sees advisoryPos is QH3, then it can directly bump its pos to QH3.

Do you think this logic is valid?

Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/