Re: Vacuum statistics

Hi, Bharath!

Thank you for the detailed review, and sorry for the delay.  To explain
how the patch set ended up in core in the first place: vacuum is
performed by a dedicated backend and is a permanent background process,
so it seemed natural that all its metrics should be permanent as well
and, like everything else backends collect, flow into the cumulative
statistics system.  Nevertheless, the extension gives much more
flexibility, and adding filters later on will make it possible to reduce
the amount of memory reserved for storing the statistics compared to
what the in-core version claimed.  I have actually built it this way
before - see the extension-based version I posted in [0] - so this is a
return to that shape rather than a brand-new design.  Until now both
approaches looked self-sufficient and valid to me, each in its own way,
but I now think the hybrid one - where a small part of the statistics
lives in core and the rest does not - is the most complete.  So you made
me rethink the shape of the patch set, and I've reworked it around the
approach you (and Amit upthread) suggested.  Attached is v42,
restructured as follows; per-patch replies are below.

The series now follows the pg_stat_statements model you described: the
core only fills a report structure and hands it to extensions through
set_report_vacuum_hook, and a new extension, ext_vacuum_statistics,
stores the reports via pgstat's custom statistics and exposes them
through SQL views.  The few pieces that genuinely belong in the existing
core views went exactly there.

v42-0001 - Report per-index removed tuples in vacuum instrumentation.
v42-0002 - Track vacuum times for indexes and databases, and vacuum
           delay, in pg_stat views.
v42-0003 - Count wraparound-failsafe vacuums in pg_stat views.
v42-0004 - Count vacuums interrupted by errors in pg_stat_database.
v42-0005 - Vacuum report hook and the ext_vacuum_statistics extension
           (tuple counters).
v42-0006 - ext_vacuum_statistics: page counters for tables and indexes.
v42-0007 - ext_vacuum_statistics: WAL metrics and the per-database
           aggregate.
v42-0008 - ext_vacuum_statistics: shared-buffer access counters and
           I/O timing.
v42-0009 - Track table VM stability (to be moved to a separate thread,
           attached here for now - see below).

On 13.07.2026 21:07, Bharath Rupireddy wrote:
> 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.

The first two questions I answer next to your per-patch comments - each
time with how the stat is used in practice and how hard it is to get
from what exists today.  As for core versus hooks - that is exactly what
v42 changes.  The core parts are now: the report hook with counters
vacuum already maintains (v42-0005, v42-0006), plus WAL/buffer
usage sampling that only exists to feed the reports (v42-0007,
v42-0008).  The extension owns storage, aggregation (including the
per-database rollup) and the SQL views, and is controlled by a single
GUC, vacuum_statistics.enabled, which can also be attached to individual
databases with ALTER DATABASE ... SET vacuum_statistics.enabled = off -
so per-database filtering comes for free with the extension model. I
dropped the earlier object-filter machinery from the series to keep the
surface minimal; it can be resubmitted separately if there is interest.

> And what are the minimal missing pieces we could start with.

I'd say v42-0001 - v42-0004: they touch only the existing
instrumentation and views, close the gaps that exist nowhere else
(per-index tuples and times, cumulative delay, failsafe and interrupted
vacuums), and are useful on their own even if the hook/extension
discussion takes longer.  The rest of the series can be ignored for
now - I attached it so that it does not get lost in the course of the
discussion; if it gets in the way of a CI run, I can mark it no-cfbot
for the time being.

>
> 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.

Not explicitly - upthread the discussion was about observability rather
than autotuning, and I think the order matters here: the autotuning
discussion only becomes possible once these statistics are in, because
without them there is simply nothing to tune from - you cannot tune
what you cannot measure.  Some dependencies are already visible - the
delay counters in v42-0002 directly show whether the right knob is the
cost-based settings or the amount of work itself - but I'd like to take
some time before answering your list properly: to formulate which
parameter follows from which statistic directly, and to back that with
test scenarios rather than with reasoning alone.  I will come back to
this separately.  A small test extension demonstrating autotuning on
top of the report hook is a good idea - the hook receives the full
report at the end of each run, so such an extension needs no further
core changes - and it fits naturally as the follow-up once the
statistics themselves are in place.

