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@sss.pgh.pa.us>
To: "Iain" <iain@mst.co.jp>
Cc: "Michael Ryan S. Puncia" <mpuncia@census.gov.ph>;
<pgsql-performance@postgresql.org>
Sent: Tuesday, February 15, 2005 2:30 PM
Subject: Re: [PERFORM] VACCUM FULL ANALYZE PROBLEM
> "Iain" <iain@mst.co.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