Subject: Re: Disabling vacuum truncate for autovacuum

From: Nathan Bossart <[email-masked]>
Date: Fri, 14 Mar 2025 10:42:07 -0500

On Thu, Mar 06, 2025 at 08:54:59AM +0900, Fujii Masao wrote:
> +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.

One other difference in my version of the patch [0] is to call this GUC
vacuum_truncate and have it apply to both autovacuum and VACUUM.  I did
this for the following reasons:

* There is no autovacuum-specific storage parameter.  There is only
  vacuum_truncate and toast.vacuum_truncate, both of which apply to
  autovacuum and VACUUM.  Unfortunately, adding autovacuum-specific storage
  parameters at this point would break things for folks who are already
  using vacuum_truncate to prevent autovacuum from truncating.  In any
  case, I gather that we try to ordinarily keep storage parameters named
  the same as their corresponding GUCs.

* I'm not sure whether there's a real need to control the autovacuum
  default but not the VACUUM one.  I'd expect most users of this stuff to
  be worried about truncation in both cases, especially for the hot standby
  use-case mentioned upthread.

I should also mention that we just have a few weeks left in the v18
development cycle.  The code itself seems pretty straightforward, so if we
can agree on behavior and nomenclature, I'll do my darndest to get this
responsibly committed in time.

[0] https://postgr.es/m/attachment/172951/v2-0001-Add-vacuum_truncate-GUC.patch

-- 
nathan

---

From: Laurenz Albe <[email-masked]>
Date: Fri, 14 Mar 2025 22:01:09 +0100

On Fri, 2025-03-14 at 10:42 -0500, Nathan Bossart wrote:
> vacuum_truncate and have it apply to both autovacuum and VACUUM.

I agree that that is the way to go.
It makes no sense to restrict the feature to autovacuum.

Yours,
Laurenz Albe

---

From: Fujii Masao <[email-masked]>
Date: Sat, 15 Mar 2025 22:59:59 +0900

> responsibly committed in time.

+1

Here are two minor review comments from me.

+      <varlistentry id="guc-vacuum-truncate" xreflabel="autovacuum">

This xreflabel should be "vacuum_truncate", not "autovacuum".

-      lock on the table. The <literal>TRUNCATE</literal> parameter
-      of <link linkend="sql-vacuum"><command>VACUUM</command></link>, if specified, overrides the value
-      of this option.
+      Per-table value for <xref linkend="guc-vacuum-truncate"/> parameter.

It was explicitly documented that the TRUNCATE option in the VACUUM
command overrides the vacuum_truncate reloption, but this information
has been removed in the patch. Shouldn't we keep it to clarify
the priority of these settings?

Regards,

-- 
Fujii Masao
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATION

---

From: Gurjeet Singh <[email-masked]>
Date: Sat, 15 Mar 2025 17:14:14 -0700

+Andres Freund, since an old email of his is quoted here.

> this for the following reasons:

+1 for the GUC name for the reasons you identified. But -1 for the behaviour
where the reloption and vacuum command's options overrides GUC.

I did not consider the VACUUM (TRUNCATE) command, and was focused on autovacuum,
since that's what caused the replication stall and the consequent application
outage in the anecdote I was relaying.

I'd like to bring our attention back to how this thread started. Will started
the discussion by asking for a way to disable autovacuum's truncate behaviour.
Even though the production outage he faced was due to manual vacuum, he was
worried about the same behaviour that autovacuum may cause, especially since the
parameter old_snapshot_threshold is no longer available; old_snapshot_threshold
allowed sysadmins like Will to disable the truncation behaviour globally. I
provided an anecdote where autovacuum's truncation behaviour had in fact caused
a replication outage as well as the consequent application outage.

The behaviour that is being proposed here does not prevent that situation from
arising again. A sysadmin who's trying to prevent replication outage and
a consequent application outage won't benefit from tuning vacuum_truncate GUC,
because a reloption or VACUUM (TRUNCATE) command will override its behaviour,
and lead to an outage.

We want this new GUC to give the sysadmin the power to override the per-relation
and per-command settings.

> 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).

I guess what I'm looking for is a global switch that guarantees no relation
truncation will take place on the instance, so that the relevant replication
record is never emitted, and hence will never lead to a blocked replication on
the replica and never cause a consequent outage of applications connected to the
replica(s). That is, as a sysadmin, I need a global variable that overrides and
disables any relation-level and command-level truncation operations. I guess
that's why naming the GUC *disable*_vacuum_truncate made sense to me when I
initially proposed the autovacuum patch, since it was preventing autovacuum's
default truncation behaviour.

The downside of disabling _all_ truncation operations is that database size can
only grow and never shrink, and it may be frustrating for a regular user who's
trying to shrink their table sizes. Even in those situations, a sysadmin may
prefer that none of the tables ever be shrunk in normal operation, rather than
running the risk of causing a replication outage and a consequent application
outage.

In Will's words:
> I expect I would need to monitor
> tables for problematic growth, but that might be better than a surprise
> outage. I suppose the growth could cause an outage too, but I'm thinking it
> would be more controllable.

The sysadmin can schedule a periodic maintenance window where the GUC is changed
for the duration of the maintenance, allowing truncation operations globally,
and then running VACUUM (TRUNCATE), or relying on autovacuum to truncate
relations within that maintenance period.

With the proposed v2 patch behaviour, a regular user is still in control of
truncation behaviour, and hence can cause a replication outage and application
outage on the replicas. Essentially, this patch fails to help the sysadmin, and
leaves them at the mercy of table owners and database owners.

Perhaps the compromise is that that the sysadmin will run a regular script to
check that none of the relations have the reloption set to truncate the
relation, and also communicate to the application developers that they shouldn't
run the VACUUM (TRUNCATE) command. But anyone who has run a moderately large
fleet of Postgres instances would agree that both those things are not
foolproof, and will tend to be ignored or forgotten in the long run. Especially
the VACUUM (TRUNCATE) option is so enticing for the average user that it's near
impossible to prevent them from using it.

In the message linked by Will upthread, Andres says:
> The production issue is that
> autovacuums constantly cancel queries on the standbys despite
> hot_standby_feedback if you have a workload that does frequent
> truncations. If there's no way to configure it in a way that autovacuum
> takes it into account, people will just disable autovacuum and rely
> entirely on manual scripts. That's what already happens - leading to a
> much bigger foot-gun than disabling truncation.  FWIW, people already in
> production use the workaround to configuring snapshot_too_old as that,
> for undocumented reasons, disables trunctations.

The snapshot_too_old error/feature is not available anymore, and add to that the
fact that manual scripts are not effective for transient tables; tables with
short-enough lifetime that they may be truncated before the next run of the
scripts.

I think this exercise is moot if we're not solving the problem of letting
sysadmin disable truncation altogether, and eliminating the possibility of an
outage. By moving forward with v2, we're not addressing the problem that Will
started this conversation with.

It's entirely possible that I might be misunderstanding your proposal, though,
in which case please feel free to illuminate how the proposed v2 patch will help
a sysadmin counter vacuum's and autovacuum's truncation behaviour, despite the
average user unknowingly monkeying with the database availability.

If the GUC is named vacuum_truncate, then as Fujii Masao reasoned, it can be
taken to mean the default value of per-relation and VACUUM options.

So I propose that GUC be named disable_vacuum_truncate (antonym of the name you
proposed), since it _prevents_ the default truncation behaviour of vacuum and
autovacuum. When false, vacuum and autovacuum perform truncation as they do
normally. When true, no truncations are performed, neither by vacuum nor by
autovacuum; they instead emit a NOTICE, notifying that relation truncation was
prevented by the GUC disable_vacuum_truncate. Default value should be false, to
match the current behaviour. The GUC context should be PGC_SIGHUP so that it can
be changed only by the sysadmin, and can be changed without requiring a restart.

Optional: disable_vacuum_truncate=true has no effect if wal_level==minimal,
since this whole debate and feature is to avoid replication outage, and any
downstream effects on replicas, hence irrelevant to systems where replication is
not in use.

> responsibly committed in time.

Thank you for all your help and work on this patch! Unfortunately I haven't been
able to work on it as much as I would've liked to.

Best regards,
Gurjeet
http://Gurje.et

---

From: Laurenz Albe <[email-masked]>
Date: Sun, 16 Mar 2025 06:29:17 +0100

On Sat, 2025-03-15 at 17:14 -0700, Gurjeet Singh wrote:
> replica(s).

Essentially, you are looking for something that reinstates the unintended side
effect of "old_snapshot_threshold" that some people relied on.

I understand your reasoning.
What I am worried about, and why I am against that, is the POLA violation this
constitutes.  I PostgreSQL, there usually are global settings that can be
overridden by per-relation settings.  Doing it differently here would surprise
and confuse many users.

This is not the only way a user can do damage to the system by overriding the
administrator's settings.  Users can override all autovacuum settings and even
disable autovacuum on a table.  I don't think these settings are less dangerous
than VACUUM truncation.

Yours,
Laurenz Albe

---

From: Robert Treat <[email-masked]>
Date: Sun, 16 Mar 2025 18:24:59 -0400

> overridden by per-relation settings.  Doing it differently here would surprise

Agreed... I couldn't help when reading through this thread the same
thought that the normal way we do this is by trying to pick the
sensible default and then giving options to override it on a more
granular level.

> disable autovacuum on a table.  I don't think these settings are less dangerous

Agreed. To the degree I am sympathetic to Gurjeet's concern, it sounds
more like he is trying to solve a socio-technical issue, which I think
is beyond something that we can guarantee help with; ie presuming we
provide a convenient way to disable this generally, if people are
going to go out of their way to do the thing they have been told not
to...

