Re: Add mode column to pg_stat_progress_vacuum - Mailing list pgsql-hackers

On Thu, Oct 30, 2025 at 12:39 AM Shinya Kato <shinya11.kato@gmail.com> wrote:
>
> On Thu, Oct 30, 2025 at 8:40 AM Masahiko Sawada <sawada.mshk@gmail.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