Sunday, October 10, 2010

PG_REORG Utility for VACUUM FULL online

pg_reorg is a utility made by NTT for reorganizing the table structure.

Concept is simple, if you have all the require pointers and data in same page, then accessing those is much more faster. This is what pg_reorg provides to a user.

Following are some options, which pg_reorg provides.
-o [ —order-by] columns:
This option makes pg_reorg to oraganise the table data as per the mentioned column. At the backend pg_reorg will creates a new table using CTAS and SELECT Query include ORDER BY clause with columns mentioned with -o.
-n [—no-order] tablename:
When this option is being used, then pg_reorg, does the VACUUM FULL ONLINE. Now, question is how it must be doing. Simple Concept, create a new table using CTAS and create a trigger on current table to track the DML. As the New table got created play those tracked DML on new table. It works well. This option is only for table which has primary key.

pg_reorg by default does the CLUSTER of tables and it follows same concept, i.e without locking table do the CLUSTER.

After performing all the options, pg_reorg does the ANALYZE on the table.

Following are some elog information, which it performs at backend:
elog(DEBUG2, "---- reorg_one_table ----");
elog(DEBUG2, "target_name    : %s", table->target_name);
elog(DEBUG2, "target_oid     : %u", table->target_oid);
elog(DEBUG2, "target_toast   : %u", table->target_toast);
elog(DEBUG2, "target_tidx    : %u", table->target_tidx);
elog(DEBUG2, "pkid           : %u", table->pkid);
elog(DEBUG2, "ckid           : %u", table->ckid);
elog(DEBUG2, "create_pktype  : %s", table->create_pktype);
elog(DEBUG2, "create_log     : %s", table->create_log);
elog(DEBUG2, "create_trigger : %s", table->create_trigger);
elog(DEBUG2, "create_table   : %s", table->create_table);
elog(DEBUG2, "delete_log     : %s", table->delete_log);
elog(DEBUG2, "lock_table     : %s", table->lock_table);
elog(DEBUG2, "sql_peek       : %s", table->sql_peek);
elog(DEBUG2, "sql_insert     : %s", table->sql_insert);
elog(DEBUG2, "sql_delete     : %s", table->sql_delete);
elog(DEBUG2, "sql_update     : %s", table->sql_update);
elog(DEBUG2, "sql_pop        : %s", table->sql_pop);
Interesting Hunh.

Some Comparison between clusterdb and pg_reorg is given in below link:
http://reorg.projects.postgresql.org/index.html

With everything, DBA has to take care of few things:
While pg_reorg is going on one table, the DBA should not let anyone to execute DDL Changes and Index on the currently reorganizing table.

No comments:

Post a Comment