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