public inbox for [email protected]
help / color / mirror / Atom feedFrom: Sami Imseih <[email protected]>
To: Nathan Bossart <[email protected]>
Cc: Robert Haas <[email protected]>
Cc: David Rowley <[email protected]>
Cc: Robert Treat <[email protected]>
Cc: Jeremy Schneider <[email protected]>
Cc: [email protected]
Subject: Re: another autovacuum scheduling thread
Date: Tue, 10 Mar 2026 19:11:27 -0500
Message-ID: <CAA5RZ0seLxDmH9UftUA3vS8DrW+7T9HV5XK8yAuQxA8BaRkg2g@mail.gmail.com> (raw)
In-Reply-To: <abBEiOICOdbbhsEI@nathan>
References: <CAApHDvqK=dUa35oZjG8kh+A-aPof2pfsNmb-WXTMVHduKpm6bQ@mail.gmail.com>
<CA+TgmoYCLqE-1vX9uhryF7NhOAT0v6+RP8E303u6rRgpFUiWyg@mail.gmail.com>
<CAApHDvqFyEdWyEDT7NCKcqEi2sdchhOsA-+yWv_Zk=dRek4kgg@mail.gmail.com>
<CA+TgmoZE=-Pw=85u+eX6UpMdY02o1otEhQba0n2T78yVyV2N9g@mail.gmail.com>
<CAA5RZ0vzu4vfRP0podGDCBOn-OaK9Qs-oyksER5OtzanacqMQw@mail.gmail.com>
<CA+TgmoZ44jzFBB4zxoXrX6iMxv7TPzv5duPe44UPpoyBBDL48g@mail.gmail.com>
<CAApHDvo97hqpZR+vgVVSLQsPhVCA=yEerAGn9wzB_67vjmu6cA@mail.gmail.com>
<CA+TgmoaAgv5D6u+_4KKqQFGH1n+jsFZ1mFWh+CSGqDUtyBNsXg@mail.gmail.com>
<aam3dmZcia95-vB6@nathan>
<abAzhNqs5F8sJDdm@nathan>
<abBEiOICOdbbhsEI@nathan>
> Here's an updated patch with new GUCs that control how much each component
> contributes to the autovacuum score for a table. They default to 1.0, but
> can be set anywhere from 0.0 to 1.0 (inclusive). In theory, setting all of
> them to 0.0 should restore the original pg_class order prioritization that
> we have today. I haven't added corresponding reloptions for these GUCs, as
> I'm not convinced we need them, but I can add them if folks think they
> would be useful.
Starting with GUCs is OK by me.
Just a few things:
1/
+ Oid xid_age;
+ Oid mxid_age;
Is using Oid here intentional? I'm curious why not use uint32 for clarity?
2/
The new GUC docs says "...component of the score...", but without
introducing the concept of the prioritization score.
I think we should expand a bit more on this topic to help a user
understand and tune these more effectively. Attached is my
proposal for the docs. I tried to keep it informative without
being too verbose, and avoided making specific recommendations.
--
Sami Imseih
Amazon Web Services (AWS)
Attachments:
[application/octet-stream] v1-0001-autovacuum-scheduling-improvements-docs.patch (4.5K, 2-v1-0001-autovacuum-scheduling-improvements-docs.patch)
download | inline diff:
From eee71cdfdaff3295d52c1213d47ec1754e87a1f8 Mon Sep 17 00:00:00 2001
From: Sami Imseih <[email protected]>
Date: Tue, 10 Mar 2026 19:04:03 -0500
Subject: [PATCH v1 1/1] autovacuum scheduling improvements - docs
---
doc/src/sgml/maintenance.sgml | 95 +++++++++++++++++++++++++++++++++++
1 file changed, 95 insertions(+)
diff --git a/doc/src/sgml/maintenance.sgml b/doc/src/sgml/maintenance.sgml
index 7c958b06273..16b50f8e5b6 100644
--- a/doc/src/sgml/maintenance.sgml
+++ b/doc/src/sgml/maintenance.sgml
@@ -1054,6 +1054,99 @@ analyze threshold = analyze base threshold + analyze scale factor * number of tu
not automatically interrupted.
</para>
+ <sect3 id="autovacuum-priority">
+ <title>Processing Priority</title>
+
+ <para>
+ Autovacuum decides what to process in two steps: first it picks a
+ database, then it orders the tables within that database.
+ </para>
+
+ <para>
+ The launcher first checks for databases at risk of wraparound,
+ with transaction ID wraparound taking precedence over multixact
+ wraparound. If no database is at risk, the least recently
+ auto-vacuumed database is selected. Databases that have never been
+ connected to, or that have had no activity since the statistics were
+ last reset, are not considered except when at risk of wraparound.
+ </para>
+
+ <para>
+ Within a database, the autovacuum worker builds a list of all tables
+ that need vacuuming or analyzing and sorts them by a
+ <firstterm>priority score</firstterm>. Tables with higher scores are
+ processed first.
+ </para>
+
+ <para>
+ The score for each table is calculated as the maximum of several
+ component scores, each representing how far the table has exceeded a
+ particular threshold. Each component is multiplied by a configurable
+ weight parameter:
+
+ <itemizedlist>
+ <listitem>
+ <para>
+ <xref linkend="guc-autovacuum-vacuum-score-weight"/>: the ratio of
+ dead tuples to the table's vacuum threshold. For example, if a table
+ has 100 dead tuples and its vacuum threshold is 80, this component's
+ score is 1.25.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ <xref linkend="guc-autovacuum-vacuum-insert-score-weight"/>: the ratio
+ of inserted tuples (since the last vacuum) to the table's insert
+ vacuum threshold.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ <xref linkend="guc-autovacuum-analyze-score-weight"/>: the ratio of
+ modified tuples (since the last analyze) to the table's analyze
+ threshold.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ <xref linkend="guc-autovacuum-freeze-score-weight"/>: the ratio of
+ the table's transaction ID age
+ (<structfield>relfrozenxid</structfield>) to
+ <xref linkend="guc-autovacuum-freeze-max-age"/>. This component is
+ only considered for tables that have already exceeded their freeze max
+ age.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ <xref linkend="guc-autovacuum-multixact-freeze-score-weight"/>: the
+ ratio of the table's multixact age
+ (<structfield>relminmxid</structfield>) to
+ <xref linkend="guc-autovacuum-multixact-freeze-max-age"/>. Like the
+ freeze score, this is only considered for tables past their multixact
+ freeze max age.
+ </para>
+ </listitem>
+ </itemizedlist>
+
+ The final score is the maximum of these weighted components.
+ </para>
+
+ <para>
+ Tables that are approaching transaction ID or multixact wraparound receive
+ additional priority. Once a table's age surpasses
+ <xref linkend="guc-vacuum-failsafe-age"/> or
+ <xref linkend="guc-vacuum-multixact-failsafe-age"/>, its freeze score is
+ scaled aggressively so that it sorts toward the top of the list.
+ </para>
+
+ <para>
+ All weights default to 1.0. Reducing a weight to a value below 1.0
+ decreases the influence of that component on the final score, making
+ tables that exceed that particular threshold less likely to be processed
+ first.
+ </para>
+
<warning>
<para>
Regularly running commands that acquire locks conflicting with a
@@ -1061,6 +1154,8 @@ analyze threshold = analyze base threshold + analyze scale factor * number of tu
effectively prevent autovacuums from ever completing.
</para>
</warning>
+
+ </sect3>
</sect2>
</sect1>
--
2.50.1 (Apple Git-155)
view thread (143+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: another autovacuum scheduling thread
In-Reply-To: <CAA5RZ0seLxDmH9UftUA3vS8DrW+7T9HV5XK8yAuQxA8BaRkg2g@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox