0001-Add-performance-warnings-to-NOTIFY-LISTEN-documentat.patch

From 71db88dc65151bb0aa9b8d1ecdac4010bbf4272c Mon Sep 17 00:00:00 2001
From: Nikolay Samokhvalov <nik@postgres.ai>
Date: Thu, 10 Jul 2025 16:17:31 -0700
Subject: [PATCH] Add performance warnings to NOTIFY/LISTEN documentation
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit

Document serious performance limitations that affect production systems:

- Global lock warning: NOTIFY uses AccessExclusiveLock during commit affecting entire cluster
- Explains lock purpose: ensures notification ordering but creates cluster-wide bottleneck
- O(N²) performance warning: Duplicate checking complexity with specific mitigation strategies
- Architectural guidance: New subsection with high-concurrency recommendations
- PostgreSQL-native alternative: Recommend logical decoding with proper cross-reference
- Diagnostic guidance: Enable log_lock_waits with example log message to identify
- Cross-reference: Add LISTEN → NOTIFY performance limitation reference
- pg_notify() warning: Function has same performance limitations as NOTIFY command

These warnings help developers make informed architectural decisions before encountering serious performance issues in production environments.
---
 doc/src/sgml/ref/listen.sgml |  4 +++
 doc/src/sgml/ref/notify.sgml | 48 ++++++++++++++++++++++++++++++++++++
 2 files changed, 52 insertions(+)

diff --git a/doc/src/sgml/ref/listen.sgml b/doc/src/sgml/ref/listen.sgml
index 6c1f09bd455..85f6784be80 100644
--- a/doc/src/sgml/ref/listen.sgml
+++ b/doc/src/sgml/ref/listen.sgml
@@ -95,6 +95,10 @@ LISTEN <replaceable class="parameter">channel</replaceable>
    prepared for two-phase commit.
   </para>
 
+  <para>
+   See <command>NOTIFY</command> documentation for important performance limitations when using <command>LISTEN</command>/<command>NOTIFY</command> at scale.
+  </para>
+
   <para>
    There is a race condition when first setting up a listening session:
    if concurrently-committing transactions are sending notify events,
diff --git a/doc/src/sgml/ref/notify.sgml b/doc/src/sgml/ref/notify.sgml
index fd6ed54e8f9..19b75bc4795 100644
--- a/doc/src/sgml/ref/notify.sgml
+++ b/doc/src/sgml/ref/notify.sgml
@@ -153,6 +153,14 @@ NOTIFY <replaceable class="parameter">channel</replaceable> [ , <replaceable cla
  <refsect1>
   <title>Notes</title>
 
+  <para>
+   <emphasis>Warning:</emphasis> <command>NOTIFY</command> uses a global lock during transaction commit that serializes all commits containing <command>NOTIFY</command> statements across the entire <productname>PostgreSQL</productname> cluster. This lock (<literal>AccessExclusiveLock</literal>) ensures notification ordering but affects all databases in the cluster, not just the one issuing <command>NOTIFY</command>. Under high concurrency, this can cause severe performance degradation with hundreds of processes queuing for commit.
+  </para>
+
+  <para>
+   <emphasis>Performance Warning:</emphasis> Duplicate notification checking has O(N²) complexity. Transactions generating more than a few hundred distinct notifications may experience severe performance degradation. For example, 10,000 notifications in a single transaction can take several minutes just for duplicate checking. Consider using fewer distinct channel names or splitting notifications across multiple transactions if generating large volumes.
+  </para>
+
   <para>
    There is a queue that holds notifications that have been sent but not
    yet processed by all listening sessions.  If this queue becomes full,
@@ -175,6 +183,42 @@ NOTIFY <replaceable class="parameter">channel</replaceable> [ , <replaceable cla
    prepared for two-phase commit.
   </para>
 
+  <refsect2>
+   <title>Performance considerations for high-concurrency systems</title>
+
+   <para>
+    For applications with high notification rates or many concurrent connections:
+   </para>
+
+   <itemizedlist>
+    <listitem>
+     <para>
+      Each <command>NOTIFY</command> acquires a global lock during commit, limiting total cluster commit throughput
+     </para>
+    </listitem>
+    <listitem>
+     <para>
+      Multiple databases on the same cluster share this bottleneck
+     </para>
+    </listitem>
+    <listitem>
+     <para>
+      Consider <link linkend="logicaldecoding">logical decoding</link> for high-volume scenarios
+     </para>
+    </listitem>
+    <listitem>
+     <para>
+      Monitor commit wait times for <literal>AccessExclusiveLock</literal> waits during <command>COMMIT</command> operations
+     </para>
+    </listitem>
+    <listitem>
+     <para>
+      Enable <xref linkend="guc-log-lock-waits"/> to identify when transactions are waiting for notification locks. Look for messages like <quote>process X still waiting for AccessExclusiveLock on object 0 of class 1262 of database 0</quote>
+     </para>
+    </listitem>
+   </itemizedlist>
+  </refsect2>
+
   <refsect2>
    <title>pg_notify</title>
 
@@ -190,6 +234,10 @@ NOTIFY <replaceable class="parameter">channel</replaceable> [ , <replaceable cla
     to use than the <command>NOTIFY</command> command if you need to work with
     non-constant channel names and payloads.
    </para>
+
+   <para>
+    <emphasis>Note:</emphasis> <function>pg_notify()</function> is subject to the same performance limitations as the <command>NOTIFY</command> command. See the performance warnings above for important considerations about global locking and O(N²) duplicate checking performance.
+   </para>
   </refsect2>
  </refsect1>
 
-- 
2.39.5 (Apple Git-154)