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 1sboO6-001b4d-TS for pgsql-hackers@arkaria.postgresql.org; Wed, 07 Aug 2024 21:40:06 +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 1sboO5-00AMia-GV for pgsql-hackers@arkaria.postgresql.org; Wed, 07 Aug 2024 21:40:05 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sboO5-00AMiR-2X for pgsql-hackers@lists.postgresql.org; Wed, 07 Aug 2024 21:40:05 +0000 Received: from mail-io1-xd33.google.com ([2607:f8b0:4864:20::d33]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1sboO1-003bZL-7I for pgsql-hackers@lists.postgresql.org; Wed, 07 Aug 2024 21:40:03 +0000 Received: by mail-io1-xd33.google.com with SMTP id ca18e2360f4ac-8223685fbe9so9602439f.1 for ; Wed, 07 Aug 2024 14:40:01 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1723066800; x=1723671600; 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=QzVBs/FA8jTJhC2BYpZVGtNJyEgSX1sjssflW2a431M=; b=C9DZxTxZeDGF9e4DH8mYgiNlFE82t9WzowCvj8TKuHL18CAuBo0Vj018zWjNbsk+Fn khmKqnjl4b3SQq0zKT2Pc48Seg+01AsfSKkBQFjYOuxkMQtM0Rgio0+dJ8fmDMrC2hgk gwTJE+bHcxyprm5ksa3bsJaQCurq/jKjfd94IRrh/ReeNJ+3lldaXRah7J3FEnR54GAV 5uLf8fX6ePjmTs3/X9tJM16g6aCFyZOM93u3peJQtqH/adhnKkZQzhaHPHhBM/XKoEAX ejRsrrL7bt1bMPUesEm4dTWNnvqt12RL6m6F2bnqEkuctc6qW5d5F52VdvhHhLGuFBRD gxFg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1723066800; x=1723671600; 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=QzVBs/FA8jTJhC2BYpZVGtNJyEgSX1sjssflW2a431M=; b=Z91xTX7q1KkhLp3vhCdmNyt9ijdMscghfYmPvkN83U2KpG1Kl4G2918O9/uRoeddBR pw5Mnw47PYGhfeM/mFp+hz/DyKV9heZmRGqG19FuB0kg+LuT3JJyqyWhlsNJqNXHIa0u RUm4/UPFvOoGZdrPTlmDQszbZLGJF05WK+dM/jSQZoKg7QVKNO85EZij2fvHCDMA2Fy4 QQO9Jjfprg6l4MvTtUvngnqSqjQuCKDbhb/3xV8KPtJVbRSXZEnhpKkali9x+OtEJjfu On7mNNx483p/a9oSvIceqMz7QPENThqfeDkHlXhOI70fmnt6KvJo8Hm6HvfEZvRRwoeB kwOA== X-Forwarded-Encrypted: i=1; AJvYcCXLDIE6bLn2GUg5hKrs7JzCuxH9IidppoZvYBn/AMfMo8hp3cHAZDw06eaYGtJThapDfNbZRw5+Kt61+5W19aPKFRvZI3j5Jue075Bl/ZAwni8h X-Gm-Message-State: AOJu0Yy2CrDY7JIpeR8eHa31AuVGuOAojKGEQJUxM6i/WD37EhIW1epC Rfbn1R4krtHaquHtPVpcyn6iIslRSoWTAyU9cNsNlpnoBuGpJaKM X-Google-Smtp-Source: AGHT+IGLxjOcodx/JifvJFspw/r+C7ebZWxKdjCMpY93+pQ/LO1zz+K/dZy2wQu8Z0XtMR7uQszzqA== X-Received: by 2002:a05:6602:6b0e:b0:7f6:8650:9684 with SMTP id ca18e2360f4ac-822537d058fmr11504639f.5.1723066799536; Wed, 07 Aug 2024 14:39:59 -0700 (PDT) Received: from nathan (162-195-168-172.lightspeed.stlsmo.sbcglobal.net. [162.195.168.172]) by smtp.gmail.com with ESMTPSA id 8926c6da1cb9f-4c8d69a6bbdsm3006616173.38.2024.08.07.14.39.58 (version=TLS1_3 cipher=TLS_AES_256_GCM_SHA384 bits=256/256); Wed, 07 Aug 2024 14:39:58 -0700 (PDT) Date: Wed, 7 Aug 2024 16:39:57 -0500 From: Nathan Bossart To: =?iso-8859-1?Q?Fr=E9d=E9ric?= Yhuel Cc: 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: <20240507211702.GA2720371@nathanxps13> <0A799687-DE0A-4BFC-8900-E1D57EB6C211@amazon.com> <97f04e09-a08a-417c-b65d-5d5cbdd0b845@dalibo.com> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="Ao6UM5N5kJGwY4Kk" Content-Disposition: inline Content-Transfer-Encoding: 8bit In-Reply-To: <97f04e09-a08a-417c-b65d-5d5cbdd0b845@dalibo.com> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --Ao6UM5N5kJGwY4Kk Content-Type: text/plain; charset=iso-8859-1 Content-Disposition: inline Content-Transfer-Encoding: 8bit I've attached a new patch to show roughly what I think this new GUC should look like. I'm hoping this sparks more discussion, if nothing else. On Tue, Jun 18, 2024 at 12:36:42PM +0200, Frédéric Yhuel wrote: > By the way, I wonder if there were any off-list discussions after Robert's > conference at PGConf.dev (and I'm waiting for the video of the conf). I don't recall any discussions about this idea, but Robert did briefly mention it in his talk [0]. [0] https://www.youtube.com/watch?v=RfTD-Twpvac -- nathan --Ao6UM5N5kJGwY4Kk Content-Type: text/plain; charset=us-ascii Content-Disposition: attachment; filename="v2-0001-autovacuum_max_threshold.patch" From ccfaee370835d755b663357bf139fea729a5f454 Mon Sep 17 00:00:00 2001 From: Nathan Bossart Date: Wed, 7 Aug 2024 16:22:37 -0500 Subject: [PATCH v2 1/1] autovacuum_max_threshold --- doc/src/sgml/config.sgml | 24 +++++++++++++++++++ doc/src/sgml/ref/create_table.sgml | 15 ++++++++++++ src/backend/access/common/reloptions.c | 11 +++++++++ src/backend/postmaster/autovacuum.c | 11 +++++++++ src/backend/utils/misc/guc_tables.c | 9 +++++++ src/backend/utils/misc/postgresql.conf.sample | 2 ++ src/bin/psql/tab-complete.c | 2 ++ src/include/postmaster/autovacuum.h | 1 + src/include/utils/rel.h | 1 + 9 files changed, 76 insertions(+) diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml index a1a1d58a43..10855b0e19 100644 --- a/doc/src/sgml/config.sgml +++ b/doc/src/sgml/config.sgml @@ -8615,6 +8615,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 93b3f664f2..19b5ea7421 100644 --- a/doc/src/sgml/ref/create_table.sgml +++ b/doc/src/sgml/ref/create_table.sgml @@ -1596,6 +1596,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 49fd35bfc5..c18ee4c497 100644 --- a/src/backend/access/common/reloptions.c +++ b/src/backend/access/common/reloptions.c @@ -231,6 +231,15 @@ static relopt_int intRelOpts[] = }, -1, 0, INT_MAX }, + { + { + "autovacuum_max_threshold", + "Maximum number of tuple updates or deletes prior to vacuum", + RELOPT_KIND_HEAP | RELOPT_KIND_TOAST, + ShareUpdateExclusiveLock + }, + -1, 0, INT_MAX + }, { { "autovacuum_vacuum_insert_threshold", @@ -1843,6 +1852,8 @@ default_reloptions(Datum reloptions, bool validate, relopt_kind kind) offsetof(StdRdOptions, autovacuum) + offsetof(AutoVacOpts, enabled)}, {"autovacuum_vacuum_threshold", RELOPT_TYPE_INT, offsetof(StdRdOptions, autovacuum) + offsetof(AutoVacOpts, vacuum_threshold)}, + {"autovacuum_max_threshold", RELOPT_TYPE_INT, + offsetof(StdRdOptions, autovacuum) + offsetof(AutoVacOpts, vacuum_max_threshold)}, {"autovacuum_vacuum_insert_threshold", RELOPT_TYPE_INT, offsetof(StdRdOptions, autovacuum) + offsetof(AutoVacOpts, vacuum_ins_threshold)}, {"autovacuum_analyze_threshold", RELOPT_TYPE_INT, diff --git a/src/backend/postmaster/autovacuum.c b/src/backend/postmaster/autovacuum.c index 4e4a0ccbef..d43d48abc8 100644 --- a/src/backend/postmaster/autovacuum.c +++ b/src/backend/postmaster/autovacuum.c @@ -119,6 +119,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; @@ -2887,6 +2888,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 @@ -2925,6 +2928,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, @@ -2966,6 +2970,10 @@ relation_needs_vacanalyze(Oid relid, ? relopts->vacuum_threshold : autovacuum_vac_thresh; + vac_max_thresh = (relopts && relopts->vacuum_max_threshold >= 0) + ? relopts->vacuum_max_threshold + : autovacuum_max_thresh; + vac_ins_scale_factor = (relopts && relopts->vacuum_ins_scale_factor >= 0) ? relopts->vacuum_ins_scale_factor : autovacuum_vac_ins_scale; @@ -3039,6 +3047,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 c0a52cdcc3..9aae183786 100644 --- a/src/backend/utils/misc/guc_tables.c +++ b/src/backend/utils/misc/guc_tables.c @@ -3403,6 +3403,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 9ec9f97e92..e7cec694f3 100644 --- a/src/backend/utils/misc/postgresql.conf.sample +++ b/src/backend/utils/misc/postgresql.conf.sample @@ -663,6 +663,8 @@ #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.c b/src/bin/psql/tab-complete.c index 024469474d..f3ce76e23d 100644 --- a/src/bin/psql/tab-complete.c +++ b/src/bin/psql/tab-complete.c @@ -1295,6 +1295,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", @@ -1311,6 +1312,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 cae1e8b329..efbab7bfa9 100644 --- a/src/include/postmaster/autovacuum.h +++ b/src/include/postmaster/autovacuum.h @@ -32,6 +32,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 8700204953..bcc6e2607b 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.3 (Apple Git-146) --Ao6UM5N5kJGwY4Kk--