From: Nathan Bossart <nathandbossart@gmail.com>
To: David Rowley <dgrowleyml@gmail.com>
Cc: Jim Nasby <jnasby@upgrade.com>
Cc: Sami Imseih <samimseih@gmail.com>
Cc: Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>
Cc: Greg Burd <greg@burd.me>
Cc: Robert Haas <robertmhaas@gmail.com>
Cc: Robert Treat <rob@xzilla.net>
Cc: Jeremy Schneider <schneider@ardentperf.com>
Cc: pgsql-hackers <pgsql-hackers@postgresql.org>
Subject: Re: another autovacuum scheduling thread
Date: Tue, 24 Mar 2026 12:15:14 -0500
Message-ID: <acLGoktU2lp4SIlU@nathan> (raw)
In-Reply-To: <CAApHDvqQN-B2sQov8nsfZOmx-VeJMauSf4kLa3A8LsK1tUyBNw@mail.gmail.com>
References: <CAA5RZ0s7k6yve1Bq1OY+aXU3ifc_fvOiYkpE2VxwrUcHr2AtTg@mail.gmail.com>
<CAApHDvpvkKYB6z-i1kiO9iePF0fz18-CaS9DZEWnSx1eO0RGFQ@mail.gmail.com>
<CAA5RZ0tPvWLVXhDMC3t3LhwTM-OnWHSvPo7tnZzOphR=HB4kgQ@mail.gmail.com>
<ab2uoj5yU8CVUJ_H@nathan>
<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>
On Tue, Mar 24, 2026 at 01:32:40PM +1300, David Rowley wrote:
> +1 for main patch first. I do think a view would be useful as a
> follow-up. However, which columns we put in that view might have some
> influence on how the current patch should look. I think the view
> should show the individual scores and the total score as the Max() of
> the individual scores. If we didn't do that, it might be confusing to
> the user which aspect of the score the final score is derived from.
> That might mean that it'd be better to have
> relation_needs_vacanalyze() output the scores individually, or perhaps
> populate a struct that we pass in that gets allocated on the stack
> during do_autovacuum(). That'd mean a bit less churn if we go with the
> view containing individual scores.
Agreed. Here's a first try at that. I also updated the DEBUG3 at the end
of relation_needs_vacanalyze() to show the individual scores. The comment
above that function might need some work, and we might need a bit of
additional commentary elsewhere.
--
nathan
Attachments:v15-0001-autovacuum-scheduling-improvements.patch (text/plain, 30.4K) download
From f2f0b6a1f3b7593dea9a4800735fa21f9b8371e0 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <nathan@postgresql.org>
Date: Fri, 10 Oct 2025 12:28:37 -0500
Subject: [PATCH v15 1/1] autovacuum scheduling improvements
---
doc/src/sgml/config.sgml | 90 ++++++
doc/src/sgml/maintenance.sgml | 92 ++++++
src/backend/postmaster/autovacuum.c | 266 ++++++++++++++----
src/backend/utils/misc/guc_parameters.dat | 40 +++
src/backend/utils/misc/postgresql.conf.sample | 5 +
src/include/postmaster/autovacuum.h | 6 +-
src/tools/pgindent/typedefs.list | 2 +
7 files changed, 453 insertions(+), 48 deletions(-)
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgmlindex 8cdd826fbd3..229f41353eb 100644--- a/doc/src/sgml/config.sgml+++ b/doc/src/sgml/config.sgml@@ -9395,6 +9395,96 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv;
</listitem>
</varlistentry>
+ <varlistentry id="guc-autovacuum-freeze-score-weight" xreflabel="autovacuum_freeze_score_weight">+ <term><varname>autovacuum_freeze_score_weight</varname> (<type>floating point</type>)+ <indexterm>+ <primary><varname>autovacuum_freeze_score_weight</varname></primary>+ <secondary>configuration parameter</secondary>+ </indexterm>+ </term>+ <listitem>+ <para>+ Specifies the scaling factor of the transaction ID age component of+ the score used by autovacuum for prioritization purposes. The default+ is <literal>1.0</literal>. This parameter can only be set in the+ <filename>postgresql.conf</filename> file or on the server command+ line. See <xref linkend="autovacuum-priority"/> for more information.+ </para>+ </listitem>+ </varlistentry>++ <varlistentry id="guc-autovacuum-multixact-freeze-score-weight" xreflabel="autovacuum_multixact_freeze_score_weight">+ <term><varname>autovacuum_multixact_freeze_score_weight</varname> (<type>floating point</type>)+ <indexterm>+ <primary><varname>autovacuum_multixact_freeze_score_weight</varname></primary>+ <secondary>configuration parameter</secondary>+ </indexterm>+ </term>+ <listitem>+ <para>+ Specifies the scaling factor of the multixact ID age component of the+ score used by autovacuum for prioritization purposes. The default is+ <literal>1.0</literal>. This parameter can only be set in the+ <filename>postgresql.conf</filename> file or on the server command+ line. See <xref linkend="autovacuum-priority"/> for more information.+ </para>+ </listitem>+ </varlistentry>++ <varlistentry id="guc-autovacuum-vacuum-score-weight" xreflabel="autovacuum_vacuum_score_weight">+ <term><varname>autovacuum_vacuum_score_weight</varname> (<type>floating point</type>)+ <indexterm>+ <primary><varname>autovacuum_vacuum_score_weight</varname></primary>+ <secondary>configuration parameter</secondary>+ </indexterm>+ </term>+ <listitem>+ <para>+ Specifies the scaling factor of the vacuum threshold component of the+ score used by autovacuum for prioritization purposes. The default is+ <literal>1.0</literal>. This parameter can only be set in the+ <filename>postgresql.conf</filename> file or on the server command+ line. See <xref linkend="autovacuum-priority"/> for more information.+ </para>+ </listitem>+ </varlistentry>++ <varlistentry id="guc-autovacuum-vacuum-insert-score-weight" xreflabel="autovacuum_vacuum_insert_score_weight">+ <term><varname>autovacuum_vacuum_insert_score_weight</varname> (<type>floating point</type>)+ <indexterm>+ <primary><varname>autovacuum_vacuum_insert_score_weight</varname></primary>+ <secondary>configuration parameter</secondary>+ </indexterm>+ </term>+ <listitem>+ <para>+ Specifies the scaling factor of the vacuum insert threshold component+ of the score used by autovacuum for prioritization purposes. The+ default is <literal>1.0</literal>. This parameter can only be set in+ the <filename>postgresql.conf</filename> file or on the server command+ line. See <xref linkend="autovacuum-priority"/> for more information.+ </para>+ </listitem>+ </varlistentry>++ <varlistentry id="guc-autovacuum-analyze-score-weight" xreflabel="autovacuum_analyze_score_weight">+ <term><varname>autovacuum_analyze_score_weight</varname> (<type>floating point</type>)+ <indexterm>+ <primary><varname>autovacuum_analyze_score_weight</varname></primary>+ <secondary>configuration parameter</secondary>+ </indexterm>+ </term>+ <listitem>+ <para>+ Specifies the scaling factor of the analyze threshold component of the+ score used by autovacuum for prioritization purposes. The default is+ <literal>1.0</literal>. This parameter can only be set in the+ <filename>postgresql.conf</filename> file or on the server command+ line. See <xref linkend="autovacuum-priority"/> for more information.+ </para>+ </listitem>+ </varlistentry>+
</variablelist>
</sect2>
diff --git a/doc/src/sgml/maintenance.sgml b/doc/src/sgml/maintenance.sgmlindex 75c22405a09..b5a191c130b 100644--- a/doc/src/sgml/maintenance.sgml+++ b/doc/src/sgml/maintenance.sgml@@ -1062,6 +1062,98 @@ analyze threshold = analyze base threshold + analyze scale factor * number of tu
effectively prevent autovacuums from ever completing.
</para>
</warning>
++ <sect3 id="autovacuum-priority">+ <title>Autovacuum Prioritization</title>++ <para>+ Autovacuum decides what to process in two steps: first it chooses a+ database, then it chooses the tables within that database. The autovacuum+ launcher process prioritizes databases at risk of transaction ID or+ multixact ID wraparound, else it chooses the database processed least+ recently. As an exception, it skips databases with no connections or no+ activity since the last statistics reset, unless at risk of wraparound.+ </para>++ <para>+ Within a database, the autovacuum worker process builds a list of tables+ that require vacuum or analyze and sorts them using a scoring system. It+ scores each table by taking the maximum value of several component scores+ representing various criteria important to vacuum or analyze. Those+ components are as follows:+ </para>++ <itemizedlist>+ <listitem>+ <para>+ The <emphasis>transaction ID</emphasis> component measures the age in+ transactions of the table's+ <structname>pg_class</structname>.<structfield>relfrozenxid</structfield>+ field as compared to <xref linkend="guc-autovacuum-freeze-max-age"/>.+ Furthermore, this component increases greatly once the age surpasses+ <xref linkend="guc-vacuum-failsafe-age"/>. The final value for this+ component can be adjusted via+ <xref linkend="guc-autovacuum-freeze-score-weight"/>.+ </para>+ </listitem>++ <listitem>+ <para>+ The <emphasis>multixact ID</emphasis> component measures the age in+ multixacts of the table's+ <structname>pg_class</structname>.<structfield>relminmxid</structfield>+ field as compared to+ <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+ for this component can be adjusted via+ <xref linkend="guc-autovacuum-multixact-freeze-score-weight"/>.+ </para>+ </listitem>++ <listitem>+ <para>+ The <emphasis>vacuum</emphasis> component measures the number of updated+ or deleted tuples as compared to the threshold calculated with+ <xref linkend="guc-autovacuum-vacuum-threshold"/>,+ <xref linkend="guc-autovacuum-vacuum-scale-factor"/>, and+ <xref linkend="guc-autovacuum-vacuum-max-threshold"/>. The final value+ for this component can be adjusted via+ <xref linkend="guc-autovacuum-vacuum-score-weight"/>.+ </para>+ </listitem>++ <listitem>+ <para>+ The <emphasis>vacuum insert</emphasis> component measures the number of+ inserted tuples as compared to the threshold calculated with+ <xref linkend="guc-autovacuum-vacuum-insert-threshold"/> and+ <xref linkend="guc-autovacuum-vacuum-insert-scale-factor"/>. The final+ value for this component can be adjusted via+ <xref linkend="guc-autovacuum-vacuum-insert-score-weight"/>.+ </para>+ </listitem>++ <listitem>+ <para>+ The <emphasis>analyze</emphasis> component measures the number of+ inserted, updated, or deleted tuples as compared to the threshold+ calculated with+ <xref linkend="guc-autovacuum-analyze-threshold"/> and+ <xref linkend="guc-autovacuum-analyze-scale-factor"/>. The final value+ for this component can be adjusted via+ <xref linkend="guc-autovacuum-analyze-score-weight"/>.+ </para>+ </listitem>+ </itemizedlist>++ <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>+ </sect3>
</sect2>
</sect1>
diff --git a/src/backend/postmaster/autovacuum.c b/src/backend/postmaster/autovacuum.cindex 219673db930..35b9206a51a 100644--- a/src/backend/postmaster/autovacuum.c+++ b/src/backend/postmaster/autovacuum.c@@ -62,6 +62,7 @@
*/
#include "postgres.h"
+#include <math.h>
#include <signal.h>
#include <sys/time.h>
#include <unistd.h>
@@ -130,7 +131,11 @@ int autovacuum_anl_thresh;
double autovacuum_anl_scale;
int autovacuum_freeze_max_age;
int autovacuum_multixact_freeze_max_age;
-+double autovacuum_freeze_score_weight = 1.0;+double autovacuum_multixact_freeze_score_weight = 1.0;+double autovacuum_vacuum_score_weight = 1.0;+double autovacuum_vacuum_insert_score_weight = 1.0;+double autovacuum_analyze_score_weight = 1.0;
double autovacuum_vac_cost_delay;
int autovacuum_vac_cost_limit;
@@ -312,6 +317,22 @@ static AutoVacuumShmemStruct *AutoVacuumShmem;
static dlist_head DatabaseList = DLIST_STATIC_INIT(DatabaseList);
static MemoryContext DatabaseListCxt = NULL;
+typedef struct+{+ double max;+ double xid;+ double mxid;+ double vac;+ double vac_ins;+ double anl;+} AutoVacuumScores;++typedef struct+{+ Oid oid;+ double score;+} TableToProcess;+
/*
* Dummy pointer to persuade Valgrind that we've not leaked the array of
* avl_dbase structs. Make it global to ensure the compiler doesn't
@@ -350,7 +371,8 @@ static void relation_needs_vacanalyze(Oid relid, AutoVacOpts *relopts,
Form_pg_class classForm,
PgStat_StatTabEntry *tabentry,
int effective_multixact_freeze_max_age,
- bool *dovacuum, bool *doanalyze, bool *wraparound);+ bool *dovacuum, bool *doanalyze, bool *wraparound,+ AutoVacuumScores *scores);
static void autovacuum_do_vac_analyze(autovac_table *tab,
BufferAccessStrategy bstrategy);
@@ -1867,6 +1889,15 @@ get_database_list(void)
return dblist;
}
+static int+TableToProcessComparator(const ListCell *a, const ListCell *b)+{+ TableToProcess *t1 = (TableToProcess *) lfirst(a);+ TableToProcess *t2 = (TableToProcess *) lfirst(b);++ return (t2->score < t1->score) ? -1 : (t2->score > t1->score) ? 1 : 0;+}+
/*
* Process a database table-by-table
*
@@ -1880,7 +1911,7 @@ do_autovacuum(void)
HeapTuple tuple;
TableScanDesc relScan;
Form_pg_database dbForm;
- List *table_oids = NIL;+ List *tables_to_process = NIL;
List *orphan_oids = NIL;
HASHCTL ctl;
HTAB *table_toast_map;
@@ -1992,6 +2023,7 @@ do_autovacuum(void)
bool dovacuum;
bool doanalyze;
bool wraparound;
+ AutoVacuumScores scores;
if (classForm->relkind != RELKIND_RELATION &&
classForm->relkind != RELKIND_MATVIEW)
@@ -2032,11 +2064,18 @@ do_autovacuum(void)
/* Check if it needs vacuum or analyze */
relation_needs_vacanalyze(relid, relopts, classForm, tabentry,
effective_multixact_freeze_max_age,
- &dovacuum, &doanalyze, &wraparound);+ &dovacuum, &doanalyze, &wraparound,+ &scores);- /* Relations that need work are added to table_oids */+ /* Relations that need work are added to tables_to_process */
if (dovacuum || doanalyze)
- table_oids = lappend_oid(table_oids, relid);+ {+ TableToProcess *table = palloc_object(TableToProcess);++ table->oid = relid;+ table->score = scores.max;+ tables_to_process = lappend(tables_to_process, table);+ }
/*
* Remember TOAST associations for the second pass. Note: we must do
@@ -2092,6 +2131,7 @@ do_autovacuum(void)
bool dovacuum;
bool doanalyze;
bool wraparound;
+ AutoVacuumScores scores;
/*
* We cannot safely process other backends' temp tables, so skip 'em.
@@ -2124,11 +2164,18 @@ do_autovacuum(void)
relation_needs_vacanalyze(relid, relopts, classForm, tabentry,
effective_multixact_freeze_max_age,
- &dovacuum, &doanalyze, &wraparound);+ &dovacuum, &doanalyze, &wraparound,+ &scores);
/* ignore analyze for toast tables */
if (dovacuum)
- table_oids = lappend_oid(table_oids, relid);+ {+ TableToProcess *table = palloc_object(TableToProcess);++ table->oid = relid;+ table->score = scores.max;+ tables_to_process = lappend(tables_to_process, table);+ }
/* Release stuff to avoid leakage */
if (free_relopts)
@@ -2252,6 +2299,8 @@ do_autovacuum(void)
MemoryContextSwitchTo(AutovacMemCxt);
}
+ list_sort(tables_to_process, TableToProcessComparator);+
/*
* Optionally, create a buffer access strategy object for VACUUM to use.
* We use the same BufferAccessStrategy object for all tables VACUUMed by
@@ -2280,9 +2329,9 @@ do_autovacuum(void)
/*
* Perform operations on collected tables.
*/
- foreach(cell, table_oids)+ foreach_ptr(TableToProcess, table, tables_to_process)
{
- Oid relid = lfirst_oid(cell);+ Oid relid = table->oid;
HeapTuple classTup;
autovac_table *tab;
bool isshared;
@@ -2513,7 +2562,7 @@ deleted:
pg_atomic_test_set_flag(&MyWorkerInfo->wi_dobalance);
}
- list_free(table_oids);+ list_free_deep(tables_to_process);
/*
* Perform additional work items, as requested by backends.
@@ -2915,6 +2964,7 @@ recheck_relation_needs_vacanalyze(Oid relid,
bool *wraparound)
{
PgStat_StatTabEntry *tabentry;
+ AutoVacuumScores scores;
/* fetch the pgstat table entry */
tabentry = pgstat_fetch_stat_tabentry_ext(classForm->relisshared,
@@ -2922,15 +2972,12 @@ recheck_relation_needs_vacanalyze(Oid relid,
relation_needs_vacanalyze(relid, avopts, classForm, tabentry,
effective_multixact_freeze_max_age,
- dovacuum, doanalyze, wraparound);+ dovacuum, doanalyze, wraparound,+ &scores);
/* Release tabentry to avoid leakage */
if (tabentry)
pfree(tabentry);
-- /* ignore ANALYZE for toast tables */- if (classForm->relkind == RELKIND_TOASTVALUE)- *doanalyze = false;
}
/*
@@ -2971,6 +3018,43 @@ recheck_relation_needs_vacanalyze(Oid relid,
* autovacuum_vacuum_threshold GUC variable. Similarly, a vac_scale_factor
* value < 0 is substituted with the value of
* autovacuum_vacuum_scale_factor GUC variable. Ditto for analyze.
+ *+ * This function also returns a score that can be used to sort the list of+ * tables to process. The idea is to have autovacuum prioritize tables that+ * are furthest beyond their thresholds (e.g., a table nearing transaction ID+ * wraparound should be vacuumed first). This prioritization scheme is+ * certainly far from perfect; there are simply too many possibilities for any+ * scoring technique to work across all workloads, and the situation might+ * change significantly between the time we calculate the score and the time+ * that autovacuum gets to processing it. However, we have attempted to+ * develop something that is expected to work for a large portion of workloads+ * with reasonable parameter settings.+ *+ * 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. If all other scores are below that value, the returned score+ * will be 1.25. The other criteria considered for the score are the table+ * ages (both relfrozenxid and relminmxid) compared to the corresponding+ * freeze-max-age setting, the number of updated/deleted tuples compared to the+ * vacuum threshold, and the number of inserted/updated/deleted tuples compared+ * to the analyze threshold.+ *+ * One exception to the previous paragraph is for tables nearing wraparound,+ * i.e., those that have surpassed the effective failsafe ages. In that case,+ * the relfrozen/relminmxid-based score is scaled aggressively so that the+ * table has a decent chance of sorting to the top of the list.+ *+ * To adjust how strongly each component contributes to the score, the+ * following parameters can be adjusted from their default of 1.0 to anywhere+ * between 0.0 and 10.0 (inclusive). Setting all of these to 0.0 restores+ * pre-v19 prioritization behavior:+ *+ * autovacuum_freeze_score_weight+ * autovacuum_multixact_freeze_score_weight+ * autovacuum_vacuum_score_weight+ * autovacuum_vacuum_insert_score_weight+ * autovacuum_analyze_score_weight
*/
static void
relation_needs_vacanalyze(Oid relid,
@@ -2981,7 +3065,8 @@ relation_needs_vacanalyze(Oid relid,
/* output params below */
bool *dovacuum,
bool *doanalyze,
- bool *wraparound)+ bool *wraparound,+ AutoVacuumScores *scores)
{
bool force_vacuum;
bool av_enabled;
@@ -3010,11 +3095,16 @@ relation_needs_vacanalyze(Oid relid,
int multixact_freeze_max_age;
TransactionId xidForceLimit;
TransactionId relfrozenxid;
+ MultiXactId relminmxid;
MultiXactId multiForceLimit;
Assert(classForm != NULL);
Assert(OidIsValid(relid));
+ memset(scores, 0, sizeof(AutoVacuumScores));+ *dovacuum = false;+ *doanalyze = false;+
/*
* Determine vacuum/analyze equation parameters. We have two possible
* sources: the passed reloptions (which could be a main table or a toast
@@ -3062,17 +3152,17 @@ relation_needs_vacanalyze(Oid relid,
av_enabled = (relopts ? relopts->enabled : true);
+ relfrozenxid = classForm->relfrozenxid;+ relminmxid = classForm->relminmxid;+
/* Force vacuum if table is at risk of wraparound */
xidForceLimit = recentXid - freeze_max_age;
if (xidForceLimit < FirstNormalTransactionId)
xidForceLimit -= FirstNormalTransactionId;
- relfrozenxid = classForm->relfrozenxid;
force_vacuum = (TransactionIdIsNormal(relfrozenxid) &&
TransactionIdPrecedes(relfrozenxid, xidForceLimit));
if (!force_vacuum)
{
- MultiXactId relminmxid = classForm->relminmxid;-
multiForceLimit = recentMulti - multixact_freeze_max_age;
if (multiForceLimit < FirstMultiXactId)
multiForceLimit -= FirstMultiXactId;
@@ -3081,13 +3171,65 @@ relation_needs_vacanalyze(Oid relid,
}
*wraparound = force_vacuum;
+ /* Update the score. */+ if (force_vacuum)+ {+ uint32 xid_age;+ uint32 mxid_age;+ int effective_xid_failsafe_age;+ int effective_mxid_failsafe_age;++ /*+ * To calculate the (M)XID age portion of the score, divide the age by+ * its respective *_freeze_max_age parameter.+ */+ xid_age = TransactionIdIsNormal(relfrozenxid) ? recentXid - relfrozenxid : 0;+ mxid_age = MultiXactIdIsValid(relminmxid) ? recentMulti - relminmxid : 0;++ scores->xid = (double) xid_age / freeze_max_age;+ scores->mxid = (double) mxid_age / multixact_freeze_max_age;++ /*+ * To ensure tables are given increased priority once they begin+ * approaching wraparound, we scale the score aggressively if the ages+ * surpass vacuum_failsafe_age or vacuum_multixact_failsafe_age.+ *+ * As in vacuum_xid_failsafe_check(), the effective failsafe age is no+ * less than 105% the value of the respective *_freeze_max_age+ * parameter. Note that per-table settings could result in a low+ * score even if the table surpasses the failsafe settings. However,+ * this is a strange enough corner case that we don't bother trying to+ * handle it.+ *+ * We further adjust the effective failsafe ages with the weight+ * parameters so that increasing them lowers the ages at which we+ * begin scaling aggressively.+ */+ effective_xid_failsafe_age = Max(vacuum_failsafe_age,+ autovacuum_freeze_max_age * 1.05);+ effective_mxid_failsafe_age = Max(vacuum_multixact_failsafe_age,+ autovacuum_multixact_freeze_max_age * 1.05);++ if (autovacuum_freeze_score_weight > 1.0)+ effective_xid_failsafe_age /= autovacuum_freeze_score_weight;+ if (autovacuum_multixact_freeze_score_weight > 1.0)+ effective_mxid_failsafe_age /= autovacuum_multixact_freeze_score_weight;++ if (xid_age >= effective_xid_failsafe_age)+ scores->xid = pow(scores->xid, Max(1.0, (double) xid_age / 100000000));+ if (mxid_age >= effective_mxid_failsafe_age)+ scores->mxid = pow(scores->mxid, Max(1.0, (double) mxid_age / 100000000));++ scores->xid *= autovacuum_freeze_score_weight;+ scores->mxid *= autovacuum_multixact_freeze_score_weight;++ scores->max = Max(scores->xid, scores->mxid);+ *dovacuum = true;+ }+
/* User disabled it in pg_class.reloptions? (But ignore if at risk) */
if (!av_enabled && !force_vacuum)
- {- *doanalyze = false;- *dovacuum = false;
return;
- }
/*
* If we found stats for the table, and autovacuum is currently enabled,
@@ -3136,34 +3278,64 @@ relation_needs_vacanalyze(Oid relid,
vac_ins_scale_factor * reltuples * pcnt_unfrozen;
anlthresh = (float4) anl_base_thresh + anl_scale_factor * reltuples;
+ /*+ * Determine if this table needs vacuum, and update the score if it+ * does.+ */+ if (vactuples > vacthresh)+ {+ scores->vac = (double) vactuples / Max(vacthresh, 1);+ scores->vac *= autovacuum_vacuum_score_weight;+ scores->max = Max(scores->max, scores->vac);+ *dovacuum = true;+ }++ if (vac_ins_base_thresh >= 0 && instuples > vacinsthresh)+ {+ scores->vac_ins = (double) instuples / Max(vacinsthresh, 1);+ scores->vac_ins *= autovacuum_vacuum_insert_score_weight;+ scores->max = Max(scores->max, scores->vac_ins);+ *dovacuum = true;+ }++ /*+ * Determine if this table needs analyze, and update the score if it+ * does. Note that we don't analyze TOAST tables and pg_statistic.+ */+ if (anltuples > anlthresh &&+ relid != StatisticRelationId &&+ classForm->relkind != RELKIND_TOASTVALUE)+ {+ scores->anl = (double) anltuples / Max(anlthresh, 1);+ scores->anl *= autovacuum_analyze_score_weight;+ scores->max = Max(scores->max, scores->anl);+ *doanalyze = true;+ }+
if (vac_ins_base_thresh >= 0)
- 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",
NameStr(classForm->relname),
- vactuples, vacthresh, instuples, vacinsthresh, anltuples, anlthresh);+ vactuples, vacthresh, scores->vac,+ instuples, vacinsthresh, scores->vac_ins,+ anltuples, anlthresh, scores->anl,+ scores->xid, scores->mxid);
else
- elog(DEBUG3, "%s: vac: %.0f (threshold %.0f), ins: (disabled), anl: %.0f (threshold %.0f)",+ elog(DEBUG3,+ "%s: "+ "vac: %.0f (thresh %.0f, score %.2f), "+ "ins: (disabled), "+ "anl: %.0f (thresh %.0f, score %.2f), "+ "xid score: %.2f, mxid score: %.2f",
NameStr(classForm->relname),
- vactuples, vacthresh, anltuples, anlthresh);-- /* Determine if this table needs vacuum or analyze. */- *dovacuum = force_vacuum || (vactuples > vacthresh) ||- (vac_ins_base_thresh >= 0 && instuples > vacinsthresh);- *doanalyze = (anltuples > anlthresh);- }- else- {- /*- * Skip a table not found in stat hash, unless we have to force vacuum- * for anti-wrap purposes. If it's not acted upon, there's no need to- * vacuum it.- */- *dovacuum = force_vacuum;- *doanalyze = false;+ vactuples, vacthresh, scores->vac,+ anltuples, anlthresh, scores->anl,+ scores->xid, scores->mxid);
}
-- /* ANALYZE refuses to work with pg_statistic */- if (relid == StatisticRelationId)- *doanalyze = false;
}
/*
diff --git a/src/backend/utils/misc/guc_parameters.dat b/src/backend/utils/misc/guc_parameters.datindex 0c9854ad8fc..0a862693fcd 100644--- a/src/backend/utils/misc/guc_parameters.dat+++ b/src/backend/utils/misc/guc_parameters.dat@@ -136,6 +136,14 @@
max => '100.0',
},
+{ name => 'autovacuum_analyze_score_weight', type => 'real', context => 'PGC_SIGHUP', group => 'VACUUM_AUTOVACUUM',+ short_desc => 'Scaling factor of analyze score for autovacuum prioritization.',+ variable => 'autovacuum_analyze_score_weight',+ boot_val => '1.0',+ min => '0.0',+ max => '10.0',+},+
{ name => 'autovacuum_analyze_threshold', type => 'int', context => 'PGC_SIGHUP', group => 'VACUUM_AUTOVACUUM',
short_desc => 'Minimum number of tuple inserts, updates, or deletes prior to analyze.',
variable => 'autovacuum_anl_thresh',
@@ -154,6 +162,14 @@
max => '2000000000',
},
+{ name => 'autovacuum_freeze_score_weight', type => 'real', context => 'PGC_SIGHUP', group => 'VACUUM_AUTOVACUUM',+ short_desc => 'Scaling factor of freeze score for autovacuum prioritization.',+ variable => 'autovacuum_freeze_score_weight',+ boot_val => '1.0',+ min => '0.0',+ max => '10.0',+},+
{ name => 'autovacuum_max_workers', type => 'int', context => 'PGC_SIGHUP', group => 'VACUUM_AUTOVACUUM',
short_desc => 'Sets the maximum number of simultaneously running autovacuum worker processes.',
variable => 'autovacuum_max_workers',
@@ -171,6 +187,14 @@
max => '2000000000',
},
+{ name => 'autovacuum_multixact_freeze_score_weight', type => 'real', context => 'PGC_SIGHUP', group => 'VACUUM_AUTOVACUUM',+ short_desc => 'Scaling factor of multixact freeze score for autovacuum prioritization.',+ variable => 'autovacuum_multixact_freeze_score_weight',+ boot_val => '1.0',+ min => '0.0',+ max => '10.0',+},+
{ name => 'autovacuum_naptime', type => 'int', context => 'PGC_SIGHUP', group => 'VACUUM_AUTOVACUUM',
short_desc => 'Time to sleep between autovacuum runs.',
flags => 'GUC_UNIT_S',
@@ -207,6 +231,14 @@
max => '100.0',
},
+{ name => 'autovacuum_vacuum_insert_score_weight', type => 'real', context => 'PGC_SIGHUP', group => 'VACUUM_AUTOVACUUM',+ short_desc => 'Scaling factor of vacuum insert score for autovacuum prioritization.',+ variable => 'autovacuum_vacuum_insert_score_weight',+ boot_val => '1.0',+ min => '0.0',+ max => '10.0',+},+
{ name => 'autovacuum_vacuum_insert_threshold', type => 'int', context => 'PGC_SIGHUP', group => 'VACUUM_AUTOVACUUM',
short_desc => 'Minimum number of tuple inserts prior to vacuum.',
long_desc => '-1 disables insert vacuums.',
@@ -233,6 +265,14 @@
max => '100.0',
},
+{ name => 'autovacuum_vacuum_score_weight', type => 'real', context => 'PGC_SIGHUP', group => 'VACUUM_AUTOVACUUM',+ short_desc => 'Scaling factor of vacuum score for autovacuum prioritization.',+ variable => 'autovacuum_vacuum_score_weight',+ boot_val => '1.0',+ min => '0.0',+ max => '10.0',+},+
{ name => 'autovacuum_vacuum_threshold', type => 'int', context => 'PGC_SIGHUP', group => 'VACUUM_AUTOVACUUM',
short_desc => 'Minimum number of tuple updates or deletes prior to vacuum.',
variable => 'autovacuum_vac_thresh',
diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sampleindex e4abe6c0077..ab9cbebbc3f 100644--- a/src/backend/utils/misc/postgresql.conf.sample+++ 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
#autovacuum_vacuum_cost_delay = 2ms # default vacuum cost delay for
# autovacuum, in milliseconds;
# -1 means use vacuum_cost_delay
diff --git a/src/include/postmaster/autovacuum.h b/src/include/postmaster/autovacuum.hindex 5aa0f3a8ac1..b21d111d4d5 100644--- a/src/include/postmaster/autovacuum.h+++ b/src/include/postmaster/autovacuum.h@@ -43,7 +43,11 @@ extern PGDLLIMPORT int autovacuum_freeze_max_age;
extern PGDLLIMPORT int autovacuum_multixact_freeze_max_age;
extern PGDLLIMPORT double autovacuum_vac_cost_delay;
extern PGDLLIMPORT int autovacuum_vac_cost_limit;
-+extern PGDLLIMPORT double autovacuum_freeze_score_weight;+extern PGDLLIMPORT double autovacuum_multixact_freeze_score_weight;+extern PGDLLIMPORT double autovacuum_vacuum_score_weight;+extern PGDLLIMPORT double autovacuum_vacuum_insert_score_weight;+extern PGDLLIMPORT double autovacuum_analyze_score_weight;
extern PGDLLIMPORT int Log_autovacuum_min_duration;
extern PGDLLIMPORT int Log_autoanalyze_min_duration;
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.listindex 8df23840e57..43ae8ddffc8 100644--- a/src/tools/pgindent/typedefs.list+++ b/src/tools/pgindent/typedefs.list@@ -187,6 +187,7 @@ AuthToken
AutoPrewarmReadStreamData
AutoPrewarmSharedState
AutoVacOpts
+AutoVacuumScores
AutoVacuumShmemStruct
AutoVacuumWorkItem
AutoVacuumWorkItemType
@@ -3098,6 +3099,7 @@ TableScanDesc
TableScanDescData
TableSpaceCacheEntry
TableSpaceOpts
+TableToProcess
TablespaceList
TablespaceListCell
TapeBlockTrailer
--
2.50.1 (Apple Git-155)
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: pgsql-hackers@postgresql.org
Cc: nathandbossart@gmail.com, dgrowleyml@gmail.com, jnasby@upgrade.com, samimseih@gmail.com, bharath.rupireddyforpostgres@gmail.com, greg@burd.me, robertmhaas@gmail.com, rob@xzilla.net, schneider@ardentperf.com
Subject: Re: another autovacuum scheduling thread
In-Reply-To: <acLGoktU2lp4SIlU@nathan>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox