Re: [HACKERS] Proposal VACUUM SCHEMA

2014-12-28 Thread Oskari Saarenmaa
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



-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Proposal VACUUM SCHEMA

2014-12-24 Thread Jim Nasby

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.na...@bluetreble.com 
mailto:jim.na...@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 the thread 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 background worker 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


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Proposal VACUUM SCHEMA

2014-12-23 Thread Robert Haas
On Mon, Dec 22, 2014 at 5:00 PM, Jim Nasby jim.na...@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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Proposal VACUUM SCHEMA

2014-12-23 Thread Robert Haas
On Mon, Dec 22, 2014 at 11:51 AM, Alvaro Herrera
alvhe...@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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Proposal VACUUM SCHEMA

2014-12-23 Thread Fabrízio de Royes Mello
On Mon, Dec 22, 2014 at 8:02 PM, Jim Nasby jim.na...@bluetreble.com wrote:

 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 apply a 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.


ANALYZE too...



 Right now a lot of people just work around this with things like DO
blocks, but as mentioned elsewhere in the thread that fails for commands
that can't be in a transaction.


I use dblink to solve it. :-)


Regards,

--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL
 Timbira: http://www.timbira.com.br
 Blog: http://fabriziomello.github.io
 Linkedin: http://br.linkedin.com/in/fabriziomello
 Twitter: http://twitter.com/fabriziomello
 Github: http://github.com/fabriziomello


Re: [HACKERS] Proposal VACUUM SCHEMA

2014-12-23 Thread Alvaro Herrera
Robert Haas wrote:
 On Mon, Dec 22, 2014 at 11:51 AM, Alvaro Herrera
 alvhe...@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 Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training  Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Proposal VACUUM SCHEMA

2014-12-23 Thread Jim Nasby

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 thread 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 background worker work?
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Proposal VACUUM SCHEMA

2014-12-23 Thread Jim Nasby

On 12/23/14, 7:44 AM, Robert Haas wrote:

On Mon, Dec 22, 2014 at 5:00 PM, Jim Nasby jim.na...@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 it should work.
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Proposal VACUUM SCHEMA

2014-12-23 Thread Fabrízio de Royes Mello
Em terça-feira, 23 de dezembro de 2014, Jim Nasby jim.na...@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 the thread 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 background worker work?


 Interesting idea.

But and what about the idea of improve the --table option from clients:
vaccumdb and clusterdb?

Regards,

Fabrízio Mello



-- 
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL
 Timbira: http://www.timbira.com.br
 Blog: http://fabriziomello.github.io
 Linkedin: http://br.linkedin.com/in/fabriziomello
 Twitter: http://twitter.com/fabriziomello
 Github: http://github.com/fabriziomello


Re: [HACKERS] Proposal VACUUM SCHEMA

2014-12-22 Thread José Luis Tallón

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 whateversounds quite dangerous, too.



Just my .02€

/ J.L.




--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Proposal VACUUM SCHEMA

2014-12-22 Thread Andres Freund
On 2014-12-21 14:18:33 -0500, Tom Lane wrote:
 =?UTF-8?Q?Fabr=C3=ADzio_de_Royes_Mello?= fabriziome...@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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Proposal VACUUM SCHEMA

2014-12-22 Thread Alvaro Herrera
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 Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training  Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Proposal VACUUM SCHEMA

2014-12-22 Thread Christoph Berg
Re: Alvaro Herrera 2014-12-22 20141222165157.gd1...@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
-- 
c...@df7cb.de | http://www.df7cb.de/


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Proposal VACUUM SCHEMA

