Re: Add MAIN_RELATION_CLEANUP and SECONDARY_RELATION_CLEANUP options to VACUUM

On Fri, Jan 24, 2020 at 09:24:45PM +0000, Bossart, Nathan wrote:
> On 1/21/20, 1:39 PM, "Vik Fearing" <vik(dot)fearing(at)2ndquadrant(dot)com> wrote:
> > On 21/01/2020 22:21, 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.
> >
> >
> > Could you explain why one would want to do this? Autovacuum will
> > already deal with the tables separately as needed, but I don't see when
> > a manual vacuum would want to make this distinction.
>
> The main use case I'm targeting is when the level of bloat or
> transaction ages of a relation and its TOAST table have significantly
> diverged. In these scenarios, it could be beneficial to be able to
> vacuum just one or the other, especially if the tables are large.

This just came up for me:

I have a daily maintenance script which pro-actively vacuums tables: freezing
historic partitions, vacuuming current tables if the table's relfrozenxid is
old, and to encourage indexonly scan.

I'm checking the greatest(age(toast,main)) and vacuum the table (and implicitly
its toast) whenever either is getting old.

But it'd be more ideal if I could independently vacuum the main table if it's
old, but not the toast table.

--
Justin