MIME-Version: 1.0
References: 
 <CALj2ACV0HboeYiT1Z-=rb509WL3N0H9LsWRPqc_xUMEM-TM85g@mail.gmail.com>
 <acFan6knPZljm1Ay@nathan>
 <CAA5RZ0tz55uoQfcvLwvRWRPM0ry6SGfZ=-enQ4F0kaUgfPh+Vg@mail.gmail.com>
 <acGqIhkP6xo4Vv1l@nathan>
 <CAMFBP2q-oMzpEua9Hdcpag15VGZn7aEO-uaHZ4yVEO_HCm55_Q@mail.gmail.com>
 <CAApHDvqQN-B2sQov8nsfZOmx-VeJMauSf4kLa3A8LsK1tUyBNw@mail.gmail.com>
 <acLGoktU2lp4SIlU@nathan>
 <CALj2ACUTuHZugAgbDk-NNPY_CPbw+c4T=HkRvvqq-FKqnVb24A@mail.gmail.com>
 <acRROBvlokDLQZlh@nathan>
 <CAApHDvrD9YZ8RBgb6NDtzyay1PsFReLvWHVqUCMFw1=qxM1N1g@mail.gmail.com>
 <acRdY_cxDvff5zW4@nathan>
In-Reply-To: <acRdY_cxDvff5zW4@nathan>
From: David Rowley <dgrowleyml@gmail.com>
Date: Thu, 26 Mar 2026 13:28:16 +1300
Message-ID: 
 <CAApHDvqTDRbSVoGCv=1Y_TsAiggfEyGzH2QE+Hp57xwcBtfAOQ@mail.gmail.com>
Subject: Re: another autovacuum scheduling thread
To: Nathan Bossart <nathandbossart@gmail.com>
Cc: Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>,
 Jim Nasby <jnasby@upgrade.com>,
	Sami Imseih <samimseih@gmail.com>, Greg Burd <greg@burd.me>,
 Robert Haas <robertmhaas@gmail.com>,
	Robert Treat <rob@xzilla.net>, Jeremy Schneider <schneider@ardentperf.com>,
	pgsql-hackers <pgsql-hackers@postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Archived-At: 
 <https://www.postgresql.org/message-id/CAApHDvqTDRbSVoGCv%3D1Y_TsAiggfEyGzH2QE%2BHp57xwcBtfAOQ%40mail.gmail.com>
Precedence: bulk

On Thu, 26 Mar 2026 at 11:10, Nathan Bossart <nathandbossart@gmail.com> wrote:
>
> Here is what I have staged for commit.  I'll give this at least one more
> close read-through beforehand, but I'm hoping to commit it Thursday or
> Friday.  Thanks everybody for the thoughtful discussion.

A review:

1. I don't think the following is exactly true:

+       <xref linkend="guc-autovacuum-multixact-freeze-max-age"/>.  Furthermore,
+       this component increases greatly once the age surpasses
+       <xref linkend="guc-vacuum-multixact-failsafe-age"/>.  The final value

Should it mention dividing by the autovacuum_freeze_score_weight?

2. Is it worth expanding the following paragraph with some examples?
For example, raising the priority of analyze, someone might want to
set autovacuum_analyze_score_weight to 2.0, effectively doubling the
analyze scores.

+    <para>
+     To revert to the prioritization strategy used before
+     <productname>PostgreSQL</productname> 19 (i.e., the order the tables are
+     listed in the <literal>pg_class</literal> system catalog), set all of the
+     aforementioned "weight" parameters to <literal>0.0</literal>.
+    </para>

3. We talked about this a bit, but after reading the below comment and
looking at sort_template.h, I wonder if we might be putting too much
faith into the current qsort implementation. The presorted check
should pass when all the scores are 0.0. There's also the code that
runs before that for when n < 7 which shouldn't do any swapping. I'm
wondering if that comment might be putting a little too much faith
into that remaining true in the future. You're also calling the same
thing out in #2, so maybe we better take some safer measures to ensure
it remains true instead of relying on the current qsort code not
changing.

+ * between 0.0 and 10.0 (inclusive).  Setting all of these to 0.0 restores
+ * pre-v19 prioritization behavior:

4. I think we tend to favour not breaking ERROR strings up like this:

- elog(DEBUG3, "%s: vac: %.0f (threshold %.0f), ins: %.0f (threshold
%.0f), anl: %.0f (threshold %.0f)",
+ elog(DEBUG3,
+ "%s: "
+ "vac: %.0f (thresh %.0f, score %.2f), "
+ "ins: %.0f (thresh %.0f, score %.2f), "
+ "anl: %.0f (thresh %.0f, score %.2f), "
+ "xid score: %.2f, mxid score: %.2f",

It reduces grepability of error messages. I guess you could argue that
this has so many format specifiers that it's not that greppable
anyway... I sometimes do guess those to grep for things.

5. Maybe worth specifying the range in a comment in the following:

+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -733,6 +733,11 @@
 #autovacuum_multixact_freeze_max_age = 400000000        # maximum multixact age
                                                         # before forced vacuum
                                                         # (change
requires restart)
+#autovacuum_freeze_score_weight = 1.0
+#autovacuum_multixact_freeze_score_weight = 1.0
+#autovacuum_vacuum_score_weight = 1.0
+#autovacuum_vacuum_insert_score_weight = 1.0
+#autovacuum_analyze_score_weight = 1.0

David