public inbox for [email protected]  

help / color / mirror / Atom feed

From: Sami Imseih <[email protected]>
To: Yugo Nagata <[email protected]>
Cc: Pgsql Hackers <[email protected]>
Subject: Re: Track skipped tables during autovacuum and autoanalyze
Date: Tue, 24 Mar 2026 09:58:48 -0500
Message-ID: <CAA5RZ0snnePNW1NKGKh+NyJ1CY26T5F_6-tTq+BHWM2kj1fN1g@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>

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)

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: Track skipped tables during autovacuum and autoanalyze
  In-Reply-To: <CAA5RZ0snnePNW1NKGKh+NyJ1CY26T5F_6-tTq+BHWM2kj1fN1g@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox