On Thu, Oct 09, 2025 at 11:13:48AM -0500, Nathan Bossart wrote: > On Thu, Oct 09, 2025 at 04:13:23PM +1300, David Rowley wrote: >> I think the best way to understand it is if you look at >> relation_needs_vacanalyze() and see how it calculates boolean values >> for boolean output params. So, instead of calculating just a boolean >> value it instead calculates a float4 where < 1.0 means don't do the >> operation and anything >= 1.0 means do the operation. For example, >> let's say a table has 600 dead rows and the scale factor and threshold >> settings mean that autovacuum will trigger at 200 (3 times more dead >> tuples than the trigger point). That would result in the value of 3.0 >> (600 / 200). The priority for relfrozenxid portion is basically >> age(relfrozenxid) / autovacuum_freeze_max_age (plus need to account >> for mxid by doing the same for that and taking the maximum of each >> value). For each of those component "scores", the priority for >> autovacuum would be the maximum of each of those. >> >> Effectively, it's a method of aligning the different units of measure, >> transactions or tuples into a single value which is calculated based >> on the very same values that we use today to trigger autovacuums. > > I like the idea of a "score" approach, but I'm worried that we'll never > come to an agreement on the formula to use. Perhaps we'd have more luck > getting consensus on a multifaceted strategy if we kept it brutally simple. > IMHO it's worth a try...
Here's a prototype of a "score" approach. Two notes: * I've given special priority to anti-wraparound vacuums. I think this is important to avoid focusing too much on bloat when wraparound is imminent. In any case, we need a separate wraparound score in case autovacuum is disabled. * I didn't include the analyze threshold in the score because it doesn't apply to TOAST tables, and therefore would artificially lower their prioritiy. Perhaps there is another way to deal with this. This is very much just a prototype of the basic idea. As-is, I think it'll favor processing tables with lots of bloat unless we're in an anti-wraparound scenario. Maybe that's okay. I'm not sure how scientific we want to be about all of this, but I do intend to try some long-running tests. -- nathan
>From be37a18af33aaa436a571ead5508e89a9889b679 Mon Sep 17 00:00:00 2001 From: Nathan Bossart <[email protected]> Date: Fri, 10 Oct 2025 12:28:37 -0500 Subject: [PATCH v2 1/1] autovacuum scheduling improvements --- src/backend/postmaster/autovacuum.c | 92 +++++++++++++++++++++++++---- src/tools/pgindent/typedefs.list | 1 + 2 files changed, 81 insertions(+), 12 deletions(-) diff --git a/src/backend/postmaster/autovacuum.c b/src/backend/postmaster/autovacuum.c index fb5d3b27224..95e928924cb 100644 --- a/src/backend/postmaster/autovacuum.c +++ b/src/backend/postmaster/autovacuum.c @@ -309,6 +309,13 @@ static AutoVacuumShmemStruct *AutoVacuumShmem; static dlist_head DatabaseList = DLIST_STATIC_INIT(DatabaseList); static MemoryContext DatabaseListCxt = NULL; +typedef struct +{ + Oid oid; + double wraparound_score; + double vacuum_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 +357,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 *wraparound_score, double *vacuum_score); static void autovacuum_do_vac_analyze(autovac_table *tab, BufferAccessStrategy bstrategy); @@ -1888,6 +1896,30 @@ 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); + + if (t1->wraparound_score >= 1.0 || t2->wraparound_score >= 1.0) + { + if (t2->wraparound_score < t1->wraparound_score) + return -1; + else if (t2->wraparound_score > t1->wraparound_score) + return 1; + else + return 0; + } + + if (t2->vacuum_score < t1->vacuum_score) + return -1; + else if (t2->vacuum_score > t2->vacuum_score) + return 1; + else + return 0; +} + /* * Process a database table-by-table * @@ -1901,7 +1933,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; @@ -2013,6 +2045,8 @@ do_autovacuum(void) bool dovacuum; bool doanalyze; bool wraparound; + double wraparound_score = 0.0; + double vacuum_score = 0.0; if (classForm->relkind != RELKIND_RELATION && classForm->relkind != RELKIND_MATVIEW) @@ -2053,11 +2087,20 @@ 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, + &wraparound_score, &vacuum_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(sizeof(TableToProcess)); + + table->oid = relid; + table->wraparound_score = wraparound_score; + table->vacuum_score = vacuum_score; + + tables_to_process = lappend(tables_to_process, table); + } /* * Remember TOAST associations for the second pass. Note: we must do @@ -2113,6 +2156,8 @@ do_autovacuum(void) bool dovacuum; bool doanalyze; bool wraparound; + double wraparound_score = 0.0; + double vacuum_score = 0.0; /* * We cannot safely process other backends' temp tables, so skip 'em. @@ -2145,11 +2190,20 @@ do_autovacuum(void) relation_needs_vacanalyze(relid, relopts, classForm, tabentry, effective_multixact_freeze_max_age, - &dovacuum, &doanalyze, &wraparound); + &dovacuum, &doanalyze, &wraparound, + &wraparound_score, &vacuum_score); /* ignore analyze for toast tables */ if (dovacuum) - table_oids = lappend_oid(table_oids, relid); + { + TableToProcess *table = palloc(sizeof(TableToProcess)); + + table->oid = relid; + table->wraparound_score = wraparound_score; + table->vacuum_score = vacuum_score; + + tables_to_process = lappend(tables_to_process, table); + } /* Release stuff to avoid leakage */ if (free_relopts) @@ -2273,6 +2327,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 @@ -2301,9 +2357,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; @@ -2534,7 +2590,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. @@ -2926,6 +2982,8 @@ recheck_relation_needs_vacanalyze(Oid relid, bool *wraparound) { PgStat_StatTabEntry *tabentry; + double wraparound_score; + double vacuum_score; /* fetch the pgstat table entry */ tabentry = pgstat_fetch_stat_tabentry_ext(classForm->relisshared, @@ -2933,7 +2991,8 @@ recheck_relation_needs_vacanalyze(Oid relid, relation_needs_vacanalyze(relid, avopts, classForm, tabentry, effective_multixact_freeze_max_age, - dovacuum, doanalyze, wraparound); + dovacuum, doanalyze, wraparound, + &wraparound_score, &vacuum_score); /* Release tabentry to avoid leakage */ if (tabentry) @@ -2992,7 +3051,8 @@ relation_needs_vacanalyze(Oid relid, /* output params below */ bool *dovacuum, bool *doanalyze, - bool *wraparound) + bool *wraparound, + double *wraparound_score, double *vacuum_score) { bool force_vacuum; bool av_enabled; @@ -3092,6 +3152,10 @@ relation_needs_vacanalyze(Oid relid, } *wraparound = force_vacuum; + *wraparound_score = (double) relfrozenxid / freeze_max_age; + *wraparound_score = Max(*wraparound_score, + (double) classForm->relminmxid / multixact_freeze_max_age); + /* User disabled it in pg_class.reloptions? (But ignore if at risk) */ if (!av_enabled && !force_vacuum) { @@ -3165,6 +3229,10 @@ relation_needs_vacanalyze(Oid relid, *dovacuum = force_vacuum || (vactuples > vacthresh) || (vac_ins_base_thresh >= 0 && instuples > vacinsthresh); *doanalyze = (anltuples > anlthresh); + + *vacuum_score = *wraparound_score; + *vacuum_score += (double) vactuples / vacthresh; + *vacuum_score += (double) instuples / vacinsthresh; } else { diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list index 5290b91e83e..9ba84250926 100644 --- a/src/tools/pgindent/typedefs.list +++ b/src/tools/pgindent/typedefs.list @@ -3006,6 +3006,7 @@ TableScanDesc TableScanDescData TableSpaceCacheEntry TableSpaceOpts +TableToProcess TablespaceList TablespaceListCell TapeBlockTrailer -- 2.39.5 (Apple Git-154)
