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

> "Joel Jacobson" <joel@compiler.org> writes:
>
> Unfortunately, the author of [1] jumped to conclusion and assumed
> the global lock was the problem. I'm quite sure it is probably not,
> because:
>
> We know for sure, that current users do LISTEN and NOTIFY
> in the same database. And there is no point in doing NOTIFY
> unless you also do LISTEN.

I agree that, in practice, there is likely no use of NOTIFY without
LISTEN. That being said, I disagree that the global lock is not at
least one of the bottlenecks from a performance perspective.

The global lock is not released until after the call to
RecordTransactionCommit and the reset of CommitTransaction. So if
there are multiple transactions sending notifications, each must
become durable before the next can proceed, which introduces a
significant bottleneck.

This becomes especially expensive in environments where compute and
storage are separated, such as certain cloud-based variants of Postgres.

> Tom Lane <tgl@sss.pgh.pa.us> writes:
>
> 1. In PreCommit_Notify(), gather up all the notifications this
> transaction has emitted, and write them into a WAL log message.
> Remember the LSN of this message. (I think this part should be
> parallelizable, because of work that's previously been done to
> allow parallel writes to WAL.)
>
> 2. When writing the transaction's commit WAL log entry, include
> the LSN of the previous notify-data entry.

Thanks for the input — this is a compelling idea. I'll work on
implementing a proof of concept.

Thanks,
Rishu