Date: Wed, 8 Oct 2025 10:18:17 -0500
From: Nathan Bossart <nathandbossart@gmail.com>
To: pgsql-hackers@postgresql.org
Subject: another autovacuum scheduling thread
Message-ID: <aOaAuXREwnPZVISO@nathan>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="2AXllIwTMvAwquZP"
Content-Disposition: inline
Archived-At: <https://www.postgresql.org/message-id/aOaAuXREwnPZVISO%40nathan>
Precedence: bulk


--2AXllIwTMvAwquZP
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline

/me dons flame-proof suit

My goal with this thread is to produce some incremental autovacuum
scheduling improvements for v19, but realistically speaking, I know that
it's a bit of a long-shot.  There have been many discussions over the
years, and I've read through a few of them [0] [1] [2] [3] [4], but there
are certainly others I haven't found.  Since this seems to be a contentious
topic, I figured I'd start small to see if we can get _something_
committed.

While I am by no means wedded to a specific idea, my current concrete
proposal (proof-of-concept patch attached) is to start by ordering the
tables a worker will process by (M)XID age.  Here are the reasons:

* We do some amount of prioritization of databases at risk of wraparound at
database level, per the following comment from autovacuum.c:

	 * Choose a database to connect to.  We pick the database that was least
	 * recently auto-vacuumed, or one that needs vacuuming to prevent Xid
	 * wraparound-related data loss.  If any db at risk of Xid wraparound is
	 * found, we pick the one with oldest datfrozenxid, independently of
	 * autovacuum times; similarly we pick the one with the oldest datminmxid
	 * if any is in MultiXactId wraparound.  Note that those in Xid wraparound
	 * danger are given more priority than those in multi wraparound danger.

However, we do no such prioritization of the tables within a database.  In
fact, the ordering of the tables is effectively random.  IMHO this gives us
quite a bit of wiggle room to experiment; since we are processing tables in
no specific order today, changing the order to something vacuuming-related
seems more likely to help than it is to harm.

* Prioritizing tables based on their (M)XID age might help avoid more
aggressive vacuums, not to mention wraparound.  Of course, there are
scenarios where this doesn't work.  For example, the age of a table may
have changed greatly between the time we recorded it and the time we
process it.  Or maybe there is another table in a different database that
is more important from a wraparound perspective.  We could complicate the
patch to try to handle some of these things, but I maintain that even some
basic, incremental scheduling improvements would be better than the status
quo.  And we can always change it further in the future to handle these
problems and to consider other things like bloat.

The attached patch works by storing the maximum of the XID age and the MXID
age in the list with the OIDs and sorting it prior to processing.

Thoughts?

[0] https://postgr.es/m/CA%2BTgmoafJPjB3WVqB3FrGWUU4NLRc3VHx8GXzLL-JM%2B%2BJPwK%2BQ%40mail.gmail.com
[1] https://postgr.es/m/CAEG8a3%2B3fwQbgzak%2Bh3Q7Bp%3DvK_aWhw1X7w7g5RCgEW9ufdvtA%40mail.gmail.com
[2] https://postgr.es/m/CAD21AoBUaSRBypA6pd9ZD%3DU-2TJCHtbyZRmrS91Nq0eVQ0B3BA%40mail.gmail.com
[3] https://postgr.es/m/CA%2BTgmobT3m%3D%2BdU5HF3VGVqiZ2O%2Bv6P5wN1Gj%2BPrq%2Bhj7dAm9AQ%40mail.gmail.com
[4] https://postgr.es/m/20130124215715.GE4528%40alvh.no-ip.org

-- 
nathan

--2AXllIwTMvAwquZP
Content-Type: text/plain; charset=us-ascii
Content-Disposition: attachment;
	filename=v1-0001-autovacuum-order-tables-by-m-xid-age.patch