pg_reorg

| 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.

Jetzt kommentieren

Über diese Seite

Diese Seite enthält einen einen einzelnen Eintrag von Jens Wilke vom April 21, 2011 11:42 PM.

Grundlagen der PostgreSQL Administration - Vortrag bei der [Be]LUG ist der vorherige Eintrag in diesem Blog.

PgConf.DE 2011 ist der nächste Eintrag in diesem Blog.

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

Kategorien

Hier wird OpenID akzeptiert Mehr über OpenID erfahren