> 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.
log_autovacuum_min_duration logs when the vacuum completes and not when it starts. It does provide the elapsed time in the log as you say. > 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. This is the crux of the question. I agree that pg_stat_all_tables is missing information to allow someone to determine if the vacuums for a table are becoming progressively longer to complete or to verify the vacuum is faster after some tuning was performed. The pg_stat_all_tables.vacuum(autovacuum)_count does not help answer these questions and one must then enable extra logging. > but if there are many tables in the database, the vacuum entries in the logs > might be quite numerous, making it difficult to analyze. Agree > 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. The progress view does a good job of providing the phase of the vacuum which at least tells you if your vacuum is not "stuck" on a single operation. It is not really good at providing information to predict when the vacuum will complete. It is very difficult to actually make such a prediction. IMO, I think this situation could be improved, but I am not sure if a start_time is the best answer. Let's suppose you set the start_time at the beginning of the vacuum, then if the vacuum for some reason terminates early, then you are left with some inconsistent data. Thinking about this, it will be good to have cumulative statistics for elapsed times of a all vacuums on a relation ( since last reset ). This way, someone who is monitoring pg_stat_all_tables can divide the total accumulated elapsed time by the vacuum counts. This can give a good idea of how long vacuum is taking on a table and with enough sampling over time, one can derive patterns of vacuum slowing down, etc. Of course, the time can only be accumulated when the vacuum completes, which should be good enough. Regards, Sami Imseih
