Re: Add MAIN_RELATION_CLEANUP and SECONDARY_RELATION_CLEANUP options to VACUUM

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?

> 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.

> 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.

> 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.

> 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..
--
Michael