LISTEN/NOTIFY commit-time global lock: resource review, code analysis, and v18-vs-master benchmarks

[NikolayS]

LISTEN/NOTIFY and the commit-time global lock: resource review + code analysis + benchmarks

Reading list for discussion, with per-resource notes, a code-level analysis of master
(as of 13b7a8a, PG20devel), and fresh benchmark numbers taken on that tree.

Short answer to "is it a real bottleneck?": yes, and it is still present in master today.
The v19 work (282b1cde) fixed a different bottleneck (listener wakeups). The
commit-time global lock that Recall.ai hit is untouched.

---

1. What the code actually does (master, src/backend/commands/async.c)

PreCommit_Notify() — async.c:1309 — for any transaction with pending notifications:

/*
 * Serialize writers by acquiring a special lock that we hold till
 * after commit.  This ensures that queue entries appear in commit
 * order ...
 *
 * The lock is on "database 0", which is pretty ugly but it doesn't
 * seem worth inventing a special locktag category just for this.
 */
LockSharedObject(DatabaseRelationId, InvalidOid, 0, AccessExclusiveLock);

Properties worth being precise about:

1. It is a heavyweight lock on a shared object (classid 1262, objid 0), so it is
   cluster-wide, not per-database. Notifiers in unrelated databases serialize against
   each other. This is what shows up in logs as
   AccessExclusiveLock on object 0 of class 1262 of database 0.
2. It is held across the whole commit. In CommitTransaction() (xact.c):
   PreCommit_Notify() (:2371) → RecordTransactionCommit() (:2407, which does
   XLogFlush() at :1544 and SyncRepWaitForLSN() at :1599) → heavyweight locks released
   at ResourceOwnerRelease(RESOURCE_RELEASE_LOCKS) (:2480).
   So the serialized region includes the WAL fsync and the synchronous-replication
   round trip.
3. It therefore disables group commit for notifiers. Only one notifying transaction can
   be inside commit at a time, so each pays a full fsync serially instead of amortizing one
   fsync across many concurrent committers. This is the mechanism behind Recall.ai's
   "load up, CPU and I/O down" signature.
4. Only transactions that actually issued NOTIFY take the lock — non-notifying transactions
   never touch it. The blast radius spreads indirectly: notifiers stall while holding row
   locks and open XIDs, and everything that queues behind those rows/XIDs stalls too.
5. SignalBackends() runs in AtCommit_Notify(), i.e. after the lock is released, so
   the signal storm is not inside the serialized region — it is a separate problem.
6. A bare NOTIFY is not free of transaction cost: PreCommit_Notify() calls
   GetCurrentTransactionId(), forcing XID assignment and a real commit record.

2. What PG19's 282b1cde did and did not do

282b1cde "Optimize LISTEN/NOTIFY via shared channel map and direct advancement"
(Joel Jacobson, committed by Tom Lane, Jan 2026) adds a shared dshash channel→listeners map
so that SignalBackends() wakes only genuinely interested listeners, and directly advances
the queue pointer of uninterested idle listeners instead of signalling them.

Before (v18, async.c SignalBackends()): every listener in the same database was signalled
on every notifying commit, regardless of channel — O(listeners) kill() syscalls plus
O(listeners) queue scans per notify.

After: targeted wakeups. Big win for many-channels/few-interested workloads — §3.3 measures
it at 24x–59x with 500 listeners.

But PreCommit_Notify() still takes the same LockSharedObject(... AccessExclusiveLock).
The code even acknowledges the dependency (async.c:1319):

Note: if the heavyweight lock were ever removed for scalability reasons, we could achieve
the same guarantee by holding NotifyQueueLock in EXCLUSIVE mode across all our insertions...

Residual cost after v19: SignalBackends() still scans all listeners under
NotifyQueueLock in EXCLUSIVE mode on every notifying commit (async.c:2341), so
per-commit work is still O(total listeners) even when nobody is interested — just without
the syscalls.

Also worth noting: master documentation still says nothing about any of this.
doc/src/sgml/ref/notify.sgml Notes covers only queue-full behaviour and 2PC.

---

3. Benchmarks on this tree

Two builds from this repo, same container (4 vCPU / 15 GB), fsync=on,
shared_buffers=2GB, stock config otherwise, 8 s pgbench runs:

• master = 13b7a8a (PG20devel) — includes the v19 fix 282b1cde
• v18 = REL_18_BETA1 — before it

Two identical workloads, differing only in one statement:

-- control                              -- notify
BEGIN;                                  BEGIN;
INSERT INTO t (v, pad) VALUES (...);    INSERT INTO t (v, pad) VALUES (...);
                                        NOTIFY hotchan, 'payload';
END;                                    END;

3.1 Writer side — synchronous_commit=on, zero listeners

Nobody has issued LISTEN, so no wakeup, no signal, no queue scan, no thundering herd of any
kind is possible. This isolates the commit lock.

clients	control	notify (master)	notify (v18)	notify/control
1	842	731	615	0.87
2	1,107	1,020	998	0.92
4	2,224	1,147	1,036	0.52
8	3,934	1,042	1,021	0.26
16	8,240	1,034	989	0.13
32	11,909	932	901	0.08
64	16,397	951	906	0.06

(control shown from master; v18's control tracks it within noise — 764 / 1,999 / 4,199 /
7,926 / 11,982 / 16,937.)

Three things to take from this table:

1. The control workload scales ~19x from 1 to 64 clients. The NOTIFY workload does not
   scale at all — flat at ~1,000 TPS across a 64x concurrency range, slightly negative
   past 16 clients. It is pinned at approximately the single-client commit rate, i.e.
   1/fsync: the fingerprint of "one commit at a time, group commit disabled", which is
   exactly what holding an AccessExclusiveLock across XLogFlush() produces.
2. master and v18 are identical here (951 vs 906 at 64 clients). v19 changed nothing on
   the writer side, as expected from the code.
3. At 64 clients the cost of adding one NOTIFY to a trivial transaction is 17x.

Wait-event sampling during the 32-client runs (20 samples of pg_stat_activity, active
client backends only):

build	workload	wait_event_type / wait_event	samples
master	notify	Lock / object	610
v18	notify	Lock / object	609
master	control	LWLock / WALWrite	373
v18	control	LWLock / WALWrite	427
master	notify	IO / WalSync	18
master	control	IO / WalSync	18

Under NOTIFY, essentially every backend is parked on Lock / object — the heavyweight lock
on (classid 1262, objid 0) in database 0 — at all times, on both builds. The control runs
never wait on it once; their dominant wait is LWLock / WALWrite, i.e. backends piling into a
shared group commit — precisely the optimization the NOTIFY lock forbids.

3.2 Writer side — synchronous_commit=off

clients	control	notify (master)	notify (v18)
1	5,542	4,319	4,402
2	12,313	9,253	8,849
4	43,066	19,932	30,162
16	41,475	22,138	22,329
32	39,624	14,169	14,664
64	35,495	12,260	12,815

(4 vCPUs, so everything ≥ 8 clients is CPU-oversubscribed and noisy.) With the fsync out of
the serialized region, NOTIFY reaches tens of thousands of TPS instead of one thousand. The
lock still costs ~3x at high concurrency, but it is no longer catastrophic.

This is the most important number pair in the whole discussion: same binary, same
workload, same lock — 17x penalty with synchronous_commit=on, ~3x with it off. The
severity of the global lock is proportional to how long a commit takes while holding it.
That is why this destroys production clusters on network storage and with synchronous
replicas (SyncRepWaitForLSN() is inside the lock too), and why it is easy to under-estimate
on a laptop.

3.3 Listener side — what v19 actually fixed (8 writers, synchronous_commit=off)

How much does one notifying commit cost as idle listeners pile up? Two shapes: uninterested
listeners (1000 distinct channels, notifier picks one nobody listens on — the case
282b1cde targets) and interested listeners (everyone on hotchan, everyone receives
everything). Each series normalized to its own 0-listener baseline.

Uninterested listeners

listeners	v18 TPS	v18 vs 0	master TPS	master vs 0	master/v18
0	26,476	1.00	24,964	1.00	0.94
50	4,279	0.16	19,466	0.78	4.5x
200	1,044	0.04	11,820	0.47	11x
500	385	0.01	9,313	0.37	24x

Interested listeners (every listener receives every message)

