Message-ID: <4D486D9A.9040404@amadensor.com>
Date: Tue, 01 Feb 2011 13:31:22 -0700
From: Grant Johnson <grant@amadensor.com>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US;
	rv:1.9.1.16) Gecko/20101227 Icedove/3.0.11
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
CC: Jon Nelson <jnelson+pgsql@jamponi.net>
Subject: Re: Any experience using "shake" defragmenter?
References: <4D45C607.4070101@vmsinfo.com> <4D485DFC.4090808@2ndquadrant.com>
	<AANLkTimE_qe1YD+t==xton3SdfXcOYL1Pk6kb0su2_Dh@mail.gmail.com>
In-Reply-To: <AANLkTimE_qe1YD+t==xton3SdfXcOYL1Pk6kb0su2_Dh@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit


> Instead of something like 'shake' (which more or less works, even
> though it doesn't use fallocate and friends) I frequently use either
> CLUSTER (which is what Greg Smith is suggesting) or a series of ALTER
> TABLE ... ALTER COLUMN... which rewrites the table.  With PG 9 perhaps
> VACUUM FULL is more appropriate.  Of course, the advice regarding
> using 'shake' (or any other defragmenter) on a "live" postgresql data
> directory is excellent - the potential for causing damage if the
> database is active during that time is very high.
>
>    
I agree that unless it makes sure there are no open file handles before 
moving the file, there is a high chance of corrupting data, and if it 
does check, there is little chance it will do anything useful on a live 
DB, since it will skip every open file.

Does vacuum full rewrite the whole table, or only the blocks with free 
space?   If it only rewrites the blocks with free space, the only 
solution may be exclusive table lock, alter table to new name, create 
old table name as select * from new table name.  I also like the cluster 
idea, but I am not sure if it rewrites everything, or just the blocks 
that have out of order rows, in which case, it would not work well the 
second time.