Hi hackers,
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.
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.
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.
I reused the existing VACOPT_SKIPTOAST option to implement
SECONDARY_RELATION_CLEANUP. This option is currently only used for
autovacuum.
I chose to disallow disabling both *_RELATION_CLEANUP options
together, as this would essentially cause the VACUUM command to take
no action. I disallowed using FULL when SECONDARY_RELATION_CLEANUP is
disabled, as the TOAST table is automatically rebuilt by
cluster_rel(). I do allow using FULL when MAIN_RELATION_CLEANUP is
disabled, which is taken to mean that cluster_rel() should be run on
the TOAST table. Finally, I disallowed using ANALYZE when
MAIN_RELATION_CLEANUP is disabled, as it is not presently possible to
analyze TOAST tables.
I will add this patch to the next commitfest. I look forward to your
feedback.
Nathan