Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1v1hIU-00BXMX-Iq for pgsql-hackers@arkaria.postgresql.org; Thu, 25 Sep 2025 08:25:50 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1v1hIT-001EXY-8M for pgsql-hackers@arkaria.postgresql.org; Thu, 25 Sep 2025 08:25:49 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1v1hIS-001ETB-SL for pgsql-hackers@lists.postgresql.org; Thu, 25 Sep 2025 08:25:48 +0000 Received: from mail-pf1-x435.google.com ([2607:f8b0:4864:20::435]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1v1hIO-002mFL-21 for pgsql-hackers@postgresql.org; Thu, 25 Sep 2025 08:25:48 +0000 Received: by mail-pf1-x435.google.com with SMTP id d2e1a72fcca58-77f3405c38aso780721b3a.0 for ; Thu, 25 Sep 2025 01:25:46 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1758788744; x=1759393544; darn=postgresql.org; h=references:to:cc:in-reply-to:date:subject:mime-version:message-id :from:from:to:cc:subject:date:message-id:reply-to; bh=bfUqXyCWXV3qo6L1xKI+rJ+E4qYrH53DztsOTmamRDs=; b=ChbxNxAXqrozh/zpE9I5AEnNeE6xRj2/H7MolvYXqOLJ6ADJSzXJlxE7uNngGH2uNe HKkeor18kh/QQrwpJAJItJjfWvEK+dfABOvXEDVRx6qOcwk1rShhfemUCFkQdGPxV2Fs ZIGqgHEZpwJJiFSbyJhgwdbQRFdfGkr3E6GcYwrXx9pVoD1Ea76MoHPd7plvc+THqxZM 7t1+dRn+oKGdkqj2oiF68MfonQp4G9ECtuvKtRwZIdmv1jGX+CPvMNHLaEEc9/Wkz7gx JgDwCGKhoRijPwxdQ+b3BQHacWv18Ha9HmofrPWgJf0rIHctm9E/s/TmZGHXjels/XMQ NWEw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1758788744; x=1759393544; h=references:to:cc:in-reply-to:date:subject:mime-version:message-id :from:x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=bfUqXyCWXV3qo6L1xKI+rJ+E4qYrH53DztsOTmamRDs=; b=rP53QXiZERC/WOxO94AmqqBtIOaL6fRcnwhwNRz0SN0yR0TxB4x+pLMmSy4cFeZG3q I6RSSTmH+ZO0DUtBnFNcNB1PAR7uePwlvDv0mAdztv/HxflpHpsma0/rjAAJ1+J4xB3W Hu887hGUceW7zeIu+7NY7ZNNHPRfyjz4R1xdDOz+YPNrKMJtUGxWuDWSYH5gxy3/qToh UqDWQjS4u+R3+EmJYRun7D7bGelG1+F35R+JxGtxrAmsw4Tt26Vk8bevtLlyJ7OPfjnH ccduTTQMfDULAWbjFwcIvL0leHCPGrldNgLoI6h2Bbx3bkJwiGxuUfvei4uOwg7YzJ0L CbFQ== X-Forwarded-Encrypted: i=1; AJvYcCUbgZbln2YJMYavfHU0lCDb4oxUdePwnGBUhJk+5aIQi+92gzixLbK/MJDCLM4xK6863jovhweFHzuoFZ1h@postgresql.org X-Gm-Message-State: AOJu0Yx6AUZWxtP0xJM/pP5xU69jQdbIlNAnQCXhdwNXCSvoDYAdDtab XgB/BQgTAkVxocE+e2dF2rjgWb1ud3hS+eCqEvl65mEOLfwcPC9mRRLB X-Gm-Gg: ASbGncsGNI9b481bXyWK80OxNFyixVMZtLuZomx5iPI/5mTlu6riw4cX1RzWrdfbuWQ mEFtvs0kc1qCES6js6+70GFviBsLCsjimoXB5i7VKJub6ykQ9LBh1Hy+acVjUXU7GOD1gEmTUOs bV21cYMSJzjltTfvRCFPQdfrd9Vr+4Jw9Ovp0zrRKyDWpjmR/OqpNUydUtWTiuYsUlJCoh1aRni VJBb/gK+KYR+efTw/VLDBKhaWeccBgsQgAccxBIWDfjOph/9JvCpmJxNlmQwkEHumkZNlIGoGzo Xr6JbXeuApx+6NP99zmWdRlJKQ3z/XhhgGt212OE37hZJT5g4m6Ko069+w62bzWlQvJ65yXziO0 oZdZFYzakUxQ83hpw+xbx4v30L5ELixpO X-Google-Smtp-Source: AGHT+IGiV2Hj3nzbxR4G6aOVTHfxlI9o/ThZfUFcaEbQuROex9LhtEwKilimhOkNSi8pysAGetgwIg== X-Received: by 2002:a05:6a20:7346:b0:25b:d1a8:5ccf with SMTP id adf61e73a8af0-2e7c7ea3b0dmr3269281637.21.1758788743812; Thu, 25 Sep 2025 01:25:43 -0700 (PDT) Received: from smtpclient.apple ([142.171.105.12]) by smtp.gmail.com with ESMTPSA id 98e67ed59e1d1-3347224b6d0sm1570949a91.11.2025.09.25.01.25.41 (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Thu, 25 Sep 2025 01:25:43 -0700 (PDT) From: Chao Li Message-Id: Content-Type: multipart/alternative; boundary="Apple-Mail=_A9D5C21C-9764-425E-BE4F-DF9D3880FD9D" Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3826.700.81\)) Subject: Re: Optimize LISTEN/NOTIFY Date: Thu, 25 Sep 2025 16:25:23 +0800 In-Reply-To: <0dc6a2cc-5216-4dc1-9dd2-430cafc6095b@app.fastmail.com> Cc: Tom Lane , Thomas Munro , pgsql-hackers , Heikki Linnakangas , Rishu Bagga To: Joel Jacobson References: <6899c044-4a82-49be-8117-e6f669765f7e@app.fastmail.com> <165530.1752362320@sss.pgh.pa.us> <02a7cd37-e2fc-4212-8b19-f8c239c95fb8@app.fastmail.com> <96f00bf1-cc9d-4520-9d02-9e14e7767c88@app.fastmail.com> <30c2aa7d-dd6c-4b68-a2e4-f217a1a34acf@app.fastmail.com> <0b4d402a-9ac2-4aa8-acf8-8231dbe579ea@app.fastmail.com> <3095599.1758644879@sss.pgh.pa.us> <0dc6a2cc-5216-4dc1-9dd2-430cafc6095b@app.fastmail.com> X-Mailer: Apple Mail (2.3826.700.81) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --Apple-Mail=_A9D5C21C-9764-425E-BE4F-DF9D3880FD9D Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 Hi Joel, Thanks for the patch. After reviewing it, I got a few comments. > On Sep 25, 2025, at 04:34, Joel Jacobson wrote: >=20 >=20 > Curious to hear thoughts on this approach. >=20 > /Joel > <0001-LISTEN-NOTIFY-make-the-latency-throughput-trade-off-.patch> 1. ``` --- a/src/include/utils/timeout.h +++ b/src/include/utils/timeout.h @@ -35,6 +35,7 @@ typedef enum TimeoutId IDLE_SESSION_TIMEOUT, IDLE_STATS_UPDATE_TIMEOUT, CLIENT_CONNECTION_CHECK_TIMEOUT, + NOTIFY_DEFERRED_WAKEUP_TIMEOUT, STARTUP_PROGRESS_TIMEOUT, ``` Can we define the new one after STARTUP_PROGRESS_TIMEOUT to try to = preserve the existing enum value? 2. ``` --- a/src/backend/utils/misc/postgresql.conf.sample +++ b/src/backend/utils/misc/postgresql.conf.sample @@ -766,6 +766,7 @@ autovacuum_worker_slots =3D 16 # autovacuum = worker slots to allocate #lock_timeout =3D 0 # in milliseconds, 0 is = disabled #idle_in_transaction_session_timeout =3D 0 # in milliseconds, 0 is = disabled #idle_session_timeout =3D 0 # in milliseconds, 0 is = disabled +#notify_latency_target =3D 0 # in milliseconds, 0 is disabled #bytea_output =3D 'hex' # hex, escape ``` I think we should add one more table to make the comment to align with = last line=E2=80=99s comment. 3. ``` /* GUC parameters */ bool Trace_notify =3D false; +int notify_latency_target; ``` I know compiler will auto initiate notify_latency_target to 0. But all = other global and static variables around are explicitly initiated, so it = would look better to assign 0 to it, which just keeps coding style = consistent. 4. ``` + /* + * Throttling check: if we were last active too recently, defer. = This + * check is safe without a lock because it's based on a = backend-local + * timestamp. + */ + if (notify_latency_target > 0 && + !TimestampDifferenceExceeds(last_wakeup_start_time, + = GetCurrentTimestamp(), + = notify_latency_target)) + { + /* + * Too soon. We leave wakeup_pending_flag untouched (it = must be true, + * or we wouldn't have been signaled) to tell senders we = are + * intentionally delaying. Arm a timer to re-awaken and = process the + * backlog later. + */ + enable_timeout_after(NOTIFY_DEFERRED_WAKEUP_TIMEOUT, + = notify_latency_target); + return; + } + ``` Should we avid duplicate timeout to be enabled? Now, whenever a = duplicate notification is avoid, a new timeout is enabled. I think we = can add another variable to remember if a timeout has been enabled. Best regards, -- Chao Li (Evan) HighGo Software Co., Ltd. https://www.highgo.com/ --Apple-Mail=_A9D5C21C-9764-425E-BE4F-DF9D3880FD9D Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8 Hi = Joel,

