public inbox for [email protected]
help / color / mirror / Atom feed[PATCH v6 2/7] Row pattern recognition patch (parse/analysis).
13+ messages / 5 participants
[nested] [flat]
* [PATCH v6 2/7] Row pattern recognition patch (parse/analysis).
@ 2023-09-12 05:22 Tatsuo Ishii <[email protected]>
0 siblings, 0 replies; 13+ messages in thread
From: Tatsuo Ishii @ 2023-09-12 05:22 UTC (permalink / raw)
---
src/backend/parser/parse_agg.c | 7 +
src/backend/parser/parse_clause.c | 292 +++++++++++++++++++++++++++++-
src/backend/parser/parse_expr.c | 4 +
src/backend/parser/parse_func.c | 3 +
4 files changed, 305 insertions(+), 1 deletion(-)
diff --git a/src/backend/parser/parse_agg.c b/src/backend/parser/parse_agg.c
index 85cd47b7ae..aa7a1cee80 100644
--- a/src/backend/parser/parse_agg.c
+++ b/src/backend/parser/parse_agg.c
@@ -564,6 +564,10 @@ check_agglevels_and_constraints(ParseState *pstate, Node *expr)
errkind = true;
break;
+ case EXPR_KIND_RPR_DEFINE:
+ errkind = true;
+ break;
+
/*
* There is intentionally no default: case here, so that the
* compiler will warn if we add a new ParseExprKind without
@@ -953,6 +957,9 @@ transformWindowFuncCall(ParseState *pstate, WindowFunc *wfunc,
case EXPR_KIND_CYCLE_MARK:
errkind = true;
break;
+ case EXPR_KIND_RPR_DEFINE:
+ errkind = true;
+ break;
/*
* There is intentionally no default: case here, so that the
diff --git a/src/backend/parser/parse_clause.c b/src/backend/parser/parse_clause.c
index 334b9b42bd..60020a7025 100644
--- a/src/backend/parser/parse_clause.c
+++ b/src/backend/parser/parse_clause.c
@@ -100,7 +100,10 @@ static WindowClause *findWindowClause(List *wclist, const char *name);
static Node *transformFrameOffset(ParseState *pstate, int frameOptions,
Oid rangeopfamily, Oid rangeopcintype, Oid *inRangeFunc,
Node *clause);
-
+static void transformRPR(ParseState *pstate, WindowClause *wc, WindowDef *windef, List **targetlist);
+static List *transformDefineClause(ParseState *pstate, WindowClause *wc, WindowDef *windef, List **targetlist);
+static void transformPatternClause(ParseState *pstate, WindowClause *wc, WindowDef *windef);
+static List *transformMeasureClause(ParseState *pstate, WindowClause *wc, WindowDef *windef);
/*
* transformFromClause -
@@ -2950,6 +2953,10 @@ transformWindowDefinitions(ParseState *pstate,
rangeopfamily, rangeopcintype,
&wc->endInRangeFunc,
windef->endOffset);
+
+ /* Process Row Pattern Recognition related clauses */
+ transformRPR(pstate, wc, windef, targetlist);
+
wc->runCondition = NIL;
wc->winref = winref;
@@ -3815,3 +3822,286 @@ transformFrameOffset(ParseState *pstate, int frameOptions,
return node;
}
+
+/*
+ * transformRPR
+ * Process Row Pattern Recognition related clauses
+ */
+static void
+transformRPR(ParseState *pstate, WindowClause *wc, WindowDef *windef, List **targetlist)
+{
+ /*
+ * Window definition exists?
+ */
+ if (windef == NULL)
+ return;
+
+ /*
+ * Row Pattern Common Syntax clause exists?
+ */
+ if (windef->rpCommonSyntax == NULL)
+ return;
+
+ /* Check Frame option. Frame must start at current row */
+ if ((wc->frameOptions & FRAMEOPTION_START_CURRENT_ROW) == 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("FRAME must start at current row when row patttern recognition is used")));
+
+ /* Transform AFTER MACH SKIP TO clause */
+ wc->rpSkipTo = windef->rpCommonSyntax->rpSkipTo;
+
+ /* Transform SEEK or INITIAL clause */
+ wc->initial = windef->rpCommonSyntax->initial;
+
+ /* Transform DEFINE clause into list of TargetEntry's */
+ wc->defineClause = transformDefineClause(pstate, wc, windef, targetlist);
+
+ /* Check PATTERN clause and copy to patternClause */
+ transformPatternClause(pstate, wc, windef);
+
+ /* Transform MEASURE clause */
+ transformMeasureClause(pstate, wc, windef);
+}
+
+/*
+ * transformDefineClause Process DEFINE clause and transform ResTarget into
+ * list of TargetEntry.
+ *
+ * XXX we only support column reference in row pattern definition search
+ * condition, e.g. "price". <row pattern definition variable name>.<column
+ * reference> is not supported, e.g. "A.price".
+ */
+static List *
+transformDefineClause(ParseState *pstate, WindowClause *wc, WindowDef *windef, List **targetlist)
+{
+ /* DEFINE variable name initials */
+ static char *defineVariableInitials = "abcdefghijklmnopqrstuvwxyz";
+
+ ListCell *lc, *l;
+ ResTarget *restarget, *r;
+ List *restargets;
+ char *name;
+ int initialLen;
+ int i;
+
+ /*
+ * If Row Definition Common Syntax exists, DEFINE clause must exist.
+ * (the raw parser should have already checked it.)
+ */
+ Assert(windef->rpCommonSyntax->rpDefs != NULL);
+
+ /*
+ * Check and add "A AS A IS TRUE" if pattern variable is missing in DEFINE
+ * per the SQL standard.
+ */
+ restargets = NIL;
+ foreach(lc, windef->rpCommonSyntax->rpPatterns)
+ {
+ A_Expr *a;
+ bool found = false;
+
+ if (!IsA(lfirst(lc), A_Expr))
+ ereport(ERROR,
+ errmsg("node type is not A_Expr"));
+
+ a = (A_Expr *)lfirst(lc);
+ name = strVal(a->lexpr);
+
+ foreach(l, windef->rpCommonSyntax->rpDefs)
+ {
+ restarget = (ResTarget *)lfirst(l);
+
+ if (!strcmp(restarget->name, name))
+ {
+ found = true;
+ break;
+ }
+ }
+
+ if (!found)
+ {
+ /*
+ * "name" is missing. So create "name AS name IS TRUE" ResTarget
+ * node and add it to the temporary list.
+ */
+ A_Const *n;
+
+ restarget = makeNode(ResTarget);
+ n = makeNode(A_Const);
+ n->val.boolval.type = T_Boolean;
+ n->val.boolval.boolval = true;
+ n->location = -1;
+ restarget->name = pstrdup(name);
+ restarget->indirection = NIL;
+ restarget->val = (Node *)n;
+ restarget->location = -1;
+ restargets = lappend((List *)restargets, restarget);
+ }
+ }
+
+ if (list_length(restargets) >= 1)
+ {
+ /* add missing DEFINEs */
+ windef->rpCommonSyntax->rpDefs = list_concat(windef->rpCommonSyntax->rpDefs,
+ restargets);
+ list_free(restargets);
+ }
+
+ /*
+ * Check for duplicate row pattern definition variables. The standard
+ * requires that no two row pattern definition variable names shall be
+ * equivalent.
+ */
+ restargets = NIL;
+ foreach(lc, windef->rpCommonSyntax->rpDefs)
+ {
+ restarget = (ResTarget *)lfirst(lc);
+ name = restarget->name;
+
+ /*
+ * Add DEFINE expression (Restarget->val) to the targetlist as a
+ * TargetEntry if it does not exist yet. Planner will add the column
+ * ref var node to the outer plan's target list later on. This makes
+ * DEFINE expression could access the outer tuple while evaluating
+ * PATTERN.
+ *
+ * XXX: adding whole expressions of DEFINE to the plan.targetlist is
+ * not so good, because it's not necessary to evalute the expression
+ * in the target list while running the plan. We should extract the
+ * var nodes only then add them to the plan.targetlist.
+ */
+ findTargetlistEntrySQL99(pstate, (Node *)restarget->val, targetlist, EXPR_KIND_RPR_DEFINE);
+
+ /*
+ * Make sure that the row pattern definition search condition is a
+ * boolean expression.
+ */
+ transformWhereClause(pstate, restarget->val,
+ EXPR_KIND_RPR_DEFINE, "DEFINE");
+
+ foreach(l, restargets)
+ {
+ char *n;
+
+ r = (ResTarget *) lfirst(l);
+ n = r->name;
+
+ if (!strcmp(n, name))
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("row pattern definition variable name \"%s\" appears more than once in DEFINE clause",
+ name),
+ parser_errposition(pstate, exprLocation((Node *)r))));
+ }
+ restargets = lappend(restargets, restarget);
+ }
+ list_free(restargets);
+
+ /*
+ * Create list of row pattern DEFINE variable name's initial.
+ * We assign [a-z] to them (up to 26 variable names are allowed).
+ */
+ restargets = NIL;
+ i = 0;
+ initialLen = strlen(defineVariableInitials);
+
+ foreach(lc, windef->rpCommonSyntax->rpDefs)
+ {
+ char initial[2];
+
+ restarget = (ResTarget *)lfirst(lc);
+ name = restarget->name;
+
+ if (i >= initialLen)
+ {
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("number of row pattern definition variable names exceeds %d", initialLen),
+ parser_errposition(pstate, exprLocation((Node *)restarget))));
+ }
+ initial[0] = defineVariableInitials[i++];
+ initial[1] = '\0';
+ wc->defineInitial = lappend(wc->defineInitial, makeString(pstrdup(initial)));
+ }
+
+ return transformTargetList(pstate, windef->rpCommonSyntax->rpDefs,
+ EXPR_KIND_RPR_DEFINE);
+}
+
+/*
+ * transformPatternClause
+ * Process PATTERN clause and return PATTERN clause in the raw parse tree
+ */
+static void
+transformPatternClause(ParseState *pstate, WindowClause *wc, WindowDef *windef)
+{
+ ListCell *lc, *l;
+
+ /*
+ * Row Pattern Common Syntax clause exists?
+ */
+ if (windef->rpCommonSyntax == NULL)
+ return;
+
+ /*
+ * Primary row pattern variable names in PATTERN clause must appear in
+ * DEFINE clause as row pattern definition variable names.
+ */
+ wc->patternVariable = NIL;
+ wc->patternRegexp = NIL;
+ foreach(lc, windef->rpCommonSyntax->rpPatterns)
+ {
+ A_Expr *a;
+ char *name;
+ char *regexp;
+ bool found = false;
+
+ if (!IsA(lfirst(lc), A_Expr))
+ ereport(ERROR,
+ errmsg("node type is not A_Expr"));
+
+ a = (A_Expr *)lfirst(lc);
+ name = strVal(a->lexpr);
+
+ foreach(l, windef->rpCommonSyntax->rpDefs)
+ {
+ ResTarget *restarget = (ResTarget *)lfirst(l);
+
+ if (!strcmp(restarget->name, name))
+ {
+ found = true;
+ break;
+ }
+ }
+
+ if (!found)
+ {
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("primary row pattern variable name \"%s\" does not appear in DEFINE clause",
+ name),
+ parser_errposition(pstate, exprLocation((Node *)a))));
+ }
+ wc->patternVariable = lappend(wc->patternVariable, makeString(pstrdup(name)));
+ regexp = strVal(lfirst(list_head(a->name)));
+ wc->patternRegexp = lappend(wc->patternRegexp, makeString(pstrdup(regexp)));
+ }
+}
+
+/*
+ * transformMeasureClause
+ * Process MEASURE clause
+ * XXX MEASURE clause is not supported yet
+ */
+static List *
+transformMeasureClause(ParseState *pstate, WindowClause *wc, WindowDef *windef)
+{
+ if (windef->rowPatternMeasures == NIL)
+ return NIL;
+
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("%s","MEASURE clause is not supported yet"),
+ parser_errposition(pstate, exprLocation((Node *)windef->rowPatternMeasures))));
+}
diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c
index 64c582c344..18b58ac263 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -557,6 +557,7 @@ transformColumnRef(ParseState *pstate, ColumnRef *cref)
case EXPR_KIND_COPY_WHERE:
case EXPR_KIND_GENERATED_COLUMN:
case EXPR_KIND_CYCLE_MARK:
+ case EXPR_KIND_RPR_DEFINE:
/* okay */
break;
@@ -1770,6 +1771,7 @@ transformSubLink(ParseState *pstate, SubLink *sublink)
case EXPR_KIND_VALUES:
case EXPR_KIND_VALUES_SINGLE:
case EXPR_KIND_CYCLE_MARK:
+ case EXPR_KIND_RPR_DEFINE:
/* okay */
break;
case EXPR_KIND_CHECK_CONSTRAINT:
@@ -3149,6 +3151,8 @@ ParseExprKindName(ParseExprKind exprKind)
return "GENERATED AS";
case EXPR_KIND_CYCLE_MARK:
return "CYCLE";
+ case EXPR_KIND_RPR_DEFINE:
+ return "DEFINE";
/*
* There is intentionally no default: case here, so that the
diff --git a/src/backend/parser/parse_func.c b/src/backend/parser/parse_func.c
index b3f0b6a137..2ff3699538 100644
--- a/src/backend/parser/parse_func.c
+++ b/src/backend/parser/parse_func.c
@@ -2656,6 +2656,9 @@ check_srf_call_placement(ParseState *pstate, Node *last_srf, int location)
case EXPR_KIND_CYCLE_MARK:
errkind = true;
break;
+ case EXPR_KIND_RPR_DEFINE:
+ errkind = true;
+ break;
/*
* There is intentionally no default: case here, so that the
--
2.25.1
----Next_Part(Tue_Sep_12_15_18_43_2023_359)--
Content-Type: Text/X-Patch; charset=us-ascii
Content-Transfer-Encoding: 7bit
Content-Disposition: inline;
filename="v6-0003-Row-pattern-recognition-patch-planner.patch"
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: New GUC autovacuum_max_threshold ?
@ 2025-01-09 18:06 Nathan Bossart <[email protected]>
2025-01-13 20:21 ` Re: New GUC autovacuum_max_threshold ? Nathan Bossart <[email protected]>
0 siblings, 1 reply; 13+ messages in thread
From: Nathan Bossart @ 2025-01-09 18:06 UTC (permalink / raw)
To: Robert Treat <[email protected]>; +Cc: Frédéric Yhuel <[email protected]>; wenhui qiu <[email protected]>; Robert Haas <[email protected]>; Imseih (AWS), Sami <[email protected]>; David Rowley <[email protected]>; Joe Conway <[email protected]>; Michael Banck <[email protected]>; Laurenz Albe <[email protected]>; Melanie Plageman <[email protected]>; PostgreSQL Hackers <[email protected]>
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: New GUC autovacuum_max_threshold ?
2025-01-09 18:06 Re: New GUC autovacuum_max_threshold ? Nathan Bossart <[email protected]>
@ 2025-01-13 20:21 ` Nathan Bossart <[email protected]>
2025-01-13 23:17 ` Re: New GUC autovacuum_max_threshold ? Sami Imseih <[email protected]>
0 siblings, 1 reply; 13+ messages in thread
From: Nathan Bossart @ 2025-01-13 20:21 UTC (permalink / raw)
To: Robert Treat <[email protected]>; +Cc: Frédéric Yhuel <[email protected]>; wenhui qiu <[email protected]>; Robert Haas <[email protected]>; Imseih (AWS), Sami <[email protected]>; David Rowley <[email protected]>; Joe Conway <[email protected]>; Michael Banck <[email protected]>; Laurenz Albe <[email protected]>; Melanie Plageman <[email protected]>; PostgreSQL Hackers <[email protected]>
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 protected]>
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)
Attachments:
[text/plain] v5-0001-Introduce-autovacuum_max_threshold.patch (10.3K, ../../Z4V1sDjqJ1CnqScY@nathan/2-v5-0001-Introduce-autovacuum_max_threshold.patch)
download | inline diff:
From d78d621233734abc54d0273526983bf64b88e7ba Mon Sep 17 00:00:00 2001
From: Nathan Bossart <[email protected]>
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)
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: New GUC autovacuum_max_threshold ?
2025-01-09 18:06 Re: New GUC autovacuum_max_threshold ? Nathan Bossart <[email protected]>
2025-01-13 20:21 ` Re: New GUC autovacuum_max_threshold ? Nathan Bossart <[email protected]>
@ 2025-01-13 23:17 ` Sami Imseih <[email protected]>
2025-01-14 02:09 ` Re: New GUC autovacuum_max_threshold ? Nathan Bossart <[email protected]>
0 siblings, 1 reply; 13+ messages in thread
From: Sami Imseih @ 2025-01-13 23:17 UTC (permalink / raw)
To: Nathan Bossart <[email protected]>; +Cc: Robert Treat <[email protected]>; Frédéric Yhuel <[email protected]>; wenhui qiu <[email protected]>; Robert Haas <[email protected]>; Imseih (AWS), Sami <[email protected]>; David Rowley <[email protected]>; Joe Conway <[email protected]>; Michael Banck <[email protected]>; Laurenz Albe <[email protected]>; Melanie Plageman <[email protected]>; PostgreSQL Hackers <[email protected]>
> Here is a rebased version of the patch (commit ca9c6a5 adjusted the
> 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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: New GUC autovacuum_max_threshold ?
2025-01-09 18:06 Re: New GUC autovacuum_max_threshold ? Nathan Bossart <[email protected]>
2025-01-13 20:21 ` Re: New GUC autovacuum_max_threshold ? Nathan Bossart <[email protected]>
2025-01-13 23:17 ` Re: New GUC autovacuum_max_threshold ? Sami Imseih <[email protected]>
@ 2025-01-14 02:09 ` Nathan Bossart <[email protected]>
2025-01-14 17:08 ` Re: New GUC autovacuum_max_threshold ? Sami Imseih <[email protected]>
2025-01-14 20:35 ` Re: New GUC autovacuum_max_threshold ? Alena Rybakina <[email protected]>
0 siblings, 2 replies; 13+ messages in thread
From: Nathan Bossart @ 2025-01-14 02:09 UTC (permalink / raw)
To: Sami Imseih <[email protected]>; +Cc: Robert Treat <[email protected]>; Frédéric Yhuel <[email protected]>; wenhui qiu <[email protected]>; Robert Haas <[email protected]>; Imseih (AWS), Sami <[email protected]>; David Rowley <[email protected]>; Joe Conway <[email protected]>; Michael Banck <[email protected]>; Laurenz Albe <[email protected]>; Melanie Plageman <[email protected]>; PostgreSQL Hackers <[email protected]>
On Mon, Jan 13, 2025 at 05:17:11PM -0600, Sami Imseih wrote:
> 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.
Good call. Here is an updated patch.
--
nathan
From 1a06dd9ccc2ee67a60278561f16c2c76cf3f8dc5 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <[email protected]>
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(-)
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index 3f41a17b1fe..54a1ec2084a 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-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>
+ <secondary>storage parameter</secondary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ Per-table value for <xref linkend="guc-autovacuum-vacuum-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..5731cf42f54 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_vacuum_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_vacuum_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..4b1e42635d0 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_vac_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_vac_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..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)
Attachments:
[text/plain] v6-0001-Introduce-autovacuum_vacuum_max_threshold.patch (11.5K, ../../Z4XHQR6_AVKiyFjG@nathan/2-v6-0001-Introduce-autovacuum_vacuum_max_threshold.patch)
download | inline diff:
From 1a06dd9ccc2ee67a60278561f16c2c76cf3f8dc5 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <[email protected]>
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(-)
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index 3f41a17b1fe..54a1ec2084a 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-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>
+ <secondary>storage parameter</secondary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ Per-table value for <xref linkend="guc-autovacuum-vacuum-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..5731cf42f54 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_vacuum_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_vacuum_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..4b1e42635d0 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_vac_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_vac_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..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)
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: New GUC autovacuum_max_threshold ?
2025-01-09 18:06 Re: New GUC autovacuum_max_threshold ? Nathan Bossart <[email protected]>
2025-01-13 20:21 ` Re: New GUC autovacuum_max_threshold ? Nathan Bossart <[email protected]>
2025-01-13 23:17 ` Re: New GUC autovacuum_max_threshold ? Sami Imseih <[email protected]>
2025-01-14 02:09 ` Re: New GUC autovacuum_max_threshold ? Nathan Bossart <[email protected]>
@ 2025-01-14 17:08 ` Sami Imseih <[email protected]>
2025-01-14 17:45 ` Re: New GUC autovacuum_max_threshold ? Nathan Bossart <[email protected]>
1 sibling, 1 reply; 13+ messages in thread
From: Sami Imseih @ 2025-01-14 17:08 UTC (permalink / raw)
To: Nathan Bossart <[email protected]>; Frédéric Yhuel <[email protected]>; +Cc: Robert Treat <[email protected]>; wenhui qiu <[email protected]>; Robert Haas <[email protected]>; Imseih (AWS), Sami <[email protected]>; David Rowley <[email protected]>; Joe Conway <[email protected]>; Michael Banck <[email protected]>; Laurenz Albe <[email protected]>; Melanie Plageman <[email protected]>; PostgreSQL Hackers <[email protected]>
> 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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: New GUC autovacuum_max_threshold ?
2025-01-09 18:06 Re: New GUC autovacuum_max_threshold ? Nathan Bossart <[email protected]>
2025-01-13 20:21 ` Re: New GUC autovacuum_max_threshold ? Nathan Bossart <[email protected]>
2025-01-13 23:17 ` Re: New GUC autovacuum_max_threshold ? Sami Imseih <[email protected]>
2025-01-14 02:09 ` Re: New GUC autovacuum_max_threshold ? Nathan Bossart <[email protected]>
2025-01-14 17:08 ` Re: New GUC autovacuum_max_threshold ? Sami Imseih <[email protected]>
@ 2025-01-14 17:45 ` Nathan Bossart <[email protected]>
2025-01-14 17:59 ` Re: New GUC autovacuum_max_threshold ? Sami Imseih <[email protected]>
0 siblings, 1 reply; 13+ messages in thread
From: Nathan Bossart @ 2025-01-14 17:45 UTC (permalink / raw)
To: Sami Imseih <[email protected]>; +Cc: Frédéric Yhuel <[email protected]>; Robert Treat <[email protected]>; wenhui qiu <[email protected]>; Robert Haas <[email protected]>; Imseih (AWS), Sami <[email protected]>; David Rowley <[email protected]>; Joe Conway <[email protected]>; Michael Banck <[email protected]>; Laurenz Albe <[email protected]>; Melanie Plageman <[email protected]>; PostgreSQL Hackers <[email protected]>
On Tue, Jan 14, 2025 at 11:08:36AM -0600, Sami Imseih wrote:
> 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?
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: New GUC autovacuum_max_threshold ?
2025-01-09 18:06 Re: New GUC autovacuum_max_threshold ? Nathan Bossart <[email protected]>
2025-01-13 20:21 ` Re: New GUC autovacuum_max_threshold ? Nathan Bossart <[email protected]>
2025-01-13 23:17 ` Re: New GUC autovacuum_max_threshold ? Sami Imseih <[email protected]>
2025-01-14 02:09 ` Re: New GUC autovacuum_max_threshold ? Nathan Bossart <[email protected]>
2025-01-14 17:08 ` Re: New GUC autovacuum_max_threshold ? Sami Imseih <[email protected]>
2025-01-14 17:45 ` Re: New GUC autovacuum_max_threshold ? Nathan Bossart <[email protected]>
@ 2025-01-14 17:59 ` Sami Imseih <[email protected]>
2025-01-14 19:46 ` Re: New GUC autovacuum_max_threshold ? Sami Imseih <[email protected]>
0 siblings, 1 reply; 13+ messages in thread
From: Sami Imseih @ 2025-01-14 17:59 UTC (permalink / raw)
To: Nathan Bossart <[email protected]>; +Cc: Frédéric Yhuel <[email protected]>; Robert Treat <[email protected]>; wenhui qiu <[email protected]>; Robert Haas <[email protected]>; Imseih (AWS), Sami <[email protected]>; David Rowley <[email protected]>; Joe Conway <[email protected]>; Michael Banck <[email protected]>; Laurenz Albe <[email protected]>; Melanie Plageman <[email protected]>; PostgreSQL Hackers <[email protected]>
On Tue, Jan 14, 2025 at 11:45 AM Nathan Bossart
<[email protected]> wrote:
>
> On Tue, Jan 14, 2025 at 11:08:36AM -0600, Sami Imseih wrote:
> > 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?
>
> 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.
absolutely, the documentation will need to discuss the relationship
between all 3 parameters for the documentation to make sense.
Regards,
Sami
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: New GUC autovacuum_max_threshold ?
2025-01-09 18:06 Re: New GUC autovacuum_max_threshold ? Nathan Bossart <[email protected]>
2025-01-13 20:21 ` Re: New GUC autovacuum_max_threshold ? Nathan Bossart <[email protected]>
2025-01-13 23:17 ` Re: New GUC autovacuum_max_threshold ? Sami Imseih <[email protected]>
2025-01-14 02:09 ` Re: New GUC autovacuum_max_threshold ? Nathan Bossart <[email protected]>
2025-01-14 17:08 ` Re: New GUC autovacuum_max_threshold ? Sami Imseih <[email protected]>
2025-01-14 17:45 ` Re: New GUC autovacuum_max_threshold ? Nathan Bossart <[email protected]>
2025-01-14 17:59 ` Re: New GUC autovacuum_max_threshold ? Sami Imseih <[email protected]>
@ 2025-01-14 19:46 ` Sami Imseih <[email protected]>
0 siblings, 0 replies; 13+ messages in thread
From: Sami Imseih @ 2025-01-14 19:46 UTC (permalink / raw)
To: Nathan Bossart <[email protected]>; +Cc: Frédéric Yhuel <[email protected]>; Robert Treat <[email protected]>; wenhui qiu <[email protected]>; Robert Haas <[email protected]>; Imseih (AWS), Sami <[email protected]>; David Rowley <[email protected]>; Joe Conway <[email protected]>; Michael Banck <[email protected]>; Laurenz Albe <[email protected]>; Melanie Plageman <[email protected]>; PostgreSQL Hackers <[email protected]>
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: New GUC autovacuum_max_threshold ?
2025-01-09 18:06 Re: New GUC autovacuum_max_threshold ? Nathan Bossart <[email protected]>
2025-01-13 20:21 ` Re: New GUC autovacuum_max_threshold ? Nathan Bossart <[email protected]>
2025-01-13 23:17 ` Re: New GUC autovacuum_max_threshold ? Sami Imseih <[email protected]>
2025-01-14 02:09 ` Re: New GUC autovacuum_max_threshold ? Nathan Bossart <[email protected]>
@ 2025-01-14 20:35 ` Alena Rybakina <[email protected]>
2025-02-03 19:51 ` Re: New GUC autovacuum_max_threshold ? Nathan Bossart <[email protected]>
1 sibling, 1 reply; 13+ messages in thread
From: Alena Rybakina @ 2025-01-14 20:35 UTC (permalink / raw)
To: Nathan Bossart <[email protected]>; +Cc: Sami Imseih <[email protected]>; Robert Treat <[email protected]>; Frédéric Yhuel <[email protected]>; wenhui qiu <[email protected]>; Robert Haas <[email protected]>; Imseih (AWS), Sami <[email protected]>; David Rowley <[email protected]>; Joe Conway <[email protected]>; Michael Banck <[email protected]>; Laurenz Albe <[email protected]>; Melanie Plageman <[email protected]>; PostgreSQL Hackers <[email protected]>
Hi! Thanks for your work! it is very useful.
On 14.01.2025 05:09, Nathan Bossart wrote:
> On Mon, Jan 13, 2025 at 05:17:11PM -0600, Sami Imseih wrote:
>> 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.
> 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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: New GUC autovacuum_max_threshold ?
2025-01-09 18:06 Re: New GUC autovacuum_max_threshold ? Nathan Bossart <[email protected]>
2025-01-13 20:21 ` Re: New GUC autovacuum_max_threshold ? Nathan Bossart <[email protected]>
2025-01-13 23:17 ` Re: New GUC autovacuum_max_threshold ? Sami Imseih <[email protected]>
2025-01-14 02:09 ` Re: New GUC autovacuum_max_threshold ? Nathan Bossart <[email protected]>
2025-01-14 20:35 ` Re: New GUC autovacuum_max_threshold ? Alena Rybakina <[email protected]>
@ 2025-02-03 19:51 ` Nathan Bossart <[email protected]>
2025-02-04 01:41 ` Re: New GUC autovacuum_max_threshold ? wenhui qiu <[email protected]>
0 siblings, 1 reply; 13+ messages in thread
From: Nathan Bossart @ 2025-02-03 19:51 UTC (permalink / raw)
To: Alena Rybakina <[email protected]>; +Cc: Sami Imseih <[email protected]>; Robert Treat <[email protected]>; Frédéric Yhuel <[email protected]>; wenhui qiu <[email protected]>; Robert Haas <[email protected]>; Imseih (AWS), Sami <[email protected]>; David Rowley <[email protected]>; Joe Conway <[email protected]>; Michael Banck <[email protected]>; Laurenz Albe <[email protected]>; Melanie Plageman <[email protected]>; PostgreSQL Hackers <[email protected]>
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).
On Tue, Jan 14, 2025 at 11:35:17PM +0300, Alena Rybakina wrote:
> #autovacuum_vacuum_max_threshold = 100000000 # max number of row updates
> # before vacuum; -1 disables max
> # threshold
>
> 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.
> There is a typo:
>
> * if (threshold > vac_max_thresh)
> * threshold = vac_max_thres; - here
Fixed.
> 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;
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 protected]>
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 protected]>
Reviewed-by: Melanie Plageman <[email protected]>
Reviewed-by: Robert Haas <[email protected]>
Reviewed-by: Laurenz Albe <[email protected]>
Reviewed-by: Michael Banck <[email protected]>
Reviewed-by: Joe Conway <[email protected]>
Reviewed-by: Sami Imseih <[email protected]>
Reviewed-by: David Rowley <[email protected]>
Reviewed-by: wenhui qiu <[email protected]>
Reviewed-by: Robert Treat <[email protected]>
Reviewed-by: Alena Rybakina <[email protected]>
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>
+ <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>
+ <secondary>storage parameter</secondary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ Per-table value for <xref linkend="guc-autovacuum-vacuum-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..5731cf42f54 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_vacuum_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_vacuum_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..09ec9bb6990 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_vac_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_thresh;
*
* 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_vac_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 71448bb4fdd..b887d3e5983 100644
--- a/src/backend/utils/misc/guc_tables.c
+++ b/src/backend/utils/misc/guc_tables.c
@@ -3426,6 +3426,15 @@ struct config_int ConfigureNamesInt[] =
50, 0, INT_MAX,
NULL, NULL, NULL
},
+ {
+ {"autovacuum_vacuum_max_threshold", PGC_SIGHUP, VACUUM_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, VACUUM_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)
Attachments:
[text/plain] v7-0001-Introduce-autovacuum_vacuum_max_threshold.patch (12.9K, ../../Z6EeM0CNKJlxhAYb@nathan/2-v7-0001-Introduce-autovacuum_vacuum_max_threshold.patch)
download | inline diff:
From 392b07ea4d1a14d045937894d29e6679199a513f Mon Sep 17 00:00:00 2001
From: Nathan Bossart <[email protected]>
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 protected]>
Reviewed-by: Melanie Plageman <[email protected]>
Reviewed-by: Robert Haas <[email protected]>
Reviewed-by: Laurenz Albe <[email protected]>
Reviewed-by: Michael Banck <[email protected]>
Reviewed-by: Joe Conway <[email protected]>
Reviewed-by: Sami Imseih <[email protected]>
Reviewed-by: David Rowley <[email protected]>
Reviewed-by: wenhui qiu <[email protected]>
Reviewed-by: Robert Treat <[email protected]>
Reviewed-by: Alena Rybakina <[email protected]>
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>
+ <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>
+ <secondary>storage parameter</secondary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ Per-table value for <xref linkend="guc-autovacuum-vacuum-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..5731cf42f54 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_vacuum_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_vacuum_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..09ec9bb6990 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_vac_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_thresh;
*
* 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_vac_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 71448bb4fdd..b887d3e5983 100644
--- a/src/backend/utils/misc/guc_tables.c
+++ b/src/backend/utils/misc/guc_tables.c
@@ -3426,6 +3426,15 @@ struct config_int ConfigureNamesInt[] =
50, 0, INT_MAX,
NULL, NULL, NULL
},
+ {
+ {"autovacuum_vacuum_max_threshold", PGC_SIGHUP, VACUUM_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, VACUUM_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)
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: New GUC autovacuum_max_threshold ?
2025-01-09 18:06 Re: New GUC autovacuum_max_threshold ? Nathan Bossart <[email protected]>
2025-01-13 20:21 ` Re: New GUC autovacuum_max_threshold ? Nathan Bossart <[email protected]>
2025-01-13 23:17 ` Re: New GUC autovacuum_max_threshold ? Sami Imseih <[email protected]>
2025-01-14 02:09 ` Re: New GUC autovacuum_max_threshold ? Nathan Bossart <[email protected]>
2025-01-14 20:35 ` Re: New GUC autovacuum_max_threshold ? Alena Rybakina <[email protected]>
2025-02-03 19:51 ` Re: New GUC autovacuum_max_threshold ? Nathan Bossart <[email protected]>
@ 2025-02-04 01:41 ` wenhui qiu <[email protected]>
2025-02-05 21:52 ` Re: New GUC autovacuum_max_threshold ? Nathan Bossart <[email protected]>
0 siblings, 1 reply; 13+ messages in thread
From: wenhui qiu @ 2025-02-04 01:41 UTC (permalink / raw)
To: Nathan Bossart <[email protected]>; +Cc: Alena Rybakina <[email protected]>; Sami Imseih <[email protected]>; Robert Treat <[email protected]>; Frédéric Yhuel <[email protected]>; Robert Haas <[email protected]>; Imseih (AWS), Sami <[email protected]>; David Rowley <[email protected]>; Joe Conway <[email protected]>; Michael Banck <[email protected]>; Laurenz Albe <[email protected]>; Melanie Plageman <[email protected]>; PostgreSQL Hackers <[email protected]>
HI Nathan
> 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).
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.
On Tue, Feb 4, 2025 at 3:51 AM Nathan Bossart <[email protected]>
wrote:
> 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).
>
> On Tue, Jan 14, 2025 at 11:35:17PM +0300, Alena Rybakina wrote:
> > #autovacuum_vacuum_max_threshold = 100000000 # max number of row
> updates
> > # before vacuum; -1 disables max
> > # threshold
> >
> > 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.
>
> > There is a typo:
> >
> > * if (threshold > vac_max_thresh)
> > * threshold = vac_max_thres; - here
>
> Fixed.
>
> > 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;
>
> 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
>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: New GUC autovacuum_max_threshold ?
2025-01-09 18:06 Re: New GUC autovacuum_max_threshold ? Nathan Bossart <[email protected]>
2025-01-13 20:21 ` Re: New GUC autovacuum_max_threshold ? Nathan Bossart <[email protected]>
2025-01-13 23:17 ` Re: New GUC autovacuum_max_threshold ? Sami Imseih <[email protected]>
2025-01-14 02:09 ` Re: New GUC autovacuum_max_threshold ? Nathan Bossart <[email protected]>
2025-01-14 20:35 ` Re: New GUC autovacuum_max_threshold ? Alena Rybakina <[email protected]>
2025-02-03 19:51 ` Re: New GUC autovacuum_max_threshold ? Nathan Bossart <[email protected]>
2025-02-04 01:41 ` Re: New GUC autovacuum_max_threshold ? wenhui qiu <[email protected]>
@ 2025-02-05 21:52 ` Nathan Bossart <[email protected]>
0 siblings, 0 replies; 13+ messages in thread
From: Nathan Bossart @ 2025-02-05 21:52 UTC (permalink / raw)
To: wenhui qiu <[email protected]>; +Cc: Alena Rybakina <[email protected]>; Sami Imseih <[email protected]>; Robert Treat <[email protected]>; Frédéric Yhuel <[email protected]>; Robert Haas <[email protected]>; Imseih (AWS), Sami <[email protected]>; David Rowley <[email protected]>; Joe Conway <[email protected]>; Michael Banck <[email protected]>; Laurenz Albe <[email protected]>; Melanie Plageman <[email protected]>; PostgreSQL Hackers <[email protected]>
Committed.
--
nathan
^ permalink raw reply [nested|flat] 13+ messages in thread
end of thread, other threads:[~2025-02-05 21:52 UTC | newest]
Thread overview: 13+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-09-12 05:22 [PATCH v6 2/7] Row pattern recognition patch (parse/analysis). Tatsuo Ishii <[email protected]>
2025-01-09 18:06 Re: New GUC autovacuum_max_threshold ? Nathan Bossart <[email protected]>
2025-01-13 20:21 ` Re: New GUC autovacuum_max_threshold ? Nathan Bossart <[email protected]>
2025-01-13 23:17 ` Re: New GUC autovacuum_max_threshold ? Sami Imseih <[email protected]>
2025-01-14 02:09 ` Re: New GUC autovacuum_max_threshold ? Nathan Bossart <[email protected]>
2025-01-14 17:08 ` Re: New GUC autovacuum_max_threshold ? Sami Imseih <[email protected]>
2025-01-14 17:45 ` Re: New GUC autovacuum_max_threshold ? Nathan Bossart <[email protected]>
2025-01-14 17:59 ` Re: New GUC autovacuum_max_threshold ? Sami Imseih <[email protected]>
2025-01-14 19:46 ` Re: New GUC autovacuum_max_threshold ? Sami Imseih <[email protected]>
2025-01-14 20:35 ` Re: New GUC autovacuum_max_threshold ? Alena Rybakina <[email protected]>
2025-02-03 19:51 ` Re: New GUC autovacuum_max_threshold ? Nathan Bossart <[email protected]>
2025-02-04 01:41 ` Re: New GUC autovacuum_max_threshold ? wenhui qiu <[email protected]>
2025-02-05 21:52 ` Re: New GUC autovacuum_max_threshold ? Nathan Bossart <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox