MIME-Version: 1.0
References: 
 <CAA5RZ0vSPqd5vP4-17E6QELRgQzaoKChgp5TDPK9GhZEK=0Gjg@mail.gmail.com>
 <aPp4VyLo2Zqk7oCV@nathan>
 <CAA5RZ0sfQ-VSCSafsrvyJ7wsW1utLwtPVJ5N6hB0726BGRDrgQ@mail.gmail.com>
 <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>
In-Reply-To: 
 <CAA5RZ0sybfRyKp+DY+r=2U+-r7HfSF4GL1oVOOcVtEWmk2ewUw@mail.gmail.com>
From: David Rowley <dgrowleyml@gmail.com>
Date: Tue, 28 Oct 2025 12:16:28 +1300
Message-ID: 
 <CAApHDvpVE5F-_8rpPC+-L98mA0yK0S_jtQGqLn69fkRevf726g@mail.gmail.com>
Subject: Re: another autovacuum scheduling thread
To: Sami Imseih <samimseih@gmail.com>
Cc: Nathan Bossart <nathandbossart@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/CAApHDvpVE5F-_8rpPC%2B-L98mA0yK0S_jtQGqLn69fkRevf726g%40mail.gmail.com>
Precedence: bulk

On Tue, 28 Oct 2025 at 11:35, Sami Imseih <samimseih@gmail.com> wrote:
> We discuss the threshold calculations in the documentation, and users
> can write scripts to monitor which tables are eligible. However, there
> is nothing that indicates which table autovacuum will work on next (I
> have been asked that question by users a few times, sometimes out of
> curiosity, or because they are monitoring vacuum activity and wondering
> when their important table will get a vacuum cycle, or if they should
> kick off a manual vacuum). With the scoring system, it will be much more
> difficult to explain, unless someone walks through the code.

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.

David