Thanks for the patch. After reviewing it, I got = a few comments.

On Sep 25, 2025, at 04:34, Joel Jacobson = <joel@compiler.org> wrote:


Curious to hear = thoughts on this approach.

/Joel
<0001-LISTEN-NOTIFY-mak= e-the-latency-throughput-trade-off-.patch>

1.
```
--- = a/src/include/utils/timeout.h
+++ = b/src/include/utils/timeout.h
@@ -35,6 +35,7 @@ typedef enum = TimeoutId
  = IDLE_SESSION_TIMEOUT,
  = IDLE_STATS_UPDATE_TIMEOUT,
  = CLIENT_CONNECTION_CHECK_TIMEOUT,
+ = NOTIFY_DEFERRED_WAKEUP_TIMEOUT,
  = STARTUP_PROGRESS_TIMEOUT,
```

<= div>Can we define the new one after STARTUP_PROGRESS_TIMEOUT to try to = preserve the existing enum = value?

2.
```
--- = a/src/backend/utils/misc/postgresql.conf.sample
+++ = b/src/backend/utils/misc/postgresql.conf.sample
@@ -766,6 = +766,7 @@ autovacuum_worker_slots =3D 16 # autovacuum worker slots to = allocate
 #lock_timeout =3D 0 # in = milliseconds, 0 is = disabled
 #idle_in_transaction_session_timeout =3D 0 # in = milliseconds, 0 is disabled
 #idle_session_timeout =3D = 0 = # in milliseconds, 0 is = disabled
+#notify_latency_target =3D 0 # in = milliseconds, 0 is disabled
 #bytea_output =3D 'hex' = # hex, escape
```

I = think we should add one more table to make the comment to align with = last line=E2=80=99s = comment.

3.
```
 /*= GUC parameters */
 bool Trace_notify =3D = false;
+int = notify_latency_target;
```

I know compiler will auto initiate notify_latency_target to 0. But all = other global and static variables around are explicitly initiated, so it = would look better to assign 0 to it, which just keeps coding style = consistent.

4.
```
+ = /*
+ * Throttling check: if we were = last active too recently, defer. This
+ * check = is safe without a lock because it's based on a = backend-local
+ * timestamp.
+ = */
+ = if (notify_latency_target > 0 &&
+ = !TimestampDifferenceExceeds(last_wakeup_start_time,
+ = = GetCurrentTimestamp(),
+ = notify_latency_target))
+ = {
+ /*
+ = * Too soon. We leave wakeup_pending_flag untouched (it must be = true,
+= * or we wouldn't have been signaled) to tell = senders we are
+ * intentionally = delaying. Arm a timer to re-awaken and process the
+ = * backlog later.
+ */
+ = enable_timeout_after(NOTIFY_DEFERRED_WAKEUP_TIMEOUT,
+ = notify_latency_target);
+ = return;
+ = }
+
```

Should = we avid duplicate timeout to be enabled? Now, whenever a duplicate = notification is avoid, a new timeout is enabled. I think we can add = another variable to remember if a timeout has been = enabled.

Best regards,
--
Chao Li (Evan)
HighGo Software = Co., Ltd.
https://www.highgo.com/




= --Apple-Mail=_A9D5C21C-9764-425E-BE4F-DF9D3880FD9D--