Thread: How long - Vacumm full - 10 million to 90,000

Good morning,

A question about VACUUM FULL. The docs say:

VACUUM FULL is recommended for cases where you know you have deleted the 
majority of rows in a table, so that the steady-state size of the table 
can be shrunk substantially with VACUUM FULL's more aggressive approach. 
Use plain VACUUM, not VACUUM FULL, for routine vacuuming for space recovery.

I have a table from around 10 million to 90,000, after deletion, I tried 
to use vacuum full, but it seems that it takes forever to finish. Could 
anyone tell me how long it will take to finish the Recovering disk space 
please?

Thanks a lot!

On Thu, May 29, 2008 at 11:33:36AM -0400, Emi Lu wrote:

> I have a table from around 10 million to 90,000, after deletion, I tried to 
> use vacuum full, but it seems that it takes forever to finish. Could anyone 
> tell me how long it will take to finish the Recovering disk space please?

A really really long time.  You might actually be better off to dump
the table and restore it from that.

A

-- 
Andrew Sullivan
ajs@commandprompt.com
+1 503 667 4564 x104
http://www.commandprompt.com/

Emi Lu wrote:
> ...
> I have a table from around 10 million to 90,000, after deletion, I 
> tried to use vacuum full, but it seems that it takes forever to 
> finish. Could anyone tell me how long it will take to finish the 
> Recovering disk space please?
No. Probably a looooonnnnnng time.

In a case like this where you are retaining less than 1% of your records 
you are likely to see far, far faster results using CLUSTER - with the 
added advantage that CLUSTER will create fresh indexes for you, too.

Alternately, you can:
CREATE TABLE xxx as SELECT * from yourtable;
TRUNCATE yourtable;
INSERT into yourtable SELECT * from xxx;
DROP TABLE xxx;

If you are so close to out-of-space on your disk that you don't have the 
room those 90,000 records will require, you may have to dump/restore 
using another machine.

Cheers,
Steve