So if the general idea is a guc "vacuum_truncate" which sets a global
default for whether vacuums and autovacuums should do truncation, and
we then have the storage parameter which would override the global
setting. And to be clear, there is also the decision on whether the
VACUUM commands default should default to truncate=on (like the
existing behavior) or truncate == vacuum_truncate guc, unless
explicitly set. I think the latter is probably the right way to go.

As an aside, thinking through a bunch of different scenarios, I think
I would actually be in favor of changing the default behavior to false
(I don't think it buys much for most workloads, and I'd love to see us
move towards defaults that minimize risk), but I suspect that may be a
bridge too far, at least in this release; but maybe down the line...
for now though I'd take an easy way for users to make it the default.

Robert Treat
https://xzilla.net

---

From: Nathan Bossart <[email-masked]>
Date: Mon, 17 Mar 2025 10:14:51 -0500

> explicitly set. I think the latter is probably the right way to go.

Thank you all for the discussion.  I've attempted to address the
outstanding feedback into the new version of the patch.

-- 
nathan
>From 8d367eb19f4adf01410d5f6b96ee36f15fb345ad Mon Sep 17 00:00:00 2001
From: Nathan Bossart <[email-masked]>
Date: Mon, 17 Mar 2025 10:11:56 -0500
Subject: [PATCH v4 1/1] Add vacuum_truncate configuration parameter.

This new parameter works just like the eponymous storage parameter:
if set to true (which is the default), autovacuum and VACUUM
attempt to truncate any empty pages at the end of the table.  The
setting can be overridden for individual tables with the storage
parameter or VACUUM's TRUNCATE option.  It is primarily intended to
help users avoid locking issues on hot standbys.

Since there's presently no way to determine whether a Boolean
storage parameter is explicitly set or has just picked up the
default value, this commit introduces an optional isset_offset
member to relopt_parse_elt.

Suggested-by: Will Storey <[email-masked]>
Author: Nathan Bossart <[email-masked]>
Co-authored-by: Gurjeet Singh <[email-masked]>
Reviewed-by: Laurenz Albe <[email-masked]>
Reviewed-by: Robert Haas <[email-masked]>
Reviewed-by: Fujii Masao <[email-masked]>
Reviewed-by: Robert Treat <[email-masked]>
Discussion: https://postgr.es/m/Z2DE4lDX4tHqNGZt%40dev.null
---
 doc/src/sgml/config.sgml                      | 22 +++++++++++++++++++
 doc/src/sgml/ref/create_table.sgml            | 13 ++++-------
 doc/src/sgml/ref/vacuum.sgml                  |  3 ++-
 src/backend/access/common/reloptions.c        | 14 +++++++++++-
 src/backend/commands/vacuum.c                 | 17 ++++++++++----
 src/backend/utils/misc/guc_tables.c           |  9 ++++++++
 src/backend/utils/misc/postgresql.conf.sample |  4 ++++
 src/include/access/reloptions.h               |  1 +
 src/include/commands/vacuum.h                 |  1 +
 src/include/utils/rel.h                       |  1 +
 10 files changed, 70 insertions(+), 15 deletions(-)

diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index 3d62c8bd274..18c11e284e4 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -9399,6 +9399,28 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv;
       </listitem>
      </varlistentry>
 
+      <varlistentry id="guc-vacuum-truncate" xreflabel="vacuum_truncate">
+       <term><varname>vacuum_truncate</varname> (<type>boolean</type>)
+       <indexterm>
+        <primary><varname>vacuum_truncate</varname> configuration parameter</primary>
+       </indexterm>
+       </term>
+       <listitem>
+        <para>
+         Enables or disables vacuum to try to truncate off any empty pages at
+         the end of the table.  The default value is <literal>true</literal>.
+         If <literal>true</literal>, <command>VACUUM</command> and autovacuum
+         do the truncation and the disk space for the truncated pages is
+         returned to the operating system.  Note that the truncation requires
+         an <literal>ACCESS EXCLUSIVE</literal> lock on the table.  The
+         <literal>TRUNCATE</literal> parameter of
+         <link linkend="sql-vacuum"><command>VACUUM</command></link>, if
+         specified, overrides the value of this parameter.  The setting can be
+         overridden for individual tables by changing table storage parameters.
+        </para>
+       </listitem>
+      </varlistentry>
+
      </variablelist>
     </sect2>
    </sect1>
diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml
index 5304b738322..e5c034d724e 100644
--- a/doc/src/sgml/ref/create_table.sgml
+++ b/doc/src/sgml/ref/create_table.sgml
@@ -1692,15 +1692,10 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
     </term>
     <listitem>
      <para>
