Re: Add mode column to pg_stat_progress_vacuum

On Fri, Oct 24, 2025 at 12:15 AM Shinya Kato <shinya11(dot)kato(at)gmail(dot)com> wrote:
>
> Thank you all for the reviews!
>
> On Wed, Oct 8, 2025 at 4:34 AM Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com> wrote:
> >
> > 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 have updated the patch according to your comments. I have modified
> this to display the behavior mode (normal, aggressive, failsafe) in
> the mode column,

The new 'mode' column with the possible three values looks good to me.

> and the trigger reason (manual, autovacuum,
> anti-wraparound) in the reason column

Showing 'anti-wraparound' value hides the fact that the process is an
autovacuum worker. How about 'ant-wraparound_autovacuum',
'autovacuum_wraparound', or something along those lines? Also, we can
probably find a better column name than 'reason'. How about 'source'
or 'triggered_by'?

I think we need to update the documentation in maintenance.sgml as
well. For instance, we can add the reference to the new columns in the
following description:

<para>
Autovacuum workers generally don't block other commands. If a process
attempts to acquire a lock that conflicts with the
<literal>SHARE UPDATE EXCLUSIVE</literal> lock held by autovacuum, lock
acquisition will interrupt the autovacuum. For conflicting lock modes,
see <xref linkend="table-lock-compatibility"/>. However, if
the autovacuum
is running to prevent transaction ID wraparound (i.e., the
autovacuum query
name in the <structname>pg_stat_activity</structname> view ends with
<literal>(to prevent wraparound)</literal>), the autovacuum is not
automatically interrupted.
</para>

Regards,

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