Re: Proposal "VACUUM SCHEMA"

José Luis Tallón wrote:
> On 12/21/2014 10:30 PM, Fabrízio de Royes Mello wrote:
> >[snip]
>
> I do agree that "vacuum schema" might very well be useful (I'll probably use
> it myself from time to time, too).
> ANALYZE SCHEMA (specially coupled with some transaction-wide "SET
> statistics_target" could be beneficial)

We already have transanction-wide SET -- it's spelled SET LOCAL.

> >
> >> And why that, but not say schema-wide ANALYZE, CLUSTER, TRUNCATE,
> >> ...
> >
> >+1. I can write patches for each of this maintenance statement too.
>
> Hmm... I think Tom might have been a bit rethorical (or even sarcastic with
> that),

That was my impression too.

> Do we really want to have some such operation potentially (and
> inadvertently) locking for *hours* at a time?
>
> CLUSTER SCHEMA somename;
>
> ... where schema "somename" contains "myHugeTable"
>
> Given that the cluster command exclusively locks and rewrites the table,
> it might lock queries and overwhelm the I/O subsystem for quite a long time.

Multi-table CLUSTER uses multiple transactions, so this should not be an
issue. That said, I don't think there's much point in CLUSTER SCHEMA,
much less TRUNCATE SCHEMA. Do you normally organize your schemas so
that there are some that contain only tables that need to be truncated
together? That would be a strange use case.

Overall, this whole line of development seems like bloating the parse
tables for little gain.

--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services