Thread: Re: Disabling vacuum truncate for autovacuum
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. My suggestion for the parameter name is "autovacuum_disable_truncate". > One additional improvement I can think of is to emit a WARNING or NOTICE message > that truncate operation is being skipped, perhaps only if the truncation > would've freed up space over a certain threshold. Interesting idea, but I think it is independent from this patch. > 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. Eventually, the patch should have documentation and regression tests. Yours, Laurenz Albe
On Mon, Jan 27, 2025 at 4:55 AM Laurenz Albe <laurenz.albe@cybertec.at> 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. 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. -- Robert Haas EDB: http://www.enterprisedb.com
On Mon, Jan 27, 2025 at 03:38:39PM -0500, Robert Haas wrote: > 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. My first reaction is that a global setting is probably fine most of the time. I'm sure it's possible to get into bad situations if you try hard enough, but that's not a unique characteristic. There are probably many situations where the truncation is wasted effort because we'll just end up extending the relation shortly afterwards, anyway. 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. Of course, ideally we'd "fix" truncation on standbys, but that's at least v19 work at this point, and past discussion from many years ago [0] leads me to believe it's a difficult problem. That's not to say we should shy away from difficult problems... [0] https://postgr.es/m/flat/CAHGQGwE5UqFqSq1%3DkV3QtTUtXphTdyHA-8rAj4A%3DY%2Be4kyp3BQ%40mail.gmail.com -- nathan
On Tue, Feb 18, 2025 at 01:56:09PM -0600, Nathan Bossart wrote: > On Mon, Jan 27, 2025 at 03:38:39PM -0500, Robert Haas wrote: >> 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. > > My first reaction is that a global setting is probably fine most of the > time. I'm sure it's possible to get into bad situations if you try hard > enough, but that's not a unique characteristic. There are probably many > situations where the truncation is wasted effort because we'll just end up > extending the relation shortly afterwards, anyway. 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. I spent some time on this one today. A couple of notes: * Since the reloption is a Boolean, there isn't a good way to tell whether it is actually set for the table or if it just inherited the default value. This is important to know because we want the reloption to override the GUC. I considered a bunch of different ways to handle this, but everything felt like a cowboy hack. The cleanest cowboy hack I could come up with is an optional offset field in relopt_parse_elt that points to a variable that stores whether the option was explicitly set. * I didn't see a good GUC category for vacuum_truncate. I suppose we could create a new one, but for now I've just stashed it into the autovacuum one. Bikeshedding welcome. Thoughts? -- nathan
Attachment
On Mon, Jan 27, 2025 at 1:55 AM Laurenz Albe <laurenz.albe@cybertec.at> 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 <robertmhaas@gmail.com> wrote:
>
> On Mon, Jan 27, 2025 at 4:55 AM Laurenz Albe <laurenz.albe@cybertec.at> 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
<nathandbossart@gmail.com> 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
Attachment
On Thu, 2025-02-27 at 21:35 -0600, Nathan Bossart wrote: > I spent some time on this one today. A couple of notes: > > * Since the reloption is a Boolean, there isn't a good way to tell whether > it is actually set for the table or if it just inherited the default > value. This is important to know because we want the reloption to > override the GUC. I agree with that, without being able to think of a better solution. > * I didn't see a good GUC category for vacuum_truncate. I suppose we could > create a new one, but for now I've just stashed it into the autovacuum > one. Bikeshedding welcome. Why not use "Client Connection Defaults / Statement Behavior", just like for "vacuum_freeze_min_age"? I don't think that "autovacuum" is appropriate, since it applies to manual VACUUM as well. Yours, Laurenz Albe
On Thu, Feb 27, 2025 at 08:29:16PM -0800, Gurjeet Singh wrote: > On Mon, Jan 27, 2025 at 1:55 AM Laurenz Albe <laurenz.albe@cybertec.at> wrote: >> I hope it is possible to override the global setting with the "vacuum_truncate" >> option on an individual table. > > Current patch behaviour is that if the autovacuum_vacuum_truncate is false, then > autovacuum will _not_ truncate any relations. If the parameter's value is true > (the default), then the relation's reloption will be honored. > > A table-owner, or the database-owner, may enable truncation of a table, as they > may be trying to be nice and return the unused disk space back to the > OS/filesystem. But if the sysadmin/DBA (who is ultimately responsible for the > health of the entire db instance, as well as of any replicas of the db > instance), > wants to disable truncation across all databases to ensure that the replication > does not get stuck, then IMHO Postgres should empower the sysadmin to make > that decision, and override the relation-level setting enabled by the table- > owner or the database-owner. IIUC reloptions with corresponding GUCs typically override the GUC setting, although autovacuum_enabled is arguably an exception. If setting the GUC to false overrides the relation-specific settings, it also becomes more difficult to enable truncation for just a couple of tables, although that might not be a popular use-case. Furthermore, even if we do want the GUC to override the reloption, it won't override VACUUM (TRUNCATE). >> > One additional improvement I can think of is to emit a WARNING or NOTICE message >> > that truncate operation is being skipped, perhaps only if the truncation >> > would've freed up space over a certain threshold. >> >> Interesting idea, but I think it is independent from this patch. > > Agreed. I'll consider writing a separate patch for this. Perhaps it would be useful to say whether truncation was attempted in the output of VACUUM (VERBOSE) and the autovacuum logs. >> > Perhaps there's value in letting this parameter be specified at database level, >> > but I'm not able to think of a reason why someone would want to disable this >> > behaviour on just one database. So leaving the parameter context to be the same >> > as most other autovacuum parameters: SIGHUP. >> >> I can imagine setting that on only a certain database. Different databases >> typically have different applications, which have different needs. > > Makes sense. I don't think anything special needs to be done in the patch to > address this. Hm. I was thinking PGC_USERSET might make sense for this one, but that was only because I didn't see any technical reason to restrict it. I don't know whether limiting it accomplishes anything beyond making it more cumbersome for users to choose their desired default truncation setting. > PS: Nathan, your latest email arrived as I was preparing this email and patch, > so this email and patch does not address concerns, if any, in your latest email. > I will try to respond to it soon. Oops, sorry for the conflict. I'm happy to take a step back and be the reviewer/committer for this one. -- nathan
On 2025/03/01 3:21, Nathan Bossart wrote: > On Thu, Feb 27, 2025 at 08:29:16PM -0800, Gurjeet Singh wrote: >> On Mon, Jan 27, 2025 at 1:55 AM Laurenz Albe <laurenz.albe@cybertec.at> wrote: >>> I hope it is possible to override the global setting with the "vacuum_truncate" >>> option on an individual table. >> >> Current patch behaviour is that if the autovacuum_vacuum_truncate is false, then >> autovacuum will _not_ truncate any relations. If the parameter's value is true >> (the default), then the relation's reloption will be honored. >> >> A table-owner, or the database-owner, may enable truncation of a table, as they >> may be trying to be nice and return the unused disk space back to the >> OS/filesystem. But if the sysadmin/DBA (who is ultimately responsible for the >> health of the entire db instance, as well as of any replicas of the db >> instance), >> wants to disable truncation across all databases to ensure that the replication >> does not get stuck, then IMHO Postgres should empower the sysadmin to make >> that decision, and override the relation-level setting enabled by the table- >> owner or the database-owner. > > IIUC reloptions with corresponding GUCs typically override the GUC setting, > although autovacuum_enabled is arguably an exception. If setting the GUC > to false overrides the relation-specific settings, it also becomes more > difficult to enable truncation for just a couple of tables, although that > might not be a popular use-case. Furthermore, even if we do want the GUC > to override the reloption, it won't override VACUUM (TRUNCATE). +1 to having the reloption (if specified) override the GUC setting. That is, I think that autovacuum_vacuum_truncate as defining the default behavior for VACUUM truncation, and that the GUC should only apply when neither the TRUNCATE option in VACUUM nor the reloption is set. Regards, -- Fujii Masao Advanced Computing Technology Center Research and Development Headquarters NTT DATA CORPORATION