Thread: Proposal "VACUUM SCHEMA"

Fabrízio de Royes Mello <fabriziomello@gmail.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.

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, ...
        regards, tom lane

<div dir="ltr"><div class="gmail_extra"><br />On Sun, Dec 21, 2014 at 5:18 PM, Tom Lane <<a
href="mailto:tgl@sss.pgh.pa.us">tgl@sss.pgh.pa.us</a>>wrote:<br />><br />> Fabrízio de Royes Mello <<a
href="mailto:fabriziomello@gmail.com">fabriziomello@gmail.com</a>>writes:<br />> > I work with some customer
thathave databases with a lot of schemas and<br />> > sometimes we need to run manual VACUUM in one schema, and
wouldbe nice to<br />> > have a new option to run vacuum in relations from a specific schema.<br />><br />>
I'mpretty skeptical of this alleged use-case.  Manual vacuuming ought<br />> to be mostly a thing of the past, and
evenif it's not, hitting<br />> *everything* in a schema should seldom be an appropriate thing to do.<br />><br
/><br/></div><div class="gmail_extra">I agree manual vacuum is a thing of the past, but autovacuum doesn't solve 100%
ofthe cases, and sometimes we need to use it so my proposal is just do help DBAs and/or Sysadmins to write simple
maintenancescripts.<br /></div><div class="gmail_extra"><br /><br />> While the feature itself might be fairly
innocuous,I'm just wondering<br />> why we need to encourage manual vacuuming.  <br /><br /></div><div
class="gmail_extra">IMHOwe will not encourage manual vacuuming, just give more flexibility to users.<br /></div><div
class="gmail_extra"><br/><br />> And why that, but not<br />> say schema-wide ANALYZE, CLUSTER, TRUNCATE, ...<br
/>><br/><br /></div><div class="gmail_extra">+1. I can write patches for each of this maintenance statement too.<br
/></div><divclass="gmail_extra"><br /></div><div class="gmail_extra">Regards,<br /></div><div class="gmail_extra"><br
/>--<br/>Fabrízio de Royes Mello<br />Consultoria/Coaching PostgreSQL<br />>> Timbira: <a
href="http://www.timbira.com.br">http://www.timbira.com.br</a><br/>>> Blog: <a
href="http://fabriziomello.github.io">http://fabriziomello.github.io</a><br/>>> Linkedin: <a
href="http://br.linkedin.com/in/fabriziomello">http://br.linkedin.com/in/fabriziomello</a><br/>>> Twitter: <a
href="http://twitter.com/fabriziomello">http://twitter.com/fabriziomello</a><br/>>> Github: <a
href="http://github.com/fabriziomello">http://github.com/fabriziomello</a></div></div>

On 12/21/14, 3:30 PM, Fabrízio de Royes Mello wrote:
>
> On Sun, Dec 21, 2014 at 5:18 PM, Tom Lane <tgl@sss.pgh.pa.us <mailto:tgl@sss.pgh.pa.us>> wrote:
>  >
>  > Fabrízio de Royes Mello <fabriziomello@gmail.com <mailto:fabriziomello@gmail.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.
>  >
>
> I agree manual vacuum is a thing of the past, but autovacuum doesn't solve 100% of the cases, and sometimes we need
touse it so my proposal is just do help DBAs and/or Sysadmins to write simple maintenance scripts.
 

Just one example of that is pre-emptively vacuuming during slower periods. Nothing spells "fun" like a freeze vacuum in
themiddle of a busy lunch period for a website.
 

Similarly, it's common to need to proactively vacuum after a data load, and since it's not unusual for there to be a
schemadedicated to loading data, this makes that easier.
 

>  > And why that, but not
>  > say schema-wide ANALYZE, CLUSTER, TRUNCATE, ...
>  >
>
> +1. I can write patches for each of this maintenance statement too.

If we're going to go that route, then perhaps it would make more sense to create a command that allows you to apply a
secondcommand to every object in a schema. We would have to be careful about PreventTransactionChain commands.
 
