[RFC] An ETW backend for the static probes on Windows

Hackers,

I have been working on additional features and fixes for Windows PG for
a bit in the background now-- what follows is an Opus and Grammarly
cleaned-up text based on my ramblings and PoC patches. Due to the
length of my ramblings-- the corresponding text you are about to,
hopefully, read is somewhat lengthy. Any mistakes are fully mine.

---------

I'd like to propose adding an ETW backend for our static probes on
Windows. I have a working proof-of-concept, but before I clean it into
a patch series and add a CommitFest entry I wanted to check the
direction with the list, since a few of the design choices are ones I
would rather agree on up front than defend after the fact.

On Windows every TRACE_POSTGRESQL_* probe compiles to do {} while (0).
This isn't a matter of less detail on Windows; the probes are simply not
present in the running binary. Gen_dummy_probes.pl generates a header in
which each probe is an empty statement so the tree builds without a
tracing backend, and the compiler then removes the call sites. On the
other platforms these macros can sit on a real backend, DTrace on
FreeBSD, NetBSD, macOS, Solaris and Oracle Linux, and SystemTap's USDT
on Linux, though it is opt-in and off by default there. On Windows there
has only ever been the dummy.

Before proposing anything I went looking for an earlier attempt that had
been rejected, expecting to find a reason I would have to deal with. I
could not find one. There is no patch and no proposal in the archives or
the CommitFest history that I can see. If I've missed it I'll happily
stand corrected, but as far as I can tell the Windows backend was simply
never written.

I think the reason is partly that the obvious approach is a dead end.
DTrace was ported to Windows, so it looks like the answer, but it is a
kernel-driver facility you enable with bcdedit and a reboot, and its
providers are syscall, fbt, pid and etw. It has no USDT provider, and
our probes are compile-time USDT-style markers, so DTrace-on-Windows
cannot see them at all. One of its providers is an etw provider, though,
so once these probes are ETW events DTrace could pick them up that way,
which is a fair hint about where this belongs. The other part is timing.
The manifest-based ETW API is painful for a multi-version application,
since it wants a schema compiled by mc.exe and a manifest registered
against the OS at install time. The manifest-free API that avoids all of
that, TraceLogging, arrived with the Windows 10 SDK, well after these
probes were written, so the earlier option was the painful one.

What I'm proposing is to add ETW as a third backend for the same probes,
using TraceLogging. The probe set and the call sites do not change;
probes.d stays the single source of truth for the argument types, and
only the macro expansion differs. On a DTrace build the macro expands to
a DTrace probe, on the dummy build to nothing, and on an ETW build to a
TraceLoggingWrite. That is what keeps this contained rather than
rippling through the rest of the tree.

As an example, a buffer read that is do {} while (0) today would instead
generate roughly this:

#define TRACE_POSTGRESQL_BUFFER_READ_START(arg1, arg2, ...) \
TraceLoggingWrite(g_hPostgresProvider, "buffer_read_start", \
TraceLoggingKeyword(PG_KW_BUFFER), \
TraceLoggingLevel(WINEVENT_LEVEL_VERBOSE), \
TraceLoggingInt32((arg1), "forknum"), \
TraceLoggingUInt32((arg2), "blocknum"), ...)

The parameters stay positional so the call sites don't move, and the
field labels (forknum, blocknum) are what a consumer such as WPA
displays. Those names come from the argument descriptions already in
monitoring.sgml. Because TraceLogging events are self-describing, there
is no manifest or resource DLL involved. The standard Windows tools
record them (wpr, logman, xperf) and decode each field by name (WPA,
PerfView, tracerpt).

The change itself is small, and none of it affects non-Windows builds.
It comes down to a handful of pieces.

There is a new generator, Gen_etw_probes.pl, alongside the dtrace and
dummy ones. It reads probes.d and emits the same TRACE_POSTGRESQL_*
macro names, with each expanding to a TraceLoggingWrite plus an
enabled-check.

The keyword and level for each event, and a name for each argument, are
things probes.d does not carry, so they go in a small sidecar,
probes_etw.dat, keyed by probe name, in the same spirit as
wait_event_names.txt. I would rather not touch probes.d for this, since
it is shared with the DTrace path. The generator still cross-checks the
two on argument count, so a type mismatch fails the build, but a probe
with no sidecar entry is not an error. It falls back to a keyword taken
from the probe name, a default level, and positional field names, so
adding a probe on Linux never forces anyone to edit a Windows policy
file. The sidecar is where you go to improve an event, not a file every
new probe has to touch.

The generator selection in src/include/utils/meson.build becomes a
three-way choice rather than the current two. The output is still
probes.h, so pg_trace.h and the call sites are untouched.

A new pair of files, pg_trace_etw.c and pg_trace_etw.h, declares and
defines the single "PostgreSQL" provider and its register/unregister
functions. On a build without ETW those functions become no-op macros,
so the call sites need no #ifdef.

Registration has to happen in each process, because a TraceLogging
handle is process-local. That means InitPostmasterChild, which covers
every backend and auxiliary process, and InitStandaloneProcess for the
bootstrap and single-user cases, with on_proc_exit handling teardown.
The postmaster itself fires no probes today, so I register it there for
symmetry and in case one is ever added; the backends are the ones that
matter, and missing InitPostmasterChild would be the easy mistake.

The build glue is a Meson feature option (etw), header detection for
<TraceLoggingProvider.h> to set USE_ETW, an error if it is requested off
Windows or without the header, and mutual exclusion with dtrace, since a
build uses one probe backend. Like dtrace it defaults to disabled, so a
stock build is unchanged and you opt in with -Detw=enabled. The idle
cost of having it built is small, so defaulting off is caution rather
than a real overhead concern.

I should be clear about which Windows this reaches. TraceLogging lives
in the Windows SDK, so the MSVC toolchain has the header and mingw-w64
does not ship it. You opt in with -Detw=enabled, and that needs the
header, so only an MSVC build can turn it on. A MinGW or Cygwin build
stays on the dummy it uses today. That is also why I have not added an
ETW branch to the autoconf probe selection or the Makefiles. The build
that has the header is meson-only, and the autoconf targets cannot see
the header at all, so there is nothing for them to select. The practical
effect is that the backend is MSVC-only for now. A MinGW build would
need either the TraceLogging headers to appear in mingw-w64 or a
fallback onto the older manifest-based ETW API, and I left that out
rather than carry a second code path on day one.

The remaining pieces are a documentation section in monitoring.sgml, the
-Detw option in installation.sgml, and one line of CI to build it on the
MSVC task.

A few of the design decisions are ones I would rather hear opinions on
than settle myself. For keywords I would group the probes under eight
bits (query, transaction, lock, buffer, storage, WAL, checkpoint and
sort) so a session can record one subsystem at a time; it is a 64-bit
mask with room to spare. For levels I would use two tiers by volume. The
low-rate, high-interest probes such as transactions, queries, the
checkpoint family, deadlock and wal_switch would go at INFO, and the
high-volume ones such as lwlocks, buffers, per-block storage I/O, sorts
and wal_insert at VERBOSE. A consumer that only wants the low-rate
events restricts the session to INFO; the level and keyword are there so
a capture does not have to be all-or-nothing. I am not attached to
exactly where that line falls.

On provider identity, there is one provider named "PostgreSQL". Rather
than pick a GUID and ask everyone to bless it, I would derive it from
the name with the standard ETW name-hash, the way EventSource and the
wpr and PerfView tools do when you enable "*PostgreSQL". That makes the
name the contract and the GUID a function of it, so there is nothing
separate to agree on and nothing to keep stable by hand. The name does
have to stay fixed across releases, since saved capture profiles resolve
it back to that GUID.

To keep the first version reviewable I would leave several things out.
It would not add any new probes or consumer-side tooling. I would also
keep the monitoring.sgml probe table hand-maintained rather than
generating it from the sidecar, since the build-time argument-count
check catches drift regardless.

As for why it is worth doing, the immediate benefit is that a Windows
administrator can finally see buffer misses, lock waits, checkpoint
behavior and sort spills through the same probes the other platforms
have had for years, instead of inferring them from the pg_stat_* views.
The part I find more compelling is that ETW is the same facility the
Windows kernel logs to, so the provider's events and the kernel's disk
and CPU events can be recorded into one trace on a common clock. That
lets you line up a checkpoint against the disk behaviour underneath it
on a single timeline, which is not something you can really assemble any
other way on Windows.

Two things about the payload the list should weigh. The events carry
whatever the probes carry, and some of it is sensitive. query__start
ships the statement text, which can include a CREATE ROLE ... PASSWORD.
On Windows an ETW session can be started by an Administrator or a member
of Performance Log Users, a lower bar than a database superuser and
unlike DTrace's root, and the trace lands in a file with its own ACLs.
Providers can carry a security descriptor if we want to restrict
capture, but the default exposure should be stated plainly. Separately,
an ETW event is capped near 64 KB, so a very large statement in
query__start has to be truncated rather than split, and that is a
behavior to document.

The proof-of-concept builds clean with -Detw=enabled and I have run it
end to end. The provider registers in every process, and the standard
Windows tools decode the events by field name. On a pgbench -S worst
case the idle cost with ETW built but no session recording is in the
noise, and the active cost scales with the volume a consumer asks for,
so the keyword and level split is what keeps a capture from being
all-or-nothing.

So my questions for the list are whether a Windows probe backend is
wanted at all, and if so whether the keyword grouping, the two-level
split, and the sidecar with its fallback are reasonable or should be
done differently. If the direction seems right I will turn the
proof-of-concept into a patch series. I would rather settle that now
than after polishing it.

--
Bryan Green
EDB: https://www.enterprisedb.com