Skip site navigation (1) Skip section navigation (2)

Re: VACCUM FULL ANALYZE PROBLEM

From: "Iain" <iain(at)mst(dot)co(dot)jp>
To: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: "Michael Ryan S(dot) Puncia" <mpuncia(at)census(dot)gov(dot)ph>,<pgsql-performance(at)postgresql(dot)org>
Subject: Re: VACCUM FULL ANALYZE PROBLEM
Date: 2005-02-15 05:58:16
Message-ID: 001701c51323$5b1c1f80$7201a8c0@mst1x5r347kymb (view raw or flat)
Thread:
Lists: pgsql-performance
OK, that's interesting. So the original assumption that vacuum full was 
needed was completely wrong anyway.

If table re-organisation isn't required a plain vacuum would be fastest. I 
will take a guess that the next best alternative is to do the "create table 
newtable as select ... order by ..." thing and then create the indexes and 
stuff. This would reorganize the table completely. After that you have the 
cluster command, and coming in last place is vacuum full. Sound about right?

Michael, you said that a vacuum that runs for 3 days is too long, but hasn't 
given any specific requirements or limitations. Hopefully you can find 
something suitable in the alternatives listed above.

regards
Iain

----- Original Message ----- 
From: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Iain" <iain(at)mst(dot)co(dot)jp>
Cc: "Michael Ryan S. Puncia" <mpuncia(at)census(dot)gov(dot)ph>; 
<pgsql-performance(at)postgresql(dot)org>
Sent: Tuesday, February 15, 2005 2:30 PM
Subject: Re: [PERFORM] VACCUM FULL ANALYZE PROBLEM


> "Iain" <iain(at)mst(dot)co(dot)jp> writes:
>>> another  way  to speed up full vacuum?
>
>> Hmmm... a full vacuum may help to re-organize the structure of modified
>> tables, but whether this is significant or not is another matter.
>
> Actually, VACUUM FULL is designed to work nicely for the situation where
> a table has say 10% wasted space and you want the wasted space all
> compressed out.  When there is a lot of wasted space, so that nearly all
> the rows have to be moved to complete the compaction operation, VACUUM
> FULL is not a very good choice.  And it simply moves rows around, it
> doesn't modify the rows internally; so it does nothing at all to reclaim
> space that would have been freed up by DROP COLUMN operations.
>
> CLUSTER is actually a better bet if you want to repack a table that's
> suffered a lot of updates or deletions.  In PG 8.0 you might also
> consider one of the rewriting variants of ALTER TABLE.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
>               http://archives.postgresql.org 


In response to

pgsql-performance by date

Next:From: IainDate: 2005-02-15 06:55:02
Subject: Re: seq scan cache vs. index cache smackdown
Previous:From: Tom LaneDate: 2005-02-15 05:30:30
Subject: Re: VACCUM FULL ANALYZE PROBLEM

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group