Re: Optimize LISTEN/NOTIFY - Mailing list pgsql-hackers

On Thu, Nov 6, 2025, at 00:21, Chao Li wrote:
> That’s what we don’t know. We now lack a performance test for
> evaluating how “direct advancement” efficiently helps if it only
> handles sleeping listeners. So what I was suggesting is that we should
> first create some tests, maybe also add a few more statistics, so that
> we can evaluate different solutions. If a simple implementation that
> only handles sleeping listeners would have performed good enough, of
> course we can take it; otherwise we may need to either pursue a better
> solution.

Just for the sake of evaluating this patch, I've added instrumentation
of async.c that increments counters for the different branches in
asyncQueueReadAllNotifications and SignalBackends. (I'm just using
atomics without any locking, but should be fine since this is just
statistics.)

pg_get_async_wakeup_stats-patch.txt adds the SQL-callable
catalog functions pg_reset_async_wakeup_stats() and
pg_get_async_wakeup_stats(), which should not be included in the patch,
they are just for evaluating. It can be applied on top of the v23 patch.

Below is just an example of how to compile and an arbitrary mix of
command line options. I've tired a lot of combinations, and we seem to
be holding up fine in all cases I've tried.

async-notify-test-5.c will detect if the pg_*_async_wakeup_stats() functions
exists, and only show the extra histograms if so.

% gcc -Wall -Wextra -O2 -pthread -I/Users/joel/pg19/include/postgresql/server -I/Users/joel/pg19/include -o
async-notify-test-5async-notify-test-5.c -L/Users/joel/pg19/lib -lpq -pthread -lm 

% ./async-notify-test-5 --listeners 10 --notifiers 10 --channels 10 --sleep 0.1 --sleep-exp 2.0 --batch 10
10 s: 38100 sent (3690/s), 381000 received (36900/s)
Notification Latency Distribution:
 0.00-0.01ms                0 (0.0%) avg: 0.000ms
 0.01-0.10ms     #          23 (0.0%) avg: 0.092ms
 0.10-1.00ms     #######    298002 (78.2%) avg: 0.563ms
 1.00-10.00ms    ##         82975 (21.8%) avg: 1.506ms
 10.00-100.00ms             0 (0.0%) avg: 0.000ms
>100.00ms                  0 (0.0%) avg: 0.000ms

asyncQueueReadAllNotifications Statistics:
necessary_wakeups    ########   35469 (88.2%)
unnecessary_wakeups  #          4762 (11.8%)

SignalBackends Statistics:
signaled_needed     #          34983 (9.5%)
avoided_wakeups     ########   325874 (88.9%)
already_advancing   #          3 (0.0%)
signaled_uncertain  #          5347 (1.5%)
already_ahead       #          375 (0.1%)

Thoughts on how to interpret results:

- Is the notification latency distribution good enough, for the given
  workload? Naturally, if the workload is too high, we cannot expect to
  ever achieve sub millisecond latency anyway, so it's a judgement.

- Even if the "unnecessary_wakeups" is high relative to
  "necessary_wakeups", it's not necessarily a problem, if the latency
  distribution still is good enough. We should also think about the
  ratio between "unnecessary_wakeups" and "avoided_wakeups", since even
  if "unnecessary_wakeups" is high in absolute numbers, if the
  "avoided_wakeups" is magnitudes larger, that means the cost of the
  context switching has been dramatically reduced already. I think there
  is always a risk when optimizing to forget what problem one was trying
  to solve initially, usually a bottleneck. When the bottleneck is gone
  and is somewhere else instead, then the efforts should IMO usually be
  spent elsewhere, especially if more optimizations would need a
  insignificant increase of code complexity.

- It's the "signaled_uncertain" that primarily contribute to
  "unnecessary_wakeups".

/Joel