Re: [Proposal] vacuumdb --schema only

On Wed, Mar 30, 2022 at 02:22:58PM -0700, Nathan Bossart wrote:
> I'm personally -1 for the --exclude-schema option. I don't see any
> existing "exclude" options in vacuumdb, and the uses for such an option
> seem rather limited. If we can point to specific use-cases for this
> option, I might be willing to change my vote.

I suggested it because I would consider using it, even though I don't currently
use the vacuumdb script at all. I think this would allow partially
retiring/simplifying our existing vacuum script.

We 1) put all our partitions in a separate "child" schema (so \d is more
usable), and also 2) put some short-lived tables into their own schemas. Some
of those tables may only exist for ~1 day so I'd perfer to neither vacuum nor
analyze them (they're only used for SELECT *). But there can be a lot of them,
so a nightly job could do something like vacuumdb --schema public or vacuumdb
--exclude-schema ephemeral.

Everything would be processed nightly using vacuumdb --min-xid (to keep the
monitoring system happy).

The non-partitioned tables could be vacuumed nightly (without min-xid), with
--exclude ephemeral.

The partitioned tables could be processed monthly with vacuumdb --analyze.

I'd also want to be able to run vacuumdb --analyze nightly, but I'd want to
exclude the schema with short-lived tables. I'd also need a way to exclude
our partitioned tables from nightly analyze (they should run monthly only).

Maybe this could share something with this patch:
https://commitfest.postgresql.org/37/2573/
pg_dump - read data for some options from external file

The goal of that patch was to put it in a file, which isn't really needed here.
But if there were common infrastructure for matching tables, it could be
shared. The interesting part for this patch is to avoid adding separate
commandline arguments for --include-table, --exclude-table, --include-schema,
--exclude-schema (and anything else?)