PostgreSQL のテーブルを無停止で最適化する
PostgreSQL は追記型アーキテクチャを採用していますので、データが大量に削除された場合には大量の空き領域が発生して次第にディスク領域を圧迫していきます。特に24時間365日稼動システムを運用している場合にはデータベースは停止できませんね。そのまま放置すれば当然ながらディスク上でデータはバラバラになった状態で、結果としてデータを検索する速度が極端に低下します。これを回避するために pg_reorg というツールを利用しましょう。このツールを使えば無停止で無駄な空き領域を一気に開放することが出来ると共にパフォーマンスアップにも効果があります。
https://github.com/ossc-db/pg_reorg
例えば次のように大量のデータを削除したとします。
=> DELETE FROM big_table WHERE id < 420547597; DELETE 1527822 Time: 7703.905 ms => \q
そして pg_reorg コマンドを次のように実行してあげると、一時的に別のテーブルを作成し旧テーブルからデータを全部コピーしてインデックス含め全て再構築して、最終的に古いテーブルを破棄して新規テーブルに同じテーブル名・テーブル構造・インデックス・外部キーを保ちつつ入替えてくれます。
さらに再編成中に発生した変更も更新ログ情報として漏らさず再編成してくれます。これまで無停止システムの本番環境で何度となく様々なテーブルの再編成を pg_reorg コマンドで実施してきましたが、これまで問題が発生したことは全くありません。また VACUUM FULL コマンドのようにロックをかけないので安心して実行できます。
$ time pg_reorg -h 127.0.0.1 -p 5432 -U postgres -d hogehoge -e -no-order --table public.big_table
LOG: (query) SET statement_timeout = 0
LOG: (query) SET search_path = pg_catalog, pg_temp, public
LOG: (query) SET client_min_messages = warning
LOG: (query) SELECT * FROM reorg.tables WHERE relid = $1::regclass
LOG: (param:0) = public.big_table
LOG: (query) BEGIN ISOLATION LEVEL READ COMMITTED
LOG: (query) SET LOCAL statement_timeout = 100
LOG: (query) LOCK TABLE public.big_table IN ACCESS EXCLUSIVE MODE
LOG: (query) RESET statement_timeout
LOG: (query) SELECT reorg.conflicted_triggers($1)
LOG: (param:0) = 16907
LOG: (query) CREATE TYPE reorg.pk_16907 AS (name integer)
LOG: (query) CREATE TABLE reorg.log_16907 (id bigserial PRIMARY KEY, pk reorg.pk_16907, row public.big_table)
LOG: (query) CREATE TRIGGER z_reorg_trigger BEFORE INSERT OR DELETE OR UPDATE ON public.big_table FOR EACH ROW EXECUTE PROCEDURE reorg.reorg_trigger('INSERT INTO reorg.log_16907(pk, row) VALUES( CASE WHEN $1 IS NULL THEN NULL ELSE (ROW($1.name)::reorg.pk_16907) END, $2)')
LOG: (query) SELECT reorg.disable_autovacuum('reorg.log_16907')
LOG: (query) COMMIT
LOG: (query) BEGIN ISOLATION LEVEL SERIALIZABLE
LOG: (query) SELECT set_config('work_mem', current_setting('maintenance_work_mem'), true)
LOG: (query) SELECT reorg.array_accum(transactionid) FROM pg_locks WHERE locktype = 'transactionid' AND pid <> pg_backend_pid()
LOG: (query) DELETE FROM reorg.log_16907
LOG: (query) CREATE TABLE reorg.table_16907 WITH (oids=false) TABLESPACE pg_default AS SELECT id,name FROM ONLY public.big_table
LOG: (query) SELECT reorg.disable_autovacuum('reorg.table_16907')
LOG: (query) COMMIT
LOG: (query) SELECT indexrelid, reorg.reorg_indexdef(indexrelid, indrelid), indisvalid, pg_get_indexdef(indexrelid) FROM pg_index WHERE indrelid = $1
LOG: (param:0) = 16907
LOG: (query) CREATE INDEX index_65471 ON reorg.table_16907 USING btree (name)
LOG: (query) CREATE UNIQUE INDEX index_62417 ON reorg.table_16907 USING btree (id)
LOG: (query) SELECT reorg.reorg_apply($1, $2, $3, $4, $5, $6)
LOG: (param:0) = SELECT * FROM reorg.log_16907 ORDER BY id LIMIT $1
LOG: (param:1) = INSERT INTO reorg.table_16907 VALUES ($1.*)
LOG: (param:2) = DELETE FROM reorg.table_16907 WHERE (name) = ($1.name)
LOG: (param:3) = UPDATE reorg.table_16907 SET (id, name) = ($2.id, $2.name) WHERE (name) = ($1.name)
LOG: (param:4) = DELETE FROM reorg.log_16907 WHERE id <= $1
LOG: (param:5) = 1000
LOG: (query) SELECT pid FROM pg_locks WHERE locktype = 'transactionid' AND pid <> pg_backend_pid() AND transactionid = ANY($1)
LOG: (param:0) = {}
LOG: (query) BEGIN ISOLATION LEVEL READ COMMITTED
LOG: (query) SET LOCAL statement_timeout = 100
LOG: (query) LOCK TABLE public.big_table IN ACCESS EXCLUSIVE MODE
LOG: (query) RESET statement_timeout
LOG: (query) SELECT reorg.reorg_apply($1, $2, $3, $4, $5, $6)
LOG: (param:0) = SELECT * FROM reorg.log_16907 ORDER BY id LIMIT $1
LOG: (param:1) = INSERT INTO reorg.table_16907 VALUES ($1.*)
LOG: (param:2) = DELETE FROM reorg.table_16907 WHERE (name) = ($1.name)
LOG: (param:3) = UPDATE reorg.table_16907 SET (id, name) = ($2.id, $2.name) WHERE (name) = ($1.name)
LOG: (param:4) = DELETE FROM reorg.log_16907 WHERE id <= $1
LOG: (param:5) = 0
LOG: (query) SELECT reorg.reorg_swap($1)
LOG: (param:0) = 16907
LOG: (query) COMMIT
LOG: (query) BEGIN ISOLATION LEVEL READ COMMITTED
LOG: (query) SELECT reorg.reorg_drop($1)
LOG: (param:0) = 16907
LOG: (query) COMMIT
LOG: (query) BEGIN ISOLATION LEVEL READ COMMITTED
LOG: (query) ANALYZE public.big_table
LOG: (query) COMMIT
real 0m9.541s
user 0m0.001s
sys 0m0.004s
但し注意点としては、テーブルのコピーをデータ及びインデックスも含めて別テーブルで作成しますので、再編成しようとしているテーブルと同じ空き領域が必要となります。
ですので実行前にはディスク容量の空きが十分であることを確認しましょう。もう１つの注意点は再編成中には絶対にテーブル構造を変更するような DDL を実行しないようにしましょう。最悪の場合テーブルが消失してしまう可能性があります。
なお pg_reorg は今後メンテナンスされず、下記 pg_repack プロジェクトが後継となるそうです。pg_repack はインデックスのみの再構築が出来るなど pg_reorg に比して機能が多いのですが、私が試した限りでは pg_repack はまだまだ問題があって本番システムへの適用は見送っています。実際にテスト環境でインデックスのみを pg_repack を用いて再構築している最中に、プログラム自体がダウンしてしまいました。。。今後に期待です。
https://github.com/reorg/pg_repack
