[PATCH v1 0/7] Wait event timing and tracing instrumentation

Started by Dmitry Fomin5 months ago22 messageshackers
Jump to latest
#1Dmitry Fomin
fomin.list@gmail.com

Hi hackers,

This series adds opt-in instrumentation that captures the duration of
every wait event reported through pgstat_report_wait_start()/_end(),
behind a configure-time flag (--enable-wait-event-timing) and a runtime
GUC (wait_event_capture = off | stats | trace, default off).

PostgreSQL exposes a rich taxonomy of wait events in pg_stat_activity,
but only as instantaneous snapshots: there is no in-core way to ask
"how long do my backends actually spend in each wait?", or "what was
the wait sequence of this session's last N events?". External tools
either sample at coarse resolution (pg_wait_sampling, default 10 ms) or
pay ~200-300 ns per transition via hardware watchpoints.

This is a reworked submission of an earlier 8k-line single patch,
following Andrey Borodin's advice to split it into independently
committable pieces with the DSA machinery deferred. The series has
three groups; each patch builds and passes check-world on its own:

Group 1 -- stats level (patches 1-4, committable on their own):

0001 adds the configure flag and the wait_event_capture GUC; pure
scaffolding, no behavior.
0002 records per-(backend, event) statistics -- count, total/max
duration, and a 32-bucket log2 histogram -- in eagerly-allocated
shared memory, with a single load+branch gate in the inline
wait_start/wait_end fast path and the recording bodies
out-of-line. Exposed via pg_stat_wait_event_timing. No DSA.
0003 exposes the overflow counters and adds reset functions (own
backend synchronous; cross-backend via a lock-free
request/response on an atomic generation counter).
0004 enables the flag on one CI task, so both the instrumented and
the stub build paths are exercised on every run.

Group 2 -- storage refactor:

0005 converts the per-backend array from eager shared memory to a
lazily-created DSA, so a build with the feature compiled in but
never enabled pays no per-backend memory. Pure refactor: the
SQL surface is unchanged and 0002/0003's tests pass as-is. This
is the riskiest patch -- it adds the lazy-attach guards
(critical sections, LWLock wait queues, re-entrancy) and the
proc_exit teardown gate -- which is exactly why it is isolated.

Group 3 -- trace level:

0006 adds wait_event_capture = trace: a per-session DSA ring buffer
of individual waits, readable from the owning session and
cross-backend (including post-mortem reads of rings whose
backend has exited). Cross-backend reads are protected by a
position-encoded identity seqlock; a TAP test drives an
injection point inside the writer to prove it rejects
stale-cycle reads that a parity-only seqlock would accept.
0007 interleaves query-attribution markers (executor and protocol
boundaries) into the ring, so a reader can tell which query
each wait belongs to.

Off-mode overhead: pgbench -S and TPC-B on a dedicated 16-vCPU box
show the compiled-in-but-off configuration within run-to-run noise of
an unpatched build (the inline gate is one global load and a predicted
branch; an earlier unlikely() annotation was dropped after measurement
showed byte-identical codegen without it). stats mode costs <= ~0.5%
vs off across read-only, TPC-B, and a wait-saturated
32MB-shared_buffers workload. I will post the full numbers and
methodology in a follow-up message.

Open questions:

* Whether the configure flag should eventually be dropped in favor of
always-compiled (the off-mode cost is one predicted branch).
* Defaults worth litigating: 32 histogram buckets,
wait_event_timing_max_tranches = 192, and
wait_event_trace_ring_size = 4MB.
* The trace level's orphan-ring retention tradeoff (post-mortem reads
vs bounded memory), documented on WaitEventTraceControl.

The patches deliberately contain no catversion bump; one is needed at
commit time for 0002, 0003 and 0006.

Thanks to Andrey Borodin for the first review of the earlier
single-patch version -- his advice to break it into independently
committable pieces shaped this series -- and to Nikolay Samokhvalov and
Kirk Wolak for discussion and independent testing of the overhead and
the orphaned-ring lifecycle.

Regards,
Dmitry Fomin

Dmitry Fomin (7):
wait_event_timing: add --enable-wait-event-timing flag and
wait_event_capture GUC
wait_event_timing: record per-backend wait event statistics (stats level)
wait_event_timing: expose overflow counters and add reset functions
ci: build one task with --enable-wait-event-timing
wait_event_timing: allocate the per-backend array lazily in DSA
wait_event_timing: add trace level with a per-session ring buffer
wait_event_timing: add query-attribution markers to the trace ring

.github/workflows/pg-ci.yml | 7 +
configure | 32 +
configure.ac | 8 +
doc/src/sgml/config.sgml | 106 +
doc/src/sgml/monitoring.sgml | 783 +++++
meson.build | 1 +
meson_options.txt | 3 +
src/backend/catalog/system_views.sql | 103 +
src/backend/executor/execMain.c | 5 +
src/backend/postmaster/auxprocess.c | 11 +
src/backend/storage/lmgr/proc.c | 5 +
src/backend/tcop/postgres.c | 39 +
src/backend/utils/.gitignore | 1 +
src/backend/utils/Makefile | 9 +-
src/backend/utils/activity/Makefile | 4 +-
src/backend/utils/activity/backend_status.c | 13 +
.../activity/generate-wait_event_types.pl | 179 +
src/backend/utils/activity/meson.build | 1 +
src/backend/utils/activity/wait_event.c | 3 +-
.../utils/activity/wait_event_names.txt | 2 +
.../utils/activity/wait_event_timing.c | 3111 +++++++++++++++++
src/backend/utils/init/postinit.c | 11 +
src/backend/utils/misc/guc_parameters.dat | 29 +
src/backend/utils/misc/guc_tables.c | 1 +
src/backend/utils/misc/postgresql.conf.sample | 5 +
src/include/catalog/pg_proc.dat | 59 +
src/include/pg_config.h.in | 3 +
src/include/storage/lwlocklist.h | 2 +
src/include/storage/subsystemlist.h | 2 +
src/include/utils/.gitignore | 1 +
src/include/utils/guc.h | 1 +
src/include/utils/guc_hooks.h | 3 +
src/include/utils/meson.build | 4 +-
src/include/utils/wait_classes.h | 9 +
src/include/utils/wait_event.h | 49 +
src/include/utils/wait_event_timing.h | 374 ++
src/test/modules/test_misc/meson.build | 1 +
.../t/015_wait_event_trace_seqlock.pl | 122 +
src/test/regress/expected/rules.out | 30 +
.../regress/expected/wait_event_timing.out | 188 +
.../regress/expected/wait_event_timing_1.out | 181 +
src/test/regress/parallel_schedule | 4 +
src/test/regress/sql/wait_event_timing.sql | 113 +
src/tools/pgindent/typedefs.list | 12 +
44 files changed, 5621 insertions(+), 9 deletions(-)
create mode 100644 src/backend/utils/activity/wait_event_timing.c
create mode 100644 src/include/utils/wait_event_timing.h
create mode 100644 src/test/modules/test_misc/t/015_wait_event_trace_seqlock.pl
create mode 100644 src/test/regress/expected/wait_event_timing.out
create mode 100644 src/test/regress/expected/wait_event_timing_1.out
create mode 100644 src/test/regress/sql/wait_event_timing.sql

Attachments:

