agora inbox for pgsql-hackers@postgresql.org
help / color / mirror / Atom feedFrom: Nathan Bossart <nathandbossart@gmail.com>
To: David Rowley <dgrowleyml@gmail.com>
Cc: Jeremy Schneider <schneider@ardentperf.com>
Cc: Sami Imseih <samimseih@gmail.com>
Cc: pgsql-hackers@postgresql.org
Subject: Re: another autovacuum scheduling thread
Date: Fri, 10 Oct 2025 12:31:13 -0500
Message-ID: <aOlC4aDoQcgW8ZpC@nathan> (raw)
In-Reply-To: <aOffPCBoQLG5dGd8@nathan>
References: <aOaAuXREwnPZVISO@nathan>
<CAA5RZ0ucgx6VgVCUYryZY33StMtHSpoND_7wEFjGCVCSjouUsA@mail.gmail.com>
<20251008164057.6bceb9ed@ardentperf.com>
<CAApHDvo3maZsrZVq=Bur=Z6Gtse4asSEgHU0HzBhhcTfM-AfeA@mail.gmail.com>
<20251008172727.3befd129@ardentperf.com>
<CAApHDvpMEs3rewULyJZ=huT8ZM1C1PboV-G7K7jfGtJSgdx-fA@mail.gmail.com>
<20251008182520.6e05a8b8@ardentperf.com>
<20251008184740.328d45de@ardentperf.com>
<CAApHDvq76BBseUh2cG0=m=8r-j6HF_jrQt16Eszgsxp3bciGQw@mail.gmail.com>
<aOffPCBoQLG5dGd8@nathan>
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 <nathan@postgresql.org>
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)
Attachments:
[text/plain] v2-0001-autovacuum-scheduling-improvements.patch (7.4K, ../aOlC4aDoQcgW8ZpC@nathan/2-v2-0001-autovacuum-scheduling-improvements.patch)
download | inline diff:
From be37a18af33aaa436a571ead5508e89a9889b679 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <nathan@postgresql.org>
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)
view thread (150+ messages) latest in thread
Message-ID: <aOlC4aDoQcgW8ZpC@nathan>
Permalink: ../aOlC4aDoQcgW8ZpC@nathan/
Also on: postgresql.org/message-id/aOlC4aDoQcgW8ZpC@nathan
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: pgsql-hackers@postgresql.org
Cc: nathandbossart@gmail.com, dgrowleyml@gmail.com, schneider@ardentperf.com, samimseih@gmail.com
Subject: Re: another autovacuum scheduling thread
In-Reply-To: <aOlC4aDoQcgW8ZpC@nathan>
* 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