Re: Vacuum statistics

Hi, all!

I have updated the core patch that implements the machinery for 
collecting extended vacuum statistics (I didn't touch the first patch 
that is ready for commit, only patches that are related to extension), 
and rebased the ext_vacuum_statistics extension on top of it. The split 
is intentional: the core only gathers metrics and hands them out, while 
the actual storage and SQL-level access to the statistics live entirely 
in the extension. If the extension is not loaded, the overhead is 
essentially zero - we only fill a small struct on the stack and do a 
NULL check on the hook.

What was updated in the core

The core gains the machinery and the hook through which the extension 
receives metrics after each vacuum.

The hook. A new hook has been added in pgstat - set_report_vacuum_hook. 
It is fired once per vacuumed table and once per vacuumed index, plus 
when forming the per-database aggregate. The extension registers its 
handler in _PG_init and by default the hook is NULL, so without an 
extension the core behaves exactly as before.

The set of statistics is the same as before. Common to tables, indexes 
and the database - hits and misses in shared buffers, number of dirtied 
and written pages, WAL volume, buffer read and write times, sleep time 
spent in delay points, total wall-clock vacuum time (including I/O and 
lock waits), counter of emergency anti-wraparound vacuums, number of 
interrupts and removed tuples. Tables additionally report frozen tuples, 
pages marked all-frozen / all-visible in the visibility map, number of 
scanned and removed pages, number of index passes, etc. Indexes report 
freed pages.

The least obvious part of the implementation is subtracting index 
statistics from the table statistics. This is the bit worth 
highlighting. The thing is that indexes are vacuumed before the heap, 
and the buffer and WAL statistics that we capture at the heap level by 
the end of the heap vacuum already include everything that was spent on 
the indexes. If we simply expose the diff of pgBufferUsage/pgWalUsage 
between start and end, the table ends up with double-counted pages/WAL: 
once in its own report, and a second time inside the reports of its 
indexes. This is especially noticeable with parallel index vacuum: 
workers accumulate their usage in the leader only after they finish, so 
without subtraction the heap report would receive the combined cost of 
all workers as a "bonus".

To handle this, as each index finishes vacuuming, its counters are 
accumulated into the state of the current operation, and at the moment 
the heap report is built these sums are subtracted out. As a result, the 
extension receives clean numbers: "this is what was actually spent on 
the table itself", and separately "this is what was actually spent on 
each index". The behaviour is idempotent for both serial and parallel 
vacuum.

The ext_vacuum_statistics extension

The extension registers the hook handler and stores the received data 
through the pgstat custom statistics infrastructure. That is, vacuum 
counters are kept not in the extension's own files, but together with 
the regular cumulative statistics - they survive a restart and are reset 
together with pg_stat_reset_*. Access is provided through three views: 
one for tables, one for indexes, and one with the per-database aggregate.

Filtering

This is where the main flexibility lives - the extension does not force 
"collect everything", but lets you choose both what to track and which 
metrics to keep.

By object type. You can limit collection to databases only (without 
per-table detail), to tables only, or collect both. Among tables, you 
can additionally filter system / user / all.

By an explicit list. An alternative to "by type" is a whitelist: you 
turn the corresponding mode on, and the extension starts collecting 
statistics only for the databases and tables that were explicitly 
registered via add_track_database / add_track_relation (with matching 
remove_* for removal). When the lists are off, the type filter is in 
effect; when they are on, only the list applies. This is convenient when 
you are interested in monitoring specific "hot" tables and do not want 
to spend memory on statistics for everything else.
This list is persisted to disk, and there is one more non-trivial part 
here. List changes are concurrent - multiple sessions may call 
add_track_* simultaneously, plus there is an object-access hook that 
cleans the entry on DROP. To avoid ending up with a torn file, access to 
the list is serialized via a dedicated LWLock tranche (requested from a 
shmem_request_hook), and the file itself is written atomically: first 
into a temporary file, then fflush + pg_fsync + durable_rename. All I/O 
return codes are checked; on error the temporary file is removed and the 
real one is left untouched; PG_TRY/PG_CATCH guarantees cleanup on 
ereport(ERROR). Reading the list takes the same lock in shared mode, so 
a concurrent write cannot tear the load.

By metric category. There is also a GUC that takes a list and turns on 
the categories of interest - buffers, WAL, general counters, timings (or 
all). Unwanted categories are simply skipped on the hook handler side 
and never make it into the pgstat entry, which reduces the overhead of 
the handler itself. This is useful when, for example, only timings are 
needed - in that case the extension does not waste time copying the 
buffer and WAL fields.

Privileges. The add_track_* / remove_track_* functions require superuser 
or pg_read_all_stats. At the SQL level, EXECUTE is revoked from PUBLIC 
and granted only to pg_read_all_stats, so a regular user has no access 
to mutating the list. The views are unrestricted, like regular statistics.

What is in the patches

0002-Machinery-for-grabbing-extended-vacuum-statistics.patch - the 
machinery in the core plus the hook.
0003-ext_vacuum_statistics-...patch - the extension itself, filtering, 
views, tests.

-----------
Best regards,
Alena Rybakina
Yandex Cloud