Re: Add mode column to pg_stat_progress_vacuum

On Tue, Oct 7, 2025 at 12:01 PM Nathan Bossart <nathandbossart(at)gmail(dot)com> wrote:
>
> On Tue, Oct 07, 2025 at 10:17:38AM -0700, Masahiko Sawada wrote:
> > Right. I think we cannot display both things in one mode column. Since
> > both manual vacuums and anti-wraparound autovacuums can enter the
> > failsafe mode dynamically, if we show "failsafe" in the mode column,
> > we would lose the information "why is this vacuum running". I guess we
> > would need separate columns. For example, I guess that the column
> > showing "how is it operating under the hood" can have three values:
> > "normal", "aggressive" (disables VM optimization), and "failsafe"
> > (implies aggressive vacuum and disables many things to prioritize XID
> > freezing).
>
> Am I understanding correctly that your idea is to have a "reason" column
> that would have values like "manual", "normal autovacuum", and "autovacuum
> for wraparound", and a "mode" column that would have values like "normal",
> "agressive", and "failsafe"?

Right. The first column provides an insight into whether or not the
running vacuum is cancellable, and the second column provides
information on how vacuums are actually processing tables under the
hood. Users are able to get the former information by checking
pg_stat_activity too but the latter information is available only in
server logs.

> I wonder if we could be even more granular
> for the "normal autovacuum" case and point to the reason the table was
> chosen. For example, was it the insert threshold, the update/delete
> threshold, etc.?

Sounds like reasonable information. I guess we might want to have such
information in a cumulative statistics view but do you think it's
better to have it in a dynamic statistics view?

Regards,

--
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com