April 2011 Archive


| Keine Kommentare
pg_reorg rewrites tables "online", that means without an appreciable lock like VACUUM FULL or CLUSTER. Unfortunately the documentation uses these terms, which leads to confusion about locking problems.
Don't be confused with the "vacuum full" term. This has nothing to do with the postgresql 8.4 "vacuum full" command. (Vacuum full was completly reimplemented in Postgres 9.0 but still does lock the table)
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, which probably slows down this mode.
Both of them lock the original table at the end of the reorganisation process just for the switch of the rewritten to the active table, which is done by updates of the sytem catalogue. If the lock is not granted within -T seconds, the backends holding locks are canceled and the switch is performed.
It's possible to reorg single tables or the whole database at once.

Limitations of pg_reorg are:
- Tables need to have primary keys.
- Additional diskspace is required during the rewrite.

I used pg_reorg to reduce bloat after Database splits, to shrink Databases about 50%.
This was done using the "unordered" mode.
pg_reorg worked to my complete satisfaction for this case.

Über dieses Archiv

Diese Seite enthält alle Einträge von Jens Wilkes Blog von neu nach alt.

March 2011 ist das vorherige Archiv.

November 2011 ist das nächste Archiv.

Aktuelle Einträge finden Sie auf der Startseite, alle Einträge in den Archiven.


Hier wird OpenID akzeptiert Mehr über OpenID erfahren