public inbox for [email protected]
help / color / mirror / Atom feedRe: Disabling vacuum truncate for autovacuum
13+ messages / 6 participants
[nested] [flat]
* Re: Disabling vacuum truncate for autovacuum
@ 2025-03-14 15:42 Nathan Bossart <[email protected]>
0 siblings, 3 replies; 13+ messages in thread
From: Nathan Bossart @ 2025-03-14 15:42 UTC (permalink / raw)
To: Fujii Masao <[email protected]>; +Cc: Gurjeet Singh <[email protected]>; Robert Haas <[email protected]>; Laurenz Albe <[email protected]>; Postgres Hackers <[email protected]>; Will Storey <[email protected]>
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Disabling vacuum truncate for autovacuum
@ 2025-03-14 21:01 Laurenz Albe <[email protected]>
parent: Nathan Bossart <[email protected]>
2 siblings, 0 replies; 13+ messages in thread
From: Laurenz Albe @ 2025-03-14 21:01 UTC (permalink / raw)
To: Nathan Bossart <[email protected]>; Fujii Masao <[email protected]>; +Cc: Gurjeet Singh <[email protected]>; Robert Haas <[email protected]>; Postgres Hackers <[email protected]>; Will Storey <[email protected]>
On Fri, 2025-03-14 at 10:42 -0500, Nathan Bossart wrote:
> 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 agree that that is the way to go.
It makes no sense to restrict the feature to autovacuum.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Disabling vacuum truncate for autovacuum
@ 2025-03-15 13:59 Fujii Masao <[email protected]>
parent: Nathan Bossart <[email protected]>
2 siblings, 0 replies; 13+ messages in thread
From: Fujii Masao @ 2025-03-15 13:59 UTC (permalink / raw)
To: Nathan Bossart <[email protected]>; +Cc: Gurjeet Singh <[email protected]>; Robert Haas <[email protected]>; Laurenz Albe <[email protected]>; Postgres Hackers <[email protected]>; Will Storey <[email protected]>
On 2025/03/15 0:42, Nathan Bossart wrote:
> 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.
+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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Disabling vacuum truncate for autovacuum
@ 2025-03-16 00:14 Gurjeet Singh <[email protected]>
parent: Nathan Bossart <[email protected]>
2 siblings, 1 reply; 13+ messages in thread
From: Gurjeet Singh @ 2025-03-16 00:14 UTC (permalink / raw)
To: Nathan Bossart <[email protected]>; Andres Freund <[email protected]>; Will Storey <[email protected]>; +Cc: Fujii Masao <[email protected]>; Robert Haas <[email protected]>; Laurenz Albe <[email protected]>; Postgres Hackers <[email protected]>
+Andres Freund, since an old email of his is quoted here.
On Fri, Mar 14, 2025 at 8:45 AM Nathan Bossart <[email protected]> wrote:
>
> 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:
+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.
> 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.
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Disabling vacuum truncate for autovacuum
@ 2025-03-16 05:29 Laurenz Albe <[email protected]>
parent: Gurjeet Singh <[email protected]>
0 siblings, 1 reply; 13+ messages in thread
From: Laurenz Albe @ 2025-03-16 05:29 UTC (permalink / raw)
To: Gurjeet Singh <[email protected]>; Nathan Bossart <[email protected]>; Andres Freund <[email protected]>; Will Storey <[email protected]>; +Cc: Fujii Masao <[email protected]>; Robert Haas <[email protected]>; Postgres Hackers <[email protected]>
On Sat, 2025-03-15 at 17:14 -0700, Gurjeet Singh wrote:
> > 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:
>
> +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'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.
>
>
> 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).
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Disabling vacuum truncate for autovacuum
@ 2025-03-16 22:24 Robert Treat <[email protected]>
parent: Laurenz Albe <[email protected]>
0 siblings, 1 reply; 13+ messages in thread
From: Robert Treat @ 2025-03-16 22:24 UTC (permalink / raw)
To: Laurenz Albe <[email protected]>; +Cc: Gurjeet Singh <[email protected]>; Nathan Bossart <[email protected]>; Andres Freund <[email protected]>; Will Storey <[email protected]>; Fujii Masao <[email protected]>; Robert Haas <[email protected]>; Postgres Hackers <[email protected]>
On Sun, Mar 16, 2025 at 1:29 AM Laurenz Albe <[email protected]> wrote:
>
> On Sat, 2025-03-15 at 17:14 -0700, Gurjeet Singh wrote:
> > > 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:
> >
> > +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'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.
> >
> >
> > 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).
>
> 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.
>
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.
> 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.
>
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Disabling vacuum truncate for autovacuum
@ 2025-03-17 15:14 Nathan Bossart <[email protected]>
parent: Robert Treat <[email protected]>
0 siblings, 1 reply; 13+ messages in thread
From: Nathan Bossart @ 2025-03-17 15:14 UTC (permalink / raw)
To: Robert Treat <[email protected]>; +Cc: Laurenz Albe <[email protected]>; Gurjeet Singh <[email protected]>; Andres Freund <[email protected]>; Will Storey <[email protected]>; Fujii Masao <[email protected]>; Robert Haas <[email protected]>; Postgres Hackers <[email protected]>
On Sun, Mar 16, 2025 at 06:24:59PM -0400, Robert Treat wrote:
> 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.
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 protected]>
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 protected]>
Author: Nathan Bossart <[email protected]>
Co-authored-by: Gurjeet Singh <[email protected]>
Reviewed-by: Laurenz Albe <[email protected]>
Reviewed-by: Robert Haas <[email protected]>
Reviewed-by: Fujii Masao <[email protected]>
Reviewed-by: Robert Treat <[email protected]>
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)
Attachments:
[text/plain] v4-0001-Add-vacuum_truncate-configuration-parameter.patch (11.1K, 2-v4-0001-Add-vacuum_truncate-configuration-parameter.patch)
download | inline diff:
From 8d367eb19f4adf01410d5f6b96ee36f15fb345ad Mon Sep 17 00:00:00 2001
From: Nathan Bossart <[email protected]>
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 protected]>
Author: Nathan Bossart <[email protected]>
Co-authored-by: Gurjeet Singh <[email protected]>
Reviewed-by: Laurenz Albe <[email protected]>
Reviewed-by: Robert Haas <[email protected]>
Reviewed-by: Fujii Masao <[email protected]>
Reviewed-by: Robert Treat <[email protected]>
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)
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Disabling vacuum truncate for autovacuum
@ 2025-03-19 01:42 Nathan Bossart <[email protected]>
parent: Nathan Bossart <[email protected]>
0 siblings, 1 reply; 13+ messages in thread
From: Nathan Bossart @ 2025-03-19 01:42 UTC (permalink / raw)
To: Robert Treat <[email protected]>; +Cc: Laurenz Albe <[email protected]>; Gurjeet Singh <[email protected]>; Andres Freund <[email protected]>; Will Storey <[email protected]>; Fujii Masao <[email protected]>; Robert Haas <[email protected]>; Postgres Hackers <[email protected]>
On Mon, Mar 17, 2025 at 10:14:51AM -0500, Nathan Bossart wrote:
> Thank you all for the discussion. I've attempted to address the
> 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 protected]>
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 protected]>
Author: Nathan Bossart <[email protected]>
Co-authored-by: Gurjeet Singh <[email protected]>
Reviewed-by: Laurenz Albe <[email protected]>
Reviewed-by: Fujii Masao <[email protected]>
Reviewed-by: Robert Treat <[email protected]>
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>
+ <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
+ also 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..645b5c00467 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 relopts yet */
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 60a40ed445a..7da11793a1b 100644
--- a/src/backend/utils/misc/guc_tables.c
+++ b/src/backend/utils/misc/guc_tables.c
@@ -2130,6 +2130,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 beb05a89501..bc4ebbba2ae 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -722,6 +722,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..146aed47c2d 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 "is 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
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)
Attachments:
[text/plain] v5-0001-Add-vacuum_truncate-configuration-parameter.patch (13.7K, 2-v5-0001-Add-vacuum_truncate-configuration-parameter.patch)
download | inline diff:
From daba49ce70965f657296d8e452f8d73992d54c48 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <[email protected]>
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 protected]>
Author: Nathan Bossart <[email protected]>
Co-authored-by: Gurjeet Singh <[email protected]>
Reviewed-by: Laurenz Albe <[email protected]>
Reviewed-by: Fujii Masao <[email protected]>
Reviewed-by: Robert Treat <[email protected]>
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>
+ <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
+ also 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..645b5c00467 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 relopts yet */
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 60a40ed445a..7da11793a1b 100644
--- a/src/backend/utils/misc/guc_tables.c
+++ b/src/backend/utils/misc/guc_tables.c
@@ -2130,6 +2130,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 beb05a89501..bc4ebbba2ae 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -722,6 +722,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..146aed47c2d 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 "is 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
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)
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Disabling vacuum truncate for autovacuum
@ 2025-03-19 15:34 Fujii Masao <[email protected]>
parent: Nathan Bossart <[email protected]>
0 siblings, 1 reply; 13+ messages in thread
From: Fujii Masao @ 2025-03-19 15:34 UTC (permalink / raw)
To: Nathan Bossart <[email protected]>; Robert Treat <[email protected]>; +Cc: Laurenz Albe <[email protected]>; Gurjeet Singh <[email protected]>; Andres Freund <[email protected]>; Will Storey <[email protected]>; Robert Haas <[email protected]>; Postgres Hackers <[email protected]>
On 2025/03/19 10:42, Nathan Bossart wrote:
> On Mon, Mar 17, 2025 at 10:14:51AM -0500, Nathan Bossart wrote:
>> Thank you all for the discussion. I've attempted to address the
>> 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.
+# - Default Behavior -
+
+#vacuum_truncate = on # enable truncation after vacuum
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,
--
Fujii Masao
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATION
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Disabling vacuum truncate for autovacuum
@ 2025-03-19 15:40 Nathan Bossart <[email protected]>
parent: Fujii Masao <[email protected]>
0 siblings, 1 reply; 13+ messages in thread
From: Nathan Bossart @ 2025-03-19 15:40 UTC (permalink / raw)
To: Fujii Masao <[email protected]>; +Cc: Robert Treat <[email protected]>; Laurenz Albe <[email protected]>; Gurjeet Singh <[email protected]>; Andres Freund <[email protected]>; Will Storey <[email protected]>; Robert Haas <[email protected]>; Postgres Hackers <[email protected]>
On Thu, Mar 20, 2025 at 12:34:59AM +0900, Fujii Masao wrote:
> +# - Default Behavior -
> +
> +#vacuum_truncate = on # enable truncation after vacuum
>
> 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?
Ah, you're right, I need to fix the category. Will post an updated patch
soon.
--
nathan
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Disabling vacuum truncate for autovacuum
@ 2025-03-19 16:09 Nathan Bossart <[email protected]>
parent: Nathan Bossart <[email protected]>
0 siblings, 1 reply; 13+ messages in thread
From: Nathan Bossart @ 2025-03-19 16:09 UTC (permalink / raw)
To: Fujii Masao <[email protected]>; +Cc: Robert Treat <[email protected]>; Laurenz Albe <[email protected]>; Gurjeet Singh <[email protected]>; Andres Freund <[email protected]>; Will Storey <[email protected]>; Robert Haas <[email protected]>; Postgres Hackers <[email protected]>
On Wed, Mar 19, 2025 at 10:40:31AM -0500, Nathan Bossart wrote:
> On Thu, Mar 20, 2025 at 12:34:59AM +0900, Fujii Masao wrote:
>> +# - Default Behavior -
>> +
>> +#vacuum_truncate = on # enable truncation after vacuum
>>
>> 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?
>
> Ah, you're right, I need to fix the category. Will post an updated patch
> soon.
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 protected]>
Date: Tue, 18 Mar 2025 20:23:50 -0500
Subject: [PATCH v6 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 protected]>
Author: Nathan Bossart <[email protected]>
Co-authored-by: Gurjeet Singh <[email protected]>
Reviewed-by: Laurenz Albe <[email protected]>
Reviewed-by: Fujii Masao <[email protected]>
Reviewed-by: Robert Treat <[email protected]>
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>
+ <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
+ also be overridden for individual tables by changing table storage
+ parameters.
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+ </sect2>
+
<sect2 id="runtime-config-vacuum-freezing">
<title>Freezing</title>
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..645b5c00467 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 relopts yet */
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 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 -
+
+#vacuum_truncate = on # enable truncation after vacuum
+
# - Freezing -
#vacuum_freeze_table_age = 150000000
diff --git a/src/include/access/reloptions.h b/src/include/access/reloptions.h
index 43445cdcc6c..146aed47c2d 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 "is 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/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 */
/*
* 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)
Attachments:
[text/plain] v6-0001-Add-vacuum_truncate-configuration-parameter.patch (14.7K, 2-v6-0001-Add-vacuum_truncate-configuration-parameter.patch)
download | inline diff:
From 28508114e193a3c9be6aca697c390b9bf790d0c8 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <[email protected]>
Date: Tue, 18 Mar 2025 20:23:50 -0500
Subject: [PATCH v6 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 protected]>
Author: Nathan Bossart <[email protected]>
Co-authored-by: Gurjeet Singh <[email protected]>
Reviewed-by: Laurenz Albe <[email protected]>
Reviewed-by: Fujii Masao <[email protected]>
Reviewed-by: Robert Treat <[email protected]>
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>
+ <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
+ also be overridden for individual tables by changing table storage
+ parameters.
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+ </sect2>
+
<sect2 id="runtime-config-vacuum-freezing">
<title>Freezing</title>
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..645b5c00467 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 relopts yet */
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 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 -
+
+#vacuum_truncate = on # enable truncation after vacuum
+
# - Freezing -
#vacuum_freeze_table_age = 150000000
diff --git a/src/include/access/reloptions.h b/src/include/access/reloptions.h
index 43445cdcc6c..146aed47c2d 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 "is 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/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 */
/*
* 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)
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Disabling vacuum truncate for autovacuum
@ 2025-03-20 15:18 Nathan Bossart <[email protected]>
parent: Nathan Bossart <[email protected]>
0 siblings, 1 reply; 13+ messages in thread
From: Nathan Bossart @ 2025-03-20 15:18 UTC (permalink / raw)
To: Fujii Masao <[email protected]>; +Cc: Robert Treat <[email protected]>; Laurenz Albe <[email protected]>; Gurjeet Singh <[email protected]>; Andres Freund <[email protected]>; Will Storey <[email protected]>; Robert Haas <[email protected]>; Postgres Hackers <[email protected]>
Committed.
--
nathan
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Disabling vacuum truncate for autovacuum
@ 2025-03-20 16:59 David G. Johnston <[email protected]>
parent: Nathan Bossart <[email protected]>
0 siblings, 0 replies; 13+ messages in thread
From: David G. Johnston @ 2025-03-20 16:59 UTC (permalink / raw)
To: Nathan Bossart <[email protected]>; +Cc: Fujii Masao <[email protected]>; Robert Treat <[email protected]>; Laurenz Albe <[email protected]>; Gurjeet Singh <[email protected]>; Andres Freund <[email protected]>; Will Storey <[email protected]>; Robert Haas <[email protected]>; Postgres Hackers <[email protected]>
On Thu, Mar 20, 2025 at 8:18 AM Nathan Bossart <[email protected]>
wrote:
> 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.
^ permalink raw reply [nested|flat] 13+ messages in thread
end of thread, other threads:[~2025-03-20 16:59 UTC | newest]
Thread overview: 13+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-03-14 15:42 Re: Disabling vacuum truncate for autovacuum Nathan Bossart <[email protected]>
2025-03-14 21:01 ` Laurenz Albe <[email protected]>
2025-03-15 13:59 ` Fujii Masao <[email protected]>
2025-03-16 00:14 ` Gurjeet Singh <[email protected]>
2025-03-16 05:29 ` Laurenz Albe <[email protected]>
2025-03-16 22:24 ` Robert Treat <[email protected]>
2025-03-17 15:14 ` Nathan Bossart <[email protected]>
2025-03-19 01:42 ` Nathan Bossart <[email protected]>
2025-03-19 15:34 ` Fujii Masao <[email protected]>
2025-03-19 15:40 ` Nathan Bossart <[email protected]>
2025-03-19 16:09 ` Nathan Bossart <[email protected]>
2025-03-20 15:18 ` Nathan Bossart <[email protected]>
2025-03-20 16:59 ` David G. Johnston <[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