Re: Add MAIN_RELATION_CLEANUP and SECONDARY_RELATION_CLEANUP options to VACUUM

On Wed, Jan 27, 2021 at 11:16:26PM +0000, Bossart, Nathan wrote:
> On 1/27/21, 11:07 AM, "Justin Pryzby" <pryzby(at)telsasoft(dot)com> wrote:
> > 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.
>
> Thanks for chiming in.
>
> It looks like we were leaning towards only adding the
> TOAST_TABLE_CLEANUP option, which is already implemented internally
> with VACOPT_SKIPTOAST. It's already possible to vacuum a TOAST table
> directly, so we can probably do without the MAIN_RELATION_CLEANUP
> option.
>
> I've attached a new patch that only adds TOAST_TABLE_CLEANUP.

Thanks, I wrote my message after running into the issue and remembered this
thread. I didn't necessarily mean to send another patch :)

My only comment is on the name: TOAST_TABLE_CLEANUP. "Cleanup" suggests that
the (main or only) purpose is to "clean" dead tuples to avoid bloat. But in my
use case, the main purpose is to avoid XID wraparound (or its warnings).

Okay, my second only comment is that this:

| This option cannot be disabled when the <literal>FULL</literal> option is
| used.

Should it instead be ignored if FULL is also specified ? Currently only
PARALLEL and DISABLE_PAGE_SKIPPING cause an error when used with FULL. That's
documented for PARALLEL, but I think it should also be documented for
DISABLE_PAGE_SKIPPING (which is however an advanced option).

--
Justin