Proposal: Out-of-Order NOTIFY via GUC to Improve LISTEN/NOTIFY Throughput

Hi all,

There’s been some renewed attention on improving the performance of the
LISTEN/NOTIFY system, which historically hasn’t scaled well under high
notify frequency. Joel Jacobson recently shared some work on optimizing
the LISTEN path [1], and I wanted to follow up with a proposal focused on
the NOTIFY side.

One of the main bottlenecks in the current implementation is the global
lock taken in `PreCommit_Notify`, which serializes all notifications.
In many use cases (especially where NOTIFY is used for non–mission-critical
caching or coordination), users may not care about strict notification
ordering or delivery semantics in the event of a transaction rollback.

To explore this further, I’ve drafted a patch that introduces a new GUC:
`publish_out_of_order_notifications`. When enabled, this skips the global
lock in `PreCommit_Notify`, allowing notifications to be queued in parallel.
This comes at the cost of possible out-of-order delivery and the potential
for notifications to be delivered from rolled-back transactions.

For benchmarking, I used pgbench with a custom SQL script that sends a
single NOTIFY message per transaction. The test was run using 8 connections
and 2000 transactions per client.

Here are the results on a MacBook Air (Apple M2 chip, 8 cores, 16 GB memory):

publish_out_of_order_notifications = off:

• Run 1: 158,190 TPS (latency: 0.051 ms)
• Run 2: 189,771 TPS (latency: 0.042 ms)
• Run 3: 189,401 TPS (latency: 0.042 ms)
• Run 4: 190,288 TPS (latency: 0.042 ms)
• Run 5: 185,001 TPS (latency: 0.043 ms)

publish_out_of_order_notifications = on:

• Run 1: 298,982 TPS (latency: 0.027 ms)
• Run 2: 345,162 TPS (latency: 0.023 ms)
• Run 3: 351,309 TPS (latency: 0.023 ms)
• Run 4: 333,035 TPS (latency: 0.024 ms)
• Run 5: 353,834 TPS (latency: 0.023 ms)

This shows roughly a 2x improvement in TPS in this basic benchmark.

I believe this could serve as a useful knob for users who want performance
over guarantees, and it may help guide future efforts to reduce contention
in NOTIFY more generally. I also have some ideas for stricter-but-faster
implementations that preserve ordering, but I wanted to start with a
minimal and illustrative patch.

I'd appreciate thoughts on the direction and whether this seems worth
pursuing further.

Relevant prior discussions:
[1] https://www.postgresql.org/message-id/flat/6899c044-4a82-49be-8117-e6f669765f7e%40app.fastmail.com
[2] https://www.postgresql.org/message-id/flat/CAM527d_s8coiXDA4xbJRyVOcNnnjnf%2BezPYpn214y3-5ixn75w%40mail.gmail.com

Thanks,
Rishu