Subject: Re: New GUC autovacuum_max_threshold ?

From: Frédéric Yhuel <[email-masked]>
Date: Mon, 12 Aug 2024 15:41:26 +0200

On 8/7/24 23:39, Nathan Bossart wrote:
> I've attached a new patch to show roughly what I think this new GUC should
> look like.  I'm hoping this sparks more discussion, if nothing else.
>

Thank you. FWIW, I would prefer a sub-linear growth, so maybe something 
like this:

vacthresh = Min(vac_base_thresh + vac_scale_factor * reltuples, 
vac_base_thresh + vac_scale_factor * pow(reltuples, 0.7) * 100);

This would give :

* 386M (instead of 5.1 billion currently) for a 25.6 billion tuples table ;
* 77M for a 2.56 billion tuples table (Robert's example) ;
* 15M (instead of 51M currently) for a 256M tuples table ;
* 3M (instead of 5M currently) for a 25.6M tuples table.

The other advantage is that you don't need another GUC.

> On Tue, Jun 18, 2024 at 12:36:42PM +0200, Frédéric Yhuel wrote:
>> By the way, I wonder if there were any off-list discussions after Robert's
>> conference at PGConf.dev (and I'm waiting for the video of the conf).
> 
> I don't recall any discussions about this idea, but Robert did briefly
> mention it in his talk [0].
> 
> [0] https://www.youtube.com/watch?v=RfTD-Twpvac
> 

Very interesting, thanks!

---

From: wenhui qiu <[email-masked]>
Date: Wed, 6 Nov 2024 20:51:07 +0800

Hi frederic.yhuel

> like this

> The other advantage is that you don't need another GUC.
Argee ,We just need to change the calculation formula，But I prefer this
formula because it calculates a smoother value.

vacthresh =  (float4) fmin(vac_base_thresh + vac_scale_factor *
reltuples,vac_base_thresh
+ vac_scale_factor * log2(reltuples) * 10000);
or
vacthresh = (float4) fmin(vac_base_thresh + (vac_scale_factor * reltuples)
, sqrt(1000.0 * reltuples));

---

From: Nathan Bossart <[email-masked]>
Date: Fri, 8 Nov 2024 11:44:24 -0600

On Wed, Nov 06, 2024 at 08:51:07PM +0800, wenhui qiu wrote:
> Argee ,We just need to change the calculation formula,But I prefer this

I apologize for the curt response, but I don't understand how we could
decide which of these three complicated formulas to use, let alone how we
could expect users to reason about the behavior.

-- 
nathan

---

From: wenhui qiu <[email-masked]>
Date: Sat, 9 Nov 2024 22:08:51 +0800

Hi Nathan Bossart
> could expect users to reason about the behavior.
      Sorry ,I forgot to explain the reason in my last email，In fact, I
submitted the patch to the community，（[email-masked]） told me
there has a same idea ，so ，
      Let me explain those two formulas here，about （   vacthresh = (float4)
fmin(vac_base_thresh + (vac_scale_factor * reltuples), sqrt(1000.0 *
reltuples));   A few days ago, I was looking at the sql server
documentation and found that sql server has optimized the algorithm related
to updating statistics in the 2016 ,version,I think we can also learn from
the implementation method of sql server to optimize the problem of
automatic vacuum triggered by large tables，The Document link(
https://learn.microsoft.com/en-us/sql/relational-databases/statistics/statistics?view=sql-server-ver16
),about （ vacthresh =  (float4) fmin(vac_base_thresh + vac_scale_factor *
 reltuples,vac_base_thresh+ vac_scale_factor * log2(reltuples) * 10000);）I
came to the conclusion by trying to draw a function graph，I personally
think it is a smooth formula

####
SQL SERVER
log2
log

original

Thanks

---

From: Nathan Bossart <[email-masked]>
Date: Sat, 9 Nov 2024 09:59:42 -0600

On Sat, Nov 09, 2024 at 10:08:51PM +0800, wenhui qiu wrote:
>       Sorry ,I forgot to explain the reason in my last email,In fact, I
> submitted the patch to the community,([email-masked]) told me
> there has a same idea ,so ,
>       Let me explain those two formulas here,about (   vacthresh = (float4)
> automatic vacuum triggered by large tables,The Document link(
> ),about ( vacthresh =  (float4) fmin(vac_base_thresh + vac_scale_factor *
>  reltuples,vac_base_thresh+ vac_scale_factor * log2(reltuples) * 10000);)I
> came to the conclusion by trying to draw a function graph,I personally

AFAICT the main advantage of these formulas is that you don't need another
GUC, but they also makes the existing ones more difficult to configure.
Plus, there's no way to go back to the existing behavior.

-- 
nathan

---

From: wenhui qiu <[email-masked]>
Date: Sun, 10 Nov 2024 19:25:45 +0800

Hi Nathan Bossart
> Plus, there's no way to go back to the existing behavior.
There is indeed this problem,But I think this formula should not be a
linear relationship in the first place,SQL Server was realized and
optimized eight years ago. I think we can definitely draw on the experience
of SQL Server.Maybe many people are worried that frequent vacuum will
affect io performance, but we can learn from the experience of SQL Server
in vacuum analysis   .

---

From: Frédéric Yhuel <[email-masked]>
Date: Wed, 13 Nov 2024 11:03:25 +0100

> GUC, but they also makes the existing ones more difficult to configure.

I wouldn't say that's the main advantage. It doesn't seem very clean to 
me to cap to a fixed value. Because you could take Robert's 
demonstration with a bigger table, and come to the same conclusion:

Let's compare the current situation to the situation post-Nathan's-patch 
with a cap of 100M. Consider a table 100 times larger than the one of 
Robert's previous example, so pgbench scale factor 2_560_000, size on 
disk 32TB.
Currently, that table will be vacuumed for bloat when the number of
dead tuples exceeds 20% of the table size, because that's the default
value of autovacuum_vacuum_scale_factor. The table has 256 billion
tuples, so that means that we're going to vacuum it when there are
more than 51 billion dead tuples. Post-patch, we will vacuum when we
have 100 million dead tuples. Suppose a uniform workload that slowly
updates rows in the table. If we were previously autovacuuming the
table once per day (1440 minutes) we're now going to try to vacuum it
almost every minute (1440 minutes / 512 = 168 seconds).

(compare with every 55 min with my formula)

Of course, this a theoretical example that is probably unrealistic. I 
don't know, really. I don't know if Robert's example was realistic in 
the first place.

In any case, we should do the tests that Robert suggested and/or come up 
with a good mathematical model, because we are in the dark at the moment.

> Plus, there's no way to go back to the existing behavior.

I think we should indeed provide a retro-compatible behaviour (so maybe 
another GUC after all).

---

From: wenhui qiu <[email-masked]>
Date: Wed, 13 Nov 2024 18:33:17 +0800

HI
> with a good mathematical model, because we are in the dark at the moment.
I think SQL Server has given us great inspiration
>I think we should indeed provide a retro-compatible behaviour (so maybe
I am ready to implement a new guc parameter,Enable database administrators
to configure appropriate calculation methods（The default value is the
original calculation formula）

---

From: Nathan Bossart <[email-masked]>
Date: Tue, 7 Jan 2025 16:57:41 -0600

Here is a rebased patch for cfbot.  AFAICT we are still pretty far from
consensus on which approach to take, unfortunately.

-- 
nathan
>From df17b1f8b18300bc8426c06ab4e4d2d1c9169d85 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <[email-masked]>
Date: Wed, 7 Aug 2024 16:22:37 -0500
Subject: [PATCH v3 1/1] autovacuum_max_threshold

---
 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           | 11 +++++++++
 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, 76 insertions(+)

diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index 8683f0bdf53..3c03bdd5790 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -8712,6 +8712,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 70fa929caa4..77cee4a2888 100644
--- a/doc/src/sgml/ref/create_table.sgml
+++ b/doc/src/sgml/ref/create_table.sgml
@@ -1682,6 +1682,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..c28c7517aeb 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
+		},
+		-1, 0, 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..680b3dcae48 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,10 @@ relation_needs_vacanalyze(Oid relid,
 		? relopts->vacuum_threshold
 		: autovacuum_vac_thresh;
 
+	vac_max_thresh = (relopts && relopts->vacuum_max_threshold >= 0)
+		? 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 +3055,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: Frédéric Yhuel <[email-masked]>
Date: Wed, 8 Jan 2025 14:48:10 +0100

> consensus on which approach to take, unfortunately.

For what it's worth, although I would have preferred the sub-linear 
growth thing, I'd much rather have this than nothing.

And I have to admit that the proposed formulas were either too 
convoluted or wrong.

This very patch is more straightforward. Please let me know if I can 
help and how.

---

From: Nathan Bossart <[email-masked]>
Date: Wed, 8 Jan 2025 14:01:25 -0600

On Wed, Jan 08, 2025 at 02:48:10PM +0100, Frédéric Yhuel wrote:
> For what it's worth, although I would have preferred the sub-linear growth
> thing, I'd much rather have this than nothing.

+1, this is how I feel, too.  But I also don't want to add something that
folks won't find useful.

> And I have to admit that the proposed formulas were either too convoluted or
> wrong.
> 
> This very patch is more straightforward. Please let me know if I can help
> and how.

I read through the thread from the top, and it does seem like there is
reasonably strong support for the hard cap.  Upon a closer review of the
patch, I noticed that the relopt was defined such that you couldn't disable
autovacuum_max_threshold on a per-table basis, so I fixed that in v4.

-- 
nathan
>From 6004127a57ff6062da7e4696cd9358d18e6b6141 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <[email-masked]>
Date: Wed, 8 Jan 2025 13:11:52 -0600
Subject: [PATCH v4 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(+)

 
index e587abd9990..fbae300a128 100644
+		-2, -1, INT_MAX
index 0ab921a169b..ea48fba73f8 100644
 
 	/* 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;

---

From: Vinícius Abrahão <[email-masked]>
Date: Wed, 8 Jan 2025 21:32:58 +0000

On Wed, Jan 8, 2025 at 8:01 PM Nathan Bossart <[email-masked]>
wrote:

> growth
> convoluted or

nathan,

Please also provide the tests on the new parameter you want to introduce.

Best,
vini

---

From: Robert Treat <[email-masked]>
Date: Wed, 8 Jan 2025 19:01:53 -0500

> autovacuum_max_threshold on a per-table basis, so I fixed that in v4.

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.

There is some talk of "simplifying" autovacuum configuration, but some
noted that we already have a rather complex set of GUCs to deal with,
and adding another one, along with more math, into the equation
doesn't seem simpler to mel I'd like to think the bar should be that
the problem should be clear. So what is the problem?

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.

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)

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 guess that means this is supposed to help with bloat management? but
only on large tables? I guess because you run vacuums more often?
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.

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.

This isn't to say the system is perfect; I do think there are some
fundamental issues that need addressing, but adding this guc just
feels a little less baked than usual.

Robert Treat
https://xzilla.net

---

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

> Please also provide the tests on the new parameter you want to introduce.

I skimmed around and didn't see any existing tests for these kinds of
parameters, which of course isn't a great reason not to add tests, but it's
also not clear what such tests might look like.  Do you have any ideas?

-- 
nathan

---

From: Robert Haas <[email-masked]>
Date: Tue, 14 Jan 2025 14:30:13 -0500

> Robert's previous example, so pgbench scale factor 2_560_000, size on

This is a great thought experiment.

> more than 51 billion dead tuples.

Are we, though? In previous releases, maintenance_work_mem was capped
at 1GB, and it took 6 bytes per dead TID, so we were limited to 1/6 of
a billion dead tuples per indexvac cycle. So, if we really were
vacuuming only every 51 billion dead tuples, we would be doing about
300 indexvac cycles per vacuum. I think somebody in this situation
would likely have needed to adjust the settings or things would just
stop working, long before they got to this point. It's not impossible
that somebody out there has a low-criticality, largely-unmonitored
system that is like this, but I've never seen anything like it.

> Post-patch, we will vacuum when we

If we were vacuuming the table one per day, and it had 51 billion dead
tuples each time, that would mean we were generating ~591000 dead
tuples per second during that day. I'm not sure that's physically
possible with PostgreSQL on any hardware. I am almost positive that
you couldn't get by with vacuuming once a day if you were. I actually
think there's no amount of vacuuming that can turn this into a
success, at least on old releases with the 1GB limit, and maybe even
now.  In 168 seconds you would have generated almost 100 million dead
tuples, which is already closing in on the 1GB autovacuum_work_mem
limit, so you probably need to vacuum at least that often to avoid
having to do multiple indexvac passes, but you also probably can't
finish vacuuming the table in 168 seconds, so you're probably just
going to get runaway bloat no matter what you do. The new dead TID
store should help, but I suspect if you are generating dead tuples
this fast on a table this large you are in a lot of trouble even on
the latest release.

> don't know, really. I don't know if Robert's example was realistic in

I intended it to be realistic, but I might not have entirely
succeeded. Even if it's a bit off, I think it's far closer to being
realistic as I presented it than in your hundred-times-larger
scenario, which makes me think that the higher cap is more sensible
than the one you originally proposed. I don't think that the argument
I made can be scaled up or down by an arbitrary multiple without
becoming silly. The practical limits here have to do with the
capabilities of the hardware that is possible to buy, and they'll need
to be adjusted if, say, disks get ten times bigger and a hundred times
faster and memory becomes cheap as water.

-- 
Robert Haas
EDB: http://www.enterprisedb.com