Re: pg_reorg

On Tue, Apr 19, 2011 at 8:48 AM, Jens Wilke <jens(at)wilke(dot)org> wrote:
> On Tue, Apr 19, 2011 at 04:02:01AM +0530, Vibhor Kumar wrote:
>
>> > IIRC "vacuum full" mode rewrites the indexes as well.
>>
>> Till 8.4 no. From 9.0 onwards yes. However VACUUM FULL still locks the table.
>
> Don't be confused with the "vacuum full" term.
> This has nothing to do with the postgresql "vacuum full" command.
> Both pg_reorg's "vacuum full" and "cluster" mode do the pretty same thing. They rewrite the table and all their indexes. They use triggers to update the new table during the reorganisation.
> The only difference is that "cluster" does an additional order by.

pg_reorg allows you to do natural ordering (meaning, no defined
ordering), or define any ordering you like, so it's in fact far
superior to cluster in that sense. Natural ordering is the fastest
and should complete faster than cluster.

If you've ever contemplated using triggers to stage data to a table
temporarily while locking and going to town on a large bloated table,
then pg_reorg is for you -- that's more or less what it does.

merlin