Обсуждение: pg_reorg
...is an amazing tool! merlin
On Mon, Apr 18, 2011 at 4:39 PM, Merlin Moncure <mmoncure@gmail.com> wrote:
I've seen it, but catalog hacks always make me nervous. Anybody else have good / bad experience to share?
--scott
...is an amazing tool!
I've seen it, but catalog hacks always make me nervous. Anybody else have good / bad experience to share?
--scott
merlin
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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.
Eg:-
If the database is 4 gig, there should be 8gigs space.
Best Regards,
Raghavendra
EnterpriseDB Corporation
Raghavendra
EnterpriseDB Corporation
merlin
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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
On Tue, Apr 19, 2011 at 9:48 AM, Jens Wilke <jens@wilke.org> wrote:
How does it do with tables that have huge amounts (50 - 100 GB ) of TOASTed data?
On Tue, Apr 19, 2011 at 04:02:01AM +0530, Vibhor Kumar wrote:Don't be confused with the "vacuum full" term.
> > 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.
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
How does it do with tables that have huge amounts (50 - 100 GB ) of TOASTed data?
regards, Jens
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general