Thread: Vacuum non-clustered tables only
I think I know the answer to this, but... Is there a semi-easy way vacuum all tables in a database *except* those that are clustered? (VACUUM [full]; CLUSTER;) is a huge waste of time and resources when you have clustered tables. If there isn't a way to do this, can we consider it a feature request? Perhaps "VACUUM unclustered" or something? -Glen
Glen Parker wrote:
> I think I know the answer to this, but...
>
> Is there a semi-easy way vacuum all tables in a database *except* those
> that are clustered?
Not that I know of.
J
>
> (VACUUM [full]; CLUSTER;) is a huge waste of time and resources when you
> have clustered tables. If there isn't a way to do this, can we consider
> it a feature request?
>
> Perhaps "VACUUM unclustered" or something?
>
>
> -Glen
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/
On Wed, May 16, 2007 at 03:40:27PM -0700, Glen Parker wrote: > I think I know the answer to this, but... > > Is there a semi-easy way vacuum all tables in a database *except* those > that are clustered? You could query for tables that aren't clustered and use that to build a list of VACUUM commands, but a better question is... why? If you create dead tuples in a CLUSTERed table you still need to vacuum it eventually... or do you just cluster the database often enough that it doesn't matter? -- Jim Nasby decibel@decibel.org EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
Glen Parker wrote: > I think I know the answer to this, but... > > Is there a semi-easy way vacuum all tables in a database *except* those > that are clustered? > > (VACUUM [full]; CLUSTER;) is a huge waste of time and resources when you > have clustered tables. If there isn't a way to do this, can we consider > it a feature request? > > Perhaps "VACUUM unclustered" or something? A couple of thoughts: 1. You shouldn't routinely be running VACUUM FULL on a recent installation. 2. Autovacuum should effectively do this, assuming the clustered table isn't being updated. -- Richard Huxton Archonet Ltd
Richard Huxton wrote: >> Perhaps "VACUUM unclustered" or something? > > A couple of thoughts: > > 1. You shouldn't routinely be running VACUUM FULL on a recent installation. In my experience, some tables still must be VACUUM FULL'd from time to time. I switched to clustering because it's much more efficient. I don't actually do full vacuums anymore. Not to mention, of course, that keeping a table clustered usually has very favorable performance benefits. We have a nice big maintenance window every Sunday night/early morning, so I have a fair amount of latitude on how I beat the DB up. I do clustering, vacuuming, reindexing, and some data maintenance during that time. I'm just looking to lose some redundancy. > 2. Autovacuum should effectively do this, assuming the clustered table > isn't being updated. These are heavily updated tables. Plain vacuum isn't enough, and the autovacuum facility isn't functional enough for me yet. If autovacuum worked for me, and if clustering updated statistics (does it yet?), I would probably be set. -Glen
Glen Parker wrote:
>
>> 2. Autovacuum should effectively do this, assuming the clustered table
>> isn't being updated.
>
> These are heavily updated tables. Plain vacuum isn't enough, and the
> autovacuum facility isn't functional enough for me yet.
Can you elaborate on this?
Joshua D. Drake
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/
Joshua D. Drake wrote: > Glen Parker wrote: >> >>> 2. Autovacuum should effectively do this, assuming the clustered >>> table isn't being updated. >> >> These are heavily updated tables. Plain vacuum isn't enough, and the >> autovacuum facility isn't functional enough for me yet. > > Can you elaborate on this? I have nothing resembling a useful answer in regards to lazy vacuum vs. full vacuum. It seems that there have been times when vacuum started taking too long, and then full vacuum took forever but fixed the problem. I haven't dug enough to provide anything better than that. Cluster, however, always seems to perform quite well for me. With regards to auto vacuum, it isn't useful for me because is seems to put too much strain on the machine during peak hours, and there is not yet a stomachable way to control when auto vacuum can run. I'll be reviewing it again as soon as I can get a version 8.3.1+ install into production. But again, even with auto vacuum in place, I'd prefer not to spend time and cycles doing a vacuum when a cluster is going to happen within minutes or hours. -Glen
On Wed, 2007-05-16 at 15:40 -0700, Glen Parker wrote:
> Is there a semi-easy way vacuum all tables in a database *except*
> those that are clustered?
Yes, it can be done "semi-easily". Here's an example:
select N.nspname,relname
from pg_class C
join pg_namespace N on C.relnamespace=N.oid
where relkind='r' and not exists
(select * from pg_index I
where C.oid=I.indrelid and I.indisclustered);
Many of us build statements in SQL itself. For instance, replace the
first line above with
select 'vacuum '||N.nspname||'.'||C.relname||';' as vacuum_cmd
and now the query returns executable statements. You probably want to
restrict by namespace/schema name as well. If you put all of that into
a view, it's then easy to do something like
$ psql -c 'select vacuum_cmd from view' | psql -aX
which uses one connection to select the script, and another to execute
it.
Good luck,
Reece
--
Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0
> Yes, it can be done "semi-easily". Here's an example: Ah!! Thank you. Now that someone else has done the leg work it'll be better than "semi-easy" for me :D -Glen