使用pg_reorg在线重新整理表

了解PostgreSQL存储原理的话，应该知道PostgreSQL在大量的更新删除插入操作后。会出现垃圾空间，索引也会变得比正常的情况大一些，直接导致的后果是，BUFFER空间浪费，IO请求增加，数据库性能下降。

一般通过vacuum来回收这些垃圾空间。但是这些还不够，为了达到更好的性能，一般需要重建表或者把表按顺序重新处理存储。通常reindex和cluster 在更新频繁的大表上操作的话会引起锁等待的问题。

下面的话介绍一下使用pg_reorg组建，解决锁等待的问题，由于作者给出的说明文档实在太少了，所以我稍微写一下，方便大家使用：

来看看作者是怎么说的（不需要加载任何锁，太好了.有点类似ORACLE的在线重定义）：

pg_reorg can re-organize tables on a postgres database without any locks so that
you can retrieve or update rows in tables being reorganized.
The module is developed to be a better alternative of CLUSTER and VACUUM FULL.

1. 下载

到pgfoundry下载源代码

http://pgfoundry.org/frs/download.php/2745/pg_reorg-1.1.3.tar.gz

2. 安装

安装的套路其实很简单，加载到源码中编译即可。

解压后拷贝源代码目录到$PG_SOURCE/contrib

su – postgres

cd $PG_SOURCE/contrib/pg_reorg-1.1.3

USE_PGXS=1 gmake

su – root

. /home/postgres/.bash_profile (can find pg_config)

cd $PG_SOURCE/contrib/pg_reorg-1.1.3

USE_PGXS=1 gmake install

3. 加载

su – postgres

cd $PG_HOME/share/contrib

psql yourdb superuser -f ./pg_reorg.sql

4. 使用范例

要使用pg_reorg vacuum full或cluster表。表必须有主键。

如：

test=> create table tbl_test1 (id int primary key,name varchar(10));
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index “tbl_test1_pkey” for table “tbl_test1″
CREATE TABLE
test=> insert into tbl_test1 (id,name) select generate_series(1,10000),’digoal’;
INSERT 0 10000
test=> delete from tbl_Test1;
DELETE 10000
test=> select pg_relation_size(‘tbl_test1′);
 pg_relation_size
——————
           450560
(1 row)

test=> insert into tbl_test1 (id,name) select generate_series(1,10000),’digoal’;
INSERT 0 10000
test=> select pg_relation_size(‘tbl_test1′);
 pg_relation_size
——————
           892928
(1 row)

test=> delete from tbl_Test1;
DELETE 10000
test=> select pg_relation_size(‘tbl_test1′);
 pg_relation_size
——————
           892928
(1 row)

test=> select pg_relation_size(‘tbl_test1_pkey’);
 pg_relation_size
——————
           245760
(1 row)

test=> insert into tbl_test1 (id,name) select generate_series(1,10000),’digoal’;
INSERT 0 10000
test=> select pg_relation_size(‘tbl_test1_pkey’);
 pg_relation_size
——————
           245760
(1 row)

test=> delete from tbl_Test1;
DELETE 10000
test=> insert into tbl_test1 (id,name) select generate_series(10001,20000),’digoal’;
INSERT 0 10000
test=> select pg_relation_size(‘tbl_test1_pkey’);
 pg_relation_size
——————
           466944
(1 row)

test=> select pg_relation_size(‘tbl_test1′);
 pg_relation_size
——————
          1335296
(1 row)

处理后回到初始大小：

postgres@db-172-16-3-33-> pg_reorg –help
pg_reorg re-organizes a PostgreSQL database.

Usage:
  pg_reorg [OPTION]… [DBNAME]
Options:
  -a, –all                 reorg all databases
  -t, –table=TABLE         reorg specific table only
  -n, –no-order            do vacuum full instead of cluster
  -o, –order-by=columns    order by columns instead of cluster keys
  -T, –wait-timeout=secs   timeout to cancel other backends on conflict.
  -Z, –no-analyze          don’t analyze at end

Connection options:
  -d, –dbname=DBNAME       database to connect
  -h, –host=HOSTNAME       database server host or socket directory
  -p, –port=PORT           database server port
  -U, –username=USERNAME   user name to connect as
  -w, –no-password         never prompt for password
  -W, –password            force password prompt

Generic options:
  -e, –echo                echo queries
  -E, –elevel=LEVEL        set output message level
  –help                    show this help, then exit
  –version                 output version information, then exit

Read the website for details. <http://reorg.projects.postgresql.org/>
Report bugs to <[email protected]>.
postgres@db-172-16-3-33-> pg_reorg -t test.tbl_test1 -o id -d test -U postgres
postgres@db-172-16-3-33-> psql test test
psql (9.0beta2)
Type “help” for help.

test=> select pg_relation_size(‘tbl_test1′);
 pg_relation_size
——————
           450560
(1 row)

test=> select pg_relation_size(‘tbl_test1_pkey’);
 pg_relation_size
——————
           245760
(1 row)

同时可以看出，INDEX的值不变的话，怎么删除插入都不会改变INDEX的大小。