Re: [Proposal] vacuumdb --schema only

On Fri, Apr 01, 2022 at 10:01:28AM -0500, Justin Pryzby wrote:
> 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).

Thanks for elaborating. I retract my -1 vote.

--
Nathan Bossart
Amazon Web Services: https://aws.amazon.com