Thread

Add last_(auto)vacuum_duration column to pg_stat_all_tables

wenhui qiu <qiuwenhuifx@gmail.com> — 2025-07-14T03:42:03Z

Hi hackers

In most cases, the historical execution time of vacuum operations on tables
is crucial for fine-tuning the autovacuum. For example, it helps determine
whether the current vacuum frequency is appropriate or if we should
consider tuning parameters to speed it up, especially when combined with
the table's SQL performance behavior.

I've encountered many situations where autovacuum or autoanalyze did not
trigger in time, resulting in suboptimal query plans and subsequent
performance issues. When analyzing such problems, I often need to trace
back to when autovacuum or autoanalyze was last triggered on the affected
table.

Currently, the only way to check the duration of (auto)vacuum is through
the server logs, which has several limitations:

   1.

   The log threshold parameters require a trade-off between capturing most
   vacuum events and the storage cost of excessive log entries.
   2.

   Not all users have host-level access to view the logs.
   3.

   Even if server logs are collected into a centralized logging system,
   querying and analyzing them becomes cumbersome as the number of tables
   grows.

Therefore, I propose adding a last_(auto)vacuum_duration column to
pg_stat_all_tables to record the duration of the most recent vacuum or
analyze. This would significantly improve observability and make it more
convenient to monitor vacuum/analyze durations directly from within the
database.

This is based on postgresql 17 ,I know  18 has
total_[auto]{vacuum,analyze}_time

[image: 5690b513afc96a6f2ea7994e2be62286.jpg]



I'd like to hear from everyone.

Thanks