Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tVcFD-00A1ow-Kn for pgsql-hackers@arkaria.postgresql.org; Wed, 08 Jan 2025 20:01:36 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1tVcFB-005t2W-Oo for pgsql-hackers@arkaria.postgresql.org; Wed, 08 Jan 2025 20:01:33 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tVcFB-005t2O-Bg for pgsql-hackers@lists.postgresql.org; Wed, 08 Jan 2025 20:01:33 +0000 Received: from mail-io1-xd2a.google.com ([2607:f8b0:4864:20::d2a]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1tVcF7-000amc-27 for pgsql-hackers@lists.postgresql.org; Wed, 08 Jan 2025 20:01:32 +0000 Received: by mail-io1-xd2a.google.com with SMTP id ca18e2360f4ac-84ceaf2667aso8322339f.3 for ; Wed, 08 Jan 2025 12:01:30 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1736366488; x=1736971288; darn=lists.postgresql.org; h=in-reply-to:content-transfer-encoding:content-disposition :mime-version:references:message-id:subject:cc:to:from:date:from:to :cc:subject:date:message-id:reply-to; bh=wivIt9kdwLhH3y2uG7Q5UTJZPGMumZA57c/sjjLgBKA=; b=ihVV0Zoz5UelciKNFXFFq+TH/bL8Yvroj7RiZKO8mTlgJqyiQ8md9yI4N0cC6WHCMT MRdfFjzbc4uFpMGJy5dCeUp+uwFopCNLbnyCJRFln0jmJ+Mqpw8+GjMAaz/Y0bppLFLq NKlMPdNUg3Grj25ZQ9PxvOvK+bOaQZ1UThEQQ11o3YMUoaOaOrQD2jLRQ0yAmE95gbet u9MIpRGnBXTFMdb8qigWJVlDZnVBns69mrMB/WLaQFx1FFazJTwjOLMHnPBBYU/LfDt2 tm1QDCiF3qhUuernN1pBkTyTK5xZc+bsyAgqfjxVg+O+DkLyDO1Bz6zl7yyCuZIbtGLK 7ySg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1736366488; x=1736971288; h=in-reply-to:content-transfer-encoding:content-disposition :mime-version:references:message-id:subject:cc:to:from:date :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=wivIt9kdwLhH3y2uG7Q5UTJZPGMumZA57c/sjjLgBKA=; b=ky7Lmw5W17ZVeDVkqylOtazbmK1UE7OGx5aqEo+3s+1ZVswzRy5ykim9rc3Wg3fG0G WNaTpJOGPdUb/32m+wgqX09SAV9/2npBgYryGbCz4qwGNs8nhMwaLS3rLIU6A61v56Zm 9im7imhnmwnFyeUDRVh2bWF67eWcBPic2CcfllmiRuzK6NepIGYp5FTUer6fm5HzqAOP PKOqoRgg96z3I2ORgnQOZot2qCsCe3YfTjlLYLsmZ0csK5TAmQ7t/ARd5mWzym9P01mI qIQFW1y41zTVt3Cg7MuN9EzlYdu/GhnNGaD+aStMBUdN2bP7F+JCuQ9f1T9v9uyVCUar luPQ== X-Forwarded-Encrypted: i=1; AJvYcCV0x8+k10vufoNrBpcwOW84Snadf5RSd84KL5H5GENlJY18oJbH6bZiOwsHQuIRnEOS6mD5k31vnUPT0ooD@lists.postgresql.org X-Gm-Message-State: AOJu0YwORPMSZkVzxusqLLQqUFRZqot3dyH/DE/zYqHKTcCnj68T47Fy e0AVUbki7BP2ZDherb8qlWJxlI/WMrxKqKdNc76HkIcxrEphXDt6 X-Gm-Gg: ASbGncupOE7AVWSm93z2Njec5jfgulAbEfm/tOgv/QJmM2cwfy5WnfuooxVjUzRZZnd fCFFdkKFktpVkNIc5rvcsNyLKC9PwJcwDxXgIP0NVLLT2kF7W17CAbedpMqDR97AqrfVrMBqKEH u3mGL8viCNiPy3aFHxhhW35hWNuo7B0v1qC0IV8bB6LBiE8mcU7MNMTNol0nihChq3W78FxNjYr QrsjzNZfdTqyfoATaX3MUOKSbdT5Qp/kxEQPSXlAGMd7JzfgjTdeqhHVdvxOiwvircBuIqjRbi3 X+hVLQEC6bkFfH7yb2dZ1YweyPOpyxr1o0CnI942Ps+R5ZM= X-Google-Smtp-Source: AGHT+IEZ8BHMYhfC2/9+ayGUw+r6x3lgUZOWrbZgYddxAAeUGwiY8ef/AkccwJQp6a9AgA3oQkY1lg== X-Received: by 2002:a05:6e02:248c:b0:3cd:d6a7:dbd1 with SMTP id e9e14a558f8ab-3ce3a9d4345mr36117495ab.13.1736366488413; Wed, 08 Jan 2025 12:01:28 -0800 (PST) Received: from nathan (162-195-168-172.lightspeed.stlsmo.sbcglobal.net. [162.195.168.172]) by smtp.gmail.com with ESMTPSA id e9e14a558f8ab-3c0df9496edsm112740735ab.43.2025.01.08.12.01.26 (version=TLS1_3 cipher=TLS_AES_256_GCM_SHA384 bits=256/256); Wed, 08 Jan 2025 12:01:27 -0800 (PST) Date: Wed, 8 Jan 2025 14:01:25 -0600 From: Nathan Bossart To: =?iso-8859-1?Q?Fr=E9d=E9ric?= Yhuel Cc: wenhui qiu , Robert Haas , "Imseih (AWS), Sami" , David Rowley , Joe Conway , Michael Banck , Laurenz Albe , Melanie Plageman , PostgreSQL Hackers Subject: Re: New GUC autovacuum_max_threshold ? Message-ID: References: <6a2ac9b7-6535-4bb1-8274-0647f7c31c82@dalibo.com> <2ca7f9c7-5da9-46cb-9072-39b71bfab948@dalibo.com> <93f3dab5-caa4-4213-9be0-855d5aaa19bf@dalibo.com> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="yunJ6xgZ48gn23uM" Content-Disposition: inline Content-Transfer-Encoding: 8bit In-Reply-To: <93f3dab5-caa4-4213-9be0-855d5aaa19bf@dalibo.com> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --yunJ6xgZ48gn23uM Content-Type: text/plain; charset=iso-8859-1 Content-Disposition: inline Content-Transfer-Encoding: 8bit On Wed, Jan 08, 2025 at 02:48:10PM +0100, Frédéric Yhuel wrote: > For what it's worth, although I would have preferred the sub-linear growth > thing, I'd much rather have this than nothing. +1, this is how I feel, too. But I also don't want to add something that folks won't find useful. > And I have to admit that the proposed formulas were either too convoluted or > wrong. > > This very patch is more straightforward. Please let me know if I can help > and how. I read through the thread from the top, and it does seem like there is reasonably strong support for the hard cap. Upon a closer review of the patch, I noticed that the relopt was defined such that you couldn't disable autovacuum_max_threshold on a per-table basis, so I fixed that in v4. -- nathan --yunJ6xgZ48gn23uM Content-Type: text/plain; charset=iso-8859-1 Content-Disposition: attachment; filename="v4-0001-Introduce-autovacuum_max_threshold.patch" Content-Transfer-Encoding: 8bit From 6004127a57ff6062da7e4696cd9358d18e6b6141 Mon Sep 17 00:00:00 2001 From: Nathan Bossart Date: Wed, 8 Jan 2025 13:11:52 -0600 Subject: [PATCH v4 1/1] Introduce autovacuum_max_threshold. MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit Author: Nathan Bossart, Frédéric Yhuel Reviewed-by: Melanie Plageman, Robert Haas, Laurenz Albe, Michael Banck, Joe Conway, Sami Imseih, David Rowley, wenhui qiu Discussion: https://postgr.es/m/956435f8-3b2f-47a6-8756-8c54ded61802%40dalibo.com --- doc/src/sgml/config.sgml | 24 +++++++++++++++++++ doc/src/sgml/ref/create_table.sgml | 15 ++++++++++++ src/backend/access/common/reloptions.c | 11 +++++++++ src/backend/postmaster/autovacuum.c | 12 ++++++++++ src/backend/utils/misc/guc_tables.c | 9 +++++++ src/backend/utils/misc/postgresql.conf.sample | 2 ++ src/bin/psql/tab-complete.in.c | 2 ++ src/include/postmaster/autovacuum.h | 1 + src/include/utils/rel.h | 1 + 9 files changed, 77 insertions(+) diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml index 8683f0bdf53..3c03bdd5790 100644 --- a/doc/src/sgml/config.sgml +++ b/doc/src/sgml/config.sgml @@ -8712,6 +8712,30 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv; + + autovacuum_max_threshold (integer) + + autovacuum_max_threshold + configuration parameter + + + + + Specifies the maximum number of updated or deleted tuples needed to + trigger a VACUUM in any one table, i.e., a cap on + the value calculated with + autovacuum_vacuum_threshold and + autovacuum_vacuum_scale_factor. 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 + VACUUM operation. This parameter can only be set in + the postgresql.conf file or on the server command + line; but the setting can be overridden for individual tables by + changing storage parameters. + + + + autovacuum_vacuum_insert_threshold (integer) diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml index 70fa929caa4..77cee4a2888 100644 --- a/doc/src/sgml/ref/create_table.sgml +++ b/doc/src/sgml/ref/create_table.sgml @@ -1682,6 +1682,21 @@ WITH ( MODULUS numeric_literal, REM + + autovacuum_max_threshold, toast.autovacuum_max_threshold (integer) + + autovacuum_max_threshold + storage parameter + + + + + Per-table value for + parameter. + + + + autovacuum_vacuum_scale_factor, toast.autovacuum_vacuum_scale_factor (floating point) 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) --yunJ6xgZ48gn23uM--