listeners	v18 TPS	v18 vs 0	master TPS	master vs 0	master/v18
0	39,026	1.00	33,956	1.00	0.87
50	4,698	0.12	35,989	1.06	7.7x
200	1,251	0.03	34,268	1.01	27x
500	491	0.013	28,881	0.85	59x

v19 is a genuinely large win — 24x to 59x at 500 listeners — and it deserves the credit.
On v18 a mere 50 listeners cost 6–8x throughput regardless of whether they cared about the
message, because SignalBackends() woke every listener in the database on every notifying
commit. That is the "thundering herd" Joel Jacobson was pointing at, and it was severe enough
to completely mask the lock in any benchmark that had listeners attached.

Two subtleties in the master column worth noting:

• 500 listeners actively consuming every message now cost the notifier only ~15%, while 500
  uninterested listeners cost ~2.7x. That inversion is real: interested listeners get
  wakeupPending set once and are skipped by subsequent commits (async.c:2314), whereas
  uninterested ones fall into the second loop of SignalBackends() (async.c:2341), which
  walks every listener on every notifying commit to direct-advance its queue pointer,
  under NotifyQueueLock in EXCLUSIVE mode.
• So v19 converted an O(listeners) syscall storm into an O(listeners) scan under an
  exclusive LWLock. Two orders of magnitude cheaper — still O(listeners) per commit.

---

4. The resources

4.1 Recall.ai — Postgres LISTEN/NOTIFY does not scale (Jul 2025)

Incident report: three outages 19–22 Mar 2025 on a write-heavy workload with tens of
thousands of concurrent writers. Signature: load and active sessions spike while CPU, disk
I/O and network all drop. They found AccessExclusiveLock on object 0 of class 1262 of database 0 in the logs, traced it to the PreCommit_Notify() comment, removed LISTEN/NOTIFY
from the one code path that used it, and shipped in under a day.

Considerations. The diagnosis is correct and matches the source exactly; the
"resources go down while load goes up" signature is the classic fingerprint of a serializing
mutex and is the most reusable part of the post. Three caveats for our discussion:

• The phrasing "global lock on the entire instance" is easy to misread. Non-notifying
  transactions never acquire this lock. What is global is the scope (cluster-wide, all
  databases) — not the set of victims. The damage reaches non-notifiers indirectly, via
  row locks and XIDs held by stalled notifiers.
• The post doesn't identify the real amplifier: the lock is held across XLogFlush() and
  SyncRepWaitForLSN(), so notifying commits cannot group-commit. That is why the effect
  is so violent on cloud storage / synchronous replicas and mild on a laptop with
  synchronous_commit=off. See §3 — same binary, same workload, the gap moves from 17x to
  ~3x purely by changing synchronous_commit.
• No reproducible harness or TPS numbers were published, which is part of why the hackers
  thread turned into an argument about which bottleneck was real.

4.2 pgsql-hackers — docs: LISTEN/NOTIFY performance considerations (Nikolay Samokhvalov, 10 Jul 2025)

Patch proposing to document, and backpatch to all supported versions: (1) the cluster-wide
commit lock, (2) O(N²) duplicate checking, (3) diagnosis via log_lock_waits, (4) logical
decoding as an alternative. Companion report on pgsql-docs two days later
(msg06859):
"engineers had to read the source to realize NOTIFY takes a global instance lock".

Considerations.

• Still not committed. Verified against this tree: doc/src/sgml/ref/notify.sgml
  Notes still covers only queue-full behaviour and the 2PC restriction. Nothing about the
  lock. Given that a full fix is years out (§4.5), this remains the highest
  value-per-byte change available, and the only one that helps users on v13–v18 today.
• The O(N²) point needs re-checking before re-posting. Per-transaction duplicate
  elimination is not generally quadratic and has not been since PG13:
  AsyncExistsPendingNotify() switches to a hash table once a transaction has
  ≥ MIN_HASHABLE_NOTIFIES (16) pending events — present in v18 (async.c:2294) and in
  master (async.c:3169). Below 16 events the linear scan is deliberate and harmless.
  What is genuinely still quadratic is the subtransaction merge path, which re-probes
  each child notification against the parent list (async.c:2497 in master). Separately,
  v19's new unique-channel-name list keeps an explicit O(N^2) fallback below the same
  threshold (async.c:1253). The doc wording should target those specific cases rather
  than make a blanket O(N²) claim, otherwise the patch is easy to reject on accuracy.
• Worth adding to the patch: that the lock is held across the WAL flush and sync-rep wait
  (i.e. it defeats group commit), and that pg_notification_queue_usage() /
  max_notify_queue_pages are the other operational cliff.

4.3 Postgres.FM — DBOS episode (24 Jul 2026)

Qian Li and Peter Kraft (DBOS) with Michael Christofides and Nikolay Samokhvalov. Covers the
Recall.ai post, the global lock, the v19 commit, and DBOS's use of Postgres as a durable
workflow/queue store rather than via LISTEN/NOTIFY.

Considerations. Useful mainly as the framing document — it is the bridge between the
incident report and the "actually scales" rebuttal, and it is where the "PG19 fixed it" belief
gets corrected in public. Worth pinning down on the record which of the two bottlenecks each
speaker means, because that ambiguity is what kept the hackers thread going for months.

4.4 DBOS — Postgres LISTEN/NOTIFY Actually Scales

Their original design issued a NOTIFY per stream write and plateaued at 2.9K writes/s
"without visibly consuming any Postgres resource (CPU, memory, or IOPS)" — the same
fingerprint Recall.ai reported. Their fix: buffer notifications in memory and flush them
periodically as batched transactions, so the lock is taken once per flush instead of once
per write, and individual writes can group-commit again between flushes. Result:
60K writes/s (~20x), 15–100 ms latency, with Postgres CPU finally saturated. Because a
crash can drop buffered notifications, readers also run a low-frequency fallback poll.
They state plainly that the v19 patch "does not resolve this bottleneck".

Considerations. The title is rhetorical; the content agrees with Recall.ai rather than
refuting it. What scales is amortized NOTIFY — fewer notifying commits — which is a
restatement of "the per-commit lock is the bottleneck". Two honest details worth keeping in
view: the fallback poll is an admission that batching weakens the delivery guarantee, and the
15–100 ms latency is the price paid for the 20x. This is currently the best-practice pattern
for anyone who must stay on LISTEN/NOTIFY: batch notifications, poll as a safety net.

4.5 pgsql-hackers — Proposal: Out-of-Order NOTIFY via GUC to Improve LISTEN/NOTIFY Throughput (Rishu Bagga, 18 Jul 2025)

The upstream attempt to actually fix it. Timeline below is summarized from the public
archive — worth spot-checking individual attributions against the original messages before
quoting anyone:

• Rishu Bagga (Jul 18): GUC publish_out_of_order_notifications to skip the lock in
  PreCommit_Notify(). ~185–190K → ~330–350K TPS (M2 MacBook Air, NOTIFY-only workload).
• Tom Lane (Jul 18): rejects a semantics-changing GUC on principle, and proposes a
  redesign — WAL as the transport: write notification payloads to WAL in
  PreCommit_Notify(), put the LSN in the commit record, and push only fixed-size
  (xid, dbid, lsn) entries into the SLRU queue, so the serialized region shrinks to the
  tiny queue insert. Listeners read payloads back from WAL. Also raises notifications on
  standbys as a long-requested side benefit.
• Joel Jacobson (Jul 18–22): disputes that the lock is the main bottleneck — with a
  realistic LISTEN+NOTIFY workload the out-of-order gain collapses from 2x to 14%, and
  points at the kill(pid, SIGUSR1) "thundering herd" instead. But he also measures
  1 listener + 1000 connections: 7,679 → 95,430 TPS without the lock (~12x). By Jul 30 he
  concedes it is workload-dependent and both matter.
• Rishu Bagga (Sep 4–10): implements Tom's WAL design, v1–v4.
• Arseniy Mukhin / Matheus Alcantara / Masahiko Sawada (Sep 10–12): correctness
  problems — the async WAL record is written after the commit record and can be lost on
  crash; palloc() inside a critical section; MVCC visibility checks (XidInMVCCSnapshot)
  cannot simply be dropped; WAL retention must be guaranteed while notifications are
  unconsumed; two SignalBackends() call sites; regress/isolation/TAP failures.

