Re: Optimize LISTEN/NOTIFY

Joel Jacobson <joel@compiler.org>

From: "Joel Jacobson" <joel@compiler.org>

To: pgsql-hackers <pgsql-hackers@postgresql.org>

Date: 2025-10-28T21:45:57Z

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 →

Optimize LISTEN/NOTIFY via shared channel map and direct advancement.
- 282b1cde9ded 19 (unreleased) landed
Fix incorrect logic for caching ResultRelInfos for triggers
- 39dcfda2d23a 19 (unreleased) cited

Attachments

0001-optimize_listen_notify-v22.patch (application/octet-stream) patch v22-0001
0002-optimize_listen_notify-v22.patch (application/octet-stream) patch v22-0002

On Tue, Oct 28, 2025, at 07:46, Chao Li wrote:
>>> But anyway, we should run some load tests to verify every solution to 
>>> see how much they really improve. Do you already have or plan to work 
>>> on a load test script?
>> 
>> Yes, I'm currently working on a combined benchmark / correctness test suite.
>> 
>
> Cool. Then we can run the benchmark and decide.

I found a concurrency bug in v21 that could cause missed wakeup when a
backend would UNLISTEN on the last channel, which called
asyncQueueUnregister, and if wakeupPending was at that time already set,
then it wouldn't get reset, since in ProcessIncomingNotify we return
early if (listenChannels == NIL), so we would never clear wakeupPending
which happens in asyncQueueReadAllNotifications.

Fixed by clearing wakeupPending in asyncQueueUnregister:

@@ -1597,6 +1597,7 @@ asyncQueueUnregister(void)
    /* Mark our entry as invalid */
    QUEUE_BACKEND_PID(MyProcNumber) = InvalidPid;
    QUEUE_BACKEND_DBOID(MyProcNumber) = InvalidOid;
+   QUEUE_BACKEND_WAKEUP_PENDING(MyProcNumber) = false;
    /* and remove it from the list */
    if (QUEUE_FIRST_LISTENER == MyProcNumber)
        QUEUE_FIRST_LISTENER = QUEUE_NEXT_LISTENER(MyProcNumber);

/Joel