Date: Tue, 28 Oct 2025 16:06:12 -0500
From: Nathan Bossart <nathandbossart@gmail.com>
To: David Rowley <dgrowleyml@gmail.com>
Cc: Sami Imseih <samimseih@gmail.com>, Robert Haas <robertmhaas@gmail.com>,
	Jeremy Schneider <schneider@ardentperf.com>,
	pgsql-hackers@postgresql.org
Subject: Re: another autovacuum scheduling thread
Message-ID: <aQEwRD5XW4wfJE6G@nathan>
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>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: 
 <CAApHDvpVE5F-_8rpPC+-L98mA0yK0S_jtQGqLn69fkRevf726g@mail.gmail.com>
Archived-At: <https://www.postgresql.org/message-id/aQEwRD5XW4wfJE6G%40nathan>
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.

-- 
nathan