Subject: Re: New GUC autovacuum_max_threshold ?

From: Nathan Bossart <[email-masked]>
Date: Thu, 9 Jan 2025 12:06:29 -0600

On Wed, Jan 08, 2025 at 07:01:53PM -0500, Robert Treat wrote:
> To be frank, this patch feels like a solution in search of a problem,
> and as I read back through the thread, it isn't clear what problem
> this is intended to fix.

Thanks for sharing your thoughts.  FWIW I've heard various strategies over
the years for ensuring large tables are vacuumed more often, including
per-table settings for autovacuum_vacuum_threshold, etc.  At its core, this
patch is intended to ensure larger tables are handled by default.  But I do
think it's also important to consider _why_ folks want to vacuum larger
tables more often.  More on that below...

> Is the patch supposed to help with wraparound prevention?
> autovac_freeze_max_age already covers that, and when it doesn't
> vacuum_failsafe_age helps out.

Not really, although I certainly don't think it hurts matters in that
department.  In any case, while autovacuum_freeze_max_age and
vacuum_failsafe_age are incredibly important backstops for wraparound
issues, it's probably not great to rely on them too much for bloat, etc.

> A couple of people mentioned issues around hitting the index wall when
> vacuuming large tables, but we believe that problem is mostly resolved
> due to radix based tid storage, so this doesn't solve that. (To the
> degree you don't think v17 has baked into enough production workloads
> to be sure, I'd agree, but that's also an argument against doing more
> work that might not be needed)

Agreed.

> Maybe the hope is that this setting will cause vacuum to run more
> often to help ameliorate i/o work from freeze vacuums kicking in, but
> I suspect that Melanie's nearby work on eager vacuuming is a smarter
> solution towards this problem (warning, it also may want to add more
> gucs), so I think we're not solving that, and in fact might be
> undercutting it.

I haven't paid enough attention to the eager freezing work to have an
opinion on this point.

> I guess that means this is supposed to help with bloat management? but
> only on large tables? I guess because you run vacuums more often?

Right.  I think Robert Haas explained it well [0] [1].

> Except that the adages of running vacuums more often don't apply as
> cleanly to large tables, because those tables typically come with
> large indexes, and while we have a lot of machinery in place to help
> with repeated scans of the heap, that same machinery doesn't exist for
> scanning the indexes, which gives you sort of an exponential curve
> around vacuum times as table size (but actually index size) grows
> larger. On the upside, this does mean we're less likely to see a 50x
> boost in vacuums on large tables that some seemed concerned about, but
> on the downside its because we're probably going to increase the
> probability of vacuum worker starvation.

IIUC your concern is that instead of incurring one gigantic vacuum every
once in a while, we are incurring multiple medium vacuums more often, to
the point that we are spending significantly more time vacuuming a table
than before.  Is that right?  If so, I'm curious what you think about the
discussion upthread on this point [2].

> But getting back to goals, if your goal is to help with bloat
> management, trying to tie that to a number that doesn't cleanly map to
> the meta information of the table in question is a poor way to do it.
> Meaning, to the degree that you are skeptical that vacuuming based on
> 20% of the rows of a table might not really be 20% of the size of the
> table, it's certainly going to be a closer map than 100million rows in
> a n number of tables of unknown (but presumably greater than
> 500million?) numbers of rows of unknown sizes. And again, we have a
> means to tackle these bloat cases already; lowering
> vacuum_scale_factor.

I disagree on this point.  I think the fact that folks are forced to make
per-table adjustments to parameters like vacuum_scale_factor and are
participating in vigorous discussions like this one indicates that the
existing system isn't sufficient (or at least isn't sufficient by default).
That's not to say that adding a hard cap is perfect, either, but I don't
think we should let perfect be the enemy of good, especially not at this
stage of v18 development.

[0] https://postgr.es/m/CA%2BTgmoY4BENJyYcnU2eLFYZ73MZOb72WymNBH7vug6DtA%2BCZZw%40mail.gmail.com
[1] https://youtu.be/RfTD-Twpvac?&t=1979
[2] https://postgr.es/m/20240507211702.GA2720371%40nathanxps13

-- 
nathan

---

From: Nathan Bossart <[email-masked]>
Date: Mon, 13 Jan 2025 14:21:04 -0600

Here is a rebased version of the patch (commit ca9c6a5 adjusted the
documentation for vacuum-related GUCs).

-- 
nathan
>From d78d621233734abc54d0273526983bf64b88e7ba Mon Sep 17 00:00:00 2001
From: Nathan Bossart <[email-masked]>
Date: Wed, 8 Jan 2025 13:11:52 -0600
Subject: [PATCH v5 1/1] Introduce autovacuum_max_threshold.
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit

Author: Nathan Bossart, Frédéric Yhuel
Reviewed-by: Melanie Plageman, Robert Haas, Laurenz Albe, Michael Banck, Joe Conway, Sami Imseih, David Rowley, wenhui qiu
Discussion: https://postgr.es/m/956435f8-3b2f-47a6-8756-8c54ded61802%40dalibo.com
---
 doc/src/sgml/config.sgml                      | 24 +++++++++++++++++++
 doc/src/sgml/ref/create_table.sgml            | 15 ++++++++++++
 src/backend/access/common/reloptions.c        | 11 +++++++++
 src/backend/postmaster/autovacuum.c           | 12 ++++++++++
 src/backend/utils/misc/guc_tables.c           |  9 +++++++
 src/backend/utils/misc/postgresql.conf.sample |  2 ++
 src/bin/psql/tab-complete.in.c                |  2 ++
 src/include/postmaster/autovacuum.h           |  1 +
 src/include/utils/rel.h                       |  1 +
 9 files changed, 77 insertions(+)

diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index 3f41a17b1fe..5a0ed9ac36b 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -8579,6 +8579,30 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv;
        </listitem>
       </varlistentry>
 
+      <varlistentry id="guc-autovacuum-max-threshold" xreflabel="autovacuum_max_threshold">
+       <term><varname>autovacuum_max_threshold</varname> (<type>integer</type>)
+       <indexterm>
+        <primary><varname>autovacuum_max_threshold</varname></primary>
+        <secondary>configuration parameter</secondary>
+       </indexterm>
+       </term>
+       <listitem>
+        <para>
+         Specifies the maximum number of updated or deleted tuples needed to
+         trigger a <command>VACUUM</command> in any one table, i.e., a cap on
+         the value calculated with
+         <varname>autovacuum_vacuum_threshold</varname> and
+         <varname>autovacuum_vacuum_scale_factor</varname>.  The default is
+         100,000,000 tuples.  If -1 is specified, autovacuum will not enforce a
+         maximum number of updated or deleted tuples that will trigger a
+         <command>VACUUM</command> operation.  This parameter can only be set
+         in the <filename>postgresql.conf</filename> file or on the server
+         command line; but the setting can be overridden for individual tables
+         by changing storage parameters.
+        </para>
+       </listitem>
+      </varlistentry>
+
       <varlistentry id="guc-autovacuum-vacuum-insert-threshold" xreflabel="autovacuum_vacuum_insert_threshold">
        <term><varname>autovacuum_vacuum_insert_threshold</varname> (<type>integer</type>)
        <indexterm>
diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml
index 2237321cb4f..155c78ad6ba 100644
--- a/doc/src/sgml/ref/create_table.sgml
+++ b/doc/src/sgml/ref/create_table.sgml
@@ -1712,6 +1712,21 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
     </listitem>
    </varlistentry>
 
+   <varlistentry id="reloption-autovacuum-max-threshold" xreflabel="autovacuum_max_threshold">
+    <term><literal>autovacuum_max_threshold</literal>, <literal>toast.autovacuum_max_threshold</literal> (<type>integer</type>)
+    <indexterm>
+     <primary><varname>autovacuum_max_threshold</varname></primary>
+     <secondary>storage parameter</secondary>
+    </indexterm>
+    </term>
+   <listitem>
+    <para>
+     Per-table value for <xref linkend="guc-autovacuum-max-threshold"/>
+     parameter.
+    </para>
+   </listitem>
+  </varlistentry>
+
    <varlistentry id="reloption-autovacuum-vacuum-scale-factor" xreflabel="autovacuum_vacuum_scale_factor">
     <term><literal>autovacuum_vacuum_scale_factor</literal>, <literal>toast.autovacuum_vacuum_scale_factor</literal> (<type>floating point</type>)
     <indexterm>
diff --git a/src/backend/access/common/reloptions.c b/src/backend/access/common/reloptions.c
index e587abd9990..fbae300a128 100644
--- a/src/backend/access/common/reloptions.c
+++ b/src/backend/access/common/reloptions.c
@@ -231,6 +231,15 @@ static relopt_int intRelOpts[] =
 		},
 		-1, 0, INT_MAX
 	},
