Welcome to the pg_reorg Project Home Page


This project provides two tools for PostgreSQL; pg_reorg and pg_batch.

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.

pg_batch is a SQL job executor program for PostgreSQL. It generates job list from an external SQL script file, and execute the jobs in serial or parallel. It can be used as "a better vacuumdb" with the attached script to run VACUUM.

The pg_reorg project is a PostgreSQL Community project that is a part of the pgFoundry.

The pgFoundry page for the project is at http://pgfoundry.org/projects/reorg, where you can find downloads, documentation, bug reports, mailing lists, and a whole lot more.

日本語のページはこちら。

Documentation

Execution time

Here shows the result of pg_reorg performance test. We have compared between non-fragmented table and fragmented table. Both tables have the same number of records, but fragmented table is bloated by UPDATEs.

Configuration
Category Item Details
Hardware CPU Xeon E5645 2.4GHz (12 core) * 2
Memory 32GB
Storage SAS 10,000rpm 2TB * 4
Software OS RHEL 6.2 (64bit)
DB PostgreSQL 9.3.4
pg_reorg 1.1.10
Data Scheme
CREATE TABLE test (
 id bigserial,
 rndkey bigint NOT NULL,
 dummy1 bigint NOT NULL,
 dummy2 bigint NOT NULL,
 dummy3 bigint NOT NULL,
 dummy4 bigint NOT NULL,
 dummy5 bigint NOT NULL,
 dummy6 bigint NOT NULL,
 dummy7 bigint NOT NULL,
 dummy8 timestamp NOT NULL,
 dummy9 timestamp NOT NULL,
 dummy10 timestamp NOT NULL,
 dummy11 timestamp NOT NULL,
 dummy12 timestamp NOT NULL,
 dummy13 timestamp NOT NULL,
 dummy14 timestamp NOT NULL,
 filler char(128) NOT NULL
);

CREATE INDEX test_idx_rndkey ON test (rndkey);
ALTER TABLE test CLUSTER ON test_idx_rndkey;