public inbox for [email protected]help / color / mirror / Atom feed
Disabling vacuum truncate for autovacuum 6+ messages / 4 participants [nested] [flat]
* Disabling vacuum truncate for autovacuum @ 2024-12-17 00:25 Will Storey <[email protected]> 2024-12-17 07:30 ` Re: Disabling vacuum truncate for autovacuum Laurenz Albe <[email protected]> 0 siblings, 1 reply; 6+ messages in thread From: Will Storey @ 2024-12-17 00:25 UTC (permalink / raw) To: [email protected] Hi! I would like to disable vacuum's truncate behaviour for autovacuum. Previously I had an outage due to its access exclusive lock when it was replicated to a hot standby. When that outage happened it was from a VACUUM call in a cronjob rather than autovacuum. I now run such VACUUMs with TRUNCATE false which avoids the issue for these. However I've realized that autovacuum could cause this as well. This is of interest to me because I'm looking at tuning autovacuum and getting rid of the cronjob, but I've realized relying on autovacuum could be dangerous because of the truncates. I believe the only way to disable this for autovacuum is by changing the vacuum_truncate storage parameters on tables. (Ignoring the now removed old_snapshot_threshold option). I'm thinking of altering all my tables to turn it off. Is this a horrible idea? 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. Would I need to disable the settings on catalog tables too? (To rule out any possibility of it happening). Are there any other things I might be missing? I am also wondering if having an autovacuum setting to control it would be a good idea for a feature. That would be simpler for me than altering all my tables and help me avoid missing any (e.g. catalogs, new tables). I might be worrying needlessly about this as maybe it is unlikely to happen. I suppose it is workload dependent. Thank you! Will ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Disabling vacuum truncate for autovacuum 2024-12-17 00:25 Disabling vacuum truncate for autovacuum Will Storey <[email protected]> @ 2024-12-17 07:30 ` Laurenz Albe <[email protected]> 2024-12-17 18:03 ` Re: Disabling vacuum truncate for autovacuum Will Storey <[email protected]> 2025-01-24 06:33 ` Re: Disabling vacuum truncate for autovacuum Gurjeet Singh <[email protected]> 0 siblings, 2 replies; 6+ messages in thread From: Laurenz Albe @ 2024-12-17 07:30 UTC (permalink / raw) To: Will Storey <[email protected]>; [email protected] On Mon, 2024-12-16 at 16:25 -0800, Will Storey wrote: > I would like to disable vacuum's truncate behaviour for autovacuum. > Previously I had an outage due to its access exclusive lock when it was > replicated to a hot standby. > > When that outage happened it was from a VACUUM call in a cronjob rather > than autovacuum. I now run such VACUUMs with TRUNCATE false which avoids > the issue for these. However I've realized that autovacuum could cause this > as well. > > I believe the only way to disable this for autovacuum is by changing the > vacuum_truncate storage parameters on tables. (Ignoring the now removed > old_snapshot_threshold option). Yes, you can only do that table by table. > I'm thinking of altering all my tables to > turn it off. Is this a horrible idea? 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. I don't see a problem with disabling VACUUM truncation for normal workloads. Some applications, like volatile queue tables, might need the feature, but I'd assume that to be the exception. > Would I need to disable the settings on catalog tables too? (To rule out > any possibility of it happening). Are there any other things I might be > missing? Potentially yes. But unless you are using temporary tables or create, alter and drop lots of objects, that shouldn't be necessary. > 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. Yours, Laurenz Albe ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Disabling vacuum truncate for autovacuum 2024-12-17 00:25 Disabling vacuum truncate for autovacuum Will Storey <[email protected]> 2024-12-17 07:30 ` Re: Disabling vacuum truncate for autovacuum Laurenz Albe <[email protected]> @ 2024-12-17 18:03 ` Will Storey <[email protected]> 1 sibling, 0 replies; 6+ messages in thread From: Will Storey @ 2024-12-17 18:03 UTC (permalink / raw) To: Laurenz Albe <[email protected]>; +Cc: [email protected] On Tue 2024-12-17 08:30:19 +0100, Laurenz Albe wrote: > > Would I need to disable the settings on catalog tables too? (To rule out > > any possibility of it happening). Are there any other things I might be > > missing? > > Potentially yes. But unless you are using temporary tables or create, > alter and drop lots of objects, that shouldn't be necessary. I see. That makes sense. I do have some things that use temporary tables as well as some jobs that create/drop objects. They are not very frequent nor are there a huge number objects involved, but I suppose it could still be an issue. I'm not keen on altering the catalogs, but it sounds like if I want to be very safe then I would need to. > > 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. I previously had old_snapshot_threshold enabled, which would have done this anyway I believe, including for the catalog tables. That was convenient! > Yours, > Laurenz Albe Thank you Laurenz! I've read a bunch of your writing and I've learned a lot from you. I'm a big fan :-). Thank you for what you do! ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Disabling vacuum truncate for autovacuum 2024-12-17 00:25 Disabling vacuum truncate for autovacuum Will Storey <[email protected]> 2024-12-17 07:30 ` Re: Disabling vacuum truncate for autovacuum Laurenz Albe <[email protected]> @ 2025-01-24 06:33 ` Gurjeet Singh <[email protected]> 2025-01-27 09:55 ` Re: Disabling vacuum truncate for autovacuum Laurenz Albe <[email protected]> 1 sibling, 1 reply; 6+ messages in thread From: Gurjeet Singh @ 2025-01-24 06:33 UTC (permalink / raw) To: Postgres Hackers <[email protected]>; +Cc: Will Storey <[email protected]>; Laurenz Albe <[email protected]> (moving discussion to -hackers, for patch-review) On Mon, Dec 16, 2024 at 11:30 PM Laurenz Albe <[email protected]> wrote: > > On Mon, 2024-12-16 at 16:25 -0800, Will Storey wrote: > > I would like to disable vacuum's truncate behaviour for autovacuum. > > Previously I had an outage due to its access exclusive lock when it was > > replicated to a hot standby. I can attest to one production outage caused by this behaviour of autovacuum. The truncate operation performed by autovacuum, when being replayed on the replica, was blocked by a query. Any new queries on that relation were blocked by replication. > > When that outage happened it was from a VACUUM call in a cronjob rather > > than autovacuum. I now run such VACUUMs with TRUNCATE false which avoids > > the issue for these. However I've realized that autovacuum could cause this > > as well. > > > > I believe the only way to disable this for autovacuum is by changing the > > vacuum_truncate storage parameters on tables. (Ignoring the now removed > > old_snapshot_threshold option). > > Yes, you can only do that table by table. That is unfortunate. Although doing so provides a granular control over which relations one would like to exclude from truncation, it may not always be desirable; a DBA/sysadmin may want to prevent this problem system-wide. Also, this not really scalable since it requires that a maintenance operation regularly connect to every database and apply this setting to all the relations, for the fear that there may be new objects somewhere in the cluster since last maintenance, which may cause this problem. It would be error prone, too, considering that the list of databases in a cluster may change over time. And then there's the added burden of monitoring the status of this maintenance operation to ensure it's running successfully every time. Turning on a system-wide setting that disables autovacuum truncation may look like a heavy hammer, but in certain situations this may be preferable to the risk of causing outage in production systems. It may be preferable to let the system consume disk space by not truncating the tables, as opposed to running the risk of blocked queries. Disk is cheap, and is possibly already being monitored in a production system. I understand Jeremy's contention upthread against adding such a feature at global level, but I'm in favor of adding this feature since it prevents a sudden and unpredictable impact on production systems, and instead leads to a gradual escalation of the problem that can be monitored and addressed by a sysadmin/DBA at a time that's convenient for them. > > 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 :-) 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. 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. Best regards, Gurjeet http://Gurje.et Attachments: [application/x-patch] autovacuum_disable_relation_truncation.v1.patch (2.7K, 2-autovacuum_disable_relation_truncation.v1.patch) download | inline diff: commit 84e8eebb87bc2c58feae847efd995bc055701688 Author: Gurjeet Singh <[email protected]> Date: Thu Jan 23 19:37:33 2025 -0800 Version 1 diff --git a/src/backend/postmaster/autovacuum.c b/src/backend/postmaster/autovacuum.c index 0ab921a169..17e22c08e5 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_disable_vacuum_truncate = false; int autovacuum_worker_slots; int autovacuum_max_workers; int autovacuum_work_mem = -1; @@ -2811,12 +2812,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_disable_vacuum_truncate + ? 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 38cb9e970d..40368e339c 100644 --- a/src/backend/utils/misc/guc_tables.c +++ b/src/backend/utils/misc/guc_tables.c @@ -1509,6 +1509,16 @@ struct config_bool ConfigureNamesBool[] = NULL, NULL, NULL }, + { + {"autovacuum_disable_vacuum_truncate", PGC_SIGHUP, VACUUM_AUTOVACUUM, + gettext_noop("Disables autovacuum behavior of truncatiing relations."), + NULL + }, + &autovacuum_disable_vacuum_truncate, + false, + 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 54e01c81d6..c7597407d2 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_disable_vacuum_truncate; extern PGDLLIMPORT int autovacuum_worker_slots; extern PGDLLIMPORT int autovacuum_max_workers; extern PGDLLIMPORT int autovacuum_work_mem; ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Disabling vacuum truncate for autovacuum 2024-12-17 00:25 Disabling vacuum truncate for autovacuum Will Storey <[email protected]> 2024-12-17 07:30 ` Re: Disabling vacuum truncate for autovacuum Laurenz Albe <[email protected]> 2025-01-24 06:33 ` Re: Disabling vacuum truncate for autovacuum Gurjeet Singh <[email protected]> @ 2025-01-27 09:55 ` Laurenz Albe <[email protected]> 2025-01-27 20:38 ` Re: Disabling vacuum truncate for autovacuum Robert Haas <[email protected]> 0 siblings, 1 reply; 6+ messages in thread From: Laurenz Albe @ 2025-01-27 09:55 UTC (permalink / raw) To: Gurjeet Singh <[email protected]>; Postgres Hackers <[email protected]>; +Cc: Will Storey <[email protected]> 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 ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Disabling vacuum truncate for autovacuum 2024-12-17 00:25 Disabling vacuum truncate for autovacuum Will Storey <[email protected]> 2024-12-17 07:30 ` Re: Disabling vacuum truncate for autovacuum Laurenz Albe <[email protected]> 2025-01-24 06:33 ` Re: Disabling vacuum truncate for autovacuum Gurjeet Singh <[email protected]> 2025-01-27 09:55 ` Re: Disabling vacuum truncate for autovacuum Laurenz Albe <[email protected]> @ 2025-01-27 20:38 ` Robert Haas <[email protected]> 0 siblings, 0 replies; 6+ messages in thread From: Robert Haas @ 2025-01-27 20:38 UTC (permalink / raw) To: Laurenz Albe <[email protected]>; +Cc: Gurjeet Singh <[email protected]>; Postgres Hackers <[email protected]>; Will Storey <[email protected]> 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. 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 ^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2025-01-27 20:38 UTC | newest] Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2024-12-17 00:25 Disabling vacuum truncate for autovacuum Will Storey <[email protected]> 2024-12-17 07:30 ` Laurenz Albe <[email protected]> 2024-12-17 18:03 ` Will Storey <[email protected]> 2025-01-24 06:33 ` Gurjeet Singh <[email protected]> 2025-01-27 09:55 ` Laurenz Albe <[email protected]> 2025-01-27 20:38 ` Robert Haas <[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