Add last_(auto)vacuum_duration column to pg_stat_all_tables

Поиск
Список
Период
Сортировка
От wenhui qiu
Тема Add last_(auto)vacuum_duration column to pg_stat_all_tables
Дата
Msg-id CAGjGUA+f-k30HwgE0nkCbsNby_YLRfzjgQn0gyJJnT_DJtUHmQ@mail.gmail.com
обсуждение исходный текст
Список 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

5690b513afc96a6f2ea7994e2be62286.jpg



I'd like to hear from everyone.

Thanks

Вложения

В списке pgsql-hackers по дате отправления: