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:

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.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s