2014-12-22 Thread Stephen Frost
* Alvaro Herrera (alvhe...@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


signature.asc
Description: Digital signature


Re: [HACKERS] Proposal VACUUM SCHEMA

2014-12-22 Thread Alvaro Herrera
Stephen Frost wrote:
 * Alvaro Herrera (alvhe...@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 Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training  Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Proposal VACUUM SCHEMA

2014-12-22 Thread Stephen Frost
* Andres Freund (and...@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


signature.asc
Description: Digital signature


Re: [HACKERS] Proposal VACUUM SCHEMA

2014-12-22 Thread Andres Freund
On 2014-12-22 12:12:12 -0500, Stephen Frost wrote:
 * Andres Freund (and...@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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Proposal VACUUM SCHEMA

2014-12-22 Thread Alvaro Herrera
Stephen Frost wrote:
 * Andres Freund (and...@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 Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training  Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Proposal VACUUM SCHEMA

2014-12-22 Thread Stephen Frost
* Andres Freund (and...@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


signature.asc
Description: Digital signature


Re: [HACKERS] Proposal VACUUM SCHEMA

2014-12-22 Thread Fabrízio de Royes Mello
On Mon, Dec 22, 2014 at 3:17 PM, Alvaro Herrera alvhe...@2ndquadrant.com
wrote:

 Stephen Frost wrote:
  * Andres Freund (and...@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.


Then to simplify can we allow the --table option of vacuumdb act similar
to the --table option of pg_dump??

Regards,

--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL
 Timbira: http://www.timbira.com.br
 Blog: http://fabriziomello.github.io
 Linkedin: http://br.linkedin.com/in/fabriziomello
 Twitter: http://twitter.com/fabriziomello
 Github: http://github.com/fabriziomello


Re: [HACKERS] Proposal VACUUM SCHEMA

2014-12-22 Thread Andrew Dunstan


On 12/21/2014 02:18 PM, Tom Lane wrote:

=?UTF-8?Q?Fabr=C3=ADzio_de_Royes_Mello?= fabriziome...@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


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Proposal VACUUM SCHEMA

2014-12-22 Thread Jim Nasby

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't the only one that 
suffers from this problem.
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Proposal VACUUM SCHEMA

2014-12-22 Thread Jim Nasby

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 apply a 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 that fails 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


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


[HACKERS] Proposal VACUUM SCHEMA

2014-12-21 Thread Fabrízio de Royes Mello
Hi all,

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?

--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL
 Timbira: http://www.timbira.com.br
 Blog: http://fabriziomello.github.io
 Linkedin: http://br.linkedin.com/in/fabriziomello
 Twitter: http://twitter.com/fabriziomello
 Github: http://github.com/fabriziomello


Re: [HACKERS] Proposal VACUUM SCHEMA

2014-12-21 Thread Tom Lane
=?UTF-8?Q?Fabr=C3=ADzio_de_Royes_Mello?= fabriziome...@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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Proposal VACUUM SCHEMA

2014-12-21 Thread Fabrízio de Royes Mello
On Sun, Dec 21, 2014 at 5:18 PM, Tom Lane t...@sss.pgh.pa.us wrote:

 =?UTF-8?Q?Fabr=C3=ADzio_de_Royes_Mello?= fabriziome...@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.


 While the feature itself might be fairly innocuous, I'm just wondering
 why we need to encourage manual vacuuming.

IMHO we will not encourage manual vacuuming, just give more flexibility to
users.


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


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

Regards,

--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL
 Timbira: http://www.timbira.com.br
 Blog: http://fabriziomello.github.io
 Linkedin: http://br.linkedin.com/in/fabriziomello
 Twitter: http://twitter.com/fabriziomello
 Github: http://github.com/fabriziomello


Re: [HACKERS] Proposal VACUUM SCHEMA

2014-12-21 Thread Jim Nasby

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


On Sun, Dec 21, 2014 at 5:18 PM, Tom Lane t...@sss.pgh.pa.us 
mailto:t...@sss.pgh.pa.us wrote:
 
  =?UTF-8?Q?Fabr=C3=ADzio_de_Royes_Mello?= fabriziome...@gmail.com 
mailto:fabriziome...@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.
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Proposal VACUUM SCHEMA

2014-12-21 Thread Fabrízio de Royes Mello
Em segunda-feira, 22 de dezembro de 2014, Jim Nasby 
jim.na...@bluetreble.com escreveu:

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


 On Sun, Dec 21, 2014 at 5:18 PM, Tom Lane t...@sss.pgh.pa.us mailto:
 t...@sss.pgh.pa.us wrote:
  
   =?UTF-8?Q?Fabr=C3=ADzio_de_Royes_Mello?= fabriziome...@gmail.com
 mailto:fabriziome...@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.


Good example. Thanks.




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.


 Sorry but I don't understand what you meant. Can you explain more about
your idea?

Regards,

Fabrízio Mello


-- 
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL
 Timbira: http://www.timbira.com.br
 Blog: http://fabriziomello.github.io
 Linkedin: http://br.linkedin.com/in/fabriziomello
 Twitter: http://twitter.com/fabriziomello
 Github: http://github.com/fabriziomello