-      Enables or disables vacuum to try to truncate off any empty pages
-      at the end of this table. The default value is <literal>true</literal>.
-      If <literal>true</literal>, <command>VACUUM</command> and
-      autovacuum do the truncation and the disk space for
-      the truncated pages is returned to the operating system.
-      Note that the truncation requires <literal>ACCESS EXCLUSIVE</literal>
-      lock on the table. The <literal>TRUNCATE</literal> parameter
-      of <link linkend="sql-vacuum"><command>VACUUM</command></link>, if specified, overrides the value
-      of this option.
+      Per-table value for <xref linkend="guc-vacuum-truncate"/> parameter.  The
+      <literal>TRUNCATE</literal> parameter of
+      <link linkend="sql-vacuum"><command>VACUUM</command></link>, if
+      specified, overrides the value of this option.
      </para>
     </listitem>
    </varlistentry>
diff --git a/doc/src/sgml/ref/vacuum.sgml b/doc/src/sgml/ref/vacuum.sgml
index 971b1237d47..bd5dcaf86a5 100644
--- a/doc/src/sgml/ref/vacuum.sgml
+++ b/doc/src/sgml/ref/vacuum.sgml
@@ -265,7 +265,8 @@ VACUUM [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] [ <re
       truncate off any empty pages at the end of the table and allow
       the disk space for the truncated pages to be returned to
       the operating system. This is normally the desired behavior
-      and is the default unless the <literal>vacuum_truncate</literal>
+      and is the default unless <xref linkend="guc-vacuum-truncate"/>
+      is set to false or the <literal>vacuum_truncate</literal>
       option has been set to false for the table to be vacuumed.
       Setting this option to false may be useful to avoid
       <literal>ACCESS EXCLUSIVE</literal> lock on the table that
diff --git a/src/backend/access/common/reloptions.c b/src/backend/access/common/reloptions.c
index 59fb53e7707..ccbc673b228 100644
--- a/src/backend/access/common/reloptions.c
+++ b/src/backend/access/common/reloptions.c
@@ -1779,6 +1779,17 @@ fillRelOptions(void *rdopts, Size basesize,
 				char	   *itempos = ((char *) rdopts) + elems[j].offset;
 				char	   *string_val;
 
+				/*
+				 * If isset_offset is provided, store whether the reloption is
+				 * set there.
+				 */
+				if (elems[j].isset_offset > 0)
+				{
+					char	   *setpos = ((char *) rdopts) + elems[j].isset_offset;
+
+					*(bool *) setpos = options[i].isset;
+				}
+
 				switch (options[i].gen->type)
 				{
 					case RELOPT_TYPE_BOOL:
@@ -1901,7 +1912,7 @@ default_reloptions(Datum reloptions, bool validate, relopt_kind kind)
 		{"vacuum_index_cleanup", RELOPT_TYPE_ENUM,
 		offsetof(StdRdOptions, vacuum_index_cleanup)},
 		{"vacuum_truncate", RELOPT_TYPE_BOOL,
-		offsetof(StdRdOptions, vacuum_truncate)},
+		offsetof(StdRdOptions, vacuum_truncate), offsetof(StdRdOptions, vacuum_truncate_set)},
 		{"vacuum_max_eager_freeze_failure_rate", RELOPT_TYPE_REAL,
 		offsetof(StdRdOptions, vacuum_max_eager_freeze_failure_rate)}
 	};
@@ -1981,6 +1992,7 @@ build_local_reloptions(local_relopts *relopts, Datum options, bool validate)
 		elems[i].optname = opt->option->name;
 		elems[i].opttype = opt->option->type;
 		elems[i].offset = opt->offset;
+		elems[i].isset_offset = 0;	/* not supported for local reloptions */
 
 		i++;
 	}
diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c
index e81c9a8aba3..f0a7b87808d 100644
--- a/src/backend/commands/vacuum.c
+++ b/src/backend/commands/vacuum.c
@@ -78,6 +78,7 @@ int			vacuum_failsafe_age;
 int			vacuum_multixact_failsafe_age;
 double		vacuum_max_eager_freeze_failure_rate;
 bool		track_cost_delay_timing;
+bool		vacuum_truncate;
 
 /*
  * Variables for cost-based vacuum delay. The defaults differ between
@@ -2198,13 +2199,21 @@ vacuum_rel(Oid relid, RangeVar *relation, VacuumParams *params,
 			((StdRdOptions *) rel->rd_options)->vacuum_max_eager_freeze_failure_rate;
 
 	/*
-	 * Set truncate option based on truncate reloption if it wasn't specified
-	 * in VACUUM command, or when running in an autovacuum worker
+	 * Set truncate option based on truncate reloption or GUC if it wasn't
+	 * specified in VACUUM command, or when running in an autovacuum worker
 	 */
 	if (params->truncate == VACOPTVALUE_UNSPECIFIED)
 	{
-		if (rel->rd_options == NULL ||
-			((StdRdOptions *) rel->rd_options)->vacuum_truncate)
+		StdRdOptions *opts = (StdRdOptions *) rel->rd_options;
+
+		if (opts && opts->vacuum_truncate_set)
+		{
+			if (opts->vacuum_truncate)
+				params->truncate = VACOPTVALUE_ENABLED;
+			else
+				params->truncate = VACOPTVALUE_DISABLED;
+		}
+		else if (vacuum_truncate)
 			params->truncate = VACOPTVALUE_ENABLED;
 		else
 			params->truncate = VACOPTVALUE_DISABLED;
diff --git a/src/backend/utils/misc/guc_tables.c b/src/backend/utils/misc/guc_tables.c
index 9c0b10ad4dc..56dcda9546c 100644
--- a/src/backend/utils/misc/guc_tables.c
+++ b/src/backend/utils/misc/guc_tables.c
@@ -2118,6 +2118,15 @@ struct config_bool ConfigureNamesBool[] =
 		NULL, NULL, NULL
 	},
 