+	{
+		{
+			"autovacuum_max_threshold",
+			"Maximum number of tuple updates or deletes prior to vacuum",
+			RELOPT_KIND_HEAP | RELOPT_KIND_TOAST,
+			ShareUpdateExclusiveLock
+		},
+		-2, -1, INT_MAX
+	},
 	{
 		{
 			"autovacuum_vacuum_insert_threshold",
@@ -1843,6 +1852,8 @@ default_reloptions(Datum reloptions, bool validate, relopt_kind kind)
 		offsetof(StdRdOptions, autovacuum) + offsetof(AutoVacOpts, enabled)},
 		{"autovacuum_vacuum_threshold", RELOPT_TYPE_INT,
 		offsetof(StdRdOptions, autovacuum) + offsetof(AutoVacOpts, vacuum_threshold)},
+		{"autovacuum_max_threshold", RELOPT_TYPE_INT,
+		offsetof(StdRdOptions, autovacuum) + offsetof(AutoVacOpts, vacuum_max_threshold)},
 		{"autovacuum_vacuum_insert_threshold", RELOPT_TYPE_INT,
 		offsetof(StdRdOptions, autovacuum) + offsetof(AutoVacOpts, vacuum_ins_threshold)},
 		{"autovacuum_analyze_threshold", RELOPT_TYPE_INT,
diff --git a/src/backend/postmaster/autovacuum.c b/src/backend/postmaster/autovacuum.c
index 0ab921a169b..ea48fba73f8 100644
--- a/src/backend/postmaster/autovacuum.c
+++ b/src/backend/postmaster/autovacuum.c
@@ -120,6 +120,7 @@ int			autovacuum_max_workers;
 int			autovacuum_work_mem = -1;
 int			autovacuum_naptime;
 int			autovacuum_vac_thresh;
+int			autovacuum_max_thresh;
 double		autovacuum_vac_scale;
 int			autovacuum_vac_ins_thresh;
 double		autovacuum_vac_ins_scale;
@@ -2895,6 +2896,8 @@ recheck_relation_needs_vacanalyze(Oid relid,
  * threshold.  This threshold is calculated as
  *
  * threshold = vac_base_thresh + vac_scale_factor * reltuples
+ * if (threshold > vac_max_thresh)
+ *     threshold = vac_max_thres;
  *
  * For analyze, the analysis done is that the number of tuples inserted,
  * deleted and updated since the last analyze exceeds a threshold calculated
@@ -2933,6 +2936,7 @@ relation_needs_vacanalyze(Oid relid,
 
 	/* constants from reloptions or GUC variables */
 	int			vac_base_thresh,
+				vac_max_thresh,
 				vac_ins_base_thresh,
 				anl_base_thresh;
 	float4		vac_scale_factor,
@@ -2974,6 +2978,11 @@ relation_needs_vacanalyze(Oid relid,
 		? relopts->vacuum_threshold
 		: autovacuum_vac_thresh;
 
+	/* -1 is used to disable max threshold */
+	vac_max_thresh = (relopts && relopts->vacuum_max_threshold >= -1)
+		? relopts->vacuum_max_threshold
+		: autovacuum_max_thresh;
+
 	vac_ins_scale_factor = (relopts && relopts->vacuum_ins_scale_factor >= 0)
 		? relopts->vacuum_ins_scale_factor
 		: autovacuum_vac_ins_scale;
@@ -3047,6 +3056,9 @@ relation_needs_vacanalyze(Oid relid,
 			reltuples = 0;
 
 		vacthresh = (float4) vac_base_thresh + vac_scale_factor * reltuples;
+		if (vac_max_thresh >= 0 && vacthresh > (float4) vac_max_thresh)
+			vacthresh = (float4) vac_max_thresh;
+
 		vacinsthresh = (float4) vac_ins_base_thresh + vac_ins_scale_factor * reltuples;
 		anlthresh = (float4) anl_base_thresh + anl_scale_factor * reltuples;
 
diff --git a/src/backend/utils/misc/guc_tables.c b/src/backend/utils/misc/guc_tables.c
index c9d8cd796a8..2a18ba80c87 100644
--- a/src/backend/utils/misc/guc_tables.c
+++ b/src/backend/utils/misc/guc_tables.c
@@ -3425,6 +3425,15 @@ struct config_int ConfigureNamesInt[] =
 		50, 0, INT_MAX,
 		NULL, NULL, NULL
 	},
+	{
+		{"autovacuum_max_threshold", PGC_SIGHUP, AUTOVACUUM,
+			gettext_noop("Maximum number of tuple updates or deletes prior to vacuum."),
+			NULL
+		},
+		&autovacuum_max_thresh,
+		100000000, -1, INT_MAX,
+		NULL, NULL, NULL
+	},
 	{
 		{"autovacuum_vacuum_insert_threshold", PGC_SIGHUP, AUTOVACUUM,
 			gettext_noop("Minimum number of tuple inserts prior to vacuum, or -1 to disable insert vacuums."),
diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample
index b2bc43383db..d0e1c9b53c0 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -667,6 +667,8 @@ autovacuum_worker_slots = 16	# autovacuum worker slots to allocate
 #autovacuum_naptime = 1min		# time between autovacuum runs
 #autovacuum_vacuum_threshold = 50	# min number of row updates before
 					# vacuum
+#autovacuum_max_threshold = 100000000	# max number of row updates before
+					# vacuum
 #autovacuum_vacuum_insert_threshold = 1000	# min number of row inserts
 						# before vacuum; -1 disables insert
 						# vacuums
diff --git a/src/bin/psql/tab-complete.in.c b/src/bin/psql/tab-complete.in.c
index 81cbf10aa28..6ede5090cc0 100644
--- a/src/bin/psql/tab-complete.in.c
+++ b/src/bin/psql/tab-complete.in.c
@@ -1361,6 +1361,7 @@ static const char *const table_storage_parameters[] = {
 	"autovacuum_freeze_max_age",
 	"autovacuum_freeze_min_age",
 	"autovacuum_freeze_table_age",
+	"autovacuum_max_threshold",
 	"autovacuum_multixact_freeze_max_age",
 	"autovacuum_multixact_freeze_min_age",
 	"autovacuum_multixact_freeze_table_age",
@@ -1377,6 +1378,7 @@ static const char *const table_storage_parameters[] = {
 	"toast.autovacuum_freeze_max_age",
 	"toast.autovacuum_freeze_min_age",
 	"toast.autovacuum_freeze_table_age",
+	"toast.autovacuum_max_threshold",
 	"toast.autovacuum_multixact_freeze_max_age",
 	"toast.autovacuum_multixact_freeze_min_age",
 	"toast.autovacuum_multixact_freeze_table_age",
diff --git a/src/include/postmaster/autovacuum.h b/src/include/postmaster/autovacuum.h
index 54e01c81d68..b5c7b9b8abb 100644
--- a/src/include/postmaster/autovacuum.h
+++ b/src/include/postmaster/autovacuum.h
@@ -33,6 +33,7 @@ extern PGDLLIMPORT int autovacuum_max_workers;
 extern PGDLLIMPORT int autovacuum_work_mem;
 extern PGDLLIMPORT int autovacuum_naptime;
 extern PGDLLIMPORT int autovacuum_vac_thresh;
+extern PGDLLIMPORT int autovacuum_max_thresh;
 extern PGDLLIMPORT double autovacuum_vac_scale;
 extern PGDLLIMPORT int autovacuum_vac_ins_thresh;
 extern PGDLLIMPORT double autovacuum_vac_ins_scale;
diff --git a/src/include/utils/rel.h b/src/include/utils/rel.h
index 33d1e4a4e2e..48b95f211f3 100644
--- a/src/include/utils/rel.h
+++ b/src/include/utils/rel.h
@@ -309,6 +309,7 @@ typedef struct AutoVacOpts
 {
 	bool		enabled;
 	int			vacuum_threshold;
+	int			vacuum_max_threshold;
 	int			vacuum_ins_threshold;
 	int			analyze_threshold;
 	int			vacuum_cost_limit;
-- 
2.39.5 (Apple Git-154)

---

From: Sami Imseih <[email-masked]>
Date: Mon, 13 Jan 2025 17:17:11 -0600

> documentation for vacuum-related GUCs).

I looked at the patch and have a few comments.

I propose renaming the GUC from "autovacuum_max_threshold" to
"autovacuum_vacuum_max_threshold" to clarify that it applies only
to the vacuum operation performed by autovacuum, not to the analyze operation.
This will also align with naming for other related GUCs, i.e.,
"autovacuum_analyze_threshold" and "autovacuum_vacuum_threshold."

The "vacuum threshold" calculation described in [1] will also need to be
updated.

[1] https://www.postgresql.org/docs/current/routine-vacuuming.html#AUTOVACUUM

Regards,

Sami

---

From: Nathan Bossart <[email-masked]>
Date: Mon, 13 Jan 2025 20:09:05 -0600

> The "vacuum threshold" calculation described in [1] will also need to be

Good call.  Here is an updated patch.

-- 
nathan
>From 1a06dd9ccc2ee67a60278561f16c2c76cf3f8dc5 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <[email-masked]>
Date: Mon, 13 Jan 2025 20:01:24 -0600
Subject: [PATCH v6 1/1] Introduce autovacuum_vacuum_max_threshold.
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit

Author: Nathan Bossart, Frédéric Yhuel
Reviewed-by: Melanie Plageman, Robert Haas, Laurenz Albe, Michael Banck, Joe Conway, Sami Imseih, David Rowley, wenhui qiu
Discussion: https://postgr.es/m/956435f8-3b2f-47a6-8756-8c54ded61802%40dalibo.com
---
 doc/src/sgml/config.sgml                      | 24 +++++++++++++++++++
 doc/src/sgml/maintenance.sgml                 |  6 +++--
 doc/src/sgml/ref/create_table.sgml            | 15 ++++++++++++
 src/backend/access/common/reloptions.c        | 11 +++++++++
 src/backend/postmaster/autovacuum.c           | 12 ++++++++++
 src/backend/utils/misc/guc_tables.c           |  9 +++++++
 src/backend/utils/misc/postgresql.conf.sample |  3 +++
 src/bin/psql/tab-complete.in.c                |  2 ++
 src/include/postmaster/autovacuum.h           |  1 +
 src/include/utils/rel.h                       |  1 +
 10 files changed, 82 insertions(+), 2 deletions(-)

index 3f41a17b1fe..54a1ec2084a 100644
 
+      <varlistentry id="guc-autovacuum-vacuum-max-threshold" xreflabel="autovacuum_vacuum_max_threshold">
+       <term><varname>autovacuum_vacuum_max_threshold</varname> (<type>integer</type>)
+       <indexterm>
+        <primary><varname>autovacuum_vacuum_max_threshold</varname></primary>
+        <secondary>configuration parameter</secondary>
+       </indexterm>
+       </term>
+       <listitem>
+        <para>
+         Specifies the maximum number of updated or deleted tuples needed to
+         trigger a <command>VACUUM</command> in any one table, i.e., a cap on
+         the value calculated with
+         <varname>autovacuum_vacuum_threshold</varname> and
+         <varname>autovacuum_vacuum_scale_factor</varname>.  The default is
+         100,000,000 tuples.  If -1 is specified, autovacuum will not enforce a
+         maximum number of updated or deleted tuples that will trigger a
+         <command>VACUUM</command> operation.  This parameter can only be set
+         in the <filename>postgresql.conf</filename> file or on the server
+         command line; but the setting can be overridden for individual tables
+         by changing storage parameters.
+        </para>
+       </listitem>
+      </varlistentry>
+
       <varlistentry id="guc-autovacuum-vacuum-insert-threshold" xreflabel="autovacuum_vacuum_insert_threshold">
        <term><varname>autovacuum_vacuum_insert_threshold</varname> (<type>integer</type>)
        <indexterm>
diff --git a/doc/src/sgml/maintenance.sgml b/doc/src/sgml/maintenance.sgml
index 0be90bdc7ef..f84ad7557d9 100644
--- a/doc/src/sgml/maintenance.sgml
+++ b/doc/src/sgml/maintenance.sgml
@@ -895,9 +895,11 @@ HINT:  Execute a database-wide VACUUM in that database.
     <command>VACUUM</command> exceeds the <quote>vacuum threshold</quote>, the
     table is vacuumed.  The vacuum threshold is defined as:
 <programlisting>
-vacuum threshold = vacuum base threshold + vacuum scale factor * number of tuples
+vacuum threshold = Minimum(vacuum max threshold, vacuum base threshold + vacuum scale factor * number of tuples)
 </programlisting>
-    where the vacuum base threshold is
+    where the vacuum max threshold is
+    <xref linkend="guc-autovacuum-vacuum-max-threshold"/>,
+    the vacuum base threshold is
     <xref linkend="guc-autovacuum-vacuum-threshold"/>,
     the vacuum scale factor is
     <xref linkend="guc-autovacuum-vacuum-scale-factor"/>,
diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml
index 2237321cb4f..417498f71db 100644
--- a/doc/src/sgml/ref/create_table.sgml
+++ b/doc/src/sgml/ref/create_table.sgml
@@ -1712,6 +1712,21 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
     </listitem>
    </varlistentry>
 
+   <varlistentry id="reloption-autovacuum-vacuum-max-threshold" xreflabel="autovacuum_vacuum_max_threshold">
+    <term><literal>autovacuum_vacuum_max_threshold</literal>, <literal>toast.autovacuum_vacuum_max_threshold</literal> (<type>integer</type>)
+    <indexterm>
+     <primary><varname>autovacuum_vacuum_max_threshold</varname></primary>
+     Per-table value for <xref linkend="guc-autovacuum-vacuum-max-threshold"/>
index e587abd9990..5731cf42f54 100644
+			"autovacuum_vacuum_max_threshold",
+		{"autovacuum_vacuum_max_threshold", RELOPT_TYPE_INT,
index 0ab921a169b..4b1e42635d0 100644
+int			autovacuum_vac_max_thresh;

+		: autovacuum_vac_max_thresh;

diff --git a/src/backend/utils/misc/guc_tables.c b/src/backend/utils/misc/guc_tables.c
index c9d8cd796a8..7270abbc64a 100644
--- a/src/backend/utils/misc/guc_tables.c
+++ b/src/backend/utils/misc/guc_tables.c
@@ -3425,6 +3425,15 @@ struct config_int ConfigureNamesInt[] =
 		50, 0, INT_MAX,
 		NULL, NULL, NULL
 	},
+	{
+		{"autovacuum_vacuum_max_threshold", PGC_SIGHUP, AUTOVACUUM,
+			gettext_noop("Maximum number of tuple updates or deletes prior to vacuum."),
+			NULL
+		},
+		&autovacuum_vac_max_thresh,
+		100000000, -1, INT_MAX,
+		NULL, NULL, NULL
+	},
 	{
 		{"autovacuum_vacuum_insert_threshold", PGC_SIGHUP, AUTOVACUUM,
 			gettext_noop("Minimum number of tuple inserts prior to vacuum, or -1 to disable insert vacuums."),
diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample
index 079efa1baa7..d38112f3943 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -661,6 +661,9 @@ autovacuum_worker_slots = 16	# autovacuum worker slots to allocate
 #autovacuum_naptime = 1min		# time between autovacuum runs
 #autovacuum_vacuum_threshold = 50	# min number of row updates before
 					# vacuum
+#autovacuum_vacuum_max_threshold = 100000000	# max number of row updates
+						# before vacuum; -1 disables max
+						# threshold
 #autovacuum_vacuum_insert_threshold = 1000	# min number of row inserts
 						# before vacuum; -1 disables insert
 						# vacuums
diff --git a/src/bin/psql/tab-complete.in.c b/src/bin/psql/tab-complete.in.c
index 81cbf10aa28..5f6897c8486 100644
--- a/src/bin/psql/tab-complete.in.c
+++ b/src/bin/psql/tab-complete.in.c
@@ -1368,6 +1368,7 @@ static const char *const table_storage_parameters[] = {
 	"autovacuum_vacuum_cost_limit",
 	"autovacuum_vacuum_insert_scale_factor",
 	"autovacuum_vacuum_insert_threshold",
+	"autovacuum_vacuum_max_threshold",
 	"autovacuum_vacuum_scale_factor",
 	"autovacuum_vacuum_threshold",
 	"fillfactor",
@@ -1384,6 +1385,7 @@ static const char *const table_storage_parameters[] = {
 	"toast.autovacuum_vacuum_cost_limit",
 	"toast.autovacuum_vacuum_insert_scale_factor",
 	"toast.autovacuum_vacuum_insert_threshold",
+	"toast.autovacuum_vacuum_max_threshold",
 	"toast.autovacuum_vacuum_scale_factor",
 	"toast.autovacuum_vacuum_threshold",
 	"toast.log_autovacuum_min_duration",
diff --git a/src/include/postmaster/autovacuum.h b/src/include/postmaster/autovacuum.h
index 54e01c81d68..06d4a593575 100644
--- a/src/include/postmaster/autovacuum.h
+++ b/src/include/postmaster/autovacuum.h
@@ -33,6 +33,7 @@ extern PGDLLIMPORT int autovacuum_max_workers;
 extern PGDLLIMPORT int autovacuum_work_mem;
 extern PGDLLIMPORT int autovacuum_naptime;
 extern PGDLLIMPORT int autovacuum_vac_thresh;
+extern PGDLLIMPORT int autovacuum_vac_max_thresh;
 extern PGDLLIMPORT double autovacuum_vac_scale;
 extern PGDLLIMPORT int autovacuum_vac_ins_thresh;
 extern PGDLLIMPORT double autovacuum_vac_ins_scale;
diff --git a/src/include/utils/rel.h b/src/include/utils/rel.h
index 33d1e4a4e2e..48b95f211f3 100644
--- a/src/include/utils/rel.h
+++ b/src/include/utils/rel.h
@@ -309,6 +309,7 @@ typedef struct AutoVacOpts
 {
 	bool		enabled;
 	int			vacuum_threshold;
+	int			vacuum_max_threshold;
 	int			vacuum_ins_threshold;
 	int			analyze_threshold;
 	int			vacuum_cost_limit;
-- 
2.39.5 (Apple Git-154)

---

From: Sami Imseih <[email-masked]>
Date: Tue, 14 Jan 2025 11:08:36 -0600

> Good call.  Here is an updated patch.

thanks for the update!

After staring at the documentation for a while, I am now
wondering whether we are adequately describing the
rationale for this GUC. The GUC documentation mentions that this is a
'cap on the value calculated with autovacuum_vacuum_threshold
and autovacuum_vacuum_scale_factor,' which is acceptable;
however, I think further elaboration is necessary in
routine-vacuuming.html#AUTOVACUUM. This is because
scale_factor and threshold are already well-known
and widely understood parameters, and introducing
a third one to the mix deserves a bit more of an
explanation. What do you think?

Based on my understanding of the discussion, the purpose
of the GUC is to prevent large tables from experiencing
extended periods without an autovacuum, particularly
because scale_factor triggers vacuuming less
frequently as the table grows.

Currently, users can handle such cases by disabling (or lowering)
autovacuum_vacuum_scale_factor and setting an appropriate
autovacuum_vacuum_threshold; Therefore, this GUC becomes
a more convenient and predictable way to ensure autovacuum
triggers on a large table. Is this correct?

Regards,

Sami

---

From: Nathan Bossart <[email-masked]>
Date: Tue, 14 Jan 2025 11:45:42 -0600

> explanation. What do you think?

I think it would be odd to explain the intent for one autovacuum parameter
while leaving the others unexplained.  IMHO it would be better to address
this for all such parameters in a follow-up patch.

-- 
nathan

---

From: Sami Imseih <[email-masked]>
Date: Tue, 14 Jan 2025 11:59:48 -0600

> this for all such parameters in a follow-up patch.

absolutely, the documentation will need to discuss the relationship
between all 3 parameters for the documentation to make sense.

Regards,

Sami

---

From: Sami Imseih <[email-masked]>
Date: Tue, 14 Jan 2025 13:46:00 -0600

Will just park the idea for the documentation here. If
you feel this should be in a follow-up patch, I am ok
with that and will follow-up on it afterwards.

+++ b/doc/src/sgml/maintenance.sgml
@@ -905,6 +905,12 @@ vacuum threshold = Minimum(vacuum max threshold,
vacuum base threshold + vacuum
     <xref linkend="guc-autovacuum-vacuum-scale-factor"/>,
     and the number of tuples is
     <structname>pg_class</structname>.<structfield>reltuples</structfield>.
+    With the default values of <xref
linkend="autovacuum_vacuum_scale_factor"/> and
+    <xref linkend="autovacuum_vacuum_threshold"/>, the <quote>vacuum
threshold</quote> increases as
+    the number of tuples in the table increases; and this reduces the
table's eligibility for
+    <command>VACUUM</command>. Therefore,
+    <xref linkend="autovacuum_vacuum_max_threshold"/> provides a cap
on the number of obsolete rows
+    before the table becomes eligible for a vacuum.
    </para>

Regards,

Sami

---

From: Alena Rybakina <[email-masked]>
Date: Tue, 14 Jan 2025 23:35:17 +0300

Hi! Thanks for your work! it is very useful.

> Good call.  Here is an updated patch.
I found one minor mistake in postgresql.conf:

#autovacuum_vacuum_max_threshold = 100000000    # max number of row updates
                         # before vacuum; -1 disables max
                         # threshold

I think instead of "# threshold" should be "#vacuum"?

There is a typo:

* if (threshold > vac_max_thresh)
* threshold = vac_max_thres; - here

I think you should add more information to the description of the 
Relations_needs_vacanalyze function: what is vac_max_thresh and how is 
it calculated. It is not clear what the below condition means.

/* -1 is used to disable max threshold */
vac_max_thresh= (relopts&& relopts->vacuum_max_threshold>= -1)
? relopts->vacuum_max_threshold
: autovacuum_vac_max_thresh;

-- 
Regards,
Alena Rybakina
Postgres Professional

---

From: Nathan Bossart <[email-masked]>
Date: Mon, 3 Feb 2025 13:51:15 -0600

I had the opportunity to bring this patch up for discussion at the
developer meeting at FOSDEM PGDay last week [0].  We discussed a subset
of the topics folks have already written about in this thread, and AFAICT
there was general approval among the attendees for proceeding with the
"hard cap" approach due to its user-friendliness.  Given that, I am
planning to commit the attached patch in the near future (although I may
fiddle with the commit message a bit more).

> I think instead of "# threshold" should be "#vacuum"?

That would more closely match the description of
autovacuum_vacuum_insert_threshold, which refers to "insert vacuums," but I
felt it would be weird to refer to "max vacuums."  IMHO it is clearer to
say that -1 disables the maximum threshold here.

> * threshold = vac_max_thres; - here

Fixed.

> Relations_needs_vacanalyze function: what is vac_max_thresh and how is it
> calculated. It is not clear what the below condition means.
> 

I looked at the commentary for this function and felt that the comments for
this new parameter are in line with the comments for all the adjacent
parameters.  There may be an opportunity to improve this commentary, but
IMHO that would be better handled in a separate patch that improved it for
all these parameters.

[0] https://2025.fosdempgday.org/devmeeting

-- 
nathan
>From 392b07ea4d1a14d045937894d29e6679199a513f Mon Sep 17 00:00:00 2001
From: Nathan Bossart <[email-masked]>
Date: Mon, 3 Feb 2025 13:20:46 -0600
Subject: [PATCH v7 1/1] Introduce autovacuum_vacuum_max_threshold.
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit

One way we trigger an autovacuum on a table is to compare the
number of updated or deleted tuples with a value calculated using
autovacuum_vacuum_threshold and autovacuum_vacuum_scale_factor.
The threshold provides the base value for comparison, and the scale
factor provides the fraction of the table size to add to that
value.  This strategy ensures that smaller tables are vacuumed
after fewer updates/deletes than larger tables, which is reasonable
in many cases but can result in infrequent vacuums of very large
tables.  This is undesirable for a couple of reasons, such as very
large tables acquiring a huge amount of bloat between vacuums.

This new parameter provides a way to set a cap on the value
calculated with autovacuum_vacuum_threshold and
autovacuum_vacuum_scale_factor, which is intended to help trigger
more frequent vacuums on very large tables.  By default, it is set
to 100,000,000 tuples, but it can be disabled by setting it to -1.
It can also be adjusted for individual tables by changing storage
parameters.

Co-authored-by: Frédéric Yhuel <[email-masked]>
Reviewed-by: Melanie Plageman <[email-masked]>
Reviewed-by: Robert Haas <[email-masked]>
Reviewed-by: Laurenz Albe <[email-masked]>
Reviewed-by: Michael Banck <[email-masked]>
Reviewed-by: Joe Conway <[email-masked]>
Reviewed-by: Sami Imseih <[email-masked]>
Reviewed-by: David Rowley <[email-masked]>
Reviewed-by: wenhui qiu <[email-masked]>
Reviewed-by: Robert Treat <[email-masked]>
Reviewed-by: Alena Rybakina <[email-masked]>
Discussion: https://postgr.es/m/956435f8-3b2f-47a6-8756-8c54ded61802%40dalibo.com
---
 doc/src/sgml/config.sgml                      | 24 +++++++++++++++++++
 doc/src/sgml/maintenance.sgml                 |  6 +++--
 doc/src/sgml/ref/create_table.sgml            | 15 ++++++++++++
 src/backend/access/common/reloptions.c        | 11 +++++++++
 src/backend/postmaster/autovacuum.c           | 12 ++++++++++
 src/backend/utils/misc/guc_tables.c           |  9 +++++++
 src/backend/utils/misc/postgresql.conf.sample |  3 +++
 src/bin/psql/tab-complete.in.c                |  2 ++
 src/include/postmaster/autovacuum.h           |  1 +
 src/include/utils/rel.h                       |  1 +
 10 files changed, 82 insertions(+), 2 deletions(-)

diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index a782f109982..1e4bb613a98 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -8580,6 +8580,30 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv;
        </listitem>
       </varlistentry>

index 0ab921a169b..09ec9bb6990 100644
+ *     threshold = vac_max_thresh;

 
 
index 71448bb4fdd..b887d3e5983 100644
@@ -3426,6 +3426,15 @@ struct config_int ConfigureNamesInt[] =
+		{"autovacuum_vacuum_max_threshold", PGC_SIGHUP, VACUUM_AUTOVACUUM,
 		{"autovacuum_vacuum_insert_threshold", PGC_SIGHUP, VACUUM_AUTOVACUUM,

---

From: wenhui qiu <[email-masked]>
Date: Tue, 4 Feb 2025 09:41:51 +0800

HI Nathan
> fiddle with the commit message a bit more).
Thanks for your work on this ,that is good news.Any method that solves the
issue of vacuum being triggered by large tables is a good method.When more
comprehensive vacuum statistics become available in the future, we can
improve the calculation method then.

---

From: Nathan Bossart <[email-masked]>
Date: Wed, 5 Feb 2025 15:52:31 -0600

Committed.

-- 
nathan