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