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 <nat...@postgresql.org> 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)