-- 
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com

On 12/21/14, 3:30 PM, Fabrízio de Royes Mello wrote:

On Sun, Dec 21, 2014 at 5:18 PM, Tom Lane <tgl@sss.pgh.pa.us <mailto:tgl@sss.pgh.pa.us>> wrote:
 >
 > Fabrízio de Royes Mello <fabriziomello@gmail.com <mailto:fabriziomello@gmail.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.
 >

I agree manual vacuum is a thing of the past, but autovacuum doesn't solve 100% of the cases, and sometimes we need to use it so my proposal is just do help DBAs and/or Sysadmins to write simple maintenance scripts.

Just one example of that is pre-emptively vacuuming during slower periods. Nothing spells "fun" like a freeze vacuum in the middle of a busy lunch period for a website.

Similarly, it's common to need to proactively vacuum after a data load, and since it's not unusual for there to be a schema dedicated to loading data, this makes that easier.

 > And why that, but not
 > say schema-wide ANALYZE, CLUSTER, TRUNCATE, ...
 >

+1. I can write patches for each of this maintenance statement too.

If we're going to go that route, then perhaps it would make more sense to create a command that allows you to apply a second command to every object in a schema. We would have to be careful about PreventTransactionChain commands.

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)

>
> > 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), but I can definitely be wrong.

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.


TRUNCATE SCHEMA whatever    sounds quite dangerous, too.



Just my .02€
    / J.L.

On 2014-12-21 14:18:33 -0500, Tom Lane wrote:
> Fabrízio de Royes Mello <fabriziomello@gmail.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

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

Re: Alvaro Herrera 2014-12-22 <20141222165157.GD1768@alvh.no-ip.org>
> 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.

Having a schema that's only used for importing data in batch jobs
doesn't sound too unreasonable. It could then be cleaned in a simple
"TRUNCATE SCHEMA import_area" command.

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

Reading the thread, my impression was that most people opposed the
idea because there's ways to script "vacuum schema", or because of
"people shouldn't be invoking manual vacuums anyway". I think the
patch tries to solve a practical problem, and does have its merits.

Christoph
-- 
cb@df7cb.de | http://www.df7cb.de/

* Alvaro Herrera (alvherre@2ndquadrant.com) wrote:
> 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.

I could see it happening in environments which use schemas when doing
partitioning.  eg: data_2014 contains all of the data_201401-201412
monthly (or perhaps weekly) tables.

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

Still, I see this point also.  I do think it'd be really great if we
could figure out a way to segregate these kinds of DDL / maintenance
commands from the normal select/insert/update/delete SQL parsing, such
that we could add more options, etc, to those longer running and less
frequent commands without impacting parse time for the high-volume
commands.

I'm less concerned about the memory impact, except to the extent that it
impacts throughput and performance.
Thanks,
    Stephen

Stephen Frost wrote:
> * Alvaro Herrera (alvherre@2ndquadrant.com) wrote:

> > Overall, this whole line of development seems like bloating the parse
> > tables for little gain.
> 
> Still, I see this point also.  I do think it'd be really great if we
> could figure out a way to segregate these kinds of DDL / maintenance
> commands from the normal select/insert/update/delete SQL parsing, such
> that we could add more options, etc, to those longer running and less
> frequent commands without impacting parse time for the high-volume
> commands.

We do have a parenthesized options clause in VACUUM.  I think adding
this as a clause there would be pretty much free.

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

* Andres Freund (andres@2ndquadrant.com) wrote:
> On 2014-12-21 14:18:33 -0500, Tom Lane wrote:
> > 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.

Wow.  That's certainly an interesting idea.

We might end up turning the autovacuum process into a generalized
scheduler/cron-like entity that way though.  I'd rather we just build
that.  Users would then be able to run a script periodically which
would add VACUUM commands to be run on whichever tables they want to
the jobs queue, either for immediate execution or at whatever time they
want (or possibly chronically :).
Thanks!
    Stephen

