Thread: Vacuum non-clustered tables only

Vacuum non-clustered tables only

From
Glen Parker
Date:
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


Re: Vacuum non-clustered tables only

From
"Joshua D. Drake"
Date:
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/


Re: Vacuum non-clustered tables only

From
"Jim C. Nasby"
Date:
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)

Re: Vacuum non-clustered tables only

From
Richard Huxton
Date:
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

Re: Vacuum non-clustered tables only

From
Glen Parker
Date:
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



Re: Vacuum non-clustered tables only

From
"Joshua D. Drake"
Date:
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/


Re: Vacuum non-clustered tables only

From
Glen Parker
Date:
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


Re: Vacuum non-clustered tables only

From
Reece Hart
Date:
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


Re: Vacuum non-clustered tables only

From
Glen Parker
Date:
> 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