> 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.

Agreed - v42 is built exactly this way, as answered above: core only
fills the report structure and hands it to the hook, and the module
does the collecting, storing and analysis.

> Most of what the patch adds is already computed by the instrumentation
> vacuum has at the end of each run [1].

Computed, yes - but then it is printed once into the server log and
thrown away.  To use it for monitoring you need
log_autovacuum_min_duration = 0, an external log parser, and you lose
everything to log rotation. "Available in the instrumentation" and
"available as queryable cumulative statistics" are different things, and
the community has already accepted that distinction once:
total_vacuum_time was added to pg_stat_all_tables even though the
elapsed time had always been in the log line.  The series keeps the
instrumentation as is and adds the cumulative side - most of it in the
extension, per your suggestion.

> 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?

Now I agree that it is enough: v42 is precisely a return to that
approach.

> 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?

No, it cannot: a VM bit is cleared by whatever backend happens to modify
the page, so this event is visible nowhere today - not in the log, not
in any view - and it cannot be derived from snapshots either:
pageinspect (or pg_visibility) gives the current state of the VM fork,
but between two observations the set and clear events cancel out. The
clear rate is exactly what explains rising heap_fetches of index-only
scans and vacuum re-freezing the same pages over and over.

As for how it ties to vacuum and whether an accumulated counter is
enough - the usefulness of these counters was discussed upthread in some
detail.  Andrei Zubkov formulated their meaning for the docs in [2]: the
all-visible bit of a heap page is cleared every time a backend modifies
a page previously marked all-visible by vacuum, so a high rate of change
of the counter means vacuum keeps re-doing its work on this table (and
likewise for the all-frozen bit and re-freezing).  Andrei Lepikhov
described the practical use cases in [5]: finding the tables with the
most VM churn, detecting DML skew (whether the changes are localised
within the table), and estimating index-only scan effectiveness - the
rate of change of the counter, normalised to relallvisible, detects
tables where an index-only scan might be inefficiently used.  I also
posted tests
demonstrating what exactly the counters capture - an isolation scenario
where the flags set by a previous VACUUM are cleared by concurrent DML,
including the transactional case where the clearing becomes visible only
after the modifying transaction commits [3] - and after those rounds of
review Andrei was happy with the patch [4].

Frankly, since the patch had been reviewed, refined and is fully
independent of the rest of the series, my expectation used to be that it
would get committed quickly and I could move on with the other
statistics.  That expectation clearly didn't work out - so, with the
usefulness question on the table again, I agree it deserves its own
thread and I'm going to start one.  Until I get around to that, the
patch is attached here as v42-0009: it is the last patch of the series,
nothing depends on it, so it can be reviewed (or ignored) without
blocking the rest.

> 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?

Agreed - v42-0001 does exactly that: the per-index line of VACUUM
VERBOSE and the autovacuum log now shows the tuples removed from each
index.

>
> 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

Reconstructing per-relation WAL from pg_walinspect requires retaining
the WAL for the whole period of interest, which is not practical for
week-scale trend analysis.  And these counters are worth having: they
show which objects generate the most WAL during vacuum, and that
matters wherever WAL volume is a constraint - a burst of
vacuum-generated WAL, say a mass freeze of a large table, directly
translates into replication lag on standbys, a larger WAL archive and
backup volume, longer PITR recovery, and, on network or cloud storage,
real transfer costs.  Knowing that most of that WAL comes from vacuums
of one particular table lets one reschedule or throttle its vacuums
instead of blaming the regular workload.  I will try to reproduce this
scenario in a test later and share the results in the thread.  The WAL
counters live in the extension (v42-0007), not in core views.

> 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?

A vacuum-scoped per-relation breakdown is exactly the question these
counters answer - "which relations or databases were the most expensive
to vacuum, and why" - and pg_statio_* cannot answer it, since it mixes
vacuum I/O with all other activity, as you say yourself.

Aggregated per database, the buffer and WAL counters are also the only
statistics (besides the interrupts counter) that show the total vacuum
load on a database and tell on which objects vacuum spends most of its
work and on which it does not.

The blk_read_time/blk_write_time pair (counted only when
track_io_timing is enabled, like its pg_stat_database counterpart) is
what turns the block counts into a cost: the counts say how many pages
vacuum touched, the timings say what that actually cost on this
storage.  Together with the other counters they complete the
decomposition of a slow vacuum: total time = real work + cost-based
delay sleeps (v42-0002) + waiting for I/O.  A table whose vacuums read
few blocks but accumulate a large read time immediately points at the
storage, not at bloat or throttling.

So the buffer counters, including the blk_read/blk_write times, live in
the extension - v42-0008 - like the WAL ones, not in core views, which
I hope addresses the "new views and functions in core" part of the
concern.

>
> 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.

On the failsafe mode - captured, but again only as a WARNING in the
log, stored nowhere.  Entering the failsafe means the table has come
dangerously close to transaction ID wraparound: its relfrozenxid age
crossed vacuum_failsafe_age, so vacuum abandons cost-based delay and
skips index vacuuming and heap truncation to freeze the table as fast
as it can.  Getting there at all means autovacuum on that table
systematically fails to keep up, and a table that hits the failsafe
repeatedly is the first candidate for tuning - the next stop after the
failsafe is the anti-wraparound shutdown.  So v42-0003 counts these
vacuums -
vacuum_failsafe_count in pg_stat_all_tables and, aggregated over the
database's tables, in pg_stat_database.  Unlike an interrupted vacuum,
a failsafe one still reaches the end of the run, so the counters go
through the regular pgstat_report_vacuum() path.

As you suggested, v42-0004 moves the counter of interrupted vacuums into
core - at the database level: an interrupted vacuum is aborting its
transaction, so the counter is updated directly in the shared entry from
the vacuum error callback, and pg_stat_database gets a
vacuum_interrupt_count column.  As you note, this is the one event the
end-of-run instrumentation can never capture.

>
> 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)

Agreed - I dropped the "pages newly set all-visible/all-frozen"
counters from the series entirely: the current level is already stored
in pg_class.relallvisible/relallfrozen and the per-run deltas are in the
log, so that part was indeed duplicative.  Only the clear-side counters
remain (v42-0009) - see my reply on your 0001 comment above.

>
> 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

They cover one cell of the matrix: total time, tables only, with the
index processing time indistinguishable from heap time inside it.
v42-0002 completes the picture in the existing views, without any new
ones: total_(auto)vacuum_time and total_(auto)vacuum_delay_time for
indexes in pg_stat_all_indexes and for databases in pg_stat_database,
plus the delay pair for tables.

The idea behind the per-index time is to let one assess the vacuum load
on the indexes themselves, not on the indexes and the heap lumped
together, and so pinpoint which particular objects need attention and
closer monitoring.  This is especially useful when investigating bloat:
a bloated index keeps dragging every vacuum of its table through extra
index scan passes, and today that cost is invisible - per-index vacuum
time exists nowhere, not even in the instrumentation - although "which
index makes vacuuming this table slow" is one of the most common
questions in practice.

The delay time should be accounted alongside it, for both the heap and
the indexes, because total time alone is ambiguous: a long vacuum may be
doing a lot of real work, or it may simply be sleeping in cost-based
delay points.  The delay counters split those apart - comparing
total_vacuum_delay_time against total_vacuum_time immediately shows
whether the right knob is the cost-based settings (vacuum_cost_limit /
vacuum_cost_delay) or the amount of work itself (bloat, dead tuples) -
and the per-object breakdown shows which heap or index accumulates the
throttling.  Today the delay is visible only transiently in
pg_stat_progress_vacuum and in the log line; cumulatively it was lost.

[0]
https://www.postgresql.org/message-id/9d98562b-fa97-476b-9315-6fa2d736ab6b%40yandex.ru
[2]
https://www.postgresql.org/message-id/54cbe53b-d8cf-495f-aa33-3501bec72780%40moonset.ru
[3]
https://www.postgresql.org/message-id/68939c47-fa0c-4198-853a-92d1390079da%40yandex.ru
[4]
https://www.postgresql.org/message-id/87tsudz0dk.fsf%40desktop.moonset.ru
[5]
https://www.postgresql.org/message-id/1885f257-46cc-4b90-8d90-41833eb62ea9%40gmail.com

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