In any OLTP 24*7 database there is a constant need to maintain the database to meet performance metrics. As the database and tables grow in size it gets difficult to maintain the tables while production is on. As DBA’s we try to script vacuum analyze on tables during off peak hours and that does update the pg_stats to help in performance. But how do we get rid of all the bloat due to updates and rollbacks? Also how do we rebuild our indexes in such a scenario when downtime is not possible at all?
Well the answer is pg_reorg. pg_reorg re-organizes tables on a postgres database without any locks so that you can retrieve or update rows in tables being reorganized. It places the data of a table in a temp table and then recreates the indexes on the temp table. Thus, leaving the primary production table unlocked for production use. This is a very useful tool that reorganizes the tables in an instant without impacting production. Make sure you have twice the space available for the table that you are performing a pg_reorg on.
pg_reorg can be download from here. They maintain rpm and source installers.
Once you have pg_reorg downloaded follow the steps below to install it:
$ cd pg_reorg $ make $ su $ make install
Next you need to run the following pg_reorg script to register it with your database
$ psql -f $PGSHARE/contrib/pg_reorg.sql -d your_database
Now you are all set and let the games begin:
$ pg_reorg --no-order --table test1 -d mydb
This will reorg the test1 table in the database mydb