Re: Proposal "VACUUM SCHEMA"

On 2014-12-21 14:18:33 -0500, Tom Lane wrote:
> =?UTF-8?Q?Fabr=C3=ADzio_de_Royes_Mello?= <fabriziomello(at)gmail(dot)com> writes:
> > I work with some customer that have databases with a lot of schemas and
> > sometimes we need to run manual VACUUM in one schema, and would be nice to
> > have a new option to run vacuum in relations from a specific schema.
>
> I'm pretty skeptical of this alleged use-case. Manual vacuuming ought
> to be mostly a thing of the past, and even if it's not, hitting
> *everything* in a schema should seldom be an appropriate thing to do.

Based on my experience autovacuum isn't sufficient on bigger high
throughput databases. At the very least manual vacuuming with lower
freeze_table_age settings during low-load times is required lest
anti-wraparound vacuums increase load too much during prime business
hours.
That said, I don't see how this feature is actually helpful in those
cases. In pretty much all of what I've seen you'd want to have more
complex selection criteria than the schema.

> While the feature itself might be fairly innocuous, I'm just wondering
> why we need to encourage manual vacuuming. And why that, but not
> say schema-wide ANALYZE, CLUSTER, TRUNCATE, ...

There's one argument for supporting more for VACUUM than the rest - it
can't be executed directly as the result of a query as the others
can... I wonder if that'd not better be answered by adding a feature to
vacuumdb that allows selecting the to-be-vacuumed table by a user
defined query.

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services