Hi Sami
Many people have encountered situations where autovacuum or auto analyze on tables are not triggered in time, leading to suboptimal execution plans and some performance issues. When analyzing such problems, we often need to trace back to when autovacuum or auto analyze was triggered for the corresponding table. However, if the log_autovacuum_min_duration parameter is not configured (as I’ve encountered in many cases where this parameter is either not set or has an inappropriate value), we cannot determine the trigger time and duration of the operation.
Our idea is to directly query the pg_stat_all_tables view to obtain the start time of (auto)vacuum/(auto)analyze for a table. This would help us monitor the duration of vacuum/analyze and determine whether it is necessary to tune and speed up the vacuum/analyze process.
Of course, to observe the duration of vacuum operations, we can configure the log_autovacuum_min_durationparameter, but if there are many tables in the database, the vacuum entries in the logs might be quite numerous, making it difficult to analyze.
Additionally, we are currently considering whether it would be possible to add a last_(auto)vacuum_start field to the pg_stat_all_tables view. For tables where a vacuum operation is in progress, the last_(auto)vacuum field may not be updated, and it may not be possible to estimate the vacuum duration using just these two fields.
However, this could still indicate whether a vacuum is in progress (if last_(auto)vacuum_start is more recent than last_(auto)vacuum, it means a vacuum is ongoing). While it is possible to monitor vacuum activity through the pg_stat_progress_vacuum view, this view itself does not record timestamps, so additional views might be needed, which would be less convenient than querying pg_stat_all_tables directly.
Therefore, we personally believe that adding such fields would be beneficial for monitoring the execution of (auto)vacuum and (auto)analyze.
Thanks
The last_(auto)vacuum is useful because it allows
the user to monitor vacuum to ensure that vacuums
are completing on a relation at expected intervals.
I am not sure what value a start time will provide.
Can you provide a reason for this?
Regards,
Sami Imseih
