public inbox for [email protected]help / color / mirror / Atom feed
Re: Disabling vacuum truncate for autovacuum 3+ messages / 3 participants [nested] [flat]
* Re: Disabling vacuum truncate for autovacuum @ 2025-02-28 04:29 Gurjeet Singh <[email protected]> 0 siblings, 1 reply; 3+ messages in thread From: Gurjeet Singh @ 2025-02-28 04:29 UTC (permalink / raw) To: Nathan Bossart <[email protected]>; +Cc: Robert Haas <[email protected]>; Laurenz Albe <[email protected]>; Postgres Hackers <[email protected]>; Will Storey <[email protected]> On Mon, Jan 27, 2025 at 1:55 AM Laurenz Albe <[email protected]> wrote: > > On Thu, 2025-01-23 at 22:33 -0800, Gurjeet Singh wrote: > > > > I am also wondering if having an autovacuum setting to control it would be > > > > a good idea for a feature. > > > > > > I'm all for that. > > > > Please see attached an initial patch to disable truncation behaviour in > > autovacuum. This patch retains the default behavior of autovacuum truncating > > relations. The user is allowed to change the behaviour and disable relation > > truncations system-wide by setting autovacuum_disable_vacuum_truncate = true. > > Better parameter names welcome :-) > > I hope it is possible to override the global setting with the "vacuum_truncate" > option on an individual table. Current patch behaviour is that if the autovacuum_vacuum_truncate is false, then autovacuum will _not_ truncate any relations. If the parameter's value is true (the default), then the relation's reloption will be honored. A table-owner, or the database-owner, may enable truncation of a table, as they may be trying to be nice and return the unused disk space back to the OS/filesystem. But if the sysadmin/DBA (who is ultimately responsible for the health of the entire db instance, as well as of any replicas of the db instance), wants to disable truncation across all databases to ensure that the replication does not get stuck, then IMHO Postgres should empower the sysadmin to make that decision, and override the relation-level setting enabled by the table- owner or the database-owner. > > One additional improvement I can think of is to emit a WARNING or NOTICE message > > that truncate operation is being skipped, perhaps only if the truncation > > would've freed up space over a certain threshold. > > Interesting idea, but I think it is independent from this patch. Agreed. I'll consider writing a separate patch for this. > > Perhaps there's value in letting this parameter be specified at database level, > > but I'm not able to think of a reason why someone would want to disable this > > behaviour on just one database. So leaving the parameter context to be the same > > as most other autovacuum parameters: SIGHUP. > > I can imagine setting that on only a certain database. Different databases > typically have different applications, which have different needs. Makes sense. I don't think anything special needs to be done in the patch to address this. > Eventually, the patch should have documentation and regression tests. Documentation added. Pointers on if, where, and what kind of tests to add will be appreciated. On Mon, Jan 27, 2025 at 12:38 PM Robert Haas <[email protected]> wrote: > > On Mon, Jan 27, 2025 at 4:55 AM Laurenz Albe <[email protected]> wrote: > > My suggestion for the parameter name is "autovacuum_disable_truncate". > > Then it would have a different name than the reloption, and the > opposite sense. In most cases, we've been able to keep those matching > -- autovacuum vs. autovacuum_enabled being, I believe, the only > current mismatch. If we want to maintain the convention of autovacuum parameters names to be of the format "autovacuum_<vacuum-option's-name>" then I believe the name autovacuum_vacuum_truncate (boolean) would be better, as compared to my original proposal (autovacuum_disable_vacuum_truncate), or Laurenz's proposal above. The default value should be true, to match the current autovacuum behaviour. > Also, how sure are we that turning this off globally is a solid idea? > Off-hand, it doesn't sound that bad: there are probably situations in > which autovacuum never truncates anything anyway just because the tail > blocks of the relations always contain at least 1 tuple. But we should > be careful not to add a setting that is far more likely to get people > into trouble than to get them out of it. It would be good to hear what > other people think about the risk vs. reward tradeoff in this case. Taking silence from others to be a sign of no opposition, I'm moving forward with the patch. On Tue, Feb 18, 2025 at 11:56 AM Nathan Bossart <[email protected]> wrote: > > On Mon, Jan 27, 2025 at 03:38:39 PM -0500, Robert Haas wrote: > > Also, how sure are we that turning this off globally is a solid idea? > In any case, it's > already possible to achieve $SUBJECT with a trivial script that sets > storage parameters on all tables, so IMHO it would be silly to withhold a > global setting that does the same thing just on principle. +1 For documentation of this GUC, I borrowed heavily from the relevant sections of CREATE TABLE and VACUUM docs. There are 3 ways I wrote one of the sentences in the docs. I picked the last one, as it is concise and clearer than the others. If others feel a different choice of words would be better, I'm all ears. If <literal>false</literal>, autovacuum will not perform the truncation, even if the <literal>vacuum_truncate</literal> option has been set to <literal>true</literal> for the table being processed. If <literal>false</literal>, autovacuum will not perform the truncation, and it ignores the <literal>vacuum_truncate</literal> option for the tables it processes. If <literal>false</literal>, autovacuum will not perform the truncation on any tables it vacuums. The <literal>vacuum_truncate</literal> option on the tables is ignored. PS: Nathan, your latest email arrived as I was preparing this email and patch, so this email and patch does not address concerns, if any, in your latest email. I will try to respond to it soon. Best regards, Gurjeet http://Gurje.et Attachments: [application/octet-stream] autovacuum_disable_relation_truncation.v2.patch (4.4K, 2-autovacuum_disable_relation_truncation.v2.patch) download | inline diff: diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml index e55700f35b8..17d94d7ce58 100644 --- a/doc/src/sgml/config.sgml +++ b/doc/src/sgml/config.sgml @@ -8934,6 +8934,42 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv; </listitem> </varlistentry> + <varlistentry id="guc-autovacuum-vacuum-truncate" xreflabel="autovacuum_vacuum_truncate"> + <term><varname>autovacuum_vacuum_truncate</varname> (<type>boolean</type>) + <indexterm> + <primary><varname>autovacuum_vacuum_truncate</varname></primary> + <secondary>configuration parameter</secondary> + </indexterm> + </term> + <listitem> + <para> + Enables or disables autovacuum to try to truncate off any empty pages + at the end of the tables, as it processes them. The default value is + <literal>true</literal>. + + If <literal>true</literal>, autovacuum will perforn the truncation and + the disk space for the truncated pages is returned to the operating + system. + + This is normally the desired behavior and is the default unless the + <literal>vacuum_truncate</literal> option for a table being vacuumed + has been set to <literal>false</literal>. + + If <literal>false</literal>, autovacuum will not perform the truncation + on any tables it vacuums. The <literal>vacuum_truncate</literal> option + on the tables is ignored. + + Note that the truncation requires <literal>ACCESS EXCLUSIVE</literal> + lock on the table. + + Setting this option to <literal>false</literal> may be useful to avoid + <literal>ACCESS EXCLUSIVE</literal> lock on the table that the + truncation requires. + + </para> + </listitem> + </varlistentry> + </variablelist> </sect2> diff --git a/src/backend/postmaster/autovacuum.c b/src/backend/postmaster/autovacuum.c index ddb303f5201..d8261a9b19d 100644 --- a/src/backend/postmaster/autovacuum.c +++ b/src/backend/postmaster/autovacuum.c @@ -115,6 +115,7 @@ * GUC parameters */ bool autovacuum_start_daemon = false; +bool autovacuum_vacuum_truncate = true; int autovacuum_worker_slots; int autovacuum_max_workers; int autovacuum_work_mem = -1; @@ -2812,12 +2813,16 @@ table_recheck_autovac(Oid relid, HTAB *table_toast_map, (!wraparound ? VACOPT_SKIP_LOCKED : 0); /* - * index_cleanup and truncate are unspecified at first in autovacuum. - * They will be filled in with usable values using their reloptions - * (or reloption defaults) later. + * index_cleanup is unspecified at first in autovacuum. truncate is + * unspecified, unless it is disabled via the GUC parameter. + * + * The unspecified options will be filled in with usable values using + * their reloptions (or reloption defaults) later. */ tab->at_params.index_cleanup = VACOPTVALUE_UNSPECIFIED; - tab->at_params.truncate = VACOPTVALUE_UNSPECIFIED; + tab->at_params.truncate = (autovacuum_vacuum_truncate == false) + ? VACOPTVALUE_DISABLED + : VACOPTVALUE_UNSPECIFIED; /* As of now, we don't support parallel vacuum for autovacuum */ tab->at_params.nworkers = -1; tab->at_params.freeze_min_age = freeze_min_age; diff --git a/src/backend/utils/misc/guc_tables.c b/src/backend/utils/misc/guc_tables.c index ad25cbb39c5..d1443256110 100644 --- a/src/backend/utils/misc/guc_tables.c +++ b/src/backend/utils/misc/guc_tables.c @@ -1531,6 +1531,16 @@ struct config_bool ConfigureNamesBool[] = NULL, NULL, NULL }, + { + {"autovacuum_vacuum_truncate", PGC_SIGHUP, VACUUM_AUTOVACUUM, + gettext_noop("Disables autovacuum behavior of truncatiing relations."), + NULL + }, + &autovacuum_vacuum_truncate, + true, + NULL, NULL, NULL + }, + { {"trace_notify", PGC_USERSET, DEVELOPER_OPTIONS, gettext_noop("Generates debugging output for LISTEN and NOTIFY."), diff --git a/src/include/postmaster/autovacuum.h b/src/include/postmaster/autovacuum.h index 6a95e5f55bd..d0bd6f498c7 100644 --- a/src/include/postmaster/autovacuum.h +++ b/src/include/postmaster/autovacuum.h @@ -28,6 +28,7 @@ typedef enum /* GUC variables */ extern PGDLLIMPORT bool autovacuum_start_daemon; +extern PGDLLIMPORT bool autovacuum_vacuum_truncate; extern PGDLLIMPORT int autovacuum_worker_slots; extern PGDLLIMPORT int autovacuum_max_workers; extern PGDLLIMPORT int autovacuum_work_mem; ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Disabling vacuum truncate for autovacuum @ 2025-02-28 18:21 Nathan Bossart <[email protected]> parent: Gurjeet Singh <[email protected]> 0 siblings, 1 reply; 3+ messages in thread From: Nathan Bossart @ 2025-02-28 18:21 UTC (permalink / raw) To: Gurjeet Singh <[email protected]>; +Cc: Robert Haas <[email protected]>; Laurenz Albe <[email protected]>; Postgres Hackers <[email protected]>; Will Storey <[email protected]> On Thu, Feb 27, 2025 at 08:29:16PM -0800, Gurjeet Singh wrote: > On Mon, Jan 27, 2025 at 1:55 AM Laurenz Albe <[email protected]> wrote: >> I hope it is possible to override the global setting with the "vacuum_truncate" >> option on an individual table. > > Current patch behaviour is that if the autovacuum_vacuum_truncate is false, then > autovacuum will _not_ truncate any relations. If the parameter's value is true > (the default), then the relation's reloption will be honored. > > A table-owner, or the database-owner, may enable truncation of a table, as they > may be trying to be nice and return the unused disk space back to the > OS/filesystem. But if the sysadmin/DBA (who is ultimately responsible for the > health of the entire db instance, as well as of any replicas of the db > instance), > wants to disable truncation across all databases to ensure that the replication > does not get stuck, then IMHO Postgres should empower the sysadmin to make > that decision, and override the relation-level setting enabled by the table- > owner or the database-owner. IIUC reloptions with corresponding GUCs typically override the GUC setting, although autovacuum_enabled is arguably an exception. If setting the GUC to false overrides the relation-specific settings, it also becomes more difficult to enable truncation for just a couple of tables, although that might not be a popular use-case. Furthermore, even if we do want the GUC to override the reloption, it won't override VACUUM (TRUNCATE). >> > One additional improvement I can think of is to emit a WARNING or NOTICE message >> > that truncate operation is being skipped, perhaps only if the truncation >> > would've freed up space over a certain threshold. >> >> Interesting idea, but I think it is independent from this patch. > > Agreed. I'll consider writing a separate patch for this. Perhaps it would be useful to say whether truncation was attempted in the output of VACUUM (VERBOSE) and the autovacuum logs. >> > Perhaps there's value in letting this parameter be specified at database level, >> > but I'm not able to think of a reason why someone would want to disable this >> > behaviour on just one database. So leaving the parameter context to be the same >> > as most other autovacuum parameters: SIGHUP. >> >> I can imagine setting that on only a certain database. Different databases >> typically have different applications, which have different needs. > > Makes sense. I don't think anything special needs to be done in the patch to > address this. Hm. I was thinking PGC_USERSET might make sense for this one, but that was only because I didn't see any technical reason to restrict it. I don't know whether limiting it accomplishes anything beyond making it more cumbersome for users to choose their desired default truncation setting. > PS: Nathan, your latest email arrived as I was preparing this email and patch, > so this email and patch does not address concerns, if any, in your latest email. > I will try to respond to it soon. Oops, sorry for the conflict. I'm happy to take a step back and be the reviewer/committer for this one. -- nathan ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Disabling vacuum truncate for autovacuum @ 2025-03-05 23:54 Fujii Masao <[email protected]> parent: Nathan Bossart <[email protected]> 0 siblings, 0 replies; 3+ messages in thread From: Fujii Masao @ 2025-03-05 23:54 UTC (permalink / raw) To: Nathan Bossart <[email protected]>; Gurjeet Singh <[email protected]>; +Cc: Robert Haas <[email protected]>; Laurenz Albe <[email protected]>; Postgres Hackers <[email protected]>; Will Storey <[email protected]> On 2025/03/01 3:21, Nathan Bossart wrote: > On Thu, Feb 27, 2025 at 08:29:16PM -0800, Gurjeet Singh wrote: >> On Mon, Jan 27, 2025 at 1:55 AM Laurenz Albe <[email protected]> wrote: >>> I hope it is possible to override the global setting with the "vacuum_truncate" >>> option on an individual table. >> >> Current patch behaviour is that if the autovacuum_vacuum_truncate is false, then >> autovacuum will _not_ truncate any relations. If the parameter's value is true >> (the default), then the relation's reloption will be honored. >> >> A table-owner, or the database-owner, may enable truncation of a table, as they >> may be trying to be nice and return the unused disk space back to the >> OS/filesystem. But if the sysadmin/DBA (who is ultimately responsible for the >> health of the entire db instance, as well as of any replicas of the db >> instance), >> wants to disable truncation across all databases to ensure that the replication >> does not get stuck, then IMHO Postgres should empower the sysadmin to make >> that decision, and override the relation-level setting enabled by the table- >> owner or the database-owner. > > IIUC reloptions with corresponding GUCs typically override the GUC setting, > although autovacuum_enabled is arguably an exception. If setting the GUC > to false overrides the relation-specific settings, it also becomes more > difficult to enable truncation for just a couple of tables, although that > might not be a popular use-case. Furthermore, even if we do want the GUC > to override the reloption, it won't override VACUUM (TRUNCATE). +1 to having the reloption (if specified) override the GUC setting. That is, I think that autovacuum_vacuum_truncate as defining the default behavior for VACUUM truncation, and that the GUC should only apply when neither the TRUNCATE option in VACUUM nor the reloption is set. Regards, -- Fujii Masao Advanced Computing Technology Center Research and Development Headquarters NTT DATA CORPORATION ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2025-03-05 23:54 UTC | newest] Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2025-02-28 04:29 Re: Disabling vacuum truncate for autovacuum Gurjeet Singh <[email protected]> 2025-02-28 18:21 ` Nathan Bossart <[email protected]> 2025-03-05 23:54 ` Fujii Masao <[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