Thread: pg_reorg

...is an amazing tool!

merlin

On Apr 19, 2011, at 2:29 AM, Raghavendra wrote:

>
> I've seen it, but catalog hacks always make me nervous.  Anybody else have good / bad experience to share?
>
> --scott
>
>
> It is observed, double the space required for this utility.

I have used it many times. Yes it requires double space. However it has given me what I needed. Online VACUUM FULL (for
primarykey table), Online CLUSTER. 

Thanks & Regards,
Vibhor Kumar
EnterpriseDB Corporation
The Enterprise PostgreSQL Company
vibhor.kumar@enterprisedb.com
Blog:http://vibhork.blogspot.com

On Montag, 18. April 2011, Scott Mead wrote:

> I've seen it, but catalog hacks always make me nervous.  Anybody
> else have good / bad experience to share?

Hi,

yes, we used it to reduce massive bloat after a database split and
were very satisfied.
IIRC "vacuum full" mode rewrites the indexes as well.

There's a proposal about an alternative to pg_reorg on depesz' Blog:
http://www.depesz.com/index.php/2010/10/17/reduce-bloat-of-table-without-longexclusive-locks/

regards,
Jens

On Apr 19, 2011, at 3:40 AM, Jens Wilke wrote:

> yes, we used it to reduce massive bloat after a database split and
> were very satisfied.
> 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.


> There's a proposal about an alternative to pg_reorg on depesz' Blog:
> http://www.depesz.com/index.php/2010/10/17/reduce-bloat-of-table-without-longexclusive-locks/

For huge bloats, mentioned Method is slower.

Thanks & Regards,
Vibhor Kumar
EnterpriseDB Corporation
The Enterprise PostgreSQL Company
vibhor.kumar@enterprisedb.com
Blog:http://vibhork.blogspot.com

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.
Both of them lock the original table at the end of the reorganisation just for the switch.
If the lock is not granted within -T seconds, the backends holding locks are canceled.

If you run out of diskspace, it's possible to reorg table by table.
And yes, pg_reorg does only work with tables with a primary key.
This will change in future releases, IIRC

regards, Jens

On Tue, Apr 19, 2011 at 8:48 AM, Jens Wilke <jens@wilke.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