MIME-Version: 1.0
References: <20260324151133.7940a5c1f2ebd594d54da481@sraoss.co.jp>
In-Reply-To: <20260324151133.7940a5c1f2ebd594d54da481@sraoss.co.jp>
From: Sami Imseih <samimseih@gmail.com>
Date: Tue, 24 Mar 2026 09:58:48 -0500
Message-ID: 
 <CAA5RZ0snnePNW1NKGKh+NyJ1CY26T5F_6-tTq+BHWM2kj1fN1g@mail.gmail.com>
Subject: Re: Track skipped tables during autovacuum and autoanalyze
To: Yugo Nagata <nagata@sraoss.co.jp>
Cc: Pgsql Hackers <pgsql-hackers@postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Archived-At: 
 <https://www.postgresql.org/message-id/CAA5RZ0snnePNW1NKGKh%2BNyJ1CY26T5F_6-tTq%2BBHWM2kj1fN1g%40mail.gmail.com>
Precedence: bulk

Hi,

Thanks for the patch!

> The attached patch add the following fields to pg_stat_all_tables:
>  - last_skipped_autovacuum
>  - last_skipped_autoanalyze
>  - skipped_autovacuum_count
>  - skipped_autoanalyze_count
>
> Are there any concerns about exposing this in pg_stat_all_tables, or suggestions
> for a better approach?

I am not sure about the timestamp columns. I am not saying they will
not be useful,
but I think it will be better to just start with counters for this.
The way the views get
used, a dashboard built for tracking the deltas of the counters can easily spot
when there is a spike of skipped autovacuum/autoanalyze count.
Also, for tables that are being autovacuumed and skipped quickly,
the timestamps will just be overwritten.

So, I am +1 on the counters, -1 on the timestamps.

Out of scope for this patch, but I also wonder if we should add another counter,
autovacuum_started_count. If there are other types of failure scenarios such as
corrupt indexes, checksum failures, etc. which terminate the
autovacuum in flight,
we would be able to catch this by looking at the number of autovacuums
started vs completed. The skipped counters in this patch and a started
counter would capture different stages of the autovacuum lifecycle;
skipped means
"never started" (lock contention), while a started-minus-completed delta means
"started but failed." Both are useful signals, but for different reasons.

In terms of the patch:

1/

+                       if (AmAutoVacuumWorkerProcess())
+                               pgstat_report_skipped_vacuum(relid);

Any reason why this should not also include manual vacuum/analyze?
If someone has a vacuum/analyze script that uses SKIP_LOCKED, and
the operation gets skipped, this should be included in the counter.
this can be done with separate counter fields for autovacuum/autoanalyze and
vacuum/analyze

2/

+            pg_stat_get_skipped_autovacuum_count(C.oid) AS
skipped_autovacuum_count,

How about a name like "autovacuum_lock_skip_count"?


--
Sami Imseih
Amazon Web Services (AWS)