Re: Add mode column to pg_stat_progress_vacuum

On Thu, Oct 30, 2025 at 12:39 AM Shinya Kato <shinya11(dot)kato(at)gmail(dot)com> wrote:
>
> On Thu, Oct 30, 2025 at 8:40 AM Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com> wrote:
> > > 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.
>
> Thank you for the review!
>
> > > 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?
>
> I think 'autovacuum_wraparound' is better because it's shorter and
> simpler. I've updated the patch to use it.
>
> > Also, we can
> > probably find a better column name than 'reason'. How about 'source'
> > or 'triggered_by'?
>
> I've changed it to use 'triggered_by' because 'source' is overloaded
> and can be interpreted as data origin, I/O source, or WAL source in
> other contexts, making it ambiguous. Also, I've updated the docs from
> "The reason why the current vacuum started" to "The trigger of the
> current vacuum operation".
>
> > 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>
>
> I've added a reference to the triggered_by column in the
> pg_stat_progress_vacuum view.

Thank you for updating the patch!

How about the following changes for the documentation changes?

+ <para>
+ The mode of the current vacuum operation. Possible values are:

The mode in which the current VACUUM operation is running. See Section
24.1.5 for details of each mode. Possible values are:

+ <itemizedlist>
+ <listitem>
+ <para>
+ <literal>normal</literal>: A standard vacuum operation that is not
+ required to be aggressive and has not entered failsafe mode.
+ </para>

normal: The operation is performing a standard vacuum and is neither
required to run in aggressive mode nor operating in failsafe mode.

+ </listitem>
+ <listitem>
+ <para>
+ <literal>aggressive</literal>: A vacuum that must scan the entire
+ table because <xref linkend="guc-vacuum-freeze-table-age"/> or
+ <xref linkend="guc-vacuum-freeze-min-age"/> (or the corresponding
+ per-table storage parameters) required it, or because page skipping
+ was disabled via <command>VACUUM (DISABLE_PAGE_SKIPPING)</command>.

aggressive: The operation is running an aggressive vacuum that must
scan every page that is not marked all-frozen. vacuum_freeze_table_age
and vacuum_multixact_freeze_table_age control when a table is
aggressively vacuumed.

+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ <literal>failsafe</literal>: A vacuum operation that entered failsafe
+ mode when the system was at risk of transaction ID or multixact ID
+ wraparound (see <xref linkend="guc-vacuum-failsafe-age"/> and
+ <xref linkend="guc-vacuum-multixact-failsafe-age"/>).

failsafe: The operation has entered failsafe mode, in which vacuum
performs only the minimum work needed to avoid transaction ID
wraparound failure. vacuum_failsafe_age and
vacuum_multixact_failsafe_age controls when the vacuum enters failsafe
mode.

+ </para>
+ <para>
+ The trigger of the current vacuum operation. Possible values are:

What caused the current VACUUM operation to be initiated. Possible values are:

+ <itemizedlist>
+ <listitem>
+ <para>
+ <literal>manual</literal>: Initiated by an explicit
+ <command>VACUUM</command> command.

manual: The vacuum was initiated by an explicit VACUUM command.

+ <literal>autovacuum</literal>: Launched by autovacuum based on
+ <xref linkend="guc-autovacuum-vacuum-threshold"/> or
+ <xref linkend="guc-autovacuum-vacuum-insert-threshold"/>.

autovacuum: The vacuum was started by an autovacuum worker. Autovacuum
workers launched for this purpose are interrupted due to lock
conflicts.

+ <literal>autovacuum_wraparound</literal>: Launched by autovacuum to
+ avoid transaction ID or multixact ID wraparound (see
+ <xref linkend="vacuum-for-wraparound"/> as well as
+ <xref linkend="guc-autovacuum-freeze-max-age"/> and
+ <xref linkend="guc-autovacuum-multixact-freeze-max-age"/>).

autovacuum_wraparound: The vacuum was started by an autovacuum worker
to prevent transaction ID or multixact ID wraparound. Autovacuum
workers launched for this purpose are not interrupted because of lock
conflicts.

---
+ /* Reset the progress counters and the mode */
+ pgstat_progress_update_multi_param(3, progress_index, progress_val);

This change seems not correct to me since we don't reset the mode. I'd
change it to:

/* Reset the progress counters and set the failsafe mode */

Regards,

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