Re: Add MAIN_RELATION_CLEANUP and SECONDARY_RELATION_CLEANUP options to VACUUM

Hi Michael,

Thanks for taking a look.

On 1/21/20, 9:02 PM, "Michael Paquier" <michael(at)paquier(dot)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