Re: Vacuum statistics

Hi,

On Sun, Jul 12, 2026 at 5:59 PM Alena Rybakina <lena(dot)ribackina(at)yandex(dot)ru>
wrote:
>
> I have noticed that patches need to be rebased, so I have rebased them.

Thanks for the latest patches. I reviewed mainly the design and the set of
stats added by the v40 series. I read only parts of the discussion
upthread, so please correct me if I missed something.

The way I look at this is along these lines. How are these stats used in
practice for monitoring vacuum performance and making tuning decisions. How
hard is it to get or derive them from what already exists. Why does core
need to make them available at runtime rather than via hooks. And what are
the minimal missing pieces we could start with.

As I understand it, one of the purposes of these stats is to help tune
vacuum parameters. If yes, did we agree which parameters (cost limits,
max_workers, parallelism, failsafe age, work_mem) could be autotuned from
these stats? A small test extension demonstrating how these stats are used
to autotune vacuum would, in my opinion, set the purpose correctly.

In this specific case, where the work is really an external module's job
(collecting and storing stats, historical analysis, monitoring,
autotuning), I think core would do better to provide hooks with structs
carrying the needed information and leave the rest to the module, along the
lines of pg_stat_statements. Most of what the patch adds is already
computed by the instrumentation vacuum has at the end of each run [1]. The
rest can be combined with pg_stat_*, or reconstructed via pg_walinspect and
pageinspect. Did we agree why a hook providing these already-computed
stats, with an external module storing them via custom statistics as in the
v28 approach, is not enough?

I looked at what the patches add and whether they are already available in
the instrumentation or elsewhere.

0001 (number of times all-visible and all-frozen VM bits cleared). This one
is not in the instrumentation but the intent is good for understanding
update/delete patterns. It may be more useful to know how many heap pages
get frequent updates over time, and how that relates to a write spike or
buffer pool lock contention (if only a few pages are hot, the contention
and the I/O pattern). But is an accumulated counter enough, and how does it
help tune vacuum? I would suggest a separate thread unless we can tie it to
vacuum. Can some of this be derived using pageinspect's get_raw_page on the
VM fork?

0002 (heap/tuple metrics, per-index metrics). The heap side is already in
the instrumentation.

pages: 111110 removed, 135804 remain, 246914 scanned (100.00% of total), 0
eagerly scanned
tuples: 17999919 removed, 2000081 remain, 0 are dead but not yet removable

Per-index page counts.

index "bd_id_idx": pages: 54840 in total, 49347 newly deleted, 49347
currently deleted, 0 reusable

Per-index tuples_deleted is the one piece not already there. If we need it,
why not add it to the existing instrumentation rather than new views and
functions?

0003 (recently_dead / missed dead). Already in the instrumentation.

tuples: 17999919 removed, 2000081 remain, 0 are dead but not yet removable
tuples missed: 81 dead from 1 pages not removed due to cleanup lock
contention

0004 (WAL records, FPIs, bytes). Already in the instrumentation.
Per-relation WAL can also be reconstructed from pg_walinspect over an LSN
range if this is not enough.

WAL usage: 3717897 records, 528460 full page images, 4243614245 bytes,
3831091900 full page image bytes, 15221 buffers full

0005 (wraparound_failsafe, interrupts_count). Whether the vacuum ran in
normal, aggressive, or failsafe mode is already captured by the
instrumentation. I do not have a strong opinion on interrupts_count. Errors
are reported in the logs anyway, and since an errored vacuum never reaches
the end of heap_vacuum_rel, the instrumentation cannot capture this one. If
we do want it as a counter, I would not mind adding it to
pg_stat_all_tables, similar to total_vacuum_time and friends.

0006 (VM pages newly set). Already in the instrumentation.

visibility map: 246913 pages set all-visible, 222221 pages set all-frozen
(0 were all-visible)

0007 (buffer pool access). Already in the instrumentation.

buffer usage: 1698168 hits, 6295992 reads, 3135601 dirtied

0008 (per-relation buffer access). The combined count is already in the
instrumentation as shown above. Relation-level buffer I/O also already
exists in pg_statio_all_tables and pg_statio_all_indexes (though not scoped
to vacuum), and block I/O at the database level is in pg_stat_database.
Does a vacuum-scoped per-relation breakdown change a tuning decision often
enough to justify the new instrumentation?

0009 (timing counters). Already in the instrumentation. Note that
total_vacuum_time and total_autovacuum_time already exist in
pg_stat_all_tables.

I/O timings: read: 287.357 ms, write: 3580.593 ms
system usage: CPU: user: 9.89 s, system: 5.74 s, elapsed: 69.45 s

Thanks again for all the effort on this.

[1]
INFO: finished vacuuming "postgres.public.bloat_demo": index scans: 18
pages: 111110 removed, 135804 remain, 246914 scanned (100.00% of total), 0
eagerly scanned
tuples: 17999919 removed, 2000081 remain, 0 are dead but not yet removable
tuples missed: 81 dead from 1 pages not removed due to cleanup lock
contention
removable cutoff: 711, which was 1 XIDs old when operation ended
new relfrozenxid: 704, which is 1 XIDs ahead of previous value
frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
visibility map: 246913 pages set all-visible, 222221 pages set all-frozen
(0 were all-visible)
index scan needed: 222222 pages from table (90.00% of total) had 17999919
dead item identifiers removed
parallel workers: index vacuum: 36 planned, 36 launched in total
index "bd_id_idx": pages: 54840 in total, 49347 newly deleted, 49347
currently deleted, 0 reusable
index "bd_a_idx": pages: 54840 in total, 49347 newly deleted, 49347
currently deleted, 0 reusable
index "bd_b_idx": pages: 54840 in total, 49347 newly deleted, 49347
currently deleted, 0 reusable
index "bd_c_idx": pages: 144171 in total, 1 newly deleted, 1 currently
deleted, 0 reusable
index "bd_d_idx": pages: 16920 in total, 15219 newly deleted, 15219
currently deleted, 0 reusable
I/O timings: read: 287.357 ms, write: 3580.593 ms
avg read rate: 708.185 MB/s, avg write rate: 352.698 MB/s
buffer usage: 1698168 hits, 6295992 reads, 3135601 dirtied
WAL usage: 3717897 records, 528460 full page images, 4243614245 bytes,
3831091900 full page image bytes, 15221 buffers full
memory usage: dead item storage 22.25 MB accumulated across 18 resets
(limit 1.00 MB each)
system usage: CPU: user: 9.89 s, system: 5.74 s, elapsed: 69.45 s

--
Bharath Rupireddy
Amazon Web Services: https://aws.amazon.com