MIME-Version: 1.0
References: 
 <CAApHDvqobtKMwJbhKB_c=3-TM=TgS3bcuvzcWMm3ee1c0mz9hw@mail.gmail.com>
 <aPkz9grlBAJRXrbe@nathan>
 <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>
In-Reply-To: <aP-YgrcPi0EhgR9x@nathan>
From: Sami Imseih <samimseih@gmail.com>
Date: Mon, 27 Oct 2025 12:47:15 -0500
Message-ID: 
 <CAA5RZ0u2Mbks+O2DKBYen94AH3OMUcg+A7wvxrXYkmjTddBx4g@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/CAA5RZ0u2Mbks%2BO2DKBYen94AH3OMUcg%2BA7wvxrXYkmjTddBx4g%40mail.gmail.com>
Precedence: bulk

I spent some time looking at this, and I am not sure how much this
will move the goalpost, since most of the time the bottleneck for
autovacuum is the limited number of workers and large tables that
take a long time to process.

That said, this is a good change for the simple reason that it is
better to have a well-defined prioritization strategy for autovacuum
than something that is somewhat random, as mentioned earlier.

Just a couple of comments on v5:

1/ Should we add documentation explaining this prioritization behavior in [0]?

I wrote a sql that returns the tables and scores, which I found was
useful when I was testing this out, so having the actually rules spelled out
in docs will actually be super useful.

If we don't want to go that much in depth, at minimum the docs should say:

"Autovacuum prioritizes tables based on how far they exceed their thresholds
or if they are approaching wraparound limits." so a DBA can understand
this behavior.

2/
* The score is calculated as the maximum of the ratios of each of the table's
* relevant values to its threshold. For example, if the number of inserted
* tuples is 100, and the insert threshold for the table is 80, the insert
* score is 1.25.

Should we consider clamping down on the score when
reltuples = -1, otherwise the scores for such tables ( new tables
with a large amount of ingested data ) will be over-inflated? Perhaps,
if reltuples = -1 ( # of reltuples not known ), then give a score of .5,
so we are not over-prioritizing but not pushing down to the bottom?

[0] https://www.postgresql.org/docs/current/routine-vacuuming.html#AUTOVACUUM

--
Sami Imseih
Amazon Web Services