On 2014-12-22 12:12:12 -0500, Stephen Frost wrote:
> * Andres Freund (andres@2ndquadrant.com) wrote:
> > On 2014-12-21 14:18:33 -0500, Tom Lane wrote:
> > > 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.
> 
> Wow.  That's certainly an interesting idea.
> 
> We might end up turning the autovacuum process into a generalized
> scheduler/cron-like entity that way though.

I'm not talking about autovacuum, just plain vacuumdb.

> I'd rather we just build
> that.  Users would then be able to run a script periodically which
> would add VACUUM commands to be run on whichever tables they want to
> the jobs queue, either for immediate execution or at whatever time they
> want (or possibly chronically :).

And this discussion just feature creeped beyond anything realistic... :)

Greetings,

Andres Freund

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

Stephen Frost wrote:
> * Andres Freund (andres@2ndquadrant.com) wrote:

> > 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.
> 
> Wow.  That's certainly an interesting idea.

+1.

> We might end up turning the autovacuum process into a generalized
> scheduler/cron-like entity that way though.  I'd rather we just build
> that.  Users would then be able to run a script periodically which
> would add VACUUM commands to be run on whichever tables they want to
> the jobs queue, either for immediate execution or at whatever time they
> want (or possibly chronically :).

This too.  I think there's one or two orders of magnitude of difference
in implementation effort of these two ideas, however.

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

* Andres Freund (andres@2ndquadrant.com) wrote:
> On 2014-12-22 12:12:12 -0500, Stephen Frost wrote:
> > We might end up turning the autovacuum process into a generalized
> > scheduler/cron-like entity that way though.
>
> I'm not talking about autovacuum, just plain vacuumdb.

Oh, right, clearly I was thinking of autovacuum.  Adding an option like
that to vacuumdb would certainly be a lot more straight-forward.

> > I'd rather we just build
> > that.  Users would then be able to run a script periodically which
> > would add VACUUM commands to be run on whichever tables they want to
> > the jobs queue, either for immediate execution or at whatever time they
> > want (or possibly chronically :).
>
> And this discussion just feature creeped beyond anything realistic... :)

Yeah, but I really *want* this... ;)
Thanks!
    Stephen

<div dir="ltr"><div class="gmail_extra"><br />On Mon, Dec 22, 2014 at 3:17 PM, Alvaro Herrera <<a
href="mailto:alvherre@2ndquadrant.com">alvherre@2ndquadrant.com</a>>wrote:<br />><br />> Stephen Frost
wrote:<br/>> > * Andres Freund (<a href="mailto:andres@2ndquadrant.com">andres@2ndquadrant.com</a>) wrote:<br
/>><br/>> > > There's one argument for supporting more for VACUUM than the rest - it<br />> > >
can'tbe executed directly as the result of a query as the others<br />> > > can... I wonder if that'd not
betterbe answered by adding a feature to<br />> > > vacuumdb that allows selecting the to-be-vacuumed table by
auser<br />> > > defined query.<br />> ><br />> > Wow.  That's certainly an interesting idea.<br
/>><br/>> +1.<br />><br /><br /></div><div class="gmail_extra">Then to simplify can we allow the "--table"
optionof vacuumdb act similar to the "--table" option of pg_dump??<br /></div><div class="gmail_extra"><br /></div><div
class="gmail_extra">Regards,<br/><br /></div><div class="gmail_extra">--<br />Fabrízio de Royes Mello<br
/>Consultoria/CoachingPostgreSQL<br />>> Timbira: <a
href="http://www.timbira.com.br">http://www.timbira.com.br</a><br/>>> Blog: <a
href="http://fabriziomello.github.io">http://fabriziomello.github.io</a><br/>>> Linkedin: <a
href="http://br.linkedin.com/in/fabriziomello">http://br.linkedin.com/in/fabriziomello</a><br/>>> Twitter: <a
href="http://twitter.com/fabriziomello">http://twitter.com/fabriziomello</a><br/>>> Github: <a
href="http://github.com/fabriziomello">http://github.com/fabriziomello</a></div></div>

On 12/21/2014 02:18 PM, Tom Lane wrote:
> Fabrízio de Royes Mello <fabriziomello@gmail.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.
>
> 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, ...
>
>             


Sadly, manual vacuuming is very far from a thing of the past. Autovacuum 
simply doesn't give us enough control in many cases.

Maybe this gadget would be useful, but its application seems a bit 
limited. Someone mentioned allowing multiple --table options to 
vacuumdb. That would be mopre flexible.

But really I think we need to work on how we can make autovacuum more 
useful. For example, it would be nice not to have to do "ALTER TABLE" to 
change the autovac settings. It would be nice to be able to specify 
times of day and days of week when autovacuum should be turned on or off 
for a table. I'm sure there are plenty of other ideas.

cheers

andrew

On 12/22/14, 10:05 AM, Andres Freund wrote:
>> 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.

I would MUCH rather that we find a way to special-case executing non-transactional commands dynamically, because VACUUM
isn'tthe only one that suffers from this problem.
 
-- 
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com

On 12/21/14, 8:55 PM, Fabrízio de Royes Mello wrote:
>           > And why that, but not
>           > say schema-wide ANALYZE, CLUSTER, TRUNCATE, ...
>           >
>
>         +1. I can write patches for each of this maintenance statement too.
>
>
>     If we're going to go that route, then perhaps it would make more sense to create a command that allows you to
applya second command to every object in a schema. We would have to be careful about PreventTransactionChain commands.
 
>
>
>   Sorry but I don't understand what you meant. Can you explain more about your idea?

There's a very large number of commands that could be useful to execute on every object in a schema. (RE)INDEX,
CLUSTER,ALTER come to mind besides VACUUM.
 

Right now a lot of people just work around this with things like DO blocks, but as mentioned elsewhere in the thread
thatfails for commands that can't be in a transaction.
 
-- 
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com

On Mon, Dec 22, 2014 at 5:00 PM, Jim Nasby <Jim.Nasby@bluetreble.com> wrote:
> I would MUCH rather that we find a way to special-case executing
> non-transactional commands dynamically, because VACUUM isn't the only one
> that suffers from this problem.

Is pg_background a solution to this problem?

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Mon, Dec 22, 2014 at 11:51 AM, Alvaro Herrera
<alvherre@2ndquadrant.com> wrote:
> 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.

We added REINDEX SCHEMA less than three weeks ago; if we accept that
that was a good change, but think this is a bad one, it's not clear to
me that there is any guiding principle here beyond who happened to
weigh in on which threads.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

<div dir="ltr"><div class="gmail_extra">On Mon, Dec 22, 2014 at 8:02 PM, Jim Nasby <<a
href="mailto:Jim.Nasby@bluetreble.com">Jim.Nasby@bluetreble.com</a>>wrote:<br />><br />> On 12/21/14, 8:55 PM,
Fabríziode Royes Mello wrote:<br />>><br />>>           > And why that, but not<br />>>          
>say schema-wide ANALYZE, CLUSTER, TRUNCATE, ...<br />>>           ><br />>><br />>>        
+1.I can write patches for each of this maintenance statement too.<br />>><br />>><br />>>     If
we'regoing to go that route, then perhaps it would make more sense to create a command that allows you to apply a
secondcommand to every object in a schema. We would have to be careful about PreventTransactionChain commands.<br
/>>><br/>>><br />>>   Sorry but I don't understand what you meant. Can you explain more about your
idea?<br/>><br />><br />> There's a very large number of commands that could be useful to execute on every
objectin a schema. (RE)INDEX, CLUSTER, ALTER come to mind besides VACUUM.<br />><br /><br />ANALYZE too...<br /><br
/> <br/><br />> Right now a lot of people just work around this with things like DO blocks, but as mentioned
elsewherein the thread that fails for commands that can't be in a transaction.<br />><br /><br />I use "dblink" to
solveit. :-)<br /><br /><br /></div><div class="gmail_extra">Regards,<br /><br /></div><div class="gmail_extra">--<br
/>Fabríziode Royes Mello<br />Consultoria/Coaching PostgreSQL<br />>> Timbira: <a
href="http://www.timbira.com.br">http://www.timbira.com.br</a><br/>>> Blog: <a
href="http://fabriziomello.github.io">http://fabriziomello.github.io</a><br/>>> Linkedin: <a
href="http://br.linkedin.com/in/fabriziomello">http://br.linkedin.com/in/fabriziomello</a><br/>>> Twitter: <a
href="http://twitter.com/fabriziomello">http://twitter.com/fabriziomello</a><br/>>> Github: <a
href="http://github.com/fabriziomello">http://github.com/fabriziomello</a></div></div>

