A few new options for vacuumdb

From: "Bossart, Nathan" <bossartn(at)amazon(dot)com>
To: "pgsql-hackers(at)postgresql(dot)org" <pgsql-hackers(at)postgresql(dot)org>
Subject: A few new options for vacuumdb
Date: 2018-12-19 20:50:10
Message-ID: FFE5373C-E26A-495B-B5C8-911EC4A41C5E@amazon.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi hackers,

I've attached a few patches to add some options to vacuumdb that might
be useful. Specifically, these patches add the --skip-locked,
--min-xid-age, --min-mxid-age, and --min-relation-size options.

If an option is specified for a server version that is not supported,
the option is silently ignored. For example, SKIP_LOCKED was only
added to VACUUM and ANALYZE for v12. Alternatively, I think we could
fail in vacuum_one_database() if an unsupported option is specified.
Some of these options will work on all currently supported versions,
so I am curious what others think about skipping some of these version
checks altogether.

In this set of patches, I've disallowed using --min-xid-age,
--min-mxid-age, and --min-relation-size in conjunction with the
--table option. IMO this combination of options is prone to
confusion. For example:

vacuumdb mydb --table mytable --min-relation-size 1024

It does not seem clear whether the user wants us to process mytable
only if it is at least 1 GB, or if we should process mytable in
addition to any other relations over 1 GB. Either way, I think trying
to support these combinations of options adds more complexity than it
is worth.

0001 is a minor fix that is somewhat separate from these new options,
although the new options will make the edge case it aims to fix much
easier to reach. When the catalogs are queried in parallel mode to
get the list of tables to process, we currently assume that at least
one table will be returned. If no tables are found, the tables
variable will stay as NULL, which leads to database-wide VACUUM or
ANALYZE commands. Since there are currently no user-configurable
options available for this catalog query, this case is likely
exceptionally rare. However, with the new options, it is much easier
to inadvertently filter out all relations.

I will be adding this work to the next commitfest.

Nathan

Attachment Content-Type Size
v1-0002-Add-skip-locked-option-to-vacuumdb.patch application/octet-stream 4.3 KB
v1-0003-Add-min-xid-age-and-min-mxid-age-options-to-vacuu.patch application/octet-stream 9.0 KB
v1-0004-Add-min-relation-size-option-to-vacuumdb.patch application/octet-stream 6.7 KB
v1-0001-Do-not-process-any-relations-if-the-catalog-query.patch application/octet-stream 1.6 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Andrew Dunstan 2018-12-19 20:55:51 Re: pg_dumpall --exclude-database option
Previous Message Julien Rouhaud 2018-12-19 20:48:05 Re: Ordered Partitioned Table Scans