Re: Add MAIN_RELATION_CLEANUP and SECONDARY_RELATION_CLEANUP optionsto VACUUM - Mailing list pgsql-hackers
From | Bossart, Nathan |
---|---|
Subject | Re: Add MAIN_RELATION_CLEANUP and SECONDARY_RELATION_CLEANUP optionsto VACUUM |
Date | |
Msg-id | E15A7FC3-1C47-4186-9B5E-C0E407435F90@amazon.com Whole thread Raw |
In response to | Re: Add MAIN_RELATION_CLEANUP and SECONDARY_RELATION_CLEANUP optionsto VACUUM (Michael Paquier <michael@paquier.xyz>) |
Responses |
Re: Add MAIN_RELATION_CLEANUP and SECONDARY_RELATION_CLEANUP optionsto VACUUM
(Michael Paquier <michael@paquier.xyz>)
|
List | pgsql-hackers |
Hi Michael, Thanks for taking a look. On 1/21/20, 9:02 PM, "Michael Paquier" <michael@paquier.xyz> wrote: > On Tue, Jan 21, 2020 at 09:21:46PM +0000, Bossart, Nathan wrote: >> I've attached a patch for a couple of new options for VACUUM: >> MAIN_RELATION_CLEANUP and SECONDARY_RELATION_CLEANUP. The motive >> behind these options is to allow table owners to easily vacuum only >> the TOAST table or only the main relation. This is especially useful >> for TOAST tables since roles do not have access to the pg_toast schema >> by default and some users may find it difficult to discover the name >> of a relation's TOAST table. Next, I will explain a couple of the >> main design decisions. > > So that's similar to the autovacuum reloptions, but to be able to > enforce one policy or another manually. Any issues with autovacuum > not able to keep up the bloat pace and where you need to issue manual > VACUUMs in periods of low activity, like nightly VACUUMs? There have been a couple of occasions where I have seen the TOAST table become the most bloated part of the relation. When this happens, it would be handy to be able to avoid scanning the heap and indexes. I am not aware of any concrete problems with autovacuum other than needing to tune the parameters for certain workloads. >> I chose to call the option SECONDARY_RELATION_CLEANUP instead of >> something like TOAST_TABLE_CLEANUP for two reasons. First, other >> types of secondary relations may be added in the future, and it may be >> convenient to put them under the umbrella of this option. Second, it >> seemed like it could be outside of the project's style to use the name >> of internal storage mechanisms in a user-facing VACUUM option. >> However, I am not wedded to the chosen name, as I am sure there are >> good arguments for something like TOAST_TABLE_CLEANUP. > > If other types of relations are added in the future, wouldn't it make > sense to have one switch for each one of those types then? A relation > could have a toast relation associated to it, as much as a foo > relation or a hoge relation, in which case SECONDARY brings little > control. This is a good point. I've renamed the option to TOAST_TABLE_CLEANUP in v2. >> I chose to implement MAIN_RELATION_CLEANUP within vacuum_rel() instead >> of expand_vacuum_rel()/get_all_vacuum_rels(). This allows us to reuse >> most of the existing code with minimal changes, and it avoids adding >> complexity to the lookups and ownership checks in expand_vacuum_rel() >> and get_all_vacuum_rels() (especially the partition lookup logic). >> The main tradeoffs of this approach are that we will still create a >> transaction for the main relation and that we will still lock the main >> relation. > > Yeah, likely we should not make things more confusing in this area. > This was tricky enough to deal with with the recent VACUUM > refactoring for multiple relations. Finding a way to avoid the lock on the main relation could be a future improvement, as that would allow you to manually vacuum both the main relation and its TOAST table in parallel. >> I reused the existing VACOPT_SKIPTOAST option to implement >> SECONDARY_RELATION_CLEANUP. This option is currently only used for >> autovacuum. > > My take would be to rename this option, and reuse it for consistency. Done. >> I chose to disallow disabling both *_RELATION_CLEANUP options >> together, as this would essentially cause the VACUUM command to take >> no action. > > My first reaction is why? Agreed that it is a bit crazy to combine > both options, but if you add the argument related to more relation > types like toast.. Yes, I suppose we have the same problem if you disable MAIN_RELATION_CLEANUP and the relation has no TOAST table. In any case, allowing both options to be disabled shouldn't hurt anything. Nathan
Attachment
pgsql-hackers by date: