MIME-Version: 1.0
References: <aOlC4aDoQcgW8ZpC@nathan>
 <CA+TgmoYC4ShRp8vcyrBjkefSBdFfY1fnUgCvjocN-iq55G-7bA@mail.gmail.com>
 <CAApHDvqrd=SHVUytdRj55OWnLH98Rvtzqam5zq2f4XKRZa7t9Q@mail.gmail.com>
 <aPea8dG8_lFwTsyo@nathan>
 <CAApHDvocam8_cxqO=LiSifgp0B3rs1=VWRVQiwAwz_DvOMgfVw@mail.gmail.com>
 <aPklC5V61VcTu7IP@nathan> <aPkpSXHB66islP3h@nathan>
 <CAApHDvqobtKMwJbhKB_c=3-TM=TgS3bcuvzcWMm3ee1c0mz9hw@mail.gmail.com>
 <aPkz9grlBAJRXrbe@nathan>
 <CAA5RZ0vSPqd5vP4-17E6QELRgQzaoKChgp5TDPK9GhZEK=0Gjg@mail.gmail.com>
 <aPp4VyLo2Zqk7oCV@nathan>
 <CAA5RZ0sfQ-VSCSafsrvyJ7wsW1utLwtPVJ5N6hB0726BGRDrgQ@mail.gmail.com>
In-Reply-To: 
 <CAA5RZ0sfQ-VSCSafsrvyJ7wsW1utLwtPVJ5N6hB0726BGRDrgQ@mail.gmail.com>
From: David Rowley <dgrowleyml@gmail.com>
Date: Fri, 24 Oct 2025 09:24:34 +1300
Message-ID: 
 <CAApHDvpxE8ci83d02dRE3-fMetb4Dc89-80FrjkGDz2q+ByJog@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"
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/CAApHDvpxE8ci83d02dRE3-fMetb4Dc89-80FrjkGDz2q%2BByJog%40mail.gmail.com>
Precedence: bulk

On Fri, 24 Oct 2025 at 08:33, Sami Imseih <samimseih@gmail.com> wrote:
> Yeah, you=E2=80=99re correct, the list already exists; sorry I missed tha=
t. My
> main concern is
> the additional overhead of the sort operation, especially if we have
> many eligible
> tables and an aggressive autovacuum_naptime.

It is true that there are reasons that millions of tables could
suddenly become eligible for autovacuum work with the consumption of a
single xid, but I imagine sorting the list of tables is probably the
least of the DBAs worries for that case as sorting the
tables_to_process list is going to take a tiny fraction of the time
that doing the vacuum work will take.

If your concern is that the sort could take too large a portion of
someone's 1sec autovacuum_naptime instance, then you also need to
consider that the list isn't likely to be very long as there's very
little time for tables to become eligible in such a short naptime, and
if the tables are piling up because autovacuum is configured to run
too slowly, then lets fix that at the root cause rather than be
worried about improving one area because another area needs work. If
we think like that, we'll remain gridlocked and autovacuum will never
be improved. TBH, I think that mindset has likely contributed quite a
bit to the fact that we've made about zero improvements in this area
despite nobody thinking that nothing needs to be done.

There are also things that could be done if we were genuinely
concerned and had actual proof that this could reasonably be a
problem. sort_template.h would reduce the constant factor of the
indirect function call overhead by quite a bit. On a quick test here
with a table containing 1 million random float8 values, a Seq Scan and
in-memory Sort, EXPLAIN ANALYZE reports the sort took about 21ms:
(actual time=3D172.273..193.824). I really doubt anyone will be
concerned with 21ms when there's a list of 1 million tables needing to
be autovacuumed.

David