On Thu, Mar 19, 2026 at 09:49:34AM -0400, Greg Burd wrote: > My concern isn't that wraparound vacuums are inherently alarming, I agree > with you that reaching freeze_max_age isn't a crisis. The issue is a > scoring-scale problem in the gap between freeze_max_age (200M) and > failsafe age (1.6B). > > In that 1.4B XID window, force_vacuum tables have XID scores of 1.0–8.0 > (age/freeze_max_age), while typical active tables accumulate dead-tuple > scores of 18–70+ within hours of their last vacuum. The exponential boost > doesn't activate until failsafe age, so force_vacuum tables are > systematically outranked by routine bloat cleanup for what could be days > or weeks in production.
I think "systematically outranked" makes the problem sound worse than it is. Once the freeze age is reached, the table is going to get added to the list no matter what, it just might be sorted lower. >> Having said that, I'd not realised that Nathan capped the new GUCs at >> 1.0. I think we should allow those to be set higher, likely at least >> to 10.0. > > That would definitely help. If autovacuum_freeze_score_weight could be > set to 8.0–10.0, DBAs could manually restore the priority we want. Done in the attached. >> Maybe we could consider adjusting the code that's setting the >> xid_score/mxid_score so that we start scaling the score aggressively >> when if (xid_age >= effective_xid_failsafe_age / >> Max(autovacuum_freeze_score_weight,1.0)) becomes true > > This is clever, it would make the aggressive scaling kick in earlier when > the weight is higher. At weight=8.0, you'd get exponential boost starting > at 200M (failsafe/8) instead of 1.6B. Seems reasonable. I've added this, too. Something else we might want to consider is scaling the score once the freeze age is reached, just much less aggressively than we do at the failsafe age. It probably doesn't make sense to start scaling too much at 200M, but at 1.5B, yeah, we should probably process the table sooner than later. -- nathan
>From 335b718302be1ae285145e0f55924585a9d91b8e Mon Sep 17 00:00:00 2001 From: Nathan Bossart <[email protected]> Date: Fri, 10 Oct 2025 12:28:37 -0500 Subject: [PATCH v13 1/1] autovacuum scheduling improvements --- doc/src/sgml/config.sgml | 90 +++++++ doc/src/sgml/maintenance.sgml | 92 +++++++ src/backend/postmaster/autovacuum.c | 252 ++++++++++++++---- 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 | 1 + 7 files changed, 438 insertions(+), 48 deletions(-) diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml index 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.sgml index 7c958b06273..b609f05be07 100644 --- a/doc/src/sgml/maintenance.sgml +++ b/doc/src/sgml/maintenance.sgml @@ -1061,6 +1061,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.c index 219673db930..55d3adc2909 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,12 @@ static AutoVacuumShmemStruct *AutoVacuumShmem; static dlist_head DatabaseList = DLIST_STATIC_INIT(DatabaseList); static MemoryContext DatabaseListCxt = NULL; +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 +361,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, + double *score); static void autovacuum_do_vac_analyze(autovac_table *tab, BufferAccessStrategy bstrategy); @@ -1867,6 +1879,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 +1901,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 +2013,7 @@ do_autovacuum(void) bool dovacuum; bool doanalyze; bool wraparound; + double score = 0.0; if (classForm->relkind != RELKIND_RELATION && classForm->relkind != RELKIND_MATVIEW) @@ -2032,11 +2054,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, + &score); - /* 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 = score; + tables_to_process = lappend(tables_to_process, table); + } /* * Remember TOAST associations for the second pass. Note: we must do @@ -2092,6 +2121,7 @@ do_autovacuum(void) bool dovacuum; bool doanalyze; bool wraparound; + double score = 0.0; /* * We cannot safely process other backends' temp tables, so skip 'em. @@ -2124,11 +2154,18 @@ do_autovacuum(void) relation_needs_vacanalyze(relid, relopts, classForm, tabentry, effective_multixact_freeze_max_age, - &dovacuum, &doanalyze, &wraparound); + &dovacuum, &doanalyze, &wraparound, + &score); /* ignore analyze for toast tables */ if (dovacuum) - table_oids = lappend_oid(table_oids, relid); + { + TableToProcess *table = palloc_object(TableToProcess); + + table->oid = relid; + table->score = score; + tables_to_process = lappend(tables_to_process, table); + } /* Release stuff to avoid leakage */ if (free_relopts) @@ -2252,6 +2289,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 +2319,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 +2552,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 +2954,7 @@ recheck_relation_needs_vacanalyze(Oid relid, bool *wraparound) { PgStat_StatTabEntry *tabentry; + double score; /* fetch the pgstat table entry */ tabentry = pgstat_fetch_stat_tabentry_ext(classForm->relisshared, @@ -2922,15 +2962,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, + &score); /* Release tabentry to avoid leakage */ if (tabentry) pfree(tabentry); - - /* ignore ANALYZE for toast tables */ - if (classForm->relkind == RELKIND_TOASTVALUE) - *doanalyze = false; } /* @@ -2971,6 +3008,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 1.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 +3055,8 @@ relation_needs_vacanalyze(Oid relid, /* output params below */ bool *dovacuum, bool *doanalyze, - bool *wraparound) + bool *wraparound, + double *score) { bool force_vacuum; bool av_enabled; @@ -3010,11 +3085,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)); + *score = 0.0; + *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 +3142,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 +3161,67 @@ relation_needs_vacanalyze(Oid relid, } *wraparound = force_vacuum; + /* Update the score. */ + if (force_vacuum) + { + uint32 xid_age; + uint32 mxid_age; + double xid_score; + double mxid_score; + 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; + + xid_score = (double) xid_age / freeze_max_age; + mxid_score = (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 + * being 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) + xid_score = pow(xid_score, Max(1.0, (double) xid_age / 100000000)); + if (mxid_age >= effective_mxid_failsafe_age) + mxid_score = pow(mxid_score, Max(1.0, (double) mxid_age / 100000000)); + + xid_score *= autovacuum_freeze_score_weight; + mxid_score *= autovacuum_multixact_freeze_score_weight; + + *score = Max(xid_score, mxid_score); + *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 +3270,58 @@ 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) + { + double vacthresh_score; + + vacthresh_score = (double) vactuples / Max(vacthresh, 1); + vacthresh_score *= autovacuum_vacuum_score_weight; + + *score = Max(*score, vacthresh_score); + *dovacuum = true; + } + + if (vac_ins_base_thresh >= 0 && instuples > vacinsthresh) + { + double vacinsthresh_score; + + vacinsthresh_score = (double) instuples / Max(vacinsthresh, 1); + vacinsthresh_score *= autovacuum_vacuum_insert_score_weight; + + *score = Max(*score, vacinsthresh_score); + *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) + { + double anlthresh_score; + + anlthresh_score = (double) anltuples / Max(anlthresh, 1); + anlthresh_score *= autovacuum_analyze_score_weight; + + *score = Max(*score, anlthresh_score); + *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 (threshold %.0f), ins: %.0f (threshold %.0f), anl: %.0f (threshold %.0f), score: %.3f", NameStr(classForm->relname), - vactuples, vacthresh, instuples, vacinsthresh, anltuples, anlthresh); + vactuples, vacthresh, instuples, vacinsthresh, anltuples, anlthresh, *score); else - elog(DEBUG3, "%s: vac: %.0f (threshold %.0f), ins: (disabled), anl: %.0f (threshold %.0f)", + elog(DEBUG3, "%s: vac: %.0f (threshold %.0f), ins: (disabled), anl: %.0f (threshold %.0f), score %.3f", 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); + vactuples, vacthresh, anltuples, anlthresh, *score); } - 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; - } - - /* 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.dat index 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.sample index 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.h index 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.list index 4673eca9cd6..b89ae469ddd 100644 --- a/src/tools/pgindent/typedefs.list +++ b/src/tools/pgindent/typedefs.list @@ -3088,6 +3088,7 @@ TableScanDesc TableScanDescData TableSpaceCacheEntry TableSpaceOpts +TableToProcess TablespaceList TablespaceListCell TapeBlockTrailer -- 2.50.1 (Apple Git-155)
