Add last_(auto)vacuum_duration column to pg_stat_all_tables - Mailing list pgsql-hackers

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

I'd like to hear from everyone.

Thanks