+	{
+		{"vacuum_truncate", PGC_USERSET, VACUUM_AUTOVACUUM,
+			gettext_noop("Enables vacuum to truncate empty pages at the end of the table."),
+		},
+		&vacuum_truncate,
+		true,
+		NULL, NULL, NULL
+	},
+
 	/* End-of-list marker */
 	{
 		{NULL, 0, 0, NULL, NULL}, NULL, false, NULL, NULL, NULL
diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample
index 8de86e0c945..103894f3a55 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -714,6 +714,10 @@ autovacuum_worker_slots = 16	# autovacuum worker slots to allocate
 #vacuum_multixact_failsafe_age = 1600000000
 #vacuum_max_eager_freeze_failure_rate = 0.03 # 0 disables eager scanning
 
+# - Default Behavior -
+
+#vacuum_truncate = on			# enable truncation after vacuum
+
 #------------------------------------------------------------------------------
 # CLIENT CONNECTION DEFAULTS
 #------------------------------------------------------------------------------
diff --git a/src/include/access/reloptions.h b/src/include/access/reloptions.h
index 43445cdcc6c..61e58a5daa3 100644
--- a/src/include/access/reloptions.h
+++ b/src/include/access/reloptions.h
@@ -152,6 +152,7 @@ typedef struct
 	const char *optname;		/* option's name */
 	relopt_type opttype;		/* option's datatype */
 	int			offset;			/* offset of field in result struct */
+	int			isset_offset;	/* if > 0, offset of "set" field */
 } relopt_parse_elt;
 
 /* Local reloption definition */
diff --git a/src/include/commands/vacuum.h b/src/include/commands/vacuum.h
index baacc63f590..bc37a80dc74 100644
--- a/src/include/commands/vacuum.h
+++ b/src/include/commands/vacuum.h
@@ -304,6 +304,7 @@ extern PGDLLIMPORT int vacuum_multixact_freeze_table_age;
 extern PGDLLIMPORT int vacuum_failsafe_age;
 extern PGDLLIMPORT int vacuum_multixact_failsafe_age;
 extern PGDLLIMPORT bool track_cost_delay_timing;
+extern PGDLLIMPORT bool vacuum_truncate;
 
 /*
  * Relevant for vacuums implementing eager scanning. Normal vacuums may
diff --git a/src/include/utils/rel.h b/src/include/utils/rel.h
index db3e504c3d2..d94fddd7cef 100644
--- a/src/include/utils/rel.h
+++ b/src/include/utils/rel.h
@@ -344,6 +344,7 @@ typedef struct StdRdOptions
 	int			parallel_workers;	/* max number of parallel workers */
 	StdRdOptIndexCleanup vacuum_index_cleanup;	/* controls index vacuuming */
 	bool		vacuum_truncate;	/* enables vacuum to truncate a relation */
+	bool		vacuum_truncate_set;	/* whether vacuum_truncate is set */
 
 	/*
 	 * Fraction of pages in a relation that vacuum can eagerly scan and fail
-- 
2.39.5 (Apple Git-154)

---

From: Nathan Bossart <[email-masked]>
Date: Tue, 18 Mar 2025 20:42:45 -0500

> outstanding feedback into the new version of the patch.

Here is a new version of the patch with tests and some other light edits.
I feel like this is committable, but I'll wait for a couple more days for
any other feedback or objections.

-- 
nathan
>From daba49ce70965f657296d8e452f8d73992d54c48 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <[email-masked]>
Date: Tue, 18 Mar 2025 20:23:50 -0500
Subject: [PATCH v5 1/1] Add vacuum_truncate configuration parameter.

This new parameter works just like the eponymous storage parameter:
if set to true (which is the default), autovacuum and VACUUM
attempt to truncate any empty pages at the end of the table.  It is
primarily intended to help users avoid locking issues on hot
standbys.  The setting can be overridden with the storage parameter
or VACUUM's TRUNCATE option.

Since there's presently no way to determine whether a Boolean
storage parameter is explicitly set or has just picked up the
default value, this commit also introduces an isset_offset member
to relopt_parse_elt.

Suggested-by: Will Storey <[email-masked]>
Author: Nathan Bossart <[email-masked]>
Co-authored-by: Gurjeet Singh <[email-masked]>
Reviewed-by: Laurenz Albe <[email-masked]>
Reviewed-by: Fujii Masao <[email-masked]>
Reviewed-by: Robert Treat <[email-masked]>
Discussion: https://postgr.es/m/Z2DE4lDX4tHqNGZt%40dev.null
---
 doc/src/sgml/config.sgml                      | 23 ++++++++++++++++
 doc/src/sgml/ref/create_table.sgml            | 13 +++------
 doc/src/sgml/ref/vacuum.sgml                  |  3 ++-
 src/backend/access/common/reloptions.c        | 14 +++++++++-
 src/backend/commands/vacuum.c                 | 17 +++++++++---
 src/backend/utils/misc/guc_tables.c           |  9 +++++++
 src/backend/utils/misc/postgresql.conf.sample |  4 +++
 src/include/access/reloptions.h               |  1 +
 src/include/commands/vacuum.h                 |  1 +
 src/include/utils/rel.h                       |  1 +
 src/test/regress/expected/vacuum.out          | 27 +++++++++++++++++++
 src/test/regress/sql/vacuum.sql               | 10 +++++++
 12 files changed, 108 insertions(+), 15 deletions(-)

diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index 9e9c02cde83..c5a05a96c12 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -9504,6 +9504,29 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv;
       </listitem>
      </varlistentry>
 
+         specified, overrides the value of this parameter.  The setting can
+         also be overridden for individual tables by changing table storage
+         parameters.
index 59fb53e7707..645b5c00467 100644
 
+				/*
+		elems[i].isset_offset = 0;	/* not supported for local relopts yet */
 
 		i++;
 	}
 
 /*
 
 	/*
index 60a40ed445a..7da11793a1b 100644
@@ -2130,6 +2130,15 @@ struct config_bool ConfigureNamesBool[] =
 		NULL, NULL, NULL
 	},
 
+	{
index beb05a89501..bc4ebbba2ae 100644
@@ -722,6 +722,10 @@ autovacuum_worker_slots = 16	# autovacuum worker slots to allocate
 
+# - Default Behavior -
+
index 43445cdcc6c..146aed47c2d 100644
+	int			isset_offset;	/* if > 0, offset of "is set" field */
 } relopt_parse_elt;

 /*
 
 	/*
 	 * Fraction of pages in a relation that vacuum can eagerly scan and fail
diff --git a/src/test/regress/expected/vacuum.out b/src/test/regress/expected/vacuum.out
index 3f91b69b324..0abcc99989e 100644
--- a/src/test/regress/expected/vacuum.out
+++ b/src/test/regress/expected/vacuum.out
@@ -236,6 +236,7 @@ SELECT pg_relation_size('vac_truncate_test') > 0;
  t
 (1 row)
 
+SET vacuum_truncate = false;
 VACUUM (DISABLE_PAGE_SKIPPING) vac_truncate_test;
 SELECT pg_relation_size('vac_truncate_test') = 0;
  ?column? 
@@ -244,6 +245,32 @@ SELECT pg_relation_size('vac_truncate_test') = 0;
 (1 row)
 
 VACUUM (TRUNCATE FALSE, FULL TRUE) vac_truncate_test;
+ALTER TABLE vac_truncate_test RESET (vacuum_truncate);
+INSERT INTO vac_truncate_test VALUES (1, NULL), (NULL, NULL);
+ERROR:  null value in column "i" of relation "vac_truncate_test" violates not-null constraint
+DETAIL:  Failing row contains (null, null).
+VACUUM (DISABLE_PAGE_SKIPPING) vac_truncate_test;
+SELECT pg_relation_size('vac_truncate_test') > 0;
+ ?column? 
+----------
+ t
+(1 row)
+
+RESET vacuum_truncate;
+VACUUM (TRUNCATE FALSE, DISABLE_PAGE_SKIPPING) vac_truncate_test;
+SELECT pg_relation_size('vac_truncate_test') > 0;
+ ?column? 
+----------
+ t
+(1 row)
+
+VACUUM (DISABLE_PAGE_SKIPPING) vac_truncate_test;
+SELECT pg_relation_size('vac_truncate_test') = 0;
+ ?column? 
+----------
+ t
+(1 row)
+
 DROP TABLE vac_truncate_test;
 -- partitioned table
 CREATE TABLE vacparted (a int, b char) PARTITION BY LIST (a);
diff --git a/src/test/regress/sql/vacuum.sql b/src/test/regress/sql/vacuum.sql
index 058add027f1..a72bdb5b619 100644
--- a/src/test/regress/sql/vacuum.sql
+++ b/src/test/regress/sql/vacuum.sql
@@ -194,9 +194,19 @@ CREATE TEMP TABLE vac_truncate_test(i INT NOT NULL, j text)
 INSERT INTO vac_truncate_test VALUES (1, NULL), (NULL, NULL);
 VACUUM (TRUNCATE FALSE, DISABLE_PAGE_SKIPPING) vac_truncate_test;
 SELECT pg_relation_size('vac_truncate_test') > 0;
+SET vacuum_truncate = false;
 VACUUM (DISABLE_PAGE_SKIPPING) vac_truncate_test;
 SELECT pg_relation_size('vac_truncate_test') = 0;
 VACUUM (TRUNCATE FALSE, FULL TRUE) vac_truncate_test;
+ALTER TABLE vac_truncate_test RESET (vacuum_truncate);
+INSERT INTO vac_truncate_test VALUES (1, NULL), (NULL, NULL);
+VACUUM (DISABLE_PAGE_SKIPPING) vac_truncate_test;
+SELECT pg_relation_size('vac_truncate_test') > 0;
+RESET vacuum_truncate;
+VACUUM (TRUNCATE FALSE, DISABLE_PAGE_SKIPPING) vac_truncate_test;
+SELECT pg_relation_size('vac_truncate_test') > 0;
+VACUUM (DISABLE_PAGE_SKIPPING) vac_truncate_test;
+SELECT pg_relation_size('vac_truncate_test') = 0;
 DROP TABLE vac_truncate_test;
 
 -- partitioned table
-- 
2.39.5 (Apple Git-154)

---

From: Fujii Masao <[email-masked]>
Date: Thu, 20 Mar 2025 00:34:59 +0900

+# - Default Behavior -
+

Since there's no existing GUC category that fits to vacuum_truncate,
I'm fine with adding a new category like "Vacuuming" and placing
vacuum_truncate there.

However, if we do this, ISTM that the new category should also be added to
guc_tables.h, and vacuum_truncate should be assigned to it in guc_tables.c.
Additionally, the documentation should be updated to include
the new category, with vacuum_truncate placed under it. Thought?

Regards,

---

From: Nathan Bossart <[email-masked]>
Date: Wed, 19 Mar 2025 10:40:31 -0500

> the new category, with vacuum_truncate placed under it. Thought?

Ah, you're right, I need to fix the category.  Will post an updated patch
soon.

-- 
nathan

---

From: Nathan Bossart <[email-masked]>
Date: Wed, 19 Mar 2025 11:09:21 -0500

> Ah, you're right, I need to fix the category.  Will post an updated patch

Here's a new version of the patch with the GUC placed in a new "Default
Behavior" category for vacuum-related parameters.

-- 
nathan
>From 28508114e193a3c9be6aca697c390b9bf790d0c8 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <[email-masked]>
Date: Tue, 18 Mar 2025 20:23:50 -0500
Subject: [PATCH v6 1/1] Add vacuum_truncate configuration parameter.

Suggested-by: Will Storey <[email-masked]>
Author: Nathan Bossart <[email-masked]>
Co-authored-by: Gurjeet Singh <[email-masked]>
Reviewed-by: Laurenz Albe <[email-masked]>
Reviewed-by: Fujii Masao <[email-masked]>
Reviewed-by: Robert Treat <[email-masked]>
Discussion: https://postgr.es/m/Z2DE4lDX4tHqNGZt%40dev.null
---
 doc/src/sgml/config.sgml                      | 29 +++++++++++++++++++
 doc/src/sgml/ref/create_table.sgml            | 13 +++------
 doc/src/sgml/ref/vacuum.sgml                  |  3 +-
 src/backend/access/common/reloptions.c        | 14 ++++++++-
 src/backend/commands/vacuum.c                 | 17 ++++++++---
 src/backend/utils/misc/guc_tables.c           | 10 +++++++
 src/backend/utils/misc/postgresql.conf.sample |  4 +++
 src/include/access/reloptions.h               |  1 +
 src/include/commands/vacuum.h                 |  1 +
 src/include/utils/guc_tables.h                |  1 +
 src/include/utils/rel.h                       |  1 +
 src/test/regress/expected/vacuum.out          | 27 +++++++++++++++++
 src/test/regress/sql/vacuum.sql               | 10 +++++++
 13 files changed, 116 insertions(+), 15 deletions(-)

diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index 873290daa61..bdcefa8140b 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -9311,6 +9311,35 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv;
      </note>
     </sect2>
 
+    <sect2 id="runtime-config-vacuum-default">
+     <title>Default Behavior</title>
+
+     <variablelist>
+     </variablelist>
+    </sect2>
+
     <sect2 id="runtime-config-vacuum-freezing">
      <title>Freezing</title>

+				/*
 
 		i++;
 	}
 
 /*
 
 	/*
-	 * Set truncate option based on truncate reloption if it wasn't specified
-	 * in VACUUM command, or when running in an autovacuum worker
+	 * Set truncate option based on truncate reloption or GUC if it wasn't
+	 * specified in VACUUM command, or when running in an autovacuum worker
 	 */
 	if (params->truncate == VACOPTVALUE_UNSPECIFIED)
 	{
-		if (rel->rd_options == NULL ||
-			((StdRdOptions *) rel->rd_options)->vacuum_truncate)
+		StdRdOptions *opts = (StdRdOptions *) rel->rd_options;
+
+		if (opts && opts->vacuum_truncate_set)
+		{
+			if (opts->vacuum_truncate)
+				params->truncate = VACOPTVALUE_ENABLED;
+			else
+				params->truncate = VACOPTVALUE_DISABLED;
+		}
+		else if (vacuum_truncate)
 			params->truncate = VACOPTVALUE_ENABLED;
 		else
 			params->truncate = VACOPTVALUE_DISABLED;
diff --git a/src/backend/utils/misc/guc_tables.c b/src/backend/utils/misc/guc_tables.c
index cc8f2b1230a..97cfd6e5a82 100644
--- a/src/backend/utils/misc/guc_tables.c
+++ b/src/backend/utils/misc/guc_tables.c
@@ -712,6 +712,7 @@ const char *const config_group_names[] =
 	[STATS_CUMULATIVE] = gettext_noop("Statistics / Cumulative Query and Index Statistics"),
 	[VACUUM_AUTOVACUUM] = gettext_noop("Vacuuming / Automatic Vacuuming"),
 	[VACUUM_COST_DELAY] = gettext_noop("Vacuuming / Cost-Based Vacuum Delay"),
+	[VACUUM_DEFAULT] = gettext_noop("Vacuuming / Default Behavior"),
 	[VACUUM_FREEZING] = gettext_noop("Vacuuming / Freezing"),
 	[CLIENT_CONN_STATEMENT] = gettext_noop("Client Connection Defaults / Statement Behavior"),
 	[CLIENT_CONN_LOCALE] = gettext_noop("Client Connection Defaults / Locale and Formatting"),
@@ -2131,6 +2132,15 @@ struct config_bool ConfigureNamesBool[] =
 		NULL, NULL, NULL
 	},
 
+	{
+		{"vacuum_truncate", PGC_USERSET, VACUUM_DEFAULT,
+			gettext_noop("Enables vacuum to truncate empty pages at the end of the table."),
+		},
+		&vacuum_truncate,
+		true,
+		NULL, NULL, NULL
+	},
+
 	/* End-of-list marker */
 	{
 		{NULL, 0, 0, NULL, NULL}, NULL, false, NULL, NULL, NULL
diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample
index ad54585cf1d..9f31e4071c7 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -714,6 +714,10 @@ autovacuum_worker_slots = 16	# autovacuum worker slots to allocate
 #vacuum_cost_page_dirty = 20		# 0-10000 credits
 #vacuum_cost_limit = 200		# 1-10000 credits
 
+# - Default Behavior -
+
 # - Freezing -
 
 #vacuum_freeze_table_age = 150000000

 /*
  * Relevant for vacuums implementing eager scanning. Normal vacuums may
diff --git a/src/include/utils/guc_tables.h b/src/include/utils/guc_tables.h
index ab47145ec36..f72ce944d7f 100644
--- a/src/include/utils/guc_tables.h
+++ b/src/include/utils/guc_tables.h
@@ -89,6 +89,7 @@ enum config_group
 	STATS_CUMULATIVE,
 	VACUUM_AUTOVACUUM,
 	VACUUM_COST_DELAY,
+	VACUUM_DEFAULT,
 	VACUUM_FREEZING,
 	CLIENT_CONN_STATEMENT,
 	CLIENT_CONN_LOCALE,
diff --git a/src/include/utils/rel.h b/src/include/utils/rel.h
index db3e504c3d2..d94fddd7cef 100644
--- a/src/include/utils/rel.h
+++ b/src/include/utils/rel.h
@@ -344,6 +344,7 @@ typedef struct StdRdOptions
 	int			parallel_workers;	/* max number of parallel workers */
 	StdRdOptIndexCleanup vacuum_index_cleanup;	/* controls index vacuuming */
 	bool		vacuum_truncate;	/* enables vacuum to truncate a relation */
+	bool		vacuum_truncate_set;	/* whether vacuum_truncate is set */
 
 	/*

 
 -- partitioned table
-- 
2.39.5 (Apple Git-154)

---

From: Nathan Bossart <[email-masked]>
Date: Thu, 20 Mar 2025 10:18:16 -0500

Committed.

-- 
nathan

---

From: "David G. Johnston" <[email-masked]>
Date: Thu, 20 Mar 2025 09:59:45 -0700

> Committed.
We added isset_offset to the public struct to introduce this new general
behavior of tracking whether any table reloption has been set (not just
vacuum_truncate) without any comments.

I get the need for this kind of logic, since we used a boolean for the
table option, but as a self-proclaimed hack it seems worth more comments
than provided here.  Especially pertaining to whether this is indeed
generic or vacuum_truncate specific.  It's unclear since both isset and
vacuum_truncate_set have been introduced.  If it is now a general property
applying to any setting then vacuum_truncate_set shouldn't be needed - we
should just get the isset value of the existing vacuum_truncate reloption
by name.

David J.