v1-0003-wait_event_timing-expose-overflow-counters-and-ad.patchapplication/octet-stream; name=v1-0003-wait_event_timing-expose-overflow-counters-and-ad.patchDownload+611-8
v1-0001-wait_event_timing-add-enable-wait-event-timing-fl.patchapplication/octet-stream; name=v1-0001-wait_event_timing-add-enable-wait-event-timing-fl.patchDownload+228-2
v1-0002-wait_event_timing-record-per-backend-wait-event-s.patchapplication/octet-stream; name=v1-0002-wait_event_timing-record-per-backend-wait-event-s.patchDownload+1758-56
v1-0004-ci-build-one-task-with-enable-wait-event-timing.patchapplication/octet-stream; name=v1-0004-ci-build-one-task-with-enable-wait-event-timing.patchDownload+7-1
v1-0005-wait_event_timing-allocate-the-per-backend-array-.patchapplication/octet-stream; name=v1-0005-wait_event_timing-allocate-the-per-backend-array-.patchDownload+366-88
v1-0007-wait_event_timing-add-query-attribution-markers-t.patchapplication/octet-stream; name=v1-0007-wait_event_timing-add-query-attribution-markers-t.patchDownload+166-11
v1-0006-wait_event_timing-add-trace-level-with-a-per-sess.patchapplication/octet-stream; name=v1-0006-wait_event_timing-add-trace-level-with-a-per-sess.patchDownload+2743-97
#2Dmitry Fomin
fomin.list@gmail.com
In reply to: Dmitry Fomin (#1)
Re: [PATCH v1 0/7] Wait event timing and tracing instrumentation

Hi hackers,

As promised in the cover letter, here are the overhead numbers and the
methodology behind them.

Hardware and setup
------------------

Dedicated Hetzner CCX43 (16 dedicated AMD EPYC Milan vCPUs, 64 GB RAM),
Linux, gcc 13, -O2 (meson debugoptimized), no other load. Four builds:

BASELINE unpatched master
WET/off patched, --enable-wait-event-timing, wait_event_capture=off
WET/stats same build, wait_event_capture=stats
WET/trace same build, wait_event_capture=trace (4 MB rings)

pgbench scale 100, shared_buffers = 4 GB unless noted, 16 clients /
8 threads / 30 s, 5 runs per cell, means reported. Run-to-run spread
on this machine is roughly +/- 1-2 %, so single-digit deltas below
should be read against that floor.

The numbers were taken on the pre-split development tip of this work.
The v1 series is a reorganization of that same code into reviewable
pieces; the recording hot paths are unchanged. The off- and stats-mode
results were additionally re-validated on the series itself (same
machine, same protocol) with matching results.

1. pgbench -S (read-only)
-------------------------

mean TPS vs BASELINE
BASELINE 164,859 --
WET/off 166,840 +1.20 %
WET/stats 164,126 -0.44 %
WET/trace 165,687 +0.50 %

All three modes are within run-to-run noise of vanilla. (Yes, "off"
came out faster than baseline here -- see the layout note below for
why we do not read anything into that.)

2. pgbench TPC-B
----------------

mean TPS vs BASELINE
BASELINE 17,569 --
WET/off 17,487 -0.46 %
WET/stats 17,240 -1.87 %
WET/trace 17,314 -1.45 %

3. Wait-saturated worst case (eviction stress)
----------------------------------------------

To probe the per-transition cost directly, shared_buffers = 32 MB
against a ~1.5 GB working set forces ~98 % buffer misses; every miss
produces a BufferIO/DataFileRead wait. This drives the instrumented
path at the highest rate we could construct:

scenario mode TPS vs off
W1 -S, 16 clients off 116,487 --
stats 116,625 +0.1 %
trace 114,844 -1.4 %
W2 TPC-B, 16 clients off 16,813 --
stats 16,766 -0.3 %
trace 16,698 -0.7 %
W3 -S, 32 clients (2x oversub) off 162,845 --
stats 158,880 -2.4 %
trace 159,141 -2.3 %
W4 hot-row UPDATE, 32 clients off 8,138 --
stats 7,772 -4.5 %
trace 7,814 -4.0 %

W4 is the deliberate pathological case: every transaction contends on
the same row, so each transaction goes through many lock-wait
transitions. The ~4.5 % there is the honest ceiling we could produce
for the recording cost; it is bounded and proportional to the
wait-transition rate, which is exactly the workload where you would
want this data. Typical stats-mode cost elsewhere is <= ~0.5 % vs off.

A sample of what trace mode captured under W1, as a sanity check that
the instrumented path was actually exercised:

IO / DataFileRead (dominant), IO / WalWrite, IO / AioIoCompletion,
Activity / BgwriterMain, Activity / WalWriterMain,
LWLock / WaitEventTraceDSA

4. A methodological caution: binary layout
------------------------------------------

While validating the off-mode gate we hit an apparent -3 % TPC-B
regression that survived re-runs within one session -- and then
reversed sign in a fresh session with the identical binary (-3.00 %,
then +0.18 %). A control build with the gate #ifdef'd out entirely
(same patch, no live code in the hot path) measured -2.39 %, while
re-adding the gate measured +2.64 % -- a slowdown from removing an
instruction and a speedup from adding one, which is impossible as an
instruction cost. This is the well-known code-layout effect
(Mytkowicz et al., "Producing Wrong Data Without Doing Anything
Obviously Wrong", ASPLOS 2009): TPC-B on this machine is sensitive to
where unrelated code lands in the binary at the +/- 2-3 % level.

Consequently: (a) we report off-mode as "within noise" rather than
quoting a signed per-mille number, and (b) the earlier unlikely()
annotation on the gate was dropped after objdump showed gcc 13 -O2
emits byte-identical code with and without it (the measured "effect"
of the annotation was pure layout). The gate itself is one global
load and a predicted branch; arithmetic puts it around 10^-5 of a
TPC-B transaction, far below anything measurable here.

5. Stability under the same workloads
-------------------------------------

Not performance, but run on the same box and worth one paragraph: the
full check-world passes on the instrumented and stub builds; an
ASAN-instrumented build ran the pgbench matrix, a connect storm, a
SELECT FOR UPDATE pile-up and a pg_terminate_backend storm with no
findings; stress runs covered immediate-shutdown recovery, rapid mode
flips under load, 150 concurrent cross-backend trace readers against a
live writer, 8 KB and 32 MB rings, an orphan-sweep of 50 short-lived
backends, and a primary + hot-standby pair with trace enabled on both.
No crashes or sanitizer reports in any of it.

Happy to re-run any of this with different parameters, or publish the
driver scripts if that is useful.

Regards,
Dmitry Fomin

Show quoted text

On Fri, Jul 3, 2026 at 10:22 PM Dmitry Fomin <fomin.list@gmail.com> wrote:

Hi hackers,

This series adds opt-in instrumentation that captures the duration of
every wait event reported through pgstat_report_wait_start()/_end(),
behind a configure-time flag (--enable-wait-event-timing) and a runtime
GUC (wait_event_capture = off | stats | trace, default off).

PostgreSQL exposes a rich taxonomy of wait events in pg_stat_activity,
but only as instantaneous snapshots: there is no in-core way to ask
"how long do my backends actually spend in each wait?", or "what was
the wait sequence of this session's last N events?". External tools
either sample at coarse resolution (pg_wait_sampling, default 10 ms) or
pay ~200-300 ns per transition via hardware watchpoints.

This is a reworked submission of an earlier 8k-line single patch,
following Andrey Borodin's advice to split it into independently
committable pieces with the DSA machinery deferred. The series has
three groups; each patch builds and passes check-world on its own:

Group 1 -- stats level (patches 1-4, committable on their own):

0001 adds the configure flag and the wait_event_capture GUC; pure
scaffolding, no behavior.
0002 records per-(backend, event) statistics -- count, total/max
duration, and a 32-bucket log2 histogram -- in eagerly-allocated
shared memory, with a single load+branch gate in the inline
wait_start/wait_end fast path and the recording bodies
out-of-line. Exposed via pg_stat_wait_event_timing. No DSA.
0003 exposes the overflow counters and adds reset functions (own
backend synchronous; cross-backend via a lock-free
request/response on an atomic generation counter).
0004 enables the flag on one CI task, so both the instrumented and
the stub build paths are exercised on every run.

Group 2 -- storage refactor:

0005 converts the per-backend array from eager shared memory to a
lazily-created DSA, so a build with the feature compiled in but
never enabled pays no per-backend memory. Pure refactor: the
SQL surface is unchanged and 0002/0003's tests pass as-is. This
is the riskiest patch -- it adds the lazy-attach guards
(critical sections, LWLock wait queues, re-entrancy) and the
proc_exit teardown gate -- which is exactly why it is isolated.

Group 3 -- trace level:

0006 adds wait_event_capture = trace: a per-session DSA ring buffer
of individual waits, readable from the owning session and
cross-backend (including post-mortem reads of rings whose
backend has exited). Cross-backend reads are protected by a
position-encoded identity seqlock; a TAP test drives an
injection point inside the writer to prove it rejects
stale-cycle reads that a parity-only seqlock would accept.
0007 interleaves query-attribution markers (executor and protocol
boundaries) into the ring, so a reader can tell which query
each wait belongs to.

Off-mode overhead: pgbench -S and TPC-B on a dedicated 16-vCPU box
show the compiled-in-but-off configuration within run-to-run noise of
an unpatched build (the inline gate is one global load and a predicted
branch; an earlier unlikely() annotation was dropped after measurement
showed byte-identical codegen without it). stats mode costs <= ~0.5%
vs off across read-only, TPC-B, and a wait-saturated
32MB-shared_buffers workload. I will post the full numbers and
methodology in a follow-up message.

Open questions:

* Whether the configure flag should eventually be dropped in favor of
always-compiled (the off-mode cost is one predicted branch).
* Defaults worth litigating: 32 histogram buckets,
wait_event_timing_max_tranches = 192, and
wait_event_trace_ring_size = 4MB.
* The trace level's orphan-ring retention tradeoff (post-mortem reads
vs bounded memory), documented on WaitEventTraceControl.

The patches deliberately contain no catversion bump; one is needed at
commit time for 0002, 0003 and 0006.

Thanks to Andrey Borodin for the first review of the earlier
single-patch version -- his advice to break it into independently
committable pieces shaped this series -- and to Nikolay Samokhvalov and
Kirk Wolak for discussion and independent testing of the overhead and
the orphaned-ring lifecycle.

Regards,
Dmitry Fomin

Dmitry Fomin (7):
wait_event_timing: add --enable-wait-event-timing flag and
wait_event_capture GUC
wait_event_timing: record per-backend wait event statistics (stats level)
wait_event_timing: expose overflow counters and add reset functions
ci: build one task with --enable-wait-event-timing
wait_event_timing: allocate the per-backend array lazily in DSA
wait_event_timing: add trace level with a per-session ring buffer
wait_event_timing: add query-attribution markers to the trace ring

.github/workflows/pg-ci.yml | 7 +
configure | 32 +
configure.ac | 8 +
doc/src/sgml/config.sgml | 106 +
doc/src/sgml/monitoring.sgml | 783 +++++
meson.build | 1 +
meson_options.txt | 3 +
src/backend/catalog/system_views.sql | 103 +
src/backend/executor/execMain.c | 5 +
src/backend/postmaster/auxprocess.c | 11 +
src/backend/storage/lmgr/proc.c | 5 +
src/backend/tcop/postgres.c | 39 +
src/backend/utils/.gitignore | 1 +
src/backend/utils/Makefile | 9 +-
src/backend/utils/activity/Makefile | 4 +-
src/backend/utils/activity/backend_status.c | 13 +
.../activity/generate-wait_event_types.pl | 179 +
src/backend/utils/activity/meson.build | 1 +
src/backend/utils/activity/wait_event.c | 3 +-
.../utils/activity/wait_event_names.txt | 2 +
.../utils/activity/wait_event_timing.c | 3111 +++++++++++++++++
src/backend/utils/init/postinit.c | 11 +
src/backend/utils/misc/guc_parameters.dat | 29 +
src/backend/utils/misc/guc_tables.c | 1 +
src/backend/utils/misc/postgresql.conf.sample | 5 +
src/include/catalog/pg_proc.dat | 59 +
src/include/pg_config.h.in | 3 +
src/include/storage/lwlocklist.h | 2 +
src/include/storage/subsystemlist.h | 2 +
src/include/utils/.gitignore | 1 +
src/include/utils/guc.h | 1 +
src/include/utils/guc_hooks.h | 3 +
src/include/utils/meson.build | 4 +-
src/include/utils/wait_classes.h | 9 +
src/include/utils/wait_event.h | 49 +
src/include/utils/wait_event_timing.h | 374 ++
src/test/modules/test_misc/meson.build | 1 +
.../t/015_wait_event_trace_seqlock.pl | 122 +
src/test/regress/expected/rules.out | 30 +
.../regress/expected/wait_event_timing.out | 188 +
.../regress/expected/wait_event_timing_1.out | 181 +
src/test/regress/parallel_schedule | 4 +
src/test/regress/sql/wait_event_timing.sql | 113 +
src/tools/pgindent/typedefs.list | 12 +
44 files changed, 5621 insertions(+), 9 deletions(-)
create mode 100644 src/backend/utils/activity/wait_event_timing.c
create mode 100644 src/include/utils/wait_event_timing.h
create mode 100644 src/test/modules/test_misc/t/015_wait_event_trace_seqlock.pl
create mode 100644 src/test/regress/expected/wait_event_timing.out
create mode 100644 src/test/regress/expected/wait_event_timing_1.out
create mode 100644 src/test/regress/sql/wait_event_timing.sql

#3Dmitry Fomin
fomin.list@gmail.com
In reply to: Dmitry Fomin (#2)
Re: [PATCH v1 0/7] Wait event timing and tracing instrumentation

Hi hackers,

This series adds opt-in instrumentation that captures the duration of
every wait event reported through pgstat_report_wait_start()/_end(),
behind a configure-time flag (--enable-wait-event-timing) and a runtime
GUC (wait_event_capture = off | stats | trace, default off).

PostgreSQL exposes a rich taxonomy of wait events in pg_stat_activity,
but only as instantaneous snapshots: there is no in-core way to ask
"how long do my backends actually spend in each wait?", or "what was
the wait sequence of this session's last N events?". External tools
either sample at coarse resolution (pg_wait_sampling, default 10 ms) or
pay ~200-300 ns per transition via hardware watchpoints.

This is a reworked submission of an earlier 8k-line single patch,
following Andrey Borodin's advice to split it into independently
committable pieces with the DSA machinery deferred. The series has
three groups; each patch builds and passes check-world on its own:

Group 1 -- stats level (patches 1-4, committable on their own):

0001 adds the configure flag and the wait_event_capture GUC; pure
scaffolding, no behavior.
0002 records per-(backend, event) statistics -- count, total/max
duration, and a 32-bucket log2 histogram -- in eagerly-allocated
shared memory, with a single load+branch gate in the inline
wait_start/wait_end fast path and the recording bodies
out-of-line. Exposed via pg_stat_wait_event_timing. No DSA.
0003 exposes the overflow counters and adds reset functions (own
backend synchronous; cross-backend via a lock-free
request/response on an atomic generation counter).
0004 enables the flag on one CI task, so both the instrumented and
the stub build paths are exercised on every run.

Group 2 -- storage refactor:

0005 converts the per-backend array from eager shared memory to a
lazily-created DSA, so a build with the feature compiled in but
never enabled pays no per-backend memory. Pure refactor: the
SQL surface is unchanged and 0002/0003's tests pass as-is. This
is the riskiest patch -- it adds the lazy-attach guards
(critical sections, LWLock wait queues, re-entrancy) and the
proc_exit teardown gate -- which is exactly why it is isolated.

Group 3 -- trace level:

0006 adds wait_event_capture = trace: a per-session DSA ring buffer
of individual waits, readable from the owning session and
cross-backend (including post-mortem reads of rings whose
backend has exited). Cross-backend reads are protected by a
position-encoded identity seqlock; a TAP test drives an
injection point inside the writer to prove it rejects
stale-cycle reads that a parity-only seqlock would accept.
0007 interleaves query-attribution markers (executor and protocol
boundaries) into the ring, so a reader can tell which query
each wait belongs to.

Off-mode overhead: pgbench -S and TPC-B on a dedicated 16-vCPU box
show the compiled-in-but-off configuration within run-to-run noise of
an unpatched build (the inline gate is one global load and a predicted
branch; an earlier unlikely() annotation was dropped after measurement
showed byte-identical codegen without it). stats mode costs <= ~0.5%
vs off across read-only, TPC-B, and a wait-saturated
32MB-shared_buffers workload. I will post the full numbers and
methodology in a follow-up message.

Open questions:

* Whether the configure flag should eventually be dropped in favor of
always-compiled (the off-mode cost is one predicted branch).
* Defaults worth litigating: 32 histogram buckets,
wait_event_timing_max_tranches = 192, and
wait_event_trace_ring_size = 4MB.
* The trace level's orphan-ring retention tradeoff (post-mortem reads
vs bounded memory), documented on WaitEventTraceControl.

The patches deliberately contain no catversion bump; one is needed at
commit time for 0002, 0003 and 0006.

Changes in v2 (both found by cfbot on day one -- thanks, cfbot):

* Fixed a Windows/MSVC build failure (C2370): the ShmemCallbacks
registry symbols were redeclared with PGDLLIMPORT in
wait_event_timing.h, clashing with the plain extern declarations
that storage/subsystems.h generates for every registry entry. The
header declarations are gone; the defining file now includes
storage/subsystems.h like the other shmem subsystems.

* Fixed the CompilerWarnings task: the generated
wait_event_timing_data.h is a single-inclusion data header (its
arrays are sized by macros from wait_event_types.h), so it is now
excluded from headerscheck, next to nodetags.h and friends.

No functional changes.

Thanks to Andrey Borodin for the first review of the earlier
single-patch version -- his advice to break it into independently
committable pieces shaped this series -- and to Nikolay Samokhvalov and
Kirk Wolak for discussion and independent testing of the overhead and
the orphaned-ring lifecycle.

Regards,
Dmitry Fomin

Dmitry Fomin (7):
wait_event_timing: add --enable-wait-event-timing flag and
wait_event_capture GUC
wait_event_timing: record per-backend wait event statistics (stats
level)
wait_event_timing: expose overflow counters and add reset functions
ci: build one task with --enable-wait-event-timing
wait_event_timing: allocate the per-backend array lazily in DSA
wait_event_timing: add trace level with a per-session ring buffer
wait_event_timing: add query-attribution markers to the trace ring

.github/workflows/pg-ci.yml | 7 +
configure | 32 +
configure.ac | 8 +
doc/src/sgml/config.sgml | 106 +
doc/src/sgml/monitoring.sgml | 783 +++++
meson.build | 1 +
meson_options.txt | 3 +
src/backend/catalog/system_views.sql | 103 +
src/backend/executor/execMain.c | 5 +
src/backend/postmaster/auxprocess.c | 11 +
src/backend/storage/lmgr/proc.c | 5 +
src/backend/tcop/postgres.c | 39 +
src/backend/utils/.gitignore | 1 +
src/backend/utils/Makefile | 9 +-
src/backend/utils/activity/Makefile | 4 +-
src/backend/utils/activity/backend_status.c | 13 +
.../activity/generate-wait_event_types.pl | 179 +
src/backend/utils/activity/meson.build | 1 +
src/backend/utils/activity/wait_event.c | 3 +-
.../utils/activity/wait_event_names.txt | 2 +
.../utils/activity/wait_event_timing.c | 3112 +++++++++++++++++
src/backend/utils/init/postinit.c | 11 +
src/backend/utils/misc/guc_parameters.dat | 29 +
src/backend/utils/misc/guc_tables.c | 1 +
src/backend/utils/misc/postgresql.conf.sample | 5 +
src/include/catalog/pg_proc.dat | 59 +
src/include/pg_config.h.in | 3 +
src/include/storage/lwlocklist.h | 2 +
src/include/storage/subsystemlist.h | 2 +
src/include/utils/.gitignore | 1 +
src/include/utils/guc.h | 1 +
src/include/utils/guc_hooks.h | 3 +
src/include/utils/meson.build | 4 +-
src/include/utils/wait_classes.h | 9 +
src/include/utils/wait_event.h | 49 +
src/include/utils/wait_event_timing.h | 360 ++
src/test/modules/test_misc/meson.build | 1 +
.../t/015_wait_event_trace_seqlock.pl | 122 +
src/test/regress/expected/rules.out | 30 +
.../regress/expected/wait_event_timing.out | 188 +
.../regress/expected/wait_event_timing_1.out | 181 +
src/test/regress/parallel_schedule | 4 +
src/test/regress/sql/wait_event_timing.sql | 113 +
src/tools/pginclude/headerscheck | 2 +
src/tools/pgindent/typedefs.list | 12 +
45 files changed, 5610 insertions(+), 9 deletions(-)
create mode 100644 src/backend/utils/activity/wait_event_timing.c
create mode 100644 src/include/utils/wait_event_timing.h
create mode 100644 src/test/modules/test_misc/t/015_wait_event_trace_seqlock.pl
create mode 100644 src/test/regress/expected/wait_event_timing.out
create mode 100644 src/test/regress/expected/wait_event_timing_1.out
create mode 100644 src/test/regress/sql/wait_event_timing.sql

Attachments:

v2-0004-ci-build-one-task-with-enable-wait-event-timing.patchapplication/octet-stream; name=v2-0004-ci-build-one-task-with-enable-wait-event-timing.patchDownload+7-1
v2-0005-wait_event_timing-allocate-the-per-backend-array-.patchapplication/octet-stream; name=v2-0005-wait_event_timing-allocate-the-per-backend-array-.patchDownload+366-88
v2-0001-wait_event_timing-add-enable-wait-event-timing-fl.patchapplication/octet-stream; name=v2-0001-wait_event_timing-add-enable-wait-event-timing-fl.patchDownload+228-2
v2-0002-wait_event_timing-record-per-backend-wait-event-s.patchapplication/octet-stream; name=v2-0002-wait_event_timing-record-per-backend-wait-event-s.patchDownload+1753-56
v2-0003-wait_event_timing-expose-overflow-counters-and-ad.patchapplication/octet-stream; name=v2-0003-wait_event_timing-expose-overflow-counters-and-ad.patchDownload+611-8
v2-0006-wait_event_timing-add-trace-level-with-a-per-sess.patchapplication/octet-stream; name=v2-0006-wait_event_timing-add-trace-level-with-a-per-sess.patchDownload+2737-97
v2-0007-wait_event_timing-add-query-attribution-markers-t.patchapplication/octet-stream; name=v2-0007-wait_event_timing-add-query-attribution-markers-t.patchDownload+166-11
#4Dmitry Fomin
fomin.list@gmail.com
In reply to: Dmitry Fomin (#3)
Re: [PATCH v1 0/7] Wait event timing and tracing instrumentation

Hi hackers,

Hi hackers,

v3 is v2 rebased over current master (5f14f82280d); cfbot flagged the
set as needing a rebase after typedefs.list drifted. No code changes.

Regards,
Dmitry Fomin

Dmitry Fomin (7):
wait_event_timing: add --enable-wait-event-timing flag and
wait_event_capture GUC
wait_event_timing: record per-backend wait event statistics (stats
level)
wait_event_timing: expose overflow counters and add reset functions
ci: build one task with --enable-wait-event-timing
wait_event_timing: allocate the per-backend array lazily in DSA
wait_event_timing: add trace level with a per-session ring buffer
wait_event_timing: add query-attribution markers to the trace ring

.github/workflows/pg-ci.yml | 7 +
configure | 32 +
configure.ac | 8 +
doc/src/sgml/config.sgml | 106 +
doc/src/sgml/monitoring.sgml | 783 +++++
meson.build | 1 +
meson_options.txt | 3 +
src/backend/catalog/system_views.sql | 103 +
src/backend/executor/execMain.c | 5 +
src/backend/postmaster/auxprocess.c | 11 +
src/backend/storage/lmgr/proc.c | 5 +
src/backend/tcop/postgres.c | 39 +
src/backend/utils/.gitignore | 1 +
src/backend/utils/Makefile | 9 +-
src/backend/utils/activity/Makefile | 4 +-
src/backend/utils/activity/backend_status.c | 13 +
.../activity/generate-wait_event_types.pl | 179 +
src/backend/utils/activity/meson.build | 1 +
src/backend/utils/activity/wait_event.c | 3 +-
.../utils/activity/wait_event_names.txt | 2 +
.../utils/activity/wait_event_timing.c | 3112 +++++++++++++++++
src/backend/utils/init/postinit.c | 11 +
src/backend/utils/misc/guc_parameters.dat | 29 +
src/backend/utils/misc/guc_tables.c | 1 +
src/backend/utils/misc/postgresql.conf.sample | 5 +
src/include/catalog/pg_proc.dat | 59 +
src/include/pg_config.h.in | 3 +
src/include/storage/lwlocklist.h | 2 +
src/include/storage/subsystemlist.h | 2 +
src/include/utils/.gitignore | 1 +
src/include/utils/guc.h | 1 +
src/include/utils/guc_hooks.h | 3 +
src/include/utils/meson.build | 4 +-
src/include/utils/wait_classes.h | 9 +
src/include/utils/wait_event.h | 49 +
src/include/utils/wait_event_timing.h | 360 ++
src/test/modules/test_misc/meson.build | 1 +
.../t/015_wait_event_trace_seqlock.pl | 122 +
src/test/regress/expected/rules.out | 30 +
.../regress/expected/wait_event_timing.out | 188 +
.../regress/expected/wait_event_timing_1.out | 181 +
src/test/regress/parallel_schedule | 4 +
src/test/regress/sql/wait_event_timing.sql | 113 +
src/tools/pginclude/headerscheck | 2 +
src/tools/pgindent/typedefs.list | 12 +
45 files changed, 5610 insertions(+), 9 deletions(-)
create mode 100644 src/backend/utils/activity/wait_event_timing.c
create mode 100644 src/include/utils/wait_event_timing.h
create mode 100644 src/test/modules/test_misc/t/015_wait_event_trace_seqlock.pl
create mode 100644 src/test/regress/expected/wait_event_timing.out
create mode 100644 src/test/regress/expected/wait_event_timing_1.out
create mode 100644 src/test/regress/sql/wait_event_timing.sql

--
Dmitry

Attachments:

v3-0001-wait_event_timing-add-enable-wait-event-timing-fl.patchapplication/octet-stream; name=v3-0001-wait_event_timing-add-enable-wait-event-timing-fl.patchDownload+228-2
v3-0002-wait_event_timing-record-per-backend-wait-event-s.patchapplication/octet-stream; name=v3-0002-wait_event_timing-record-per-backend-wait-event-s.patchDownload+1753-56
v3-0005-wait_event_timing-allocate-the-per-backend-array-.patchapplication/octet-stream; name=v3-0005-wait_event_timing-allocate-the-per-backend-array-.patchDownload+366-88
v3-0004-ci-build-one-task-with-enable-wait-event-timing.patchapplication/octet-stream; name=v3-0004-ci-build-one-task-with-enable-wait-event-timing.patchDownload+7-1
v3-0003-wait_event_timing-expose-overflow-counters-and-ad.patchapplication/octet-stream; name=v3-0003-wait_event_timing-expose-overflow-counters-and-ad.patchDownload+611-8
v3-0007-wait_event_timing-add-query-attribution-markers-t.patchapplication/octet-stream; name=v3-0007-wait_event_timing-add-query-attribution-markers-t.patchDownload+166-11
v3-0006-wait_event_timing-add-trace-level-with-a-per-sess.patchapplication/octet-stream; name=v3-0006-wait_event_timing-add-trace-level-with-a-per-sess.patchDownload+2737-97
#5Guillaume Lelarge
guillaume.lelarge@dalibo.com
In reply to: Dmitry Fomin (#4)
Re: [PATCH v1 0/7] Wait event timing and tracing instrumentation

Hi,

On 11/07/2026 20:42, Dmitry Fomin wrote:

Hi hackers,

Hi hackers,

v3 is v2 rebased over current master (5f14f82280d); cfbot flagged the
set as needing a rebase after typedefs.list drifted. No code changes.

Just a quick note to say I'm very interested in these patches. I've
tried them and I'm quite enthusiastic with it. For example, it's nice to
see wait events wrt the checkpointer:

select wait_event_type, wait_event, calls, total_time_ms
from pg_stat_wait_event_timing
where pid=984861
order by total_time_ms desc;

┌─────────────────┬──────────────────────┬───────┬───────────────┐
│ wait_event_type │ wait_event │ calls │ total_time_ms │
├─────────────────┼──────────────────────┼───────┼───────────────┤
│ Activity │ CheckpointerMain │ 3 │ 300109.767515 │
│ Timeout │ CheckpointWriteDelay │ 1056 │ 105928.938476 │
│ IO │ DataFileWrite │ 1751 │ 61.356984 │
│ IO │ DataFileFlush │ 77 │ 7.395531 │
│ IO │ SlruWrite │ 3 │ 0.095615 │
└─────────────────┴──────────────────────┴───────┴───────────────┘
(5 rows)

That's definitely something I was waiting for.

Anyway, it was just a quick look. The patches apply to HEAD, they
compile, and it was easy to do some quick tests. I'll try to find some
time to work more on this, but I'd be very interested to see this
landing in v20.

Kudos for the great work, Dmitry!

Regards.

Regards,
Dmitry Fomin

Dmitry Fomin (7):
wait_event_timing: add --enable-wait-event-timing flag and
wait_event_capture GUC
wait_event_timing: record per-backend wait event statistics (stats
level)
wait_event_timing: expose overflow counters and add reset functions
ci: build one task with --enable-wait-event-timing
wait_event_timing: allocate the per-backend array lazily in DSA
wait_event_timing: add trace level with a per-session ring buffer
wait_event_timing: add query-attribution markers to the trace ring

.github/workflows/pg-ci.yml | 7 +
configure | 32 +
configure.ac | 8 +
doc/src/sgml/config.sgml | 106 +
doc/src/sgml/monitoring.sgml | 783 +++++
meson.build | 1 +
meson_options.txt | 3 +
src/backend/catalog/system_views.sql | 103 +
src/backend/executor/execMain.c | 5 +
src/backend/postmaster/auxprocess.c | 11 +
src/backend/storage/lmgr/proc.c | 5 +
src/backend/tcop/postgres.c | 39 +
src/backend/utils/.gitignore | 1 +
src/backend/utils/Makefile | 9 +-
src/backend/utils/activity/Makefile | 4 +-
src/backend/utils/activity/backend_status.c | 13 +
.../activity/generate-wait_event_types.pl | 179 +
src/backend/utils/activity/meson.build | 1 +
src/backend/utils/activity/wait_event.c | 3 +-
.../utils/activity/wait_event_names.txt | 2 +
.../utils/activity/wait_event_timing.c | 3112 +++++++++++++++++
src/backend/utils/init/postinit.c | 11 +
src/backend/utils/misc/guc_parameters.dat | 29 +
src/backend/utils/misc/guc_tables.c | 1 +
src/backend/utils/misc/postgresql.conf.sample | 5 +
src/include/catalog/pg_proc.dat | 59 +
src/include/pg_config.h.in | 3 +
src/include/storage/lwlocklist.h | 2 +
src/include/storage/subsystemlist.h | 2 +
src/include/utils/.gitignore | 1 +
src/include/utils/guc.h | 1 +
src/include/utils/guc_hooks.h | 3 +
src/include/utils/meson.build | 4 +-
src/include/utils/wait_classes.h | 9 +
src/include/utils/wait_event.h | 49 +
src/include/utils/wait_event_timing.h | 360 ++
src/test/modules/test_misc/meson.build | 1 +
.../t/015_wait_event_trace_seqlock.pl | 122 +
src/test/regress/expected/rules.out | 30 +
.../regress/expected/wait_event_timing.out | 188 +
.../regress/expected/wait_event_timing_1.out | 181 +
src/test/regress/parallel_schedule | 4 +
src/test/regress/sql/wait_event_timing.sql | 113 +
src/tools/pginclude/headerscheck | 2 +
src/tools/pgindent/typedefs.list | 12 +
45 files changed, 5610 insertions(+), 9 deletions(-)
create mode 100644 src/backend/utils/activity/wait_event_timing.c
create mode 100644 src/include/utils/wait_event_timing.h
create mode 100644 src/test/modules/test_misc/t/015_wait_event_trace_seqlock.pl
create mode 100644 src/test/regress/expected/wait_event_timing.out
create mode 100644 src/test/regress/expected/wait_event_timing_1.out
create mode 100644 src/test/regress/sql/wait_event_timing.sql

--
Dmitry

--
Guillaume Lelarge
Consultant
https://dalibo.com

#6Michael Paquier
michael@paquier.xyz
In reply to: Dmitry Fomin (#1)
Re: [PATCH v1 0/7] Wait event timing and tracing instrumentation

On Fri, Jul 03, 2026 at 10:22:58PM +0200, Dmitry Fomin wrote:

PostgreSQL exposes a rich taxonomy of wait events in pg_stat_activity,
but only as instantaneous snapshots: there is no in-core way to ask
"how long do my backends actually spend in each wait?", or "what was
the wait sequence of this session's last N events?". External tools
either sample at coarse resolution (pg_wait_sampling, default 10 ms) or
pay ~200-300 ns per transition via hardware watchpoints.

This is a reworked submission of an earlier 8k-line single patch,
following Andrey Borodin's advice to split it into independently
committable pieces with the DSA machinery deferred. The series has
three groups; each patch builds and passes check-world on its own:

Please also see this thread, particularly the second message (posted
one year + 4 days ago):
/messages/by-id/aGKSzFlpQWSh/+2w@ip-10-97-1-34.eu-west-3.compute.internal
/messages/by-id/xuynb44ql3hhggvqtme7axbliww7gwuy6pbaohxc4ngu3ynbsi@rvvpjxf55aia

The core point that seems to matter most is in v1-0002, where the
patch decides to make what is now a cheap 32-bit volatile manipulation
into an optional compilation-based expensive operation. I don't want
to sound negative here, but I'd recommend to re-read the previous
thread. This kind of change could lead us to make the addition of
more wait events harder to think about, especially if these are in
deeper parts of the backend stack.
--
Michael

#7Dmitry Fomin
fomin.list@gmail.com
In reply to: Michael Paquier (#6)
Re: [PATCH v1 0/7] Wait event timing and tracing instrumentation

On Mon, Jul 13, 2026 at 4:09 AM Michael Paquier <michael@paquier.xyz> wrote:

The core point that seems to matter most is in v1-0002, where the
patch decides to make what is now a cheap 32-bit volatile manipulation
into an optional compilation-based expensive operation. I don't want
to sound negative here, but I'd recommend to re-read the previous
thread. This kind of change could lead us to make the addition of
more wait events harder to think about, especially if these are in
deeper parts of the backend stack.

Thanks for the pointers. I have read that thread end to end, and
I should have cited it in the cover letter. Having done so, I think
the cover letter also failed to distinguish two different forms of
opt-in, so let me be precise about what this series does and where it
differs from what Andres described there.

The series provides operational opt-in:

- Built without --enable-wait-event-timing (the default), the timing
condition is absent from the preprocessed inline wait_start/end
path; the stores are as today.

- Built with it, at wait_event_capture = off (the default), the
recording bodies are out of line and each boundary gains a load
of the process-local wait_event_capture value, a test, and a
not-taken branch. For one call site I examined (FileReadV around
preadv(), gcc 13 -O2, x86-64; other compilers and sites will
differ in detail):

mov 0x0(%rbp),%rax # my_wait_event_info
mov %ecx,(%rax) # *ptr = wait_event_info (as today)
mov (%rbx),%eax # wait_event_capture (new)
test %eax,%eax # (new)
jne <tail> # not taken at off (new)
... preadv() ...
mov (%rbx),%edi # wait_event_capture (new)
test %edi,%edi # (new)
jne <tail> # not taken at off (new)
mov 0x0(%rbp),%rax
movl $0x0,(%rax) # *ptr = 0 (as today)

with the calls placed at the tail of the function, reached only
when capture is enabled.

- When capture is enabled for a backend, the path is intentionally
more expensive: stats takes two timestamps per completed wait and
updates count/total/max and a histogram; trace additionally writes
a ring record.

wait_event_capture is PGC_SUSET and per-backend, so this can be
scoped to selected sessions rather than a whole cluster; but within
an enabled backend, v1 times every wait event it reaches. A newly
added wait event automatically becomes a timed event in that
configuration. The generated lookup tables and the non-persistent
per-backend storage mean a new wait event needs no manual table or
stats-file work, including in stable branches -- but they do not
remove that performance coupling, and I understand your "harder to
think about, deeper in the backend stack" as being exactly about it.

Rereading the recording path with that lens also found one thing I
will change in the next version regardless of the API discussion:
the recorder currently raises an ereport(WARNING) when an overflow
counter first increments, and a recorder has no business calling
ereport() from arbitrarily deep call sites. The same information
is already exposed by pg_stat_wait_event_timing_overflow (0003), so
the WARNINGs will go and the view becomes the only signal.

So, having reread Andres's messages: what he described is explicit
per-call-site opt-in -- code changed over to an extended form, each
conversion reasoned about or measured. v1 does not implement that
split; it makes a different tradeoff: whole-taxonomy per-session
capture behind a runtime level. I chose that deliberately, because
it is a large part of the diagnostic value: selective conversion
leaves blind spots in a wait profile and gaps in the ordered trace,
and the profile of an incident you have not predicted is precisely
where blind spots hurt.
Where the series does follow that thread directly is the payload:
"the extended wait events need to count both the number of
encounters as well as the duration, the number of encounters is not
useful on its own" -- each aggregate here is count, total, max and a
32-bucket log2 histogram, and the trace preserves ordering and query
attribution, which is what lets interval snapshots distinguish more
waits from longer waits -- the distinction Robert pointed out bare
counters cannot make.

On measurement, since methodology was the other half of that thread:
I did not attempt to measure the gate with probes or rdtsc pairs.
The numbers in my second message on this thread are end-to-end
TPS; the observed run-to-run spread on that machine was roughly
+/- 1-2%.  They could not distinguish the compiled-in/off
configuration from the unpatched build within that variation, and a
control experiment with the
gate #ifdef'd out showed signed deltas of a few percent that were
consistent with binary-layout effects rather than attributable to
the gate (which is also why the earlier unlikely() annotation was
dropped: identical codegen either way).  For enabled capture, the
four stress scenarios in that message showed stats-vs-off deltas of
+0.1%, -0.3%, -2.4% and -4.5%, the last on a deliberately contended
hot-row workload.  Those are evidence about the existing call sites
on that machine, not a platform-independent ceiling, and they do not
prove that any future call site would be cheap enough -- which I
take to be part of your point.

I also do not see this as replacing the alternatives proposed in
that thread. Sampling (Andres's suggestion) is cheaper for finding
where time goes; the purpose-built lock or SLRU instrumentation
Robert described can answer domain questions this cannot; and there
is real overlap between some I/O waits and
track_io_timing/pg_stat_io, which I can quantify if that is useful.
The per-backend histograms and the ordered trace are aimed at what
those do not provide.

That leaves two API directions I can see:

1. Keep pgstat_report_wait_start()/end() at their current cost and
add an explicit extended form for selected call sites, each
conversion justified or benchmarked -- closest to what Andres
proposed, at the price of incomplete profiles and traces.

2. Retain session-wide capture as an explicitly diagnostic mode,
with an explicit escape hatch for call sites whose frequency
makes the timing overhead unacceptable -- at the price that the
enabled-path cost of future wait events remains a tradeoff to
acknowledge when they are added.

My preference is the second, because session-wide coverage is the
feature I am trying to provide. But I agree the off-state numbers
alone do not settle your concern. Would you consider that
direction viable, or do you regard explicit per-call-site opt-in as
a prerequisite for the next version?

Regards,
Dmitry Fomin

#8Dmitry Fomin
fomin.list@gmail.com
In reply to: Dmitry Fomin (#7)
Re: [PATCH v1 0/7] Wait event timing and tracing instrumentation

Hi,

v4 attached. Two changes since v3, no design changes:

* Rebased over current master -- cfbot flagged v3 as needing a rebase
after unrelated drift (typedefs.list and neighbours); no conflicts
in the feature code.

* Dropped the two ereport(WARNING) calls from the recording path, as I
said in my previous message I would do in the next version
regardless. A recorder should not call ereport() from arbitrarily
deep wait sites; the over-cap-LWLock-tranche and unknown-class
overflow conditions stay visible through
pg_stat_wait_event_timing_overflow, which is now their only signal.
Confined to patch 0002.

The API-direction question from my previous message still stands, and
I'd value your read on it whenever you have a moment: whether
whole-session capture behind the runtime level -- with an explicit
escape hatch for call sites where the added cost would be unacceptable
-- is an acceptable shape, or whether explicit per-call-site opt-in is
a prerequisite.

The patches still carry no catversion bump; one is needed at commit
time for 0002, 0003 and 0006.

Regards,
Dmitry Fomin

Dmitry Fomin (7):
wait_event_timing: add --enable-wait-event-timing flag and
wait_event_capture GUC
wait_event_timing: record per-backend wait event statistics (stats
level)
wait_event_timing: expose overflow counters and add reset functions
ci: build one task with --enable-wait-event-timing
wait_event_timing: allocate the per-backend array lazily in DSA
wait_event_timing: add trace level with a per-session ring buffer
wait_event_timing: add query-attribution markers to the trace ring

.github/workflows/pg-ci.yml | 7 +
configure | 32 +
configure.ac | 8 +
doc/src/sgml/config.sgml | 106 +
doc/src/sgml/monitoring.sgml | 783 +++++
meson.build | 1 +
meson_options.txt | 3 +
src/backend/catalog/system_views.sql | 103 +
src/backend/executor/execMain.c | 5 +
src/backend/postmaster/auxprocess.c | 11 +
src/backend/storage/lmgr/proc.c | 5 +
src/backend/tcop/postgres.c | 39 +
src/backend/utils/.gitignore | 1 +
src/backend/utils/Makefile | 9 +-
src/backend/utils/activity/Makefile | 4 +-
src/backend/utils/activity/backend_status.c | 13 +
.../activity/generate-wait_event_types.pl | 179 +
src/backend/utils/activity/meson.build | 1 +
src/backend/utils/activity/wait_event.c | 3 +-
.../utils/activity/wait_event_names.txt | 2 +
.../utils/activity/wait_event_timing.c | 3096 +++++++++++++++++
src/backend/utils/init/postinit.c | 11 +
src/backend/utils/misc/guc_parameters.dat | 29 +
src/backend/utils/misc/guc_tables.c | 1 +
src/backend/utils/misc/postgresql.conf.sample | 5 +
src/include/catalog/pg_proc.dat | 59 +
src/include/pg_config.h.in | 3 +
src/include/storage/lwlocklist.h | 2 +
src/include/storage/subsystemlist.h | 2 +
src/include/utils/.gitignore | 1 +
src/include/utils/guc.h | 1 +
src/include/utils/guc_hooks.h | 3 +
src/include/utils/meson.build | 4 +-
src/include/utils/wait_classes.h | 9 +
src/include/utils/wait_event.h | 49 +
src/include/utils/wait_event_timing.h | 360 ++
src/test/modules/test_misc/meson.build | 1 +
.../t/015_wait_event_trace_seqlock.pl | 122 +
src/test/regress/expected/rules.out | 30 +
.../regress/expected/wait_event_timing.out | 188 +
.../regress/expected/wait_event_timing_1.out | 181 +
src/test/regress/parallel_schedule | 4 +
src/test/regress/sql/wait_event_timing.sql | 113 +
src/tools/pginclude/headerscheck | 2 +
src/tools/pgindent/typedefs.list | 12 +
45 files changed, 5594 insertions(+), 9 deletions(-)
create mode 100644 src/backend/utils/activity/wait_event_timing.c
create mode 100644 src/include/utils/wait_event_timing.h
create mode 100644 src/test/modules/test_misc/t/015_wait_event_trace_seqlock.pl
create mode 100644 src/test/regress/expected/wait_event_timing.out
create mode 100644 src/test/regress/expected/wait_event_timing_1.out
create mode 100644 src/test/regress/sql/wait_event_timing.sql

Attachments:

v4-0002-wait_event_timing-record-per-backend-wait-event-s.patchapplication/octet-stream; name=v4-0002-wait_event_timing-record-per-backend-wait-event-s.patchDownload+1737-56
v4-0003-wait_event_timing-expose-overflow-counters-and-ad.patchapplication/octet-stream; name=v4-0003-wait_event_timing-expose-overflow-counters-and-ad.patchDownload+611-8
v4-0005-wait_event_timing-allocate-the-per-backend-array-.patchapplication/octet-stream; name=v4-0005-wait_event_timing-allocate-the-per-backend-array-.patchDownload+366-88
v4-0004-ci-build-one-task-with-enable-wait-event-timing.patchapplication/octet-stream; name=v4-0004-ci-build-one-task-with-enable-wait-event-timing.patchDownload+7-1
v4-0001-wait_event_timing-add-enable-wait-event-timing-fl.patchapplication/octet-stream; name=v4-0001-wait_event_timing-add-enable-wait-event-timing-fl.patchDownload+228-2
v4-0007-wait_event_timing-add-query-attribution-markers-t.patchapplication/octet-stream; name=v4-0007-wait_event_timing-add-query-attribution-markers-t.patchDownload+166-11
v4-0006-wait_event_timing-add-trace-level-with-a-per-sess.patchapplication/octet-stream; name=v4-0006-wait_event_timing-add-trace-level-with-a-per-sess.patchDownload+2737-97
#9Ilmar Yunusov
tanswis42@gmail.com
In reply to: Dmitry Fomin (#8)
Re: [PATCH v1 0/7] Wait event timing and tracing instrumentation

Hi,

While testing v4, I ran into a backend startup failure when
wait_event_capture=stats was set through PGOPTIONS:

FATAL: ResourceOwnerEnlarge called after release started

This was on macOS arm64 with --enable-wait-event-timing,
--enable-cassert and --enable-debug. I ran a one-statement pgbench
workload with two clients (-c 2 -j 2 -t 1) 500 times. 16 runs
returned non-zero, and five runs produced the FATAL above in the
server log.

The failure is probabilistic. The same test did not reproduce with
one client in 500 runs. The two-client tests with
wait_event_capture=off or trace at startup, and with stats or trace set
after connection startup, were also clean.

The relevant part of the backtrace was:

InitPostgres
-> CommitTransactionCommand
-> ProcReleaseLocks
-> LockReleaseAll
-> LWLockAcquire
-> pgstat_report_wait_end_timing
-> pgstat_wait_event_timing_lazy_attach
-> wait_event_timing_attach_array
-> wait_event_timing_ensure_dsa
-> dsa_attach
-> dsm_attach
-> dsm_create_descriptor
-> ResourceOwnerEnlarge

The first lazy DSA attach is reached from the wait-end path while
InitPostgres is already releasing the current resource owner. The
attach path then reaches dsm_create_descriptor(), which calls
ResourceOwnerEnlarge() after release has started.

Could you address this in the next version?

Regards,
Ilmar

The new status of this patch is: Waiting on Author

#10Dmitry Fomin
fomin.list@gmail.com
In reply to: Ilmar Yunusov (#9)
Re: [PATCH v1 0/7] Wait event timing and tracing instrumentation

Hi,

v5 fixes the startup crash Ilmar Yunusov reported on v4 and rebases
over current master.

* Fix "FATAL: ResourceOwnerEnlarge called after release started" when
wait_event_capture is enabled via PGOPTIONS at startup under
concurrency (0005). The first lazy DSA attach could be triggered by
a wait event during InitPostgres's own transaction cleanup, while the
current resource owner was already mid-release, making
dsm_create_descriptor()'s ResourceOwnerEnlarge() illegal. The lazy
attach now also declines during startup (!IsNormalProcessingMode())
and attaches on the first wait once the backend reaches normal
processing, matching the existing critical-section and LWLock-wait
guards. Reproduced at 2 FATALs / 60 concurrent startups unpatched,
0 / 4800 with the fix; the stats view still populates normally and
the regression and TAP suites pass.

* Rebased over current master.

The API-direction question from the earlier discussion still stands.
The patches still carry no catversion bump; one is needed at commit
time for 0002, 0003 and 0006.

Regards,
Dmitry Fomin

Dmitry Fomin (7):
wait_event_timing: add --enable-wait-event-timing flag and
wait_event_capture GUC
wait_event_timing: record per-backend wait event statistics (stats
level)
wait_event_timing: expose overflow counters and add reset functions
ci: build one task with --enable-wait-event-timing
wait_event_timing: allocate the per-backend array lazily in DSA
wait_event_timing: add trace level with a per-session ring buffer
wait_event_timing: add query-attribution markers to the trace ring

.github/workflows/pg-ci.yml | 7 +
configure | 32 +
configure.ac | 8 +
doc/src/sgml/config.sgml | 106 +
doc/src/sgml/monitoring.sgml | 783 +++++
meson.build | 1 +
meson_options.txt | 3 +
src/backend/catalog/system_views.sql | 103 +
src/backend/executor/execMain.c | 5 +
src/backend/postmaster/auxprocess.c | 11 +
src/backend/storage/lmgr/proc.c | 5 +
src/backend/tcop/postgres.c | 39 +
src/backend/utils/.gitignore | 1 +
src/backend/utils/Makefile | 9 +-
src/backend/utils/activity/Makefile | 4 +-
src/backend/utils/activity/backend_status.c | 13 +
.../activity/generate-wait_event_types.pl | 179 +
src/backend/utils/activity/meson.build | 1 +
src/backend/utils/activity/wait_event.c | 3 +-
.../utils/activity/wait_event_names.txt | 2 +
.../utils/activity/wait_event_timing.c | 3109 +++++++++++++++++
src/backend/utils/init/postinit.c | 11 +
src/backend/utils/misc/guc_parameters.dat | 29 +
src/backend/utils/misc/guc_tables.c | 1 +
src/backend/utils/misc/postgresql.conf.sample | 5 +
src/include/catalog/pg_proc.dat | 59 +
src/include/pg_config.h.in | 3 +
src/include/storage/lwlocklist.h | 2 +
src/include/storage/subsystemlist.h | 2 +
src/include/utils/.gitignore | 1 +
src/include/utils/guc.h | 1 +
src/include/utils/guc_hooks.h | 3 +
src/include/utils/meson.build | 4 +-
src/include/utils/wait_classes.h | 9 +
src/include/utils/wait_event.h | 49 +
src/include/utils/wait_event_timing.h | 360 ++
src/test/modules/test_misc/meson.build | 1 +
.../t/015_wait_event_trace_seqlock.pl | 122 +
src/test/regress/expected/rules.out | 30 +
.../regress/expected/wait_event_timing.out | 188 +
.../regress/expected/wait_event_timing_1.out | 181 +
src/test/regress/parallel_schedule | 4 +
src/test/regress/sql/wait_event_timing.sql | 113 +
src/tools/pginclude/headerscheck | 2 +
src/tools/pgindent/typedefs.list | 12 +
45 files changed, 5607 insertions(+), 9 deletions(-)
create mode 100644 src/backend/utils/activity/wait_event_timing.c
create mode 100644 src/include/utils/wait_event_timing.h
create mode 100644 src/test/modules/test_misc/t/015_wait_event_trace_seqlock.pl
create mode 100644 src/test/regress/expected/wait_event_timing.out
create mode 100644 src/test/regress/expected/wait_event_timing_1.out
create mode 100644 src/test/regress/sql/wait_event_timing.sql

Attachments:

v5-0003-wait_event_timing-expose-overflow-counters-and-ad.patchapplication/octet-stream; name=v5-0003-wait_event_timing-expose-overflow-counters-and-ad.patchDownload+611-8
v5-0002-wait_event_timing-record-per-backend-wait-event-s.patchapplication/octet-stream; name=v5-0002-wait_event_timing-record-per-backend-wait-event-s.patchDownload+1737-56
v5-0001-wait_event_timing-add-enable-wait-event-timing-fl.patchapplication/octet-stream; name=v5-0001-wait_event_timing-add-enable-wait-event-timing-fl.patchDownload+228-2
v5-0004-ci-build-one-task-with-enable-wait-event-timing.patchapplication/octet-stream; name=v5-0004-ci-build-one-task-with-enable-wait-event-timing.patchDownload+7-1
v5-0005-wait_event_timing-allocate-the-per-backend-array-.patchapplication/octet-stream; name=v5-0005-wait_event_timing-allocate-the-per-backend-array-.patchDownload+379-88
v5-0007-wait_event_timing-add-query-attribution-markers-t.patchapplication/octet-stream; name=v5-0007-wait_event_timing-add-query-attribution-markers-t.patchDownload+166-11
v5-0006-wait_event_timing-add-trace-level-with-a-per-sess.patchapplication/octet-stream; name=v5-0006-wait_event_timing-add-trace-level-with-a-per-sess.patchDownload+2737-97
#11Dmitry Fomin
fomin.list@gmail.com
In reply to: Dmitry Fomin (#10)
Re: [PATCH v1 0/7] Wait event timing and tracing instrumentation

Hi Hackers

v6 is a rebase of v5 over current master (74c77052bc2); cfbot flagged
the set as needing a rebase after about 400 commits of drift. One
mechanical change beyond the rebase: master meanwhile added
src/test/modules/test_misc/t/015_temp_schema_exit_deferrable.pl, so the
injection-point seqlock TAP test moves from 015 to
t/016_wait_event_trace_seqlock.pl. No code changes.

The patches carry no catversion bump; one is needed at commit time for
0002, 0003 and 0006.

Regards,
Dmitry Fomin

Dmitry Fomin (7):
wait_event_timing: add --enable-wait-event-timing flag and
wait_event_capture GUC
wait_event_timing: record per-backend wait event statistics (stats
level)
wait_event_timing: expose overflow counters and add reset functions
ci: build one task with --enable-wait-event-timing
wait_event_timing: allocate the per-backend array lazily in DSA
wait_event_timing: add trace level with a per-session ring buffer
wait_event_timing: add query-attribution markers to the trace ring

.github/workflows/pg-ci.yml | 7 +
configure | 32 +
configure.ac | 8 +
doc/src/sgml/config.sgml | 106 +
doc/src/sgml/monitoring.sgml | 783 +++++
meson.build | 1 +
meson_options.txt | 3 +
src/backend/catalog/system_views.sql | 103 +
src/backend/executor/execMain.c | 5 +
src/backend/postmaster/auxprocess.c | 11 +
src/backend/storage/lmgr/proc.c | 5 +
src/backend/tcop/postgres.c | 39 +
src/backend/utils/.gitignore | 1 +
src/backend/utils/Makefile | 9 +-
src/backend/utils/activity/Makefile | 4 +-
src/backend/utils/activity/backend_status.c | 13 +
.../activity/generate-wait_event_types.pl | 179 +
src/backend/utils/activity/meson.build | 1 +
src/backend/utils/activity/wait_event.c | 3 +-
.../utils/activity/wait_event_names.txt | 2 +
.../utils/activity/wait_event_timing.c | 3109 +++++++++++++++++
src/backend/utils/init/postinit.c | 11 +
src/backend/utils/misc/guc_parameters.dat | 29 +
src/backend/utils/misc/guc_tables.c | 1 +
src/backend/utils/misc/postgresql.conf.sample | 5 +
src/include/catalog/pg_proc.dat | 59 +
src/include/pg_config.h.in | 3 +
src/include/storage/lwlocklist.h | 2 +
src/include/storage/subsystemlist.h | 2 +
src/include/utils/.gitignore | 1 +
src/include/utils/guc.h | 1 +
src/include/utils/guc_hooks.h | 3 +
src/include/utils/meson.build | 4 +-
src/include/utils/wait_classes.h | 9 +
src/include/utils/wait_event.h | 49 +
src/include/utils/wait_event_timing.h | 360 ++
src/test/modules/test_misc/meson.build | 1 +
.../t/016_wait_event_trace_seqlock.pl | 122 +
src/test/regress/expected/rules.out | 30 +
.../regress/expected/wait_event_timing.out | 188 +
.../regress/expected/wait_event_timing_1.out | 181 +
src/test/regress/parallel_schedule | 4 +
src/test/regress/sql/wait_event_timing.sql | 113 +
src/tools/pginclude/headerscheck | 2 +
src/tools/pgindent/typedefs.list | 12 +
45 files changed, 5607 insertions(+), 9 deletions(-)
create mode 100644 src/backend/utils/activity/wait_event_timing.c
create mode 100644 src/include/utils/wait_event_timing.h
create mode 100644 src/test/modules/test_misc/t/016_wait_event_trace_seqlock.pl
create mode 100644 src/test/regress/expected/wait_event_timing.out
create mode 100644 src/test/regress/expected/wait_event_timing_1.out
create mode 100644 src/test/regress/sql/wait_event_timing.sql

Attachments:

v6-0004-ci-build-one-task-with-enable-wait-event-timing.patchapplication/x-patch; name=v6-0004-ci-build-one-task-with-enable-wait-event-timing.patchDownload+7-1
v6-0001-wait_event_timing-add-enable-wait-event-timing-fl.patchapplication/x-patch; name=v6-0001-wait_event_timing-add-enable-wait-event-timing-fl.patchDownload+228-2
v6-0003-wait_event_timing-expose-overflow-counters-and-ad.patchapplication/x-patch; name=v6-0003-wait_event_timing-expose-overflow-counters-and-ad.patchDownload+611-8
v6-0002-wait_event_timing-record-per-backend-wait-event-s.patchapplication/x-patch; name=v6-0002-wait_event_timing-record-per-backend-wait-event-s.patchDownload+1737-56
v6-0005-wait_event_timing-allocate-the-per-backend-array-.patchapplication/x-patch; name=v6-0005-wait_event_timing-allocate-the-per-backend-array-.patchDownload+379-88
v6-0007-wait_event_timing-add-query-attribution-markers-t.patchapplication/x-patch; name=v6-0007-wait_event_timing-add-query-attribution-markers-t.patchDownload+166-11
v6-0006-wait_event_timing-add-trace-level-with-a-per-sess.patchapplication/x-patch; name=v6-0006-wait_event_timing-add-trace-level-with-a-per-sess.patchDownload+2737-97
#12Kirk Wolak
wolakk@gmail.com
In reply to: Dmitry Fomin (#11)
Re: [PATCH v1 0/7] Wait event timing and tracing instrumentation

On Fri, Sep 4, 2026 at 3:23 PM Dmitry Fomin <fomin.list@gmail.com> wrote:

Hi Hackers

v6 is a rebase of v5 over current master (74c77052bc2); cfbot flagged
the set as needing a rebase after about 400 commits of drift. One
mechanical change beyond the rebase: master meanwhile added
src/test/modules/test_misc/t/015_temp_schema_exit_deferrable.pl, so the
injection-point seqlock TAP test moves from 015 to
t/016_wait_event_trace_seqlock.pl. No code changes.

The patches carry no catversion bump; one is needed at commit time for
0002, 0003 and 0006.

Regards,
Dmitry Fomin

Let me +1 this, as I believe this is incredibly useful.
Keep up the good work.

Kirk Out!

#13Andrey Borodin
amborodin@acm.org
In reply to: Kirk Wolak (#12)
Re: [PATCH v1 0/7] Wait event timing and tracing instrumentation

Hi,

I looked at v6 together with Ilmar's EXPLAIN WAITS proposal [0]/messages/by-id/cover.1778280923.git.tanswis42@gmail.com and the
earlier wait-event statistics discussion [1]/messages/by-id/aGKSzFlpQWSh/+2w@ip-10-97-1-34.eu-west-3.compute.internal. I think a small common
hook with the collectors kept outside core is worth prototyping, but the
hook moves rather than removes the main performance question.

The current start/end reporting path is essentially one 32-bit store at
each boundary. The null-hook check would run at every reported wait
transition. For I/O events a predictable null check is probably small
relative to the syscall. However, some paths report a wait without
necessarily reaching a kernel wait: for example, WaitEventSetWait() starts
reporting before checking an already-set latch. The important question
is therefore the null-hook cost paid by every backend that did not enable
the feature. Dmitry's end-to-end measurements exclude a large regression
in the tested workloads, but their 1..2% noise does not isolate that cost
or possible register spills at individual hot call sites.

Michael wrote above that this could "make the addition of more wait
events harder to think about." I think that is partly a useful
constraint rather than only a drawback. In the "Missing wait events"
discussion [2]/messages/by-id/CAM527d9PkaSj-gNjLZqjJXnqaWTD8kHPtm2Yj8-1Gh_0pTRgDA@mail.gmail.com, Andres opposed turning wait events into a CPU profiler.
Adding a reporting point should already require us to establish that it
represents a potential wait and to consider its frequency. Such sites
can still be hot and return without blocking, so this does not prove the
null gate free; it does mean that unbounded proliferation is not the
intended model.

The hook contract would have to be unusually strict. The v4 startup
failure demonstrated that wait reporting can happen during
resource-owner release, and the current implementation also guards
startup, critical sections, LWLock waits, exit, and recursion. We do
already trust extensions with callbacks in awkward contexts, so I do not
think this rules out a hook. I think it should be documented as using
only preallocated backend-local state, with no waits, allocations, locks,
or errors, probably with a recursion guard in core.

The two proposals do not seem to be direct competitors. Dmitry needs
every transition for exact counts and an ordered trace. EXPLAIN WAITS
needs statement/active-node attribution, for which sampling may be a
better tradeoff. Current master already lets extensions add EXPLAIN
options and output. Alternatively, a core EXPLAIN WAITS implementation
could install its collector only for the explicitly requested statement.
In v3, every wait end also walks all active node ancestors and updates
each accumulator, so its enabled cost grows with plan depth.

I found one concrete scalability problem in v6. With the default 192
LWLock tranches, each timing slot contains 544 flat and 192 LWLock
histograms and is a little over 203 KiB. Enabling capture in one backend
allocates the complete array for every ProcNumber: about 200 MiB at 1000
slots. Patch 0005 makes the allocation lazy, but not sparse;
0002--0004 allocate it eagerly. Hundreds of MiB, and eventually GiB,
are not acceptable for realistic high-connection configurations when
only one backend requested capture. The storage should be allocated for
collecting backends rather than every possible ProcNumber.

The deployment concern about extensions is real: diagnostics unavailable
on an inherited managed installation are often useless. But the current
configure-time default also leaves the feature unavailable unless the
provider opted in, while putting the entire collector and presentation in
core leaves PostgreSQL with substantial permanent maintenance cost.

I suggest deciding the observation contract first: sampling, explicitly
selected extended wait sites, or every transition. A useful next
experiment would compare master, the compiled-in/off gate, a null hook, a
preloaded module with collection disabled, and enabled collection. The
first three are the important comparison for users who did not request
the feature. This should include an isolated start/end-pair test and
workloads dominated by short waits. If the null gate is acceptable, one
small core hook and a separately maintained timing/trace extension seem
like a promising deployment model. EXPLAIN WAITS could use the same hook
only while the requested statement is running. The hook API would need
chaining and nesting semantics so that these consumers can coexist.

Thank you!

Best regards, Andrey Borodin.

[0]: /messages/by-id/cover.1778280923.git.tanswis42@gmail.com
[1]: /messages/by-id/aGKSzFlpQWSh/+2w@ip-10-97-1-34.eu-west-3.compute.internal
[2]: /messages/by-id/CAM527d9PkaSj-gNjLZqjJXnqaWTD8kHPtm2Yj8-1Gh_0pTRgDA@mail.gmail.com

#14Dmitry Fomin
fomin.list@gmail.com
In reply to: Andrey Borodin (#13)
Re: [PATCH v1 0/7] Wait event timing and tracing instrumentation

Hi Andrey,

Thank you very much for this review. The memory finding is correct
(more details on it below).
The hook direction is the right one. I have done tests you suggested
on a dedicated bare-metal host, and below are the numbers.
One decision I would like to invite objections to,
and a list of further v6 defects I found while preparing the experiments.

The short version:

* I built and measured two hook placements against unmodified
master: the transition placement you described (a hook call
inside every pgstat_report_wait_start()/end() transition), and a
timed-pair placement (a second pair of inline functions used only
at explicitly converted call sites, with the ordinary pair left
byte-identical to master).

* With no collector loaded they cost the same: about 2 ns per pair
on the already-set-latch path, and no statistically resolved
end-to-end difference on any workload.

* I propose the timed pair for v7, for reasons that are not
performance, and attach it as a small RFC patch.

* v6 has five confirmed source defects (yours is the first) and two
documentation/API mismatches. All are listed below with the v7
fix. v6 should not be reviewed further; with this mail the CF
entry carries only the two core patches below.

Test setup (Host, build, protocol)
=====================

Intel Xeon Platinum 8462Y+ (2 sockets, 1 TiB), Linux 5.14,
performance governor, otherwise idle. Server pinned to 8 physical
cores, pgbench to 8 other physical cores on the same NUMA node, no
SMT sibling shared between the two sets. GCC 11.5, -O2 -g0, autoconf,
assertions off. Master base fc9743420ded. Every configuration is a
separate installation, with a fresh server for every measured cell.

12 repetitions per cell, 10 s untimed warm-up, 30 s measured,
randomized complete Latin-square order so that every configuration
occupies every schedule position exactly once, paired Student t 95%
intervals, no outlier removal.

Gate before anything else: two independent builds of master (they
produced byte-identical binaries) run as separate installations
against each other on select-only pgbench, 16 clients:
+0.16% [-0.44, +0.76].

Configurations, your list plus controls, each for both placements:

master unmodified
v6-off v6 compiled in, wait_event_capture = off
NULL hooks present, no collector loaded (pointers NULL)
module-off collector preloaded, capture off
stats, trace collector enabled
control same source and object layout as NULL, with the hook
call sites compiled out

The layout control exists because an earlier round of this work found
a 3% TPC-B difference that was binary layout rather than code. Every
NULL comparison below is against its own control, not against master.

Isolated start/end pair (W1)
============================

A test-module function calls WaitLatch() on an already-set latch
10^8 times, with the whole loop timed. This is the path you named: it
reports a wait and returns without entering the kernel. Medians:

ns/iteration
master 9.24
v6-off 10.18
transition: control 9.16
NULL 11.30
module-off 16.68
stats 34.28
trace 36.12
timed pair: control 9.39
NULL 11.42
module-off 16.49
stats 34.29
trace 36.12

Paired differences over the 12 repetitions:

transition NULL - control +2.11 ns [+1.82, +2.40]
timed-pair NULL - control +1.97 ns [+1.64, +2.29]
timed-pair NULL - transition NULL -0.04 ns [-0.43, +0.35]

So the unplugged hook costs about 2 ns per pair on this path, with
an upper bound of 2.4 ns, and the same under both placements. One
note on provenance, since it is visible in the package: an earlier
run of the same test (+1.92 [+1.61, +2.22] and +1.87 [+1.62, +2.12])
stopped at a 2 ns continuation gate I had set for myself; I changed
that gate to report-only, reran the W0 and W1 stages under a new run
identifier, and continued to W2-W6 with no other change to the
protocol. Both runs are in the package. For reference, v6-off is
about 0.9 ns over master on this path, but that is a different
binary with no paired control.

The other W1 paths, NULL minus control:

transition timed pair
timeout-zero WaitLatch +0.43 [-0.21,+1.07] +0.32 [-0.01,+0.66]
cached FileReadV +0.08 [-1.06,+1.21] +2.52 [+0.58,+4.47]
pg_usleep(0) +0.71 [+0.56,+0.87] +0.02 [-0.03,+0.08]
bare report pair +0.18 [+0.17,+0.20] -0.01 [-0.03,+0.01]

Two comments. The FileReadV number for the timed pair is larger than
for the other placement and I cannot explain it: the FileReadV
instruction streams of the two NULL builds are identical, each has
exactly one hook pair, and the paired differences only turn positive
in the last five repetitions. I report it as measured. The last row
is the ordinary pgstat_report_wait_start()/end() pair on its own: the
transition hook makes it 0.18 ns slower than master's; under the
timed pair it is master's code. (pg_usleep(0) returns without a
syscall on Linux, so that row is another annotation-only path, not a
sleep.)

End-to-end, NULL hook minus layout control
==========================================

W2 select-only, 1 client, 4 GB shared_buffers
W3 8 clients on a deterministic short ProcArrayLock wait, 16 MB
W4 select-only, 16 clients, 4 GB
W5 TPC-B, 16 clients
W6a select-only, 16 clients, 32 MB
W6b TPC-B, 16 clients, 32 MB
W6c select-only, 32 clients, 32 MB
W6d one-hot-row update, 32 clients, 32 MB

TPS difference in percent, positive = the NULL build was faster:

transition timed pair timed - transition
W2 +0.17 [-0.67,+1.00] +0.48 [+0.18,+0.78] +0.02 [-0.34,+0.38]
W3 +1.26 [-1.64,+4.17] +0.09 [-2.55,+2.73] +0.01 [-2.37,+2.39]
W4 +1.06 [+0.61,+1.52] +1.13 [+0.79,+1.46] +0.13 [-0.18,+0.44]
W5 +0.49 [-0.19,+1.18] +0.31 [-0.55,+1.16] -0.43 [-1.06,+0.21]
W6a +0.47 [+0.17,+0.77] +0.80 [+0.48,+1.11] +0.28 [-0.03,+0.58]
W6b -0.38 [-2.02,+1.26] +0.04 [-0.71,+0.79] +1.25 [-0.80,+3.29]
W6c +0.47 [+0.20,+0.74] +0.68 [+0.35,+1.00] +0.08 [-0.17,+0.33]
W6d -0.15 [-0.39,+0.09] -0.05 [-0.28,+0.18] +0.57 [-0.31,+1.44]

No NULL build is resolvably slower than its control, and no workload
resolves a difference between the two placements. Several NULL builds
are resolvably faster than their controls (W4, W6a, W6c). That is not
the hook helping; it is the size of layout and code-generation effects
at this scale, and the reason the control is there.

Enabled cost
============

Relative to NULL, on the CPU-bound read workloads (W2, W3, W4, W6a,
W6c), stats cost about 0.5-1.1% and trace about 1.2-1.8%, for both
placements. W5, W6b and W6d are bottlenecked elsewhere and their
intervals include zero. Loading the collector with capture off adds
about 5.2 ns per pair on W1 (the indirect call plus the module's own
check); end-to-end that is generally below 0.5%, with a few cells
resolved at 0.2-0.4%.

Where the two placements do differ is with collection on. An ordinary
report that is not a timed site costs the transition collector about
25-27 ns more than module-off, because it observes every transition;
it costs the timed-pair collector nothing. At a timed site both cost
the same, about 18 ns (stats) and 19.5 ns (trace) over module-off.

Short waits (W3)
================

Qualified in a separate run with the same fixture, so the observer
query did not touch the TPS cells: about 607,000 LWLock waits/s, mean
5.15 us, p95 at or below 16.4 us, more than 99.9998% of recorded waits
being the target ProcArrayLock wait, 12 of 12 repetitions qualifying.
Enabled cost on W3 is in the same range as the other read workloads
(stats about 1.0%, trace 1.4-1.7%), so the short-wait case did not
show a disproportionate collector cost.

Register pressure
=================

Stack-memory operand counts from objdump of the retained binaries.
These count stack references, not proven spills; the 42 disassemblies
are in the package for anyone who wants to read them.

master v6-off transition timed pair
NULL ctrl NULL ctrl
WaitEventSetWait 40 40 40 40 40 40
FileReadV 0 0 0 0 0 0
LWLockAcquire 0 0 0 0 0 0
XLogWrite 36 40 40 36 40 36
SlruInternalWritePage 12 12 12 12 12 12
CopyReadLine 32 32 32 32 32 32
pgaio_io_perform_synchronously 0 0 0 0 0 0

The hook adds four stack references in XLogWrite, under both
placements, and the same four appear in v6-off; the other six
functions are unchanged. In this sample that is the one place where
Andres's spill concern is visible in the code, and it is the same
under all three designs.

The decision I am proposing
===========================

The two placements are indistinguishable in cost. I propose the timed
pair for v7, because:

1. pgstat_report_wait_start()/end() stay byte-identical to master;
the bare-pair row above is the direct measurement of that. My
understanding is that this is the property Andres was defending
when he wrote that he is "just about dead set adding even a
single cycle to wait events" [1]/messages/by-id/uah2s5tppv3onn7bsf2uelyexfrxwrmye6qqyrbbsjepxny7l5@guymflaarnsr. The transition placement adds
the pointer test to that pair; the timed pair does not.

2. It is the explicitly opt-in, changed-over-per-call-site shape
Andres described for extended wait events [2]/messages/by-id/sofkrmi3skg3ekc3y23uwxscbviy5lcbukincoyauypg4ylfdg@6lwzhi6uagc7. The same message
asks that they count encounters as well as duration, which the
collector does, and that each converted site be justified by
reasoning or a careful experiment, which is what the tables
above are for. Andres, if that misreads you, please say so.

3. It converts every direct pgstat_report_wait_start() site the
backend executes, in one patch: 94 start sites and 111 end
calls (17 of them error-path cleanup calls) in 42 files, all of
src/backend plus the two control-file waits in src/common. So
present in-tree coverage equals the transition hook's, and
extension waits that go through core primitives (latches,
sockets, condition variables, LWLocks, file I/O) are covered
too, under the extension's own wait event name. What the timed
pair does not cover automatically is an extension's own
hand-annotated system call, the pattern in the custom-wait-event
example in xfunc.sgml: it stays visible in pg_stat_activity as
before, but is timed only once the extension switches those two
calls to the timed pair. To size that: of twenty widely used
extensions I checked, one uses that pattern (a storage engine
with its own files); the other nineteen and every in-tree
contrib module wait through core primitives and are covered
as is. The same applies to future core sites, where a site
that is not converted is one grep away. That is the coverage
the opt-in shape costs, and I would rather state it than hide
it.

The transition numbers are in every table above so that the choice is
visibly not made on performance. If you or Andres prefer the
transition placement after seeing them, the core part becomes a
smaller patch, not a different design, and I will switch.

Attached, against master at 412ef97d925c:

v7-0001 the two hook pointers, the recursion depth guard, and
pgstat_report_wait_start_timed()/end_timed() in
wait_event.h; 63 lines, nothing else in core changes.
v7-0002 the call-name conversions, 205 lines changed, no other
edits. The remaining rows of the series will follow as
v8 on this thread.

The hook contract is your list as written: void (*)(uint32
wait_event_info) for begin and for end; only preallocated
backend-local state; no waits, allocations, locks or errors; a
per-backend depth counter in core so that a wait inside a hook is
never re-entered. The collector chains by calling the previous begin
hook before its own and its own end hook before the previous end, and
both preload orders passed smoke tests, so a second consumer such as
EXPLAIN WAITS can stack on the same hook. One narrow question: is
wait_event_info alone, on both begin and end, enough for the
consumers you have in mind? I deliberately pass no timestamp, so each
consumer reads its own clock only when it is enabled.

v6 defects
==========

Reviewing v6 for this experiment found five source defects and two
documentation/API mismatches. They were established by source tracing;
none is exercised by the benchmark, which ran v6 with capture off.
All seven are in the collector, so the attached 0001/0002 touch none
of them; the fixes come with the contrib module.

1. Dense allocation on first use (your finding). v7: one sparse
slot per collecting backend, allocated at a safe point, never
inside the hook.

2. After ProcNumber reuse, a successor backend with capture off can
be shown with its predecessor's counters under its own PID and
role: the slot is zeroed only on lazy attach, and the reader
checks only that the current backend entry is live. v7: owner
identity recorded at attach and checked by every reader.

3. Under EXEC_BACKEND the trace orphan cleanup runs before the
trace control pointer is attached, returns early, and the later
attach skips the still-orphaned slot, so tracing is silently
disabled on ordinary ProcNumber reuse. v7: cleanup runs after
shared memory is attached.

4. Cross-backend reset checks only pg_signal_backend membership.
Unlike pg_signal_backend() it does not protect superuser or
role-less targets and it accepts auxiliary PIDs. v7: the same
rules as pg_signal_backend().

5. Reset resolves the PID to a ProcNumber under ProcArrayLock,
releases the lock, then bumps that slot's generation; a
successor that attached in between consumes the reset. v7: the
reset carries the target's identity and the consumer verifies
it.

6. Query markers are query-ID transitions, not the matched
Parse/Bind/Execute brackets the documentation describes. v7:
documentation and behaviour reconciled.

7. The documented direct reader for extensions references
WaitEventTraceCtl, which is file-static. v7: a supported
accessor is exported.

What v7 will contain
====================

0001/0002 above; the collector as contrib/pg_wait_event_timing (a
contrib module ships with every release and is available on managed
services the way pg_stat_statements is; if the preference is an
external extension, the core part is unchanged); sparse memory; the
seven fixes and tests for them. The collector used in this benchmark
is a port of the v6 hot path with sparse state, histograms, rings,
markers, snapshots and chaining, but without ACL, reset, post-mortem
retention or error nesting, so I will re-measure the enabled numbers
once on the real module before citing them in the cover letter.
I aim to post it before this commitfest closes at the end of
September, and in any case before the November one.

EXPLAIN WAITS: agreed that they are not competitors. Thank you for the
pointer to Ilmar's thread, which I had not connected to this one; if a
hook of this shape lands I am happy to align on one API so that his
collector can attach for the requested statement only.

Package
=======

The 2,040 raw result rows (matching the predeclared schedules), the
scripts, the exact commits, the 42 disassemblies, and a VERIFY.sh that
regenerates every report from the raw JSON:

https://github.com/DmitryNFomin/pg-wait-event-hook-benchmark/releases/download/v2026-09-09/wait-hook-peer-review-20260909-REDACTED.tar.gz

(69,367,758 bytes, SHA-256
83b01bc010f3dc330d8e04b2bad9fa1d54d1298757f5cc84bb78ff8accc286b7;
the repository README lists the contents.)

Host names, user names and workspace paths in it are replaced by
neutral tokens and its hash manifests were regenerated for the copy;
the numbers are untouched and VERIFY.sh passes on it. One caveat
recorded in it: the run finished all workloads and the post-processing
step then exited with 141 (an early-exit awk sending SIGPIPE to
objdump under pipefail). The disassembly step was rerun; no workload
was; the original exit status is preserved.

Thanks again.

[1]: /messages/by-id/uah2s5tppv3onn7bsf2uelyexfrxwrmye6qqyrbbsjepxny7l5@guymflaarnsr
[2]: /messages/by-id/sofkrmi3skg3ekc3y23uwxscbviy5lcbukincoyauypg4ylfdg@6lwzhi6uagc7

Regards,
Dmitry Fomin

Attachments:

t248739_14
v7-0001-Add-begin-end-hooks-for-timed-wait-events.patchapplication/octet-stream; name=v7-0001-Add-begin-end-hooks-for-timed-wait-events.patchDownload+63-1
v7-0002-Convert-wait_start-end-call-sites-to-the-timed-pa.patchapplication/octet-stream; name=v7-0002-Convert-wait_start-end-call-sites-to-the-timed-pa.patchDownload+205-206
#15Dmitry Fomin
fomin.list@gmail.com
In reply to: Dmitry Fomin (#14)
Re: [PATCH v1 0/7] Wait event timing and tracing instrumentation

Hi hackers,

This is v11 of the timed-wait-event series, five patches. The number
jumps from v7 because three rounds in between were measured but
never posted: v8 was the first version with the contrib module. v9
and v10 tried small changes to the null-hook and attach paths, but
the changes were too small to measure. Everything they found is
folded into these five patches.

0001 and 0002 are the pair posted as v7, unchanged. 0003 is a new
test module for the hook contract. 0004 and 0005 are the collector,
contrib/pg_wait_event_tracing, statistics level and trace level.

Below: what changed since v7, one bare-metal run of numbers, what
v11 does about the review comments, what is left out on purpose,
and where the evidence is.

What changed since v7
======================

The collector is now contrib/pg_wait_event_tracing. Patch 0004 adds
the statistics level. Patch 0005 adds the trace level. The defects
found during the v6 review are fixed. Patch 0003 adds a new test
module for the hook contract.

The hook has two states: no consumer installed, or a consumer
installed. With no consumer, each side of the timed pair is one
branch, marked unlikely(). The work for an installed consumer, the
depth guard and the indirect call, lives in two separate functions,
marked cold, out of the main code path.

The branch hint is needed. GCC's branch predictor assumes a pointer
compared with NULL is not NULL. Without the hint, GCC puts the
consumer's code on the fast path and the no-consumer case on the
slow path, the wrong way round. I checked this with disassembly.
Across seven functions, on x86-64 with GCC 13, the code without the
hint adds 20 to 44 instructions per function over master. With the
hint, and the consumer's code moved out of line, that is 4 to 7
instructions. One function, pgaio_io_perform_synchronously, also had
4 extra stack references without the hint; with the hint, it has 0,
the same as master. In 10 of the 12 call sites checked, the
no-consumer path is a plain fall-through, with no jump at all.

The module now turns its hooks on only when needed. It installs them
the first time a process sets pg_wait_event_tracing.capture to
anything other than off, and never removes them. A process that
never turns capture on costs the same as a process with no collector
loaded.

The last change moves when the collector writes down a finished
wait.

Before this change, the collector did its accounting the moment
LWLockAcquire() returned control to the caller. That is inside the
lock's critical section: the backend already holds a lock other
backends are waiting for. In that moment, the collector read the
clock, found the right slot, and updated the counters. Every backend
waiting for that lock paid for this work, on top of its own wait.

Now, at wait_end, the collector only reads the clock and saves one
record, the event, the duration, and the timestamp, in a slot local
to the backend. The counters are updated later: at the next
wait_start, or at any point where order matters, such as a capture
change, a reset, a release, a process exit, or the backend reading
its own data.

The durations, the values, and the order are the same as before.
What changes is when another backend can see a finished wait (it
waits until the next such point), and that a crash can lose at most
one saved record, the one not yet written when the backend is
killed.

I measured this. On the same server, with the same kit, an earlier
run of this series, before this change, showed about 6.6% less TPS
with capture on than with the module loaded but off, on the
lock-storm workload. With the change, on the same workload, stats
versus module-off is -3.7% [-6.4%, -1.0%] in this run. The finding
and the decision are written up in DECISION-deferred-accounting.md,
which is in the evidence package. I decided to keep this change in
v11.

Measurements
============

Host: a two-socket Intel Xeon server, 32 physical cores per socket,
SMT off, turbo off, Rocky Linux 8, GCC 8.5. Server and pgbench each
pinned to disjoint physical cores of one NUMA node.

Protocol: seven configurations, master, master-aa, control,
hook-null, module-off, stats, trace. The layout control strips the
hook-test and slow-path call back out of the timed pair, leaving
only the volatile store. master-aa is a second, independently built
master run, its own installation: the A/A twin. All seven run
against five workloads, W1 the microbenchmark and W3/W4/W5/W6c
pgbench-driven, 16 repetitions in a randomized complete-block
schedule built from a 7x7 Latin square.

Every contrast gets two estimators, a paired Student t interval and
an exact Wilcoxon signed-rank Hodges-Lehmann interval. A contrast
counts as resolved only when both agree. I compute an A/A floor per
workload. A plateau probe picks the dataset/initdb variant before
the run starts (pinned spread 0.444%, unpinned spread 0.568%). Every
cell also carries its own mode proof, module presence, capture
level, hook-installed state, so a misconfigured cell fails closed
instead of silently contaminating a contrast.

One bare-metal run: 2026-09-23, tag wet-v11-run2, tip e26e6333322.

Overhead against vanilla master, positive means slower. W1 is ns per
timed pair on the already-set latch. The rest is percent of TPS,
paired means over 16 blocks. Intervals follow below:

A/A twin (noise floor) vs vanilla
W1 0.0 W3 +0.9 W4 +0.1 W5 -0.3 W6c -0.1

layout control vs vanilla
W1 -1.9 W3 -1.8 W4 0.0 W5 -1.0 W6c -0.6

hook-null (patch, no module) vs vanilla
W1 -0.2 W3 -2.6 W4 -0.2 W5 -0.6 W6c -0.5

module-off (loaded, capture off) vs vanilla
W1 -0.3 W3 -0.4 W4 -0.1 W5 -0.6 W6c -0.4

stats (capture on) vs vanilla
W1 +36.6 W3 +3.3 W4 -0.1 W5 -0.2 W6c +0.4

trace (capture on) vs vanilla
W1 +38.1 W3 +3.1 W4 +1.0 W5 +0.3 W6c +0.5

The exact contrasts behind that table, with intervals, follow.
Disabled modes first: hook-null versus its layout control, and
module-off (collector loaded, capture off) versus hook-null. Paired
95%:

W1, already-set latch, ns per pair:
hook-null vs control: +1.63 [+1.48, +1.77]
module-off vs hook-null: -0.05 [-0.17, +0.06]

W3, deterministic short ProcArrayLock wait, 8 clients, percent TPS:
A/A floor (master-aa vs vanilla): 2.3
hook-null vs control: +0.8 [-0.4, +2.0]
module-off vs hook-null: -2.2 [-4.7, +0.2]

W4, select-only, 16 clients, percent TPS:
A/A floor (master-aa vs vanilla): 0.51
hook-null vs control: +0.26 [-0.31, +0.83]
module-off vs hook-null: -0.07 [-0.57, +0.43]

W5, TPC-B, 16 clients, percent TPS:
A/A floor (master-aa vs vanilla): 0.29
hook-null vs control: -0.40 [-0.72, -0.07]
module-off vs hook-null: -0.06 [-0.53, +0.41]

W6c, select-only, 32 clients, eviction layout, percent TPS:
A/A floor (master-aa vs vanilla): 0.24
hook-null vs control: -0.07 [-0.50, +0.36]
module-off vs hook-null: -0.10 [-0.48, +0.27]

The deferral touches neither of these paths.

Now the enabled modes, stats versus module-off and trace versus
module-off, where the collector's cost shows. Paired 95%:

W1, ns per pair:
stats vs module-off: +36.85 [+36.75, +36.94]
trace vs module-off: +38.35 [+38.26, +38.44]

W3, percent TPS:
stats vs module-off: -3.7 [-6.4, -1.0]
trace vs module-off: -3.5 [-5.7, -1.2]
lock-wait rate: 406k/s, about 5 per transaction

W4, percent TPS:
stats vs module-off: -0.05 [-0.58, +0.47]
trace vs module-off: -1.1 [-1.7, -0.6]

W5, percent TPS:
stats vs module-off: -0.31 [-0.64, +0.02]
trace vs module-off: -0.88 [-1.31, -0.44]

W6c, percent TPS:
stats vs module-off: -0.79 [-1.18, -0.40]
trace vs module-off: -0.98 [-1.31, -0.65]

The W6c persistent-backend crossover: 16 sessions, patched
installation only, no build pairing, capture toggled by reload
within each session. stats vs off -0.54 [-0.66, -0.41], trace vs off
-1.03 [-1.16, -0.90], off versus off placebo (both sides
uninstrumented, a null check) -0.14 [-0.33, +0.05]. The suitability
gate passed.

W3 is the worst case here, both for stats versus module-off and for
trace versus module-off.

What v11 does about the review comments
========================================

Thank you to Andrey, Andres and Michael for their comments on this
and the earlier threads. Below is what v11 does about each point.
Whether this is enough is for the reviewers to judge.

1. Andrey asked, on 2026-09-05, for five configurations: master,
compiled in but off, a null hook, the module preloaded with
capture off, and capture on. There is no compile-time switch in
v11: no configure flag, the hook is always compiled in.
hook-null is the closest match to the compiled-in-but-off
configuration. I also added a layout control and a second,
independent master build (master-aa), both in the table above.

2. Andrey also asked about extra instructions or register spills at
the hot call sites. I looked at the x86-64 GCC 13 disassembly of
seven functions. Without the branch hint, the code adds 20 to 44
instructions per function over master, and one function,
pgaio_io_perform_synchronously, gets 4 extra stack references.
With the hint, and the consumer's code moved out of line, that is
4 to 7 instructions, and the stack references go back to
master's count. The kit repeats the same disassembly on the
benchmark host, with GCC 8.5, and it is in the evidence package.

3. Andrey pointed at the already-set-latch path, where
WaitEventSetWait reports the wait before it checks the latch.
W1's latch_set test is that path. hook-null minus control there
is +1.63 ns [+1.48, +1.77].

4. Andrey set out the hook contract: only preallocated backend-local
state, no waits, no allocations, no locks, no errors, and a
recursion guard in core. That contract is written in 0001's
header comment. The depth guard enforces it in core, and 0003's
tests cover the chaining order, the depth guard, a NULL hook, and
the error path.

5. Andrey asked how this would sit next to EXPLAIN WAITS. The
chaining order is fixed: the previous begin hook runs before a
new consumer's own begin, and the new consumer's own end runs
before the previous end. 0003 tests this with two consumers, in
both installation orders. Thank you for pointing at Ilmar's
thread [0]/messages/by-id/cover.1778280923.git.tanswis42@gmail.com. I am happy to align on one API with that thread.

6. Andrey asked about the memory cost of the earlier design. In
v11, one small control entry sits in shared memory per possible
ProcNumber, 56 bytes on x86-64, all the time. The larger payload,
about 200 KiB (212,664 bytes at the default 192 LWLock tranches),
is allocated only for a backend that turns capture on, at a safe
point, and released when capture turns off or the backend exits.

7. Andrey asked where this should live. It is a contrib module, so
it ships with core and is available on managed services the same
way pg_stat_statements is. There is no configure-time flag.

8. Andres wrote [1]/messages/by-id/uah2s5tppv3onn7bsf2uelyexfrxwrmye6qqyrbbsjepxny7l5@guymflaarnsr that he was against adding even a single cycle
to wait events. pgstat_report_wait_start() and
pgstat_report_wait_end(), the plain pair, are byte-identical to
master in every build here. Two of the W1 rows, report_only and
usleep0, use only that plain pair. Across every configuration, in
this run, they show a 0.00 ns difference from master.

9. Andres also wrote [2]/messages/by-id/sofkrmi3skg3ekc3y23uwxscbviy5lcbukincoyauypg4ylfdg@6lwzhi6uagc7 that a hook like this should be opt-in per
call site, should count encounters as well as duration, and that
each converted site should be justified. The timed pair is that
opt-in shape. The collector records count, total, max, and a
log2 histogram per event. Each site pays the same one predicted
branch: measured alone, that is +1.63 ns per pair, and it does
not show up as a real difference against the layout control on
any pgbench workload here.

10. Andres also raised sampling as an alternative. As Andrey said in
the same thread, sampling (EXPLAIN WAITS) and exact per-wait
timing answer different questions, and both can use the same
hook. Nothing here rules out a sampling collector.

11. Michael raised [3]/messages/by-id/alQ60-N2n8NzVw5g@paquier.xyz that new wait events could get harder to add.
A new site that uses the plain pair is unaffected. A site that
wants timing uses the timed pair, and pays one branch when no
consumer is installed. This is written up in xfunc.sgml.

What is left out of v11 on purpose
==================================

Three things stay out. All were noted since v8, and I have not
reconsidered them here.

- No end-of-message marker for the pipelined extended protocol.
Nothing in postgres.c fires when one queued Bind/Execute ends
and the next begins, so a queued statement's boundary is
inferred from the next statement's own QueryStart.
- No trace rings for a server-side process before a reload. The
always-on reserved region covers per-class statistics only,
about 10 MiB at default settings. A ring per such process would
be roughly 4 MB each. That is why crash-recovery tracing
specifically is still a gap.
- No C-level reader API. SQL functions are the only supported
interface. This also closes v6 defect 7.

The deferral changes two things, each bounded.

Visibility latency: a completed wait becomes visible to a reader at
the next flush point, not immediately. In practice that is
microseconds away, and at the latest the end of the current
statement, because going idle is itself a timed wait. For a
server-side process that blocks without a timeout, such as a
caught-up standby's startup process waiting for WAL, the most recent
completed wait becomes visible only when that process's next wait
begins. This is documented in the module page.

Crash loss: if a backend is killed, not exited, between a wait
ending and the next flush, that one pending record is lost.
Statistics are released at exit regardless, so this only matters for
a post-mortem trace ring. Only the crashed backend's single most
recent wait can be missing. If someone has a consumer in mind for
which this visibility rule is a problem, I would like to hear about
it.

Where the evidence is
======================

The tip I measured, e26e6333322, and the tip I posted,
382f87e07f8, differ only in two TAP tests and one documentation
sentence. No C code changed between them.

The run is published, sanitized, on branch v11-evidence-20260923
(raw archives, matrix and crossover analysis, host check,
disassembly, early A/A gate log):

https://github.com/DmitryNFomin/postgres/tree/v11-evidence-20260923

The kit itself (scripts, workloads, the analyzer, the runbook) is on
branch v11-notes:

https://github.com/DmitryNFomin/postgres/tree/v11-notes

The raw archive, the analysis, the disassembly, the kit, and the
patches are also together in one downloadable package:

https://github.com/DmitryNFomin/postgres/releases/tag/v11-evidence-20260924
(wait-event-v11-evidence-20260924.tar.gz, 3,736,151 bytes, SHA-256
fe46d50a67d01583bc8b92e333366a1b40450fa5add4b6c04ec6b58004a5c667)

The five patches are also up as a pull request:

https://github.com/DmitryNFomin/postgres/pull/2

Every number above comes from the raw archive, through the kit's own
analyzer.

Attachments
===========

v11-0001 begin/end hook pair, wait_event.h/.c
v11-0002 call-site conversion to the timed pair
v11-0003 test_wait_hook, the hook-contract test module
v11-0004 pg_wait_event_tracing, statistics level
v11-0005 pg_wait_event_tracing, trace level

Nothing else attached, for cfbot.

[0]: /messages/by-id/cover.1778280923.git.tanswis42@gmail.com
[1]: /messages/by-id/uah2s5tppv3onn7bsf2uelyexfrxwrmye6qqyrbbsjepxny7l5@guymflaarnsr
[2]: /messages/by-id/sofkrmi3skg3ekc3y23uwxscbviy5lcbukincoyauypg4ylfdg@6lwzhi6uagc7
[3]: /messages/by-id/alQ60-N2n8NzVw5g@paquier.xyz

Regards,
Dmitry Fomin

Attachments:

t248739_15
v11-0001-Add-begin-end-hooks-for-timed-wait-events.patchapplication/octet-stream; name=v11-0001-Add-begin-end-hooks-for-timed-wait-events.patchDownload+105-1
v11-0005-pg_wait_event_tracing-trace-level.patchapplication/octet-stream; name=v11-0005-pg_wait_event_tracing-trace-level.patchDownload+3146-133
v11-0002-Convert-wait_start-end-call-sites-to-the-timed-p.patchapplication/octet-stream; name=v11-0002-Convert-wait_start-end-call-sites-to-the-timed-p.patchDownload+205-206
v11-0004-pg_wait_event_tracing-statistics-level.patchapplication/octet-stream; name=v11-0004-pg_wait_event_tracing-statistics-level.patchDownload+5988-2
v11-0003-test_wait_hook-test-module-for-the-wait-event-ho.patchapplication/octet-stream; name=v11-0003-test_wait_hook-test-module-for-the-wait-event-ho.patchDownload+791-7
#16Dmitry Fomin
fomin.list@gmail.com
In reply to: Dmitry Fomin (#15)
Re: [PATCH v1 0/7] Wait event timing and tracing instrumentation

Hi hackers,

This is v12 of the timed-wait-event series, five patches. It is a
follow-up to my v11 mail:

What changed since v11
======================

Patches 0001, 0002 and 0003 are byte-identical to v11. Their
patch-ids are the same.

In 0004, only three TAP tests changed: 002_ownership, 003_reset_acl
and 004_reset_race. Each of them reads the statistics of another
backend right after that backend ran its last statement. Since the
deferred accounting in v11, the last wait of a backend becomes
visible only when its next wait starts. This is the visibility lag
that the documentation already describes. One run on my fork's CI
failed on Linux with Autoconf because of it. The tests now poll
until the value appears. There is no code change in 0004.

0005 changes pg_wait_event_trace_by_statement(), the SQL function
that assigns the waits of a trace to statements. It adds a column
named phase, a new bucket named <no-statement>, documentation, new
regression cases and three new tests. There are also two new comments
in the C file. There is no change in compiled code.

The v11 tests checked this function with one simple-protocol query.
That is why they did not find the problems below. The new tests are:

- t/014_by_statement_pre.pl: a second session holds a lock while a
statement waits in Bind or in parse analysis, and a lock_timeout
during Bind.
- t/015_by_statement_pgbench.pl: pgbench with -M simple, extended
and prepared, while a second session holds a table lock for a
short time. No wait except Client waits may stay in <idle>, and
every lock wait must be inside a statement.
- t/016_by_statement_oracle.pl: a fixed script of statement shapes
(extended protocol, prepared statements, pipeline, explicit
transaction, DO, CALL with COMMIT, an error). The test takes the
expected query_id of each statement from EXPLAIN (VERBOSE), not
from the trace, and checks that each wait is in the right
statement.

I checked that these tests catch the bugs: with the v11 function,
both 015 and 016 fail.

Why: a lock-convoy test
=======================

While testing v11 against a lock-convoy workload, I found two
problems in by_statement(). The workload was 16 pgbench clients with
-M prepared, running a single-row lookup at 8,000 requests per
second. Autovacuum truncated the table and held ACCESS EXCLUSIVE in
bursts of about 20 ms. Capture was set to trace.

Problem 1: an execution of a named prepared statement over the
extended protocol writes ExecStart, but no QueryStart. The function
opened a bucket only on QueryStart and UtilityStart, so all the waits
of such a statement went to <idle>. The C marker code already treats
ExecStart as a start. The SQL function did not.

Problem 2: some waits happen after the request has arrived but before
the start marker is written. These are relation locks taken in Bind
by the plan cache, or in parse analysis with the simple protocol. By
position, they went to <idle>. In my run, these were exactly the
convoy waits: 208 Lock:relation waits, 38.9 ms on average, 101.5 ms
at most.

What v12 does
=============

The rule is written in full in the new documentation section. In
short:

- A top-level ExecStart opens its own bucket, unless a QueryStart
or UtilityStart of the same statement has opened one already.
- The statements run inside a top-level DO, CALL, EXECUTE or
CREATE TABLE AS stay in the bucket of that statement.
- Waits between the last Idle, TxnCommit or TxnAbort marker and the
start marker of a statement belong to that statement, with
phase = 'pre'. ClientRead stays in <idle>.
- A request that fails before any statement starts, for example
with lock_timeout during Bind, goes to <no-statement>.
- Also fixed: in v11, a utility statement sent through the simple
protocol got two buckets.

Four ambiguous cases remain. The documentation lists them:

- Without a commit between two statements of a transaction or
pipeline, the waits between them stay with the earlier statement.
- If the next request was already buffered, so no Idle was written,
the work after TxnCommit counts as 'pre' of the next statement.
- An error caught by a PL/pgSQL EXCEPTION block inside a utility
statement can leave the count of open utility statements too
high, until the next Idle or TxnAbort.
- With several Parse messages pipelined ahead of their Executes,
the first Execute is attributed to the last Parse.

Results with v12
================

Same workload as above, three runs with capture = trace. I checked
all three runs against the raw data.

  run   convoy         avg / max     in statement    lookup
        Lock:relation  ms            buckets,        DataFileRead
        waits                        phase 'pre'     in <idle>
  ---   -------------  ------------  --------------  ------------
   1    144            45.2 / 107.1  144             0
   2    208            38.8 / 110.1  208             0
   3     32            94.1 / 119.2   32             0

Only ClientRead remains in <idle>. In run 1, 231 executions started
right after a commit with no Idle marker in between, because the next
request was already buffered. Those of them that had waits each got
their own bucket.

In these runs I also used a sampling profiler, pg_wait_sampling, at
10 ms. It attributed none of the Lock:relation time to the lookup
query: in its per-query profile the share was 0.00%. log_lock_waits
stayed silent too, because each wait was shorter than
deadlock_timeout. Sampling and exact per-wait timing answer different
questions, as the v11 mail already said; this case is one where the
exact per-statement view adds something.

Cost
====

The numbers from v11 stand for v12. The measured path has not
changed: 0001 to 0003 are identical, and neither 0004 nor 0005
changes compiled code. The benchmark never calls by_statement(). The
numbers are in my v11 mail and in the evidence release:

https://github.com/DmitryNFomin/postgres/releases/tag/v11-evidence-20260924

One more observation, which is not a benchmark. In the convoy test,
requests slower than 15 ms showed no visible difference between
capture = trace and capture = off over four runs of each. With n = 4
and a large spread from autovacuum's lock time, it cannot show a
small difference.

CI and attachments
==================

The full CI of my fork is green on the tip, f8abf1f0cc9:

https://github.com/DmitryNFomin/postgres/actions/runs/39804808641

The series is at https://github.com/DmitryNFomin/postgres/tree/wet-v12
and is based on 311df1dc039, as v11. Attached: the five v12 patches
only.

Regards,
Dmitry Fomin

Attachments:

t248739_16
v12-0002-Convert-wait_start-end-call-sites-to-the-timed-p.patchapplication/octet-stream; name=v12-0002-Convert-wait_start-end-call-sites-to-the-timed-p.patchDownload+205-206
v12-0001-Add-begin-end-hooks-for-timed-wait-events.patchapplication/octet-stream; name=v12-0001-Add-begin-end-hooks-for-timed-wait-events.patchDownload+105-1
v12-0004-pg_wait_event_tracing-statistics-level.patchapplication/octet-stream; name=v12-0004-pg_wait_event_tracing-statistics-level.patchDownload+6013-2
v12-0005-pg_wait_event_tracing-trace-level.patchapplication/octet-stream; name=v12-0005-pg_wait_event_tracing-trace-level.patchDownload+4595-159
v12-0003-test_wait_hook-test-module-for-the-wait-event-ho.patchapplication/octet-stream; name=v12-0003-test_wait_hook-test-module-for-the-wait-event-ho.patchDownload+791-7
#17Dmitry Fomin
fomin.list@gmail.com
In reply to: Dmitry Fomin (#16)
Re: [PATCH v1 0/7] Wait event timing and tracing instrumentation

Hi hackers,

v13 of the timed-wait-event series: v12 rebased on current master
(4848fd6752a), because cfbot reported a conflict. There is no other
change.

The conflict came from a5ccf20801c (Fix partial two-phase files left
by crash during checkpoint). It changed error messages in
RecreateTwoPhaseFile() next to a wait call that 0002 converts. I kept
the new messages and the timed call. 0001, 0003, 0004 and 0005 are
the same as in v12. In 0002, only the context in twophase.c changed.

The overhead numbers from v11 still apply: the measured code did not
change. The full CI of my fork is green on the tip, d82aacc9054:

https://github.com/DmitryNFomin/postgres/actions/runs/40688301368

v12 mail, with the description of the changes since v11:

/messages/by-id/CAPHG-0moXufhp9ssiVHE+83A7SyTApTxf4zMr4h7X6+tEDTrEQ@mail.gmail.com

Attached: the five v13 patches only.

Regards,
Dmitry Fomin

Attachments:

t248739_17
v13-0001-Add-begin-end-hooks-for-timed-wait-events.patchapplication/octet-stream; name=v13-0001-Add-begin-end-hooks-for-timed-wait-events.patchDownload+105-1
v13-0003-test_wait_hook-test-module-for-the-wait-event-ho.patchapplication/octet-stream; name=v13-0003-test_wait_hook-test-module-for-the-wait-event-ho.patchDownload+791-7
v13-0005-pg_wait_event_tracing-trace-level.patchapplication/octet-stream; name=v13-0005-pg_wait_event_tracing-trace-level.patchDownload+4595-159
v13-0002-Convert-wait_start-end-call-sites-to-the-timed-p.patchapplication/octet-stream; name=v13-0002-Convert-wait_start-end-call-sites-to-the-timed-p.patchDownload+205-206
v13-0004-pg_wait_event_tracing-statistics-level.patchapplication/octet-stream; name=v13-0004-pg_wait_event_tracing-statistics-level.patchDownload+6013-2
#18Andres Freund
andres@anarazel.de
In reply to: Dmitry Fomin (#17)
Re: [PATCH v1 0/7] Wait event timing and tracing instrumentation

Hi,

On 2026-10-28 15:02:57 +0100, Dmitry Fomin wrote:

Subject: [PATCH v13 1/5] Add begin/end hooks for timed wait events

I continue to be dead set against that. There's no way it's ok to just inject
arbitrary code into all the places that use wait events. Nor do I think it's
ok to increase the code size of all the places that use wait events.

Add wait_event_begin_hook and wait_event_end_hook, called from a new
pair of reporting functions, pgstat_report_wait_start_timed() and
pgstat_report_wait_end_timed(), which an instrumented call site uses
in place of the ordinary pair. The ordinary pgstat_report_wait_start()
and pgstat_report_wait_end() are left exactly as they were, so an
uninstrumented site, and any build with no extension loaded, behaves
as it does today.

That's not a lot of consolation if your next patch converts everything to the
new way:

Route the existing direct wait annotations in src/backend and src/common
(94 starts and their 111 end and cleanup calls) through the explicitly
instrumented pair, so that the hooks cover every wait site the backend
executes today. The two src/common sites are the control file write and
sync in update_controlfile(), inside the existing #ifndef FRONTEND
guards; frontend builds of that file are unchanged.

I suggested earlier that it might be ok to convert *some* cases to timed wait
events, with one-by-one analysis why that's the best thing for that specific
wait event. I sure as hell didn't mean "all" by that.

I frankly just don't buy the premise that tracking the timing of wait events
alone is a useful exercise. Tracking the amount of time in specific wait
events is pretty much *useless* without other information. Knowing that
there's a lot of time in ClientRead doesn't tell you anything useful, without
also knowing whether it's in the middle of a statement or such. Even leaving
that extreme case aside, most of the other wait events aren't useful without
knowing how much time was spent doing CPU work.

Once you record with sufficient detail, you end up with the overhead being too
high to do this on every wait event.

I think it'd be much more valuable to have something that efficiently sampling
wait events at a configurable frequency, grouping it with information state,
backend_type, whether in a transaction, in a query.

Greetings,

Andres Freund

#19Dmitry Fomin
fomin.list@gmail.com
In reply to: Andres Freund (#18)
Re: [PATCH v1 0/7] Wait event timing and tracing instrumentation

Hi Andres,

Thank you for the reply.

On 2026-10-28 16:19:46, Andres Freund wrote:

I suggested earlier that it might be ok to convert *some* cases to timed wait
events, with one-by-one analysis why that's the best thing for that specific
wait event. I sure as hell didn't mean "all" by that.

You are right, and I am sorry. In the v11 mail I wrote that the timed pair
is the opt-in shape you asked for. That was my wrong reading of "some".

I still convert all sites, and the reason is the use case. To explain one
slow request, the waits inside it have to be recorded. If only some sites
are timed, the time in the other waits ends up in the unexplained part of
the request, and the answer can be wrong in the same way as with a
sampler.

I frankly just don't buy the premise that tracking the timing of wait events
alone is a useful exercise. Tracking the amount of time in specific wait
events is pretty much *useless* without other information. Knowing that
there's a lot of time in ClientRead doesn't tell you anything useful, without
also knowing whether it's in the middle of a statement or such.

I agree that timing alone is not enough. The trace level keeps, per
backend, a fixed-size ring of waits with their durations, together with
statement start/end, transaction commit/abort and idle markers (old
records are overwritten when the ring is full).
pg_wait_event_trace_by_statement() groups the recorded waits by statement
execution, with its query id:

- ClientRead after a statement has ended goes to an <idle> bucket, not to
any statement. ClientRead while a statement is still running stays with
that statement.
- The commit and abort markers are in the trace too, so ClientRead inside
an open transaction (the client is slow to send the next statement) can
be told apart from idle time outside a transaction. In the checkout test
below, ClientRead inside the transaction was the main wait in 192 of the
579 rebuilt requests over 5 ms. The SQL function does not do this split
yet; I did it from the trace.
- Waits before execution starts, such as lock waits at Bind and plan cache
revalidation, go to the following statement with phase = 'pre'. The
function's comments list the cases where attribution is still
ambiguous, for example pipelined Parse messages.
- The trace has the start and end time of each statement (while they are
still in the ring), so the time not covered by recorded waits can be put
next to the wait time. This is not CPU time. If the process is throttled
(cgroup CPU quota), the patch cannot see it, and the throttling can make
recorded waits longer.

Once you record with sufficient detail, you end up with the overhead being too
high to do this on every wait event.

Recording is off by default and is turned on by a GUC. The v11
bare-metal run (two-socket Xeon, 16 repetitions in randomized blocks)
measured it, with the module loaded and capture on, against unpatched
master:

- microbenchmark, already-set latch: +36.6 ns per wait start/end pair
(statistics level), +38.1 ns (trace level);
- a lock-heavy workload (about 5 lock waits per transaction): 3.3% (stats)
and 3.1% (trace) lower TPS;
- three other pgbench workloads: from 0.2% higher to 1.0% lower TPS.
Against the same build with capture off, trace costs 0.9% to 1.1% TPS
there, and statistics 0.05% to 0.8%.

These are v11 numbers. v12 to v14 changed the recording path only a
little (statement markers, errno), and I have not re-run the full
matrix. So the cost of recording is real, it is paid only while capture
is on, and on these workloads it is small enough to turn capture on
during an incident. The numbers, the method and the scripts are here:
https://github.com/DmitryNFomin/postgres/tree/v11-evidence-20260923

I think it'd be much more valuable to have something that efficiently sampling
wait events at a configurable frequency, grouping it with information state,
backend_type, whether in a transaction, in a query.

A sampler like that would be useful and I would be happy to see it. I
work as a DBA, and pg_wait_sampling is the first tool I open in an
incident. I am not proposing to replace sampling. This patch comes from
incidents where sampling was not enough, or pointed in the wrong
direction, and the team spent time fixing the wrong thing.

A sample does not give the exact number of waits, the duration of each
wait, or where one request ends and the next one starts. A higher rate
gives better estimates, not exact values per request.

Two examples where this mattered, reproduced on test VMs with plain SQL
workloads (no pg_sleep, no injected delays), each compared with
pg_wait_sampling at its defaults:

1. Slow-request tail. A checkout (16 clients, 2,000 requests/s, ~1.5 ms
average) has a tail of slow requests. A background job updates 100 rows
in one statement of about 17 ms and holds the row locks until it ends.
Sampled profile of the request backends, without ClientRead: WALWrite
33%, WalSync 29%, no wait event ("CPU") 22%, DataFileRead 12%,
Lock:transactionid 2.3%. The two changes this ranking suggests did not
fix the tail: with synchronous_commit = off the mean went from 1.51 to
0.83 ms and requests over 5 ms from 972 to 688, but p99.9 was 11.4 ms
against 11.0 ms before; with 2 GB of prewarmed shared buffers, 954
requests over 5 ms and p99.9 11.3 ms.
With exact per-wait timing, each request in the trace can be rebuilt
from its waits. In 65% of the 286 rebuilt requests over 8 ms,
Lock:transactionid is more than half of the request's server time
(patch; 75% of 189 in a separate run with an external eBPF tracer on
stock PG18). Splitting the job into 5-row statements cut requests over
5 ms from 972 to 388 and p99.9 from 11.0 to 5.8 ms.
The sampler's per-query view does show that the UPDATE waits on locks
(41% of its samples). What it cannot show is that these waits, and not
the commit path, make the slow requests slow: the mean hardly changes
(about 1.5 ms before and after the fix). The aggregate profile ranks
WAL and reads far above locks, and the changes it points to leave
p99.9 where it was.

2. Count vs duration. A primary-key lookup with statement_timeout = 10ms,
made slow once by a noisy neighbour on the disk and once by bloat.
Sampled profile: DataFileRead about 97% vs 95%, the same picture. Exact
timing per cancelled execution: 1.9 DataFileRead waits of 7.5 ms
average vs 31.7 waits of 427 us average, so storage vs bloat at once.
pg_stat_statements and pg_stat_io can separate the two as well, when
the completed calls show the same pattern; pg_stat_statements does not
count the cancelled executions.

Scripts, raw data and a README for each case:
1. https://github.com/DmitryNFomin/pg_wait_tracer/tree/master/tests/results/checkout-tail-20261022
2. https://github.com/DmitryNFomin/pg_wait_tracer/tree/master/tests/results/case4-case5-patch-20261022
and https://github.com/DmitryNFomin/pg_wait_tracer/tree/master/tests/results/truncation-and-cancelled-20261022

I continue to be dead set against that. There's no way it's ok to just inject
arbitrary code into all the places that use wait events. Nor do I think it's
ok to increase the code size of all the places that use wait events.

On the hook itself: the rules for a hook are written in wait_event.h (no
allocation, no locks, no waiting, no errors; a depth guard stops
re-entry, and errno is now preserved for the caller), and test_wait_hook
tests them. Reviewing the module against these rules found one call that
could raise an error from inside a hook; v14 removes it (see below).

I also measured what the hook costs when nothing is loaded, not only in
time. Builds of master (4848fd6752a) vs master + 0001 + 0002, -O2, no
extension loaded, x86-64 with GCC 8.5, GCC 13.3 and clang 21:

- At each converted site, start and end each get one test of the hook
pointer, marked unlikely(). The call itself is in a cold out-of-line
function.
- The postgres binary text grows by 0.097% (GCC 8.5), 0.105% (GCC 13) and
0.074% (clang), each compared with the same compiler's master build.
With GCC, about half of the bytes added to the changed functions are in
their .cold parts. The functions I checked that have no converted site
(ProcSleep, WaitLatch, secure_read and others) keep the same size and
stack use.
- The disassembly confirms your register pressure concern: with GCC,
FileReadV and FileWriteV grew their frame from 24 to 40 bytes, because
GCC kept the hook address in a stack slot over the EINTR loop.
LWLockAcquire had no extra spills. Summed over all functions with
converted sites, the static count of stack loads and stores is a bit
higher than in master.

In v14 I put the hook pointers in the same struct as my_wait_event_info,
so the check uses the base register that is already loaded for the wait
event store. With that, FileReadV and FileWriteV are back to a 24-byte
frame with GCC 8.5 and GCC 13, and the text growth is +0.085% / +0.052% /
+0.021% (GCC 8.5 / GCC 13 / clang) against master. These numbers come from
a build of the same change on 4848fd6752a. The summed stack load/store
count does not go down with it, so this fixes the case I found, not every
possible effect. The old names stay as macros, so existing source code
compiles unchanged.

On time, from the same v11 run with 0001 + 0002 and no extension
loaded: against a control build without the hook tests, the
microbenchmark shows +1.63 ns per start/end pair (95% interval 1.48 to
1.77 ns), and none of the four pgbench workloads shows a slowdown.

Changes in v14, besides a rebase onto current master (no conflicts):

- 0001: the struct layout above. The two out-of-line hook functions now
save and restore errno, because callers check errno after the end call
(EINTR retry in FileReadV and others); test_wait_hook tests this.
- 0004: the module no longer calls before_shmem_exit() from inside the
begin hook. That call can raise FATAL when its list is full, which the
hook rules do not allow. Registration now happens only at a safe point.
- Docs and comments no longer say that the timed pair has no cost; they
describe the one pointer test per start and end.

CI:
https://github.com/DmitryNFomin/postgres/actions/runs/41482982301

I understand this does not make the cost zero, and I do not want to argue
that a small cost is no cost. My point is that it buys answers that
sampling does not give, for problems DBAs meet in production.

I tried to answer each concrete concern with code or with measurements.
If there is a wait site where a hook can do real harm, or a cost I have
not measured, please point me to it, and I will measure it or change the
patch.

Regards,
Dmitry

Attachments:

t248739_19
v14-0002-Convert-wait_start-end-call-sites-to-the-timed-p.patchapplication/octet-stream; name=v14-0002-Convert-wait_start-end-call-sites-to-the-timed-p.patchDownload+205-206
v14-0003-test_wait_hook-test-module-for-the-wait-event-ho.patchapplication/octet-stream; name=v14-0003-test_wait_hook-test-module-for-the-wait-event-ho.patchDownload+874-7
v14-0001-Add-begin-end-hooks-for-timed-wait-events.patchapplication/octet-stream; name=v14-0001-Add-begin-end-hooks-for-timed-wait-events.patchDownload+139-4
v14-0004-pg_wait_event_tracing-statistics-level.patchapplication/octet-stream; name=v14-0004-pg_wait_event_tracing-statistics-level.patchDownload+6016-2
v14-0005-pg_wait_event_tracing-trace-level.patchapplication/octet-stream; name=v14-0005-pg_wait_event_tracing-trace-level.patchDownload+4589-159
#20Dmitry Fomin
fomin.list@gmail.com
In reply to: Dmitry Fomin (#19)
Re: [PATCH v1 0/7] Wait event timing and tracing instrumentation

Hi hackers,

v15 is attached: 9 patches, based on cea96cdfecb.

0001, 0002 and 0003 (the core hooks, the call-site conversion and
test_wait_hook) are unchanged since v14. The patch content is
identical. I did not touch core code for v15. The two kinds of change
are below: fixes in 0004 and 0005, and four new patches, 0006 to 0009.

Fixes since v14 (0004 and 0005)

0004, statistics level:

- The lookup of an LWLock tranche in a backend's table stopped after 8
probes once the table was full. A tranche that was already admitted
but sat farther from its home slot was not found. The lookup now
probes the whole table. A new test (008_lwlock_table) builds a full
table with colliding keys. With the old probe limit put back, it
fails.
- A backend reading the statistics of another backend copied the
payload without a consistency check. A tranche insertion or a reset
by the owner could run during the copy. The payload now has a
sequence counter (seqlock). The reader accepts a copy only if the
counter was even and unchanged, and it checks the table bounds
before using the copy. The owner pays this only on rare structural
changes (new tranche, reset), not at wait begin or end.
- A fixed-slot process that was disabled and then claimed its slot
again got the same pid and start token back, so a reader could not
see that the payload had been re-initialized in between. The
re-initialization now advances the counter, which never goes back.
- New test 009_stats_stress: producers, resetters and readers run
together. It checks that nothing crashes or hangs, that no row shows
a tranche of another process, and that one producer's own rows are
exact.

0005, trace level (test changes only):

- In 016_by_statement_oracle.pl, the three statements meant to be sent
as one simple-query message were sent as three, because of a Perl
escaping error in the test. The by-statement result was correct in
both cases. The test now sends the single message it was written for.
- One check in the same file failed on the Linux 32-bit CI job in one
run. It required every row of the <no-statement> bucket to be
Lock:relation. By the documented rule, that bucket holds any wait
that is not a Client wait, between an idle marker and the end of the
transaction, so an LWLock or IO wait during the failed Bind also
lands there. The test now requires exactly one Lock:relation row
and allows only LWLock, IO and Buffer rows besides it, and it prints
the rows when it fails. 50 runs under CPU load passed.

New in v15: 0006 to 0009, instance-wide totals

Aurora PostgreSQL documents three things that the per-backend views of
this module did not give: cumulative wait counts and times for the
whole instance that still include processes that have exited, waits
per transaction, and a ready interval report. These patches add them.

- 0006: test_wait_totals, a test-only module in src/test/modules. It
emits an exact number of timed waits, so the tests can check exact
counts. It is not installed with the extension.
- 0007: a lifetime ledger for each process slot, and an exit drain.
- 0008: the views pg_stat_wait_event_totals and
pg_stat_wait_event_totals_info.
- 0009: transaction counters, the reset function, and the interval
functions.

The extension goes to version 1.1 (update script
pg_wait_event_tracing--1.0--1.1.sql). New functions:

- pg_wait_event_snapshot(): one jsonb value with the totals at a point
in time.
- pg_wait_event_diff(a, b, include_idle): the difference between two
snapshots, with waits, wait_time_ms, ms_per_wait, waits_per_xact and
ms_per_xact for each event.
- pg_wait_event_report(seconds, include_idle): snapshot, sleep,
snapshot, diff, in one call.
- pg_stat_reset_wait_event_totals(): superuser only.

How it works:

- Each process slot (indexed by ProcNumber) has a fixed-size ledger in
shared memory. It is updated at the existing flush point of pending
completions, so the wait end path has no new work. The flush can be
called from the next wait begin. The ledger is never cleared on exit,
on slot reuse or on the existing per-backend resets.
- An exit drain is registered at process start, through the
init_backend_cb of a custom cumulative statistics kind. It flushes
the last pending completion when a process exits in an orderly way.
An abrupt death can lose that last completion.
- Readers copy each ledger with a seqlock and hold no lock while they
scan. They can be cancelled between retries.
- The reset does not change any ledger. It publishes a baseline and a
generation, and readers subtract the baseline. A diff between
snapshots from different generations or different server incarnations
is an error.

Memory, with the default settings except max_connections: the ledgers
take 3.5 MiB at max_connections = 100 (174 slots) and 21.9 MiB at
max_connections = 1000 (1074 slots), counting all backend, worker and
auxiliary slots. The reset baseline adds one 1 MiB structure in shared
memory. Each call that reads the totals allocates about 1 MiB of
scratch in the calling backend. These numbers are computed from the
structures (21376 bytes per ledger at the default max_tranches of 192)
and are written in the documentation.

The transaction denominator differs from Aurora's documented examples.
Here, xacts is the number of commits plus aborts counted in the
processes that were capturing. Parallel workers are not counted.
Aurora's documented examples count committed transactions. So
waits_per_xact and ms_per_xact are not directly comparable to
Aurora's figures. The documentation says this.

0006 to 0009 can be reviewed after 0001 to 0005 and can be dropped
without affecting them. They touch only contrib/pg_wait_event_tracing,
doc/src/sgml/pgwaiteventtracing.sgml and src/test/modules (the new
test_wait_totals directory and its entries in meson.build and the
Makefile). They change nothing in core.

CI on my fork, tip 49f3ad513d7, all jobs green (Linux 32-bit and
64-bit, Autoconf, macOS, Windows with VS and with MinGW,
CompilerWarnings):

https://github.com/DmitryNFomin/postgres/actions/runs/42845457896

The overhead numbers in my v14 mail still apply to the hook itself,
because 0001 and 0002 did not change. They do not cover the new ledger
update in the flush. I have not measured that cost yet. I will measure
v15 against v14, both with capture = stats and the same pgbench
workloads as in the v14 mail, and post the numbers in this thread.

Regards,
Dmitry

Attachments:

t248739_20
v15-0002-Convert-wait_start-end-call-sites-to-the-timed-p.patchapplication/octet-stream; name=v15-0002-Convert-wait_start-end-call-sites-to-the-timed-p.patchDownload+205-206
v15-0001-Add-begin-end-hooks-for-timed-wait-events.patchapplication/octet-stream; name=v15-0001-Add-begin-end-hooks-for-timed-wait-events.patchDownload+139-4
v15-0003-test_wait_hook-test-module-for-the-wait-event-ho.patchapplication/octet-stream; name=v15-0003-test_wait_hook-test-module-for-the-wait-event-ho.patchDownload+874-7
v15-0004-pg_wait_event_tracing-statistics-level.patchapplication/octet-stream; name=v15-0004-pg_wait_event_tracing-statistics-level.patchDownload+6795-2
v15-0005-pg_wait_event_tracing-trace-level.patchapplication/octet-stream; name=v15-0005-pg_wait_event_tracing-trace-level.patchDownload+4603-159
v15-0009-pg_wait_event_tracing-transaction-counters-and-i.patchapplication/octet-stream; name=v15-0009-pg_wait_event_tracing-transaction-counters-and-i.patchDownload+2309-98
v15-0006-test_wait_totals-test-producer.patchapplication/octet-stream; name=v15-0006-test_wait_totals-test-producer.patchDownload+1302-1
v15-0008-pg_wait_event_tracing-instance-totals-views.patchapplication/octet-stream; name=v15-0008-pg_wait_event_tracing-instance-totals-views.patchDownload+2368-6
v15-0007-pg_wait_event_tracing-lifetime-ledgers-and-exit-.patchapplication/octet-stream; name=v15-0007-pg_wait_event_tracing-lifetime-ledgers-and-exit-.patchDownload+481-24
#21Dmitry Fomin
fomin.list@gmail.com
In reply to: Dmitry Fomin (#20)
#22Dmitry Fomin
fomin.list@gmail.com
In reply to: Dmitry Fomin (#21)