Status: not committed.

Considerations. The central 2025 disagreement — lock vs. thundering herd — is now
resolved by construction. Joel's own v19 patch removed the herd; whatever remains must be
the lock, and §3.1 measures it on post-v19 master with zero listeners, where no herd can
exist. Joel's 14% figure was measured on a tree where the herd still dominated, so it should
not be carried forward as evidence that the lock is cheap — §3.3 measures that herd at
6–8x throughput loss with only 50 listeners on v18, more than enough to hide a 17x
writer-side effect behind it in any benchmark that had listeners attached. Both of them were
right about their own measurement; they were measuring different bottlenecks. Tom's
WAL-transport design is the right shape (it attacks exactly the "lock held across fsync"
property), but the review findings are fundamental rather than cosmetic, and durability of
notifications-in-WAL is a genuinely new problem for a subsystem that has always been
explicitly crash-lossy (async.c:34: "notifications are not expected to survive database
crashes"). Realistically this is a v20+ item, which raises the priority of §4.2.

4.6 (the second copy of the CAM527d8… link is the same thread as §4.2)

4.7 PgDog — Scaling Postgres LISTEN/NOTIFY (Lev Kokotov, 4 Aug 2025)

Product announcement for LISTEN/NOTIFY support in PgDog, a Rust pooler/sharding proxy. The
only scaling limit it names is connection count: "the problem arises once you start pushing
its max_connections limit", and "LISTEN/NOTIFY must share one database instance, so
traditional scaling techniques, like adding replicas, don't work". PgDog terminates pub/sub
in-process on Tokio broadcast channels and registers just one listener per PgDog instance, so
"Postgres only has to send a message as many times as there are instances of PgDog". Commands
are acknowledged immediately and executed in a background task, giving at-most-once delivery;
on sharded clusters the channel name is hashed and routed to a shard. No benchmarks, no
numbers, no mention of PG18/19.

Considerations. It does not conflate the writer- and listener-side bottlenecks — it
identifies neither. PreCommit_Notify(), the cluster-wide AccessExclusiveLock on
(1262, 0), the fact that it is held across XLogFlush()/SyncRepWaitForLSN(), and the
wakeup storm all go unmentioned. Multiplexing listeners attacks the listener side only —
exactly what 282b1cde has since largely fixed in core (§3.3: 500 interested listeners now
cost a notifier ~15%) — though it still helps against the residual O(listeners) scan. On the
writer side it does nothing: every client NOTIFY is forwarded as its own NOTIFY, so per-commit
lock tenure and the ~1/fsync ceiling of §3.1 are unchanged, and nothing indicates DBOS-style
batching (§4.4). Its one genuine writer-side win is left unremarked — hashing channels across
shards splits a per-cluster lock across separate clusters. Dated Aug 2025, so carrying no
"PG19 fixed it" claim is fair rather than an error; the closing advice (at-most-once, add a
polling fallback if you need guaranteed delivery) matches §4.4.

---

5. Where this leaves us

1. Yes, it is a real bottleneck, and it is still there. Not "was there before v19", not
"only with many listeners". On this master tree, with zero listeners, synchronous_commit=on
and 64 clients, adding one NOTIFY to a trivial INSERT transaction costs 17x throughput,
and pins the cluster at roughly one commit per fsync no matter how much concurrency you throw
at it. v18 and master measure identically on this path (906 vs 951 TPS at 64 clients),
which is the cleanest possible demonstration that v19 did not touch it.

2. There were always two separate bottlenecks, and the public discussion keeps merging
them. They have different victims and different fixes:

	writer-side	listener-side
mechanism	heavyweight AccessExclusiveLock on (1262, 0) held across commit	O(listeners) signals + queue scans per notifying commit
who suffers	every notifying transaction, cluster-wide	the listeners, and the notifier that has to signal them
status	open — v18 and master measure identically	fixed in v19 by 282b1cde — measured 24–59x better at 500 listeners (residual O(listeners) scan remains)
scales with	commit duration (fsync, sync rep) × notifying commit rate	number of listeners
measured cost	17x at 64 clients, synchronous_commit=on, 0 listeners	v18: 6–8x at just 50 listeners; master: 1.2–2.7x at 500

Recall.ai hit the first. Joel's 14% measurement in the hackers thread was taken on a tree
where the second one dominated and masked it. Now that v19 has removed the second, the first
is cleanly measurable — §3 is that measurement.

3. The severity is proportional to how long a commit takes while holding the lock. 17x
with synchronous_commit=on, ~3x with it off, same binary and workload. SyncRepWaitForLSN()
is inside the lock too, so a synchronous standby puts a network round trip in the serialized
region. The perverse consequence: the more durable your configuration, the worse
LISTEN/NOTIFY scales — and managed cloud Postgres on network storage with a sync replica is
the worst case, which is exactly the population that keeps filing these reports.

4. Why this is hard to fix (the part worth discussing). The lock is not protecting the
queue insert — NotifyQueueLock does that. It is enforcing queue order == commit order, so
that a listener reading the queue sequentially never encounters an entry from a
still-uncommitted transaction ahead of committed ones. asyncQueueProcessPageEntries()
stops at the first uncommitted entry by design. Release the lock any earlier and another
transaction can commit ahead of you, putting an uncommitted entry in front of committed ones;
the listener then stalls delivery of everything behind it (head-of-line blocking), which is
precisely the trade Rishu's out-of-order GUC was making. Tom's WAL-transport design is the
only proposal on the table that keeps ordering while shrinking the serialized region to a
fixed-size queue insert — ordering comes from the commit LSN instead of from lock tenure. Its
open problems (crash-durability of notification payloads now living in WAL, WAL retention
while notifications are unconsumed, critical-section violations) are real and not cosmetic,
for a subsystem whose contract has always been "notifications do not survive a crash"
(async.c:34).

5. What to do in the meantime.

For users, in rough order of value:

• Batch. One notifying commit carrying N notifications costs the same lock tenure as one
  carrying one. This is the whole of DBOS's 20x, and it is available to everyone today.
  Pair it with a low-frequency fallback poll if you need the delivery guarantee back.
• Never NOTIFY per row from a row-level trigger — aggregate in a statement-level trigger or
  a transition table and send once.
• If notification rate must track commit rate, LISTEN/NOTIFY is the wrong transport; use a
  queue table with polling (SKIP LOCKED), or logical decoding for CDC.
• Diagnosis: log_lock_waits = on, then look for AccessExclusiveLock on object 0 of class 1262 of database 0; or watch for wait_event_type = 'Lock' / wait_event = 'object' in
  pg_stat_activity. The tell-tale is throughput collapsing while CPU and I/O go down.
• Also watch pg_notification_queue_usage() and max_notify_queue_pages (default 1048576
  pages = 8 GB) — the other operational cliff: a single listener sitting in a long
  transaction stops queue truncation.

For upstream:

• Land the docs patch (§4.2), backpatched. A full fix is realistically v20+; users on
  v13–v19 need to be able to find this out without reading async.c. Fix the O(N²) wording
  first so it can't be rejected on accuracy.
• Revive Tom's WAL-transport direction with the September review findings addressed.
• Worth a separate look: the residual O(total listeners) scan in SignalBackends()
  (async.c:2341) which runs under NotifyQueueLock EXCLUSIVE on every notifying commit —
  §3.3 measures 2.7x degradation at 500 uninterested listeners even post-v19.

---

6. Reproducer

Both builds came from this repo (13b7a8a and REL_18_BETA1), configured with
./configure --prefix=... --without-readline --without-zlib --without-icu. Run as a
non-root user.

writers.sh — writer-side matrix (control vs NOTIFY, zero listeners) + wait-event sampling

#!/bin/bash
# Writer-side test: control vs notify with ZERO listeners (isolates the commit lock)
# usage: writers.sh <PGBIN> <PGDATA> <PORT> <LABEL> <OUTDIR> [initdb]
set -u
PGBIN=$1; PGDATA=$2; PORT=$3; LABEL=$4; OUT=$5; DOINIT=${6:-no}
export PATH=$PGBIN:$PATH
export PGPORT=$PORT PGHOST=/tmp PGDATABASE=bench
DUR=${DUR:-8}
RES=$OUT/$LABEL.writers.csv
mkdir -p "$OUT"

if [ "$DOINIT" = initdb ]; then
  rm -rf "$PGDATA"
  initdb -D "$PGDATA" -U postgres --no-sync -A trust >/dev/null 2>&1
  cat >> "$PGDATA/postgresql.conf" <<EOF
port = $PORT
unix_socket_directories = '/tmp'
listen_addresses = ''
shared_buffers = 2GB
max_connections = 1000
max_wal_size = 8GB
checkpoint_timeout = 30min
autovacuum = off
fsync = on
log_min_messages = warning
EOF
  pg_ctl -D "$PGDATA" -l "$OUT/$LABEL.pglog" -w start >/dev/null 2>&1
  createdb -U postgres bench 2>/dev/null
  psql -U postgres -q -c "create table t(id bigserial primary key, v int, pad text)" 2>/dev/null
else
  pg_ctl -D "$PGDATA" status >/dev/null 2>&1 || pg_ctl -D "$PGDATA" -l "$OUT/$LABEL.pglog" -w start >/dev/null 2>&1
fi

cat > /tmp/c_$LABEL.sql <<'EOF'
\set c random(1, 1000000)
BEGIN;
INSERT INTO t (v, pad) VALUES (:c, 'abcdefghij');
END;
EOF
cat > /tmp/n_$LABEL.sql <<'EOF'
\set c random(1, 1000000)
BEGIN;
INSERT INTO t (v, pad) VALUES (:c, 'abcdefghij');
NOTIFY hotchan, 'payload';
END;
EOF

run() {  # script clients tag sc
  local tps
  tps=$(pgbench -U postgres -n -f "$1" -c "$2" -j "$(( $2 > 4 ? 4 : $2 ))" -T "$DUR" bench 2>/dev/null \
        | grep -E '^tps' | head -1 | sed -E 's/tps = ([0-9.]+).*/\1/')
  echo "$LABEL,$3,$4,0,$2,${tps:-ERR}" | tee -a "$RES"
}

echo "label,workload,sync_commit,listeners,clients,tps" > "$RES"
for sc in on off; do
  psql -U postgres -q -c "alter system set synchronous_commit='$sc'" -c "select pg_reload_conf()" >/dev/null
  for c in 1 2 4 8 16 32 64; do
    run /tmp/c_$LABEL.sql "$c" control "$sc"
    run /tmp/n_$LABEL.sql "$c" notify  "$sc"
  done
done

# wait-event sampling at 32 clients, sync on
psql -U postgres -q -c "alter system set synchronous_commit='on'" -c "select pg_reload_conf()" >/dev/null
for pair in "n_$LABEL.sql notify32" "c_$LABEL.sql control32"; do
  set -- $pair
  ( pgbench -U postgres -n -f "/tmp/$1" -c 32 -j 4 -T 12 bench >/dev/null 2>&1 ) &
  bp=$!
  sleep 2
  for i in $(seq 1 20); do
    psql -U postgres -tAF, -c "select '$2', coalesce(wait_event_type,'CPU'), coalesce(wait_event,'-'), count(*) from pg_stat_activity where backend_type='client backend' and pid <> pg_backend_pid() and state='active' group by 1,2,3" >> "$OUT/$LABEL.waits.csv" 2>/dev/null
    sleep 0.4
  done
  wait $bp
done

echo "DONE $LABEL writers"

listeners.sh — listener-scaling matrix (uninterested vs interested)

#!/bin/bash
# Listener-scaling test — hard reset (server restart) between configurations
# usage: listeners.sh <PGBIN> <PGDATA> <PORT> <LABEL> <OUTDIR>
set -u
PGBIN=$1; PGDATA=$2; PORT=$3; LABEL=$4; OUT=$5
export PATH=$PGBIN:$PATH
export PGPORT=$PORT PGHOST=/tmp PGDATABASE=bench
DUR=${DUR:-8}
RES=$OUT/$LABEL.listeners.csv

reset_server() {
  pkill -u "$(id -un)" psql 2>/dev/null
  pkill -u "$(id -un)" -f "sleep 100000" 2>/dev/null
  pg_ctl -D "$PGDATA" -m immediate -w stop >/dev/null 2>&1
  rm -f /tmp/lfifo.* 2>/dev/null
  pg_ctl -D "$PGDATA" -l "$OUT/$LABEL.pglog" -w start >/dev/null 2>&1
  psql -U postgres -q -c "alter system set synchronous_commit='off'" -c "select pg_reload_conf()" >/dev/null 2>&1
}

start_listeners() {   # $1 = count, $2 = hot|spread
  local n=$1 mode=$2 ch
  for i in $(seq 1 "$n"); do
    if [ "$mode" = hot ]; then ch=hotchan; else ch="idle$i"; fi
    # FIFO keeps psql's stdin open so the session stays IDLE and actually drains
    # the queue; a backend busy inside pg_sleep() would stall the queue tail.
    mkfifo "/tmp/lfifo.$i" 2>/dev/null
    ( psql -U postgres -q -f "/tmp/lfifo.$i" >/dev/null 2>&1 ) &
    ( echo "LISTEN $ch;"; exec sleep 100000 ) > "/tmp/lfifo.$i" &
  done
  sleep 5
}

run() {   # $1 script, $2 clients, $3 tag, $4 listeners
  local live tps
  live=$(psql -U postgres -tAc "select count(*) from pg_stat_activity where backend_type='client backend'" 2>/dev/null)
  tps=$(pgbench -U postgres -n -f "$1" -c "$2" -j 4 -T "$DUR" bench 2>/dev/null \
        | grep -E '^tps' | head -1 | sed -E 's/tps = ([0-9.]+).*/\1/')
  echo "$LABEL,$3,$4,$live,$2,${tps:-ERR}" | tee -a "$RES"
}

reset_server
psql -U postgres -q -c "create table if not exists t(id bigserial primary key, v int, pad text)" >/dev/null 2>&1

cat > /tmp/nr_$LABEL.sql <<'EOF'
\set c random(1, 1000000)
\set ch random(1, 1000)
BEGIN;
INSERT INTO t (v, pad) VALUES (:c, 'abcdefghij');
SELECT pg_notify('cold' || :ch, 'payload');
END;
EOF
cat > /tmp/nh_$LABEL.sql <<'EOF'
\set c random(1, 1000000)
BEGIN;
INSERT INTO t (v, pad) VALUES (:c, 'abcdefghij');
NOTIFY hotchan, 'payload';
END;
EOF

echo "label,workload,listeners_requested,sessions_live,clients,tps" > "$RES"

for L in 0 50 200 500; do
  reset_server
  [ "$L" -gt 0 ] && start_listeners "$L" spread
  run /tmp/nr_$LABEL.sql 8 notify_uninterested "$L"
done

for L in 0 50 200 500; do
  reset_server
  [ "$L" -gt 0 ] && start_listeners "$L" hot
  run /tmp/nh_$LABEL.sql 8 notify_interested "$L"
done

reset_server
echo "DONE $LABEL listeners"

./writers.sh   <PGBIN> <PGDATA> <PORT> <LABEL> <OUTDIR> initdb
./listeners.sh <PGBIN> <PGDATA> <PORT> <LABEL> <OUTDIR>

Caveats on the numbers: 4 vCPU container, so everything at >= 8 clients is
CPU-oversubscribed and the synchronous_commit=off rows are noisy; 8-second runs; single
container-local disk, no synchronous standby (which would make §3.1 dramatically worse,
since SyncRepWaitForLSN() is inside the lock). The synchronous_commit=on writer rows and
the listener-scaling rows are the ones that reproduce cleanly.

[NikolayS]

Revised docs patch for NOTIFY (per §4.2 / §5)

Here is a rewritten version of the July 2025 proposal (CAM527d8oDVb2K+tTjpjLkd7g14HLDiwwnvv=rcn4BMEs=MatkQ@mail.gmail.com), written against master (13b7a8a) but deliberately containing only statements that are true on v13 through master, so it is backpatchable as-is.

What changed versus the July 2025 patch, and why

1. The blanket O(N²) duplicate-checking claim is gone. This is the main correction, and the one most likely to have gotten the original rejected on accuracy. Per-transaction duplicate elimination has not been quadratic since PG13: AddEventToPendingNotifies() builds a hash table once a transaction accumulates MIN_HASHABLE_NOTIFIES (16, async.c:527) pending events — async.c:3169 on master, async.c:2294 on v18. Below the threshold the linear scan is deliberate and harmless. What is still quadratic is the subtransaction merge: on subtransaction commit, each child notification is re-probed against the parent's pending list one at a time (async.c:2497 on master, AsyncExistsPendingNotify() at async.c:3123), which costs the product of the two list lengths. The patch states exactly that case and nothing broader, and gives a concrete user-visible hook for it (PL/pgSQL blocks with an EXCEPTION clause are subtransactions).

2. Added the point the original patch missed entirely: the lock is held across the commit, so notifiers cannot group-commit. PreCommit_Notify() (xact.c:2371) runs before RecordTransactionCommit() (xact.c:2407, containing XLogFlush() at :1544 and SyncRepWaitForLSN() at :1599), and heavyweight locks are not released until ResourceOwnerRelease(RESOURCE_RELEASE_LOCKS) at xact.c:2480. So the WAL flush and the synchronous-replication round trip are inside the serialized region. This is the actual mechanism, and it is what explains why the effect ranges from "barely noticeable" to "cluster is down":

On this tree (4 vCPU, fsync=on, zero listeners, 64 clients), adding one NOTIFY to a trivial INSERT transaction costs 17x throughput with synchronous_commit=on (16,397 → 951 TPS), and NOTIFY throughput is flat at ~1000 TPS from 1 to 64 clients — pinned at roughly one commit per fsync. With synchronous_commit=off the same binary and workload cost only ~3x.

Those numbers are not in the SGML (upstream reference docs don't carry benchmark figures), but they are why the patch says the effect is "correspondingly larger when commits are expensive" and names synchronous_commit, synchronous_standby_names, and high-latency storage. That sentence is the difference between a reader calibrating this correctly and dismissing it after testing on a laptop.

3. Precision on the lock's scope. It is cluster-wide (LockSharedObject(DatabaseRelationId, InvalidOid, 0, AccessExclusiveLock), async.c:1309 — dbid InvalidOid, hence class 1262 ... database 0), so notifiers in different databases contend. But it is explicitly stated that non-notifying transactions, and LISTEN/UNLISTEN, do not take it. The Recall.ai phrasing "global lock on the entire instance" is easy to misread in the other direction.

4. Cost is per notifying transaction, not per notification. Stated explicitly, because it is the one thing users can act on today (it is the whole of DBOS's 20x), together with the row-level-trigger anti-pattern.

5. Diagnosis section gives the literal log line (AccessExclusiveLock on object 0 of class 1262 of database 0), the pg_stat_activity Lock/object wait event, and the counterintuitive symptom — throughput falls while CPU and I/O fall too — which is the most reusable part of the incident reports and is precisely what makes this hard to recognize.

6. Alternatives kept short: queue table polled with SELECT ... FOR UPDATE SKIP LOCKED, and logical decoding when the goal is streaming data changes. The original mentioned only logical decoding, which is the wrong recommendation for the "I just need a work queue" case.

Deliberately not included: anything about listener-side wakeup behaviour, the shared channel map, or direct advancement (282b1cde). Those are v19+ only, and the text has to be true on v13. Also not included: pg_notification_queue_usage() / max_notify_queue_pages, since the existing Notes section already covers the queue-full cliff and the long-transaction-blocks-truncation case.

Placement is a new <refsect2> inside Notes, before the existing pg_notify one, so the section gets its own heading and anchor rather than being buried in an unheaded run of paragraphs.

Validation

xmllint is present in this environment but the DocBook 4.5 DTD is not installed and network fetch of it is blocked, so make -C doc/src/sgml check (which is xmllint --noout --valid postgres.sgml) cannot run here — it fails on the DTD, not on this patch. What I did verify:

• well-formedness: xmllint --noout on the patched file with the needed entity declared — passes;
• every linkend resolves to an existing id in the tree: guc-synchronous-commit, guc-synchronous-standby-names, guc-log-lock-waits, guc-deadlock-timeout, sql-select (ref/select.sgml), logicaldecoding, monitoring-pg-stat-activity-view;
• check-tabs / check-nbsp: no tabs, no non-breaking spaces introduced;
• element usage (<refsect2>, <structfield>, <structname>, <acronym>, <application>, <link>/<xref>) matches the idiom of neighbouring files.

Full DTD validation should be re-run before posting to -hackers.

Patch

diff --git a/doc/src/sgml/ref/notify.sgml b/doc/src/sgml/ref/notify.sgml
index fd6ed54e..0ff3eeae 100644
--- a/doc/src/sgml/ref/notify.sgml
+++ b/doc/src/sgml/ref/notify.sgml
@@ -175,6 +175,81 @@ NOTIFY <replaceable class="parameter">channel</replaceable> [ , <replaceable cla
    prepared for two-phase commit.
   </para>
 
+  <refsect2>
+   <title>Performance Considerations</title>
+
+   <para>
+    So that notifications are queued in commit order, a transaction that has
+    executed <command>NOTIFY</command> takes a heavyweight lock during commit
+    that serializes it against every other committing transaction that has
+    executed <command>NOTIFY</command>.  This lock is cluster-wide, not
+    per-database: notifying transactions running in different databases of the
+    same cluster contend with one another.  Transactions that have not executed
+    <command>NOTIFY</command> do not take this lock, and
+    <command>LISTEN</command> and <command>UNLISTEN</command> do not take it
+    either.
+   </para>
+
+   <para>
+    The lock is held until the notifying transaction's commit is complete,
+    which includes flushing the transaction's <acronym>WAL</acronym> records to
+    disk and, if synchronous replication is in use, waiting for the standby
+    server(s) to respond.  Notifying transactions therefore cannot be
+    group-committed with one another: each one pays for a commit round trip on
+    its own, and the aggregate rate of notifying transactions is limited to
+    roughly one such round trip at a time, no matter how many client sessions
+    are active.  The effect is correspondingly larger when commits are
+    expensive, that is with <xref linkend="guc-synchronous-commit"/> set to
+    <literal>on</literal>, with <xref linkend="guc-synchronous-standby-names"/>
+    configured, or on storage with high write latency.
+   </para>
+
+   <para>
+    The cost is incurred once per notifying transaction, not once per
+    notification, since a transaction that sends many notifications takes the
+    lock only once.  Applications that need a high notification rate should
+    therefore accumulate notifications and send them from as few transactions
+    as possible.  In particular, avoid executing <command>NOTIFY</command> in a
+    row-level trigger; collect the notifications in a statement-level trigger
+    instead, or send a single notification per transaction and let listeners
+    determine what changed.
+   </para>
+
+   <para>
+    Duplicate notifications are eliminated within a transaction, as described
+    above.  This is inexpensive in the ordinary case, but when a subtransaction
+    that has sent notifications commits, its notifications are checked
+    individually against those already pending in the parent transaction, at a
+    cost proportional to the product of the two lists' lengths.  Sending large
+    numbers of notifications from many subtransactions, for example from
+    <application>PL/pgSQL</application> blocks containing an
+    <literal>EXCEPTION</literal> clause, can therefore become expensive.
+   </para>
+
+   <para>
+    Contention on the commit lock can be diagnosed by setting
+    <xref linkend="guc-log-lock-waits"/>, which reports waits longer than
+    <xref linkend="guc-deadlock-timeout"/> in the server log as waits for
+    <literal>AccessExclusiveLock on object 0 of class 1262 of database 0</literal>,
+    or by looking for sessions with <structfield>wait_event_type</structfield>
+    <literal>Lock</literal> and <structfield>wait_event</structfield>
+    <literal>object</literal> in
+    <link linkend="monitoring-pg-stat-activity-view"><structname>pg_stat_activity</structname></link>.
+    A characteristic symptom is that transaction throughput falls while
+    <acronym>CPU</acronym> and disk utilization fall as well, because the
+    waiting sessions are idle rather than busy.
+   </para>
+
+   <para>
+    If the required notification rate cannot be reduced by batching, consider
+    another mechanism: a queue table that consumers poll using
+    <literal>SELECT ... FOR UPDATE SKIP LOCKED</literal> (see
+    <xref linkend="sql-select"/>) does not serialize commits, and
+    <xref linkend="logicaldecoding"/> is a better fit when the goal is to
+    stream data changes to an external consumer.
+   </para>
+  </refsect2>
+
   <refsect2>
    <title>pg_notify</title>
 

listen.sgml is unchanged — it already points at NOTIFY for the extended discussion (<xref linkend="sql-notify"/>), so the new section is reachable from there. The working tree was restored afterwards; nothing was committed or pushed.

---

Generated by Claude Code

[NikolayS]

Correction to the patch above: drop the duplicate-elimination paragraph entirely

The revised patch narrowed the original's blanket O(N²) claim down to the subtransaction merge. On checking the code, that narrowed claim is also wrong, and should be removed rather than reworded. Details, since this is the exact kind of thing that gets a docs patch rejected on -hackers:

The merge loop (async.c:2492) does re-probe each child notification individually:

pendingNotifies = pendingNotifies->upper;
/* Insert all the subxact's events into parent, except for dups */
foreach(l, childPendingNotifies->events)
{
    Notification *childn = (Notification *) lfirst(l);

    if (!AsyncExistsPendingNotify(childn))
        AddEventToPendingNotifies(childn);
}

But note that pendingNotifies has already been switched to the parent list, so AsyncExistsPendingNotify() probes the parent — and it uses the parent's hash table when one exists (async.c:3128). Crucially, AddEventToPendingNotifies() builds that hash table as soon as the list it is appending to reaches MIN_HASHABLE_NOTIFIES (async.c:3169), and it populates it from all events already in the list. So during the merge:

• parent already has ≥ 16 pending events → hash table exists → each child probe is O(1) → the merge is linear in the number of child events;
• parent has < 16 → probes scan a list of fewer than 16 entries, and the hash table is built the moment the growing parent list crosses the threshold → bounded by a small constant either way.

There is no input that makes this quadratic in any interesting sense. The same applies to the unique-channel-name list that PreCommit_Notify() builds on master — its O(N^2) fallback (async.c:1253) is only reached when uniqueChannelHash is NULL, and that hash is created in the same block at the same 16-event threshold, so the fallback is bounded by ~16 × 16 comparisons.

Conclusion: duplicate elimination in NOTIFY is effectively linear and there is nothing here worth documenting. Tom Lane's PG13 work closed this; the 2025 proposal was describing pre-PG13 behaviour. The correct edit is to delete this hunk from the patch above:

-   <para>
-    Duplicate notifications are eliminated within a transaction, as described
-    above.  This is inexpensive in the ordinary case, but when a subtransaction
-    that has sent notifications commits, its notifications are checked
-    individually against those already pending in the parent transaction, at a
-    cost proportional to the product of the two lists' lengths.  Sending large
-    numbers of notifications from many subtransactions, for example from
-    <application>PL/pgSQL</application> blocks containing an
-    <literal>EXCEPTION</literal> clause, can therefore become expensive.
-   </para>
-

Everything else in the patch stands — the commit-lock paragraphs, the group-commit/synchronous_commit point, the per-transaction-not-per-notification framing, the diagnosis section and the alternatives are all consistent with the code as verified in §1 of this issue. Removing this paragraph also makes the patch strictly stronger: it then contains only claims about the lock, which is the one thing that is genuinely undocumented and genuinely expensive.

Worth carrying into the -hackers posting: the July 2025 proposal's O(N²) point was the weakest part of it, and it is better to concede it up front than to have it found in review.

---

Generated by Claude Code

[NikolayS]

Final docs patch (v3) — supersedes both comments above

Duplicate-elimination paragraph removed per the correction. Every remaining claim is about the commit lock, verified against the code in §1, and true on v13 → master so it backpatches unchanged.

Verified in this tree: no tabs, no non-breaking spaces; well-formed under xmllint --noout with the file's entities declared; all seven linkend targets resolve to real ids — guc-synchronous-commit, guc-synchronous-standby-names, guc-log-lock-waits, guc-deadlock-timeout (config.sgml), sql-select (ref/select.sgml), logicaldecoding (logicaldecoding.sgml), monitoring-pg-stat-activity-view (monitoring.sgml). Full DTD validation (make -C doc/src/sgml check) still could not run — the DocBook 4.5 DTD is not installed here and network fetch is blocked — so that should be re-run before posting to -hackers.

diff --git a/doc/src/sgml/ref/notify.sgml b/doc/src/sgml/ref/notify.sgml
index fd6ed54e..58d602a7 100644
--- a/doc/src/sgml/ref/notify.sgml
+++ b/doc/src/sgml/ref/notify.sgml
@@ -175,6 +175,70 @@ NOTIFY <replaceable class="parameter">channel</replaceable> [ , <replaceable cla
    prepared for two-phase commit.
   </para>
 
+  <refsect2>
+   <title>Performance Considerations</title>
+
+   <para>
+    So that notifications are queued in commit order, a transaction that has
+    executed <command>NOTIFY</command> takes a heavyweight lock during commit
+    that serializes it against every other committing transaction that has
+    executed <command>NOTIFY</command>.  This lock is cluster-wide, not
+    per-database: notifying transactions running in different databases of the
+    same cluster contend with one another.  Transactions that have not executed
+    <command>NOTIFY</command> do not take this lock, and
+    <command>LISTEN</command> and <command>UNLISTEN</command> do not take it
+    either.
+   </para>
+
+   <para>
+    The lock is held until the notifying transaction's commit is complete,
+    which includes flushing the transaction's <acronym>WAL</acronym> records to
+    disk and, if synchronous replication is in use, waiting for the standby
+    server(s) to respond.  Notifying transactions therefore cannot be
+    group-committed with one another: each one pays for a commit round trip on
+    its own, and the aggregate rate of notifying transactions is limited to
+    roughly one such round trip at a time, no matter how many client sessions
+    are active.  The effect is correspondingly larger when commits are
+    expensive, that is with <xref linkend="guc-synchronous-commit"/> set to
+    <literal>on</literal>, with <xref linkend="guc-synchronous-standby-names"/>
+    configured, or on storage with high write latency.
+   </para>
+
+   <para>
+    The cost is incurred once per notifying transaction, not once per
+    notification, since a transaction that sends many notifications takes the
+    lock only once.  Applications that need a high notification rate should
+    therefore accumulate notifications and send them from as few transactions
+    as possible.  In particular, avoid executing <command>NOTIFY</command> in a
+    row-level trigger; collect the notifications in a statement-level trigger
+    instead, or send a single notification per transaction and let listeners
+    determine what changed.
+   </para>
+
+   <para>
+    Contention on the commit lock can be diagnosed by setting
+    <xref linkend="guc-log-lock-waits"/>, which reports waits longer than
+    <xref linkend="guc-deadlock-timeout"/> in the server log as waits for
+    <literal>AccessExclusiveLock on object 0 of class 1262 of database 0</literal>,
+    or by looking for sessions with <structfield>wait_event_type</structfield>
+    <literal>Lock</literal> and <structfield>wait_event</structfield>
+    <literal>object</literal> in
+    <link linkend="monitoring-pg-stat-activity-view"><structname>pg_stat_activity</structname></link>.
+    A characteristic symptom is that transaction throughput falls while
+    <acronym>CPU</acronym> and disk utilization fall as well, because the
+    waiting sessions are idle rather than busy.
+   </para>
+
+   <para>
+    If the required notification rate cannot be reduced by batching, consider
+    another mechanism: a queue table that consumers poll using
+    <literal>SELECT ... FOR UPDATE SKIP LOCKED</literal> (see
+    <xref linkend="sql-select"/>) does not serialize commits, and
+    <xref linkend="logicaldecoding"/> is a better fit when the goal is to
+    stream data changes to an external consumer.
+   </para>
+  </refsect2>
+
   <refsect2>
    <title>pg_notify</title>
 

listen.sgml unchanged — it already cross-references NOTIFY for the extended discussion. Working tree restored; nothing committed or pushed.

---

Generated by Claude Code

[NikolayS]

Added §4.7 — PgDog, Scaling Postgres LISTEN/NOTIFY (Lev Kokotov, 4 Aug 2025).

Single most important takeaway: the post is about a third constraint entirely — max_connections — and never mentions either of our two bottlenecks, so its fix (proxy-side fan-out that registers one Postgres listener per PgDog instance) only shrinks the listener count that 282b1cde has already largely neutralized in core, while every client NOTIFY is still forwarded as its own NOTIFY and the writer-side commit lock, with its ~1/fsync ceiling from §3.1, is untouched.

---

Generated by Claude Code

[NikolayS]

Plan v2, after four expert reviews — including retractions of my own numbers

Four independent reviews (PostgreSQL community process, WAL/xact internals, benchmark methodology, adversarial red team). Two of them broke things I had asserted confidently in this issue. Corrections first, because §3 above contains a wrong number.

---

1. RETRACTIONS to §3.3

1.1 The "interested listener" numbers are an artifact — retracted

§3.3 claims 500 interested listeners cost the notifier only ~15%, and that v19 is 59x better than v18 there. Both are wrong, and the bias points directly at the conclusion I was arguing.

The harness starts listeners as psql -f <fifo>. A psql blocked reading a fifo never calls PQconsumeInput, so it never collects notifications. The backend writes into the socket until the send buffer fills, then parks in ClientWrite and stops draining. On master a jammed listener latches wakeupPending = true and is then skipped forever by both loops in SignalBackends(); on v18 there is no wakeupPending, so it is signalled on every notifying commit. The jam therefore flatters v19 and punishes v18.

Verified directly — 50 interested listeners under sustained notify load, sampled three times:

Client|ClientWrite|idle|50
Client|ClientWrite|idle|50
Client|ClientWrite|idle|50

50 of 50, wedged for the entire run. Any listener experiment needs a real libpq client looping on PQconsumeInput/PQnotifies, plus the assertion that would have caught this in 30 seconds: notifications received ≈ sent × interested listeners, reported per run.

1.2 What still stands, and how it must be worded

• Uninterested listeners (24x) — survives. Those listeners receive nothing, write nothing, and never jam. But it was measured at 125 listeners per core; v18's cost there is ~193,000 signals/second on 4 cores. The direction is a code fact; the magnitude will shrink a lot at realistic ratios.
• "Flat at ~1,000 TPS" — reword. The data is 731 → 1,020 → 1,147 → 1,042 → 1,034 → 932 → 951. It peaks at 4 clients and then declines 17%. Say "non-scaling and mildly negative-scaling beyond 4 clients."
• "master and v18 identical" — reword to "no difference detectable at our resolution." It is a null result from single samples.
• synchronous_commit=off ~3x — do not publish. Cross-comparing the control arm across builds (identical code path) shows up to 31% disagreement, and master.csv has NOTIFY beating the control at 8 clients. The noise floor there is ~50%.
• "Sync rep makes it worse" — unmeasured. Code reading only (SyncRepWaitForLSN, xact.c:1599) until we have a standby.
• 17x — should never have been the headline. It is a property of this VM: the ratio is the control's group-commit batch factor, which grows with cores and fsync latency.

---

2. New result: the lock, isolated

Same binary throughout. An instrumented build skips only LockSharedObject() in PreCommit_Notify() when PG_NOTIFY_NOLOCK is set in the postmaster environment, so the arms differ by that one call and nothing else — not even compilation. 3 reps, 10 s, synchronous_commit=on, zero listeners, median [min–max]:

clients	control	NOTIFY (lock)	NOTIFY (no lock)	gap recovered
1	824 [794–835]	790 [618–844]	733 [695–778]	— (no gap at c=1)
4	2,059 [1946–2075]	1,033 [934–1051]	2,177 [2115–2300]	111%
16	7,811 [7430–8006]	1,027 [911–1074]	7,893 [7606–8290]	101%
64	16,307 [16044–16469]	1,055 [979–1135]	12,369 [11077–12973]	74%

Lock-removal speedup: 2.1x at 4 clients, 7.7x at 16, 11.7x at 64. Arm separation verified by wait events: 72 Lock/object samples in the lock arm, 0 in the no-lock arm.

This answers the question Tom asked on 2025-07-18:

How much does that really help, considering that we'll still serialize on the NotifyQueueLock?

NotifyQueueLock is real but is not the limiter. It is a short LWLock around the SLRU insert and never spans the fsync. At ≤16 clients removing the heavyweight lock closes essentially the whole gap; at 64 clients the residual — NotifyQueueLock, SLRU work, and one extra client round trip — is ~26% of the gap. (The no-lock arm is semantically broken and is a measurement device, never a proposal.)

The mechanism, stated hardware-independently

From the wait-event data already in §3.1: both arms show exactly one backend in IO/WalSync at any instant — one fsync in flight. The difference is who is queued behind it. Control shows 373 LWLock/WALWrite against 18 WalSync ≈ 21 backends per in-flight fsync. NOTIFY shows zero in WALWrite, because all the others are stuck one step earlier on the heavyweight lock.

Group-commit batch factor ~21 versus exactly 1. That is the real finding. The 17x is a derived consequence of it on this particular disk, not the headline.

---

3. Phase 2 is withdrawn — it was wrong

§5 proposed releasing the lock before the WAL flush on the grounds that "queue order still equals commit-LSN order." That claim is true and irrelevant: nothing in async.c consumes commit-LSN order. The listener's test is XidInMVCCSnapshot(qe->xid, snapshot) (async.c:2771) — a ProcArray snapshot test. The invariant is queue-insertion order == snapshot-visibility order, which is exactly why the lock is released at xact.c:2480, after ProcArrayEndTransaction() at :2431.

There is no earlier release point that preserves it. xact.c:1595: "at this stage we have marked clog, but still show as running in the procarray and continue to hold locks."

So Phase 2 was not a way around Tom's objection — it was Tom's objection, minus the opt-in that at least made Rishu's version defensible.

Concrete failure — a stranded notification. L listens on bchan; A notifies achan (synchronous_commit=on); B notifies bchan (async):

1. A inserts at queue pos P, XLogInserts, releases the lock, blocks in flush/sync-rep — still in the ProcArray.
2. B inserts at Q, commits, becomes visible, signals L.
3. L wakes, takes a snapshot still containing A, hits A's entry, XidInMVCCSnapshot fires, backs up to P, delivers nothing.
4. A finishes and calls SignalBackends(). The v19 direct-advance path (async.c:2363-2367) sees L at exactly queueHeadBeforeWrite, uninterested in achan → sets QUEUE_BACKEND_POS(L) = Q and does not signal.
5. B's notification is now delivered only by some unrelated future event, or by the far-behind path after QUEUE_CLEANUP_DELAY (4) pages. On a quiet channel, effectively never.

Three further independent blockers: the head-of-line window is unbounded, not one commit (a stalled synchronous standby pins the head of a cluster-wide queue for the duration); the pinned tail lets writers race into a queue that cannot truncate until max_notify_queue_pages turns today's benign wait into "too many notifications in the NOTIFY queue" errors; and there is no crit-section-safe heavyweight release — START_CRIT_SECTION() is at xact.c:1470 and END_CRIT_SECTION() at :1583, after the flush — while releasing at END_CRIT_SECTION forfeits the entire benefit.

A finding worth reporting upstream

The safety of 282b1cde's direct-advance optimization rests on an undocumented dependency: that the notify lock spans ProcArrayEndTransaction(). It is unreachable on master, so this is not a bug report — but it is not written down anywhere, and the in-tree note at async.c:1313-1323 suggesting NotifyQueueLock EXCLUSIVE as a future substitute is insufficient, because it preserves range atomicity but not the direct-advance precondition. A comment near async.c:2363 recording this is a small, purely additive contribution.

---

4. Also stale or wrong in §4.5 / §5

• The CLOG-truncation wart is already fixed on master. AsyncNotifyFreezeXids() (async.c:2952, async.h:47) is called from vacuum.c:1961 before advancing datfrozenxid. Citing Tom's 2025 remark as an open problem would advertise not having read master.
• "Not committed, nothing has moved since" was wrong. Rishu Bagga opened a new thread on 2025-10-18, "[PATCH] Write Notifications Through WAL", reported isolation and regression tests passing, and closed with "Please let me know what I need to do to get this into a committable state." He shipped and asked; the list went quiet on him. That inverts the abandonment story told above.
• v13 is EOL (Nov 2025). Supported branches are 14–18.
• The docs patch's diagnosis advice does not work at default settings. proc.c:1631 gates logging behind deadlock_state != DS_NOT_YET_CHECKED, which is only set after deadlock_timeout fires — default 1 s, while these waits are 1–70 ms. log_lock_waits will log nothing. The paragraph must either tell the reader to lower deadlock_timeout temporarily, or lead with pg_stat_activity. A patch v4 is needed before this goes anywhere.

---

5. What the plan becomes

Do first, in parallel:

1. Review Rishu's v4 and answer the question he asked nine months ago. Roughly 10% of the effort of the old plan and worth more standing than Phases 0–2 combined. He is the natural first author of anything that follows.
2. Docs patch v4, rewritten to describe observable behaviour and remedies rather than mechanism (mechanism text gets struck on the grounds it will be wrong the day someone fixes it), with the deadlock_timeout correction. Own thread, own CF entry.
3. Fix the harness, then publish it as an instrument rather than as an argument — real draining listener client, ≥5 runs per cell with medians and CIs, ≥60 s runs after warmup, latency percentiles, a machine that is not 4 vCPU, pgbench and listeners on separate hosts, pg_test_fsync published, and a sync-rep configuration before any sync-rep claim is made. Send it to Joel and Rishu privately before it goes to the list.

Design work, as a question rather than a patch: enumerate the possible release points against what each breaks, including Alt B — insert into the queue post-commit under a short lock, so every inserted xid is already out of the ProcArray, XidInMVCCSnapshot can never fire, and no uncommitted entry ever exists. It eliminates head-of-line rather than trading it. Its price is real and must lead the pitch: past the commit point you can no longer ereport(ERROR) on a full queue, so a committed transaction could silently drop its notification. Alt C — release just the notify lock right after xact.c:2431 instead of :2480 — is ~10 lines, preserves every invariant, and is a genuine win for DROP TABLE; NOTIFY because smgrDoPendingDeletes() at :2495 can take "many seconds."

Dropped: the SignalBackends warm-up patch as a relationship device (it entangles queue-truncation policy, and the framing is indefensible if it ever leaks); Phase 3 as a fallback unless a named person commits a year; any blog or podcast coverage before the thread concludes.

Re-testing Joel's 14%: reproduce his workload on v18 first. If we cannot reproduce his number, we have no standing to discuss it. The framing is not diplomacy, it is the actual causal story — his measurement was correct for the code as it stood; his own fix removed the herd and changed the denominator.

Stop rules, which the old plan lacked entirely: if the realistic LISTEN+NOTIFY gap is under 2x, the performance thesis is dead — ship docs and stop. If no committer posts a substantive design opinion within 6 weeks and two follow-ups, stop writing code. Two consecutive commitfests without a status change, stop. And stopping means saying so on the list with the harness and analysis handed over — not going quiet, which is precisely what happened to Rishu.

---

Generated by Claude Code

[NikolayS]

Prototype results: the serialization can be removed, and it's a small change

Three prototypes, two of my own design ideas refuted along the way, and a result that says the throughput is recoverable without the WAL redesign. Artifacts: #85 (prototype), #84 (harness + raw data).

---

1. Headline

synchronous_commit=on, zero listeners, 3 reps of 10 s, median [min–max]:

clients	control	stock	v1 prototype	v2 prototype	no-lock reference
4	2,059	1,033	2,130	2,129 [2109–2244]	2,177
16	7,811	1,027	7,767	8,155 [7961–8176]	7,893
64	16,307	1,055	10,965	16,507 [16402–16695]	12,369

15.7x over stock at 64 clients. Commit latency 60.7 ms → 3.9 ms, identical to the NOTIFY-free control. Sampling found zero backends waiting on either Lock/object or the new LWLock/NotifyQueueInsert.

The change is ~150 lines across async.c, xact.c and the LWLock registration — roughly 1/20th of Tom Lane's WAL-transport redesign.

2. What the prototype does

Move the queue insertion out of PreCommit_Notify() into PostCommitInsert_Notify(), called from CommitTransaction() after ProcArrayEndTransaction(), serialized by a dedicated NotifyQueueInsertLock.

Two design points, both of which I got wrong first:

Placement after the ProcArray removal, not before. The listener's gate is XidInMVCCSnapshot(), so the invariant is insertion order == snapshot visibility order. Once insertion happens after the ProcArray removal, every entry is written by an already-complete transaction, and since a listener reads the queue head before taking its snapshot, it can never see an in-progress xid. The invariant becomes trivially true rather than derived from lock tenure. A waiting backend also no longer pins its xid/xmin.

An LWLock, for correctness rather than tidiness. Past the commit record, any ERROR reaches RecordTransactionAbort()'s "cannot abort transaction %u, it was already committed" PANIC (xact.c:1834). LockSharedObject() can throw — lock-table exhaustion (routine and user-reachable), OOM, deadlock detection (which bypasses interrupt holdoff), and AcceptInvalidationMessages() running catalog callbacks. LWLockAcquire() can do none of those and is released by LWLockReleaseAll() during error recovery. The v1 placement was also inside HOLD_INTERRUPTS(), so waiters were uncancellable and unkillable — pg_terminate_backend a no-op, while pinning xid/xmin and every heavyweight lock held. That regression is gone in v2.

The v1→v2 delta is worth noting on its own: 10,965 → 16,507 at 64 clients. Placement and lock choice are half the win.

3. Two of my own ideas, refuted

The group-leader idea is unnecessary. I proposed amortizing the lock via a leader/follower scheme modelled on ProcArrayGroupClearXid — DBOS's batching trick, done in the server. Review verdict: those mechanisms exist to amortize LWLock handoff when the protected work is already sub-microsecond. Our serialized region was a full commit round trip. Once relocated, the protected work is 1–5 µs and a plain LWLock suffices. The measurement agrees — v2 reaches control-level throughput with an uncontended lock, so there is no headroom left for a group scheme to recover. Prior-art search says the idea appears unproposed on -hackers; it is also, on this evidence, not worth proposing.

Positional reservation is a dead end. I suggested reserving queue positions pre-commit. That re-fixes queue order at reservation time, so a slow reserver forces everyone behind it to wait — reintroducing the serialization. Capacity reservation (reserve pages, assign positions at publish) is the workable form. Relatedly: fixed-size entries are not required for this; that constraint came from WAL-record transport, not from reservation.

4. Corrections to earlier comments in this issue

• Rishu Bagga's WAL patch is at v9 (2025-11-25), not v1–v4, with commitfest entries 6079 and 6205, both left in Needs review. It reports ~60–70k vs ~30k TPS. It also carries an unexplained ~2.5x regression on NOTIFY-only workloads reported by Arseniy Mukhin and never diagnosed — which is a well-scoped question this harness could actually answer, and a far better first contribution than a competing patch.
• This lock was named in a thread title in 2019 — Martijn van Oosterhout's "Improve performance of NOTIFY over many databases (issue blocking on AccessExclusiveLock on object 0 of class 1262 of database 0)". That work was committed for PG13 but fixed asyncQueueAdvanceTail and cross-database signalling, leaving the commit-time lock intact.
• The frozenxid/CLOG bug was reported by Alexandra Wang, 2025-08-06, and is fixed on master by AsyncNotifyFreezeXids().

5. What is still missing

The prototype is not a patch, and the gaps are not cosmetic:

• asyncQueueAddEntries() can still reach SlruReportIOError() (ENOSPC on pg_notify/), which PANICs post-commit. Needs pre-faulting, or a decision to degrade to WARNING and drop — defensible under async.c's existing "notifications do not survive crashes" contract, but a semantics call only a committer can make. This is the gating item.
• asyncQueueIsFull() still errors post-commit; needs capacity reservation.
• No test would catch an ordering regression. The async test and all 130 isolation tests pass on v2, but they do not appear to exercise two concurrent notifiers racing a listener's snapshot. Passing tests are not evidence here — the ordering argument stands on the reasoning in §2, not on the suite. An injection-point test is required.
• Zero listeners, one 4-vCPU box, ~10% control variance at 64 clients, no synchronous standby. Shape, not precision.

6. What this changes about the plan

The earlier conclusion — "no small semantics-preserving throughput patch exists" — was wrong, and this supersedes it. One appears to exist, and it is far smaller than the WAL redesign. But the constraint identified earlier still binds: the gating item is a semantics decision, and the person best placed to benefit is Rishu, whose patch has been waiting on review since November. The strongest next move is still to review v9 and answer his 2.5x regression, now with a harness and a second data point on where the time actually goes.

---

Generated by Claude Code