Re: Vacuum statistics

On 28.04.2026 05:16, Alena Rybakina wrote:

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.

I noticed CI's complaints during extension installation and fixed it.

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