MIME-Version: 1.0
References: 
 <CAApHDvpxE8ci83d02dRE3-fMetb4Dc89-80FrjkGDz2q+ByJog@mail.gmail.com>
 <CAA5RZ0upTpKqgrdNfMSX7UJdjx=+=CsQ6Xct+vcCZPvUVhdZvw@mail.gmail.com>
 <CAApHDvp1=FOs6GneTzLSCHnCmC7z1_80=U3M=CKd82-pwS3YHg@mail.gmail.com>
 <aPuWev3D9M4iGCUt@nathan>
 <CAApHDvoM5MEHHBc0TNdrzkpq39WdEHSZhdWrtnx9zOWNXTSFGw@mail.gmail.com>
 <aP-YgrcPi0EhgR9x@nathan>
 <CAA5RZ0u2Mbks+O2DKBYen94AH3OMUcg+A7wvxrXYkmjTddBx4g@mail.gmail.com>
 <aP_g61kSkGAQOu3F@nathan>
 <CAA5RZ0sybfRyKp+DY+r=2U+-r7HfSF4GL1oVOOcVtEWmk2ewUw@mail.gmail.com>
 <CAApHDvpVE5F-_8rpPC+-L98mA0yK0S_jtQGqLn69fkRevf726g@mail.gmail.com>
 <aQEwRD5XW4wfJE6G@nathan>
In-Reply-To: <aQEwRD5XW4wfJE6G@nathan>
From: Sami Imseih <samimseih@gmail.com>
Date: Wed, 29 Oct 2025 10:24:17 -0500
Message-ID: 
 <CAA5RZ0uGbpaom3v+2K=bNuitnSGX_Lw4yPj3SbJdABYKgGur_A@mail.gmail.com>
Subject: Re: another autovacuum scheduling thread
To: Nathan Bossart <nathandbossart@gmail.com>
Cc: David Rowley <dgrowleyml@gmail.com>, Robert Haas <robertmhaas@gmail.com>,
	Jeremy Schneider <schneider@ardentperf.com>, pgsql-hackers@postgresql.org
Content-Type: text/plain; charset="UTF-8"
Archived-At: 
 <https://www.postgresql.org/message-id/CAA5RZ0uGbpaom3v%2B2K%3DbNuitnSGX_Lw4yPj3SbJdABYKgGur_A%40mail.gmail.com>
Precedence: bulk

> On Tue, Oct 28, 2025 at 12:16:28PM +1300, David Rowley wrote:
> > I think it's reasonable to want to document how autovacuum prioritises
> > tables, but maybe not in too much detail. Longer term, I think it
> > would be good to have a pg_catalog view for this which showed the
> > relid or schema/relname, and the output values of
> > relation_needs_vacanalyze(). If we had that and we documented that
> > autovacuum workers work from that list, but they just may have an
> > older snapshot of it, then that might help make the score easier to
> > document. It would also allow people to question the scores as I
> > expect at least some people might not agree with the priorities. That
> > would allow us to consider tuning the score calculation if someone
> > points out a deficiency with the current calculation.
> >
> > Also, longer-term, it also doesn't seem that unreasonable that the
> > autovacuum worker might want to refresh the tables_to_process once it
> > finishes a table and if autovacuum_naptime * $value units of time have
> > passed since it was last checked. That would allow the worker to deal
> > with and react accordingly when scores have changed significantly
> > since it last checked.  I mean, it might be days between when
> > autovacuum calculates the scores and finally vacuums the table when
> > the list is long, of it it was tied up with large tables. Other
> > workers may have gotten to some of the tables too, so the score may
> > have dropped, but again made its way above the threshold, but to a
> > lesser extent.
>
> Agreed on both points.

I think we do need some documentation about this behavior, which v6 is
still missing.

Another thing I have been contemplating about is the change in prioritization
and the resulting difference in the order in which tables are vacuumed
is what it means for workloads in which autovacuum tuning that was
done with the current assumptions will no longer be beneficial.

Let's imagine staging tables that get created and dropped during
some batch processing window and they see huge data
ingestion/changes. The current scan will make these less of a priority
naturally in relation to other permanent tables, but with the new priority,
we are making these staging tables more of a priority. Users will now
need to maybe turn off autovacuum on a per-table level to prevent this
scenario. That is just one example.

What I am also trying to say is should we provide a way, I hate
to say a GUC, for users to go back to the old behavior? or am I
overstating the risk here?

--
Sami Imseih
Amazon Web Services (AWS)