Re: Vacuum full takes forever

pieterjan(dot)savat(at)barclab(dot)com (Pieter-Jan Savat) writes:
> I'm facing the following problem.
>
> I have a postgres 8.0 DB with a table 'results' containing 6.000.000
> records.
> This table has 16 indexes. Each one basically created to speed up
> different queries.
>
> Because of some glitch in the system there has never been a VACUUM
> FULL on this table.
> When I try to do a full vacuum (on a dual-processor, 2GB RAM, ...) it
> takes forever. I started the
> vacuum at 6pm and 15 hours later it was still going on.
> Just before starting vacuum full, I did a vacuum analyze (which took
> about 15 minutes). I also
> checked the amount of diskspace used for the indexes => 33% of 11
> available GigaBytes.
> After killing the vacuum full my diskspace for the indexes has
> increased to 41% of the 11 available GB.
>
> So does anyone know what I can do to fully vacuum my table? Or to at
> least decrease the amount of diskspace used?

Two choices offer themselves:

1. Drop all indices.
Then VACUUM FULL the table.
Then recreate the indices.

2. CLUSTER the table based on one of the indices.

None of this is going to be pretty; it'll take hours.

1. and 2. are nearly equivalent; the conspicuous difference is that
1. will give you feedback along the way as it completes one step or
another.
--
(format nil "~S(at)~S" "cbbrowne" "acm.org")
http://www.ntlug.org/~cbbrowne/sap.html
Rules of the Evil Overlord #78. "I will not tell my Legions of Terror
"And he must be taken alive!" The command will be: ``And try to take
him alive if it is reasonably practical.''"
<http://www.eviloverlord.com/>