Robert Haas wrote:
> On Mon, Dec 22, 2014 at 11:51 AM, Alvaro Herrera
> <alvherre@2ndquadrant.com> wrote:
> > 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.
> 
> We added REINDEX SCHEMA less than three weeks ago; if we accept that
> that was a good change, but think this is a bad one, it's not clear to
> me that there is any guiding principle here beyond who happened to
> weigh in on which threads.

I didn't think much of REINDEX SCHEMA, TBH.

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

On 12/23/14, 8:54 AM, Fabrízio de Royes Mello wrote:
>  > Right now a lot of people just work around this with things like DO blocks, but as mentioned elsewhere in the
threadthat fails for commands that can't be in a transaction.
 
>  >
>
> I use "dblink" to solve it. :-)

So... how about instead of solving this only for vacuum we create something generic? :) Possibly using Robert's
backgroundworker work?
 
-- 
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com

On 12/23/14, 7:44 AM, Robert Haas wrote:
> On Mon, Dec 22, 2014 at 5:00 PM, Jim Nasby <Jim.Nasby@bluetreble.com> wrote:
>> I would MUCH rather that we find a way to special-case executing
>> non-transactional commands dynamically, because VACUUM isn't the only one
>> that suffers from this problem.
>
> Is pg_background a solution to this problem?

Yes, since it allows you to do "autonomous transactions". It's probably not the most efficient way to solve this, but
itshould work.
 
-- 
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com

On 12/23/14, 8:49 PM, Fabrízio de Royes Mello wrote:
> Em terça-feira, 23 de dezembro de 2014, Jim Nasby <Jim.Nasby@bluetreble.com <mailto:Jim.Nasby@bluetreble.com>>
escreveu:
>
>     On 12/23/14, 8:54 AM, Fabrízio de Royes Mello wrote:
>
>           > Right now a lot of people just work around this with things like DO blocks, but as mentioned elsewhere in
thethread that fails for commands that can't be in a transaction.
 
>           >
>
>         I use "dblink" to solve it. :-)
>
>
>     So... how about instead of solving this only for vacuum we create something generic? :) Possibly using Robert's
backgroundworker work?
 
>
>
>   Interesting idea.
>
> But and what about the idea of improve the "--table" option from clients: vaccumdb and clusterdb?

Seems reasonable.
-- 
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com

21.12.2014, 18:48, Fabrízio de Royes Mello kirjoitti:
> 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.
> 
> The new syntax could be:
> 
> VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] {  [ table_name ] | SCHEMA
> schema_name }
> 
> Also I'll add a new option to "vacuumdb" client:
> 
> -S, --schema=SCHEMA
> 
> I can work on this feature to 2015/02 CF.
> 
> Thoughts?

This would be useful for ANALYZE to make it easier to run analyze only
for the interesting schemas after a pg_upgrade.  I have a database with
most of the actively used data in the "public" schema and a number of
rarely accessed large logging and archive tables in other schemas.  It'd
be useful to prioritize analyzing the main tables before doing anything
about the rarely used schemas to allow the database to be put back into
production as soon as possible.

/ Oskari