Re: Add MAIN_RELATION_CLEANUP and SECONDARY_RELATION_CLEANUP options to VACUUM

On Fri, Jan 24, 2020 at 09:31:26PM +0000, Bossart, Nathan wrote:
> 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.

That's something I have faced as well. I have some applications
around here where toast tables were the most bloated, and the
vacuuming of the main relation ate time, putting more pressure on the
vacuuming of the toast relation. So that's a fair argument in my
opinion.

>>> 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 am not sure that we actually need that at all, any catalog changes
take a lock on the parent relation first, and that's the conflicts we
are looking at here with a share update exclusive lock.
--
Michael