pg_repackを用いたテーブルおよびインデックスの再編成

PostgreSQLでは、ロングトランザクションや大規模なバッチ処理による更新の影響などによってテーブルが肥大化する可能性があります。テーブルの肥大化はデータベースの性能劣化の原因となるため解消することが望ましいです。
本ページでは、pg_repackを用いてテーブルおよびインデックスの再編成を行う方法について紹介します。

pg_repackのメリット
pg_repackの使い方
動作確認

pg_repack機能概要についてはこちらをご覧ください。
※システムの処理特性によっては短時間の排他ロック取得であっても動作に影響が出る可能性があります。システム運用中に実行する場合は十分に検証を行った上で実行をご検討ください。

pg_repackのメリット

テーブルおよびインデックスの再編成を行う方法にはPostgreSQL本体のVACUUM FULLやCLUSTERコマンドがありますが、これらは処理中に継続的な排他ロックがかかります。一方、pg_repackの場合は処理中の排他ロックがごく短時間であるため、VACUUM FULLやCLUSTERと同等の処理をシステム運用中にも実行することができます。
以上のことをまとめると表1のようになります。

表1 特徴比較
	VACUUM FULL(CLUSTER)	pg_repack
排他ロックの発生	大	小
システム継続性	×	○

pg_repackの使い方

初期設定

pg_repackを使用するにあたり必要な初期設定を行います。
pg_repackを利用したいデータベース上で、pg_repackエクステンションをPostgreSQLに登録します。

postgres=# CREATE EXTENSION pg_repack;

この操作はpg_repackを利用したいデータベースごとに行う必要があります。

再編成オプション

テーブルおよびインデックスの再編成に関するオプションについて説明します。
pg_repackコマンドの基本の書式は以下です。

$ pg_repack [OPTION]...

(例)　$ pg_repack -n -d dbname -t public.test_table　(指定したテーブルでVACUUM FULL相当の処理を行う)

主なオプションを表2に示します。

表2 主な再編成オプション
オプション	説明
-t [テーブル名] または -t [スキーマ名.テーブル名]	指定したテーブルを再編成する
-d [データベース名]	指定したデータベース内のテーブルを再編成する
-c [スキーマ名]	指定したスキーマに存在する全てのテーブルを再編成する
-o [列名]	テーブル再編成の際、指定列に従った並び替えも行う
-n	テーブル再編成の際、レコード並び替えは行わない
-N	実際の処理は行わずに実施内容のメッセージを出力する
-i [インデックス名]	指定したインデックスのみを再編成する

コマンド例

実際のコマンド例を紹介します。

・VACUUM FULL相当の再編成
-nオプションをつけるとレコードの並び替えは行いません。以下のコマンドは「VACUUM FULL test_table」に相当します。

$ pg_repack -n -t test_table

・CLUSTER相当の再編成
-oオプションでは並び替えの基準となる列名を指定します。以下の例では、テーブル再編成の際、id列に従ったレコードの並び替えも行います。このコマンドは「CLUSTER test_table USING [id列のインデックス名]」に相当します。

$ pg_repack -t test_table -o id

・コマンドの実行内容確認
-Nオプションでは実際の処理は行わずに実施内容のメッセージだけを出力するため、実行内容の事前確認に利用できます。

$ pg_repack -N -c user_schema -c public

動作確認

テーブルの再編成によってテーブルサイズおよびインデックスサイズが小さくなっていることを確認します。

1. テストテーブル作成

$ psql
postgres=# CREATE TABLE test_table (
id integer PRIMARY KEY,
name varchar(128),
email varchar(128),
ver integer,
update_time timestamp
);

2. レコード追加
作成したテスト用テーブルに200万件のレコードを追加します。

postgres=# INSERT INTO test_table (id, name, email, ver, update_time)
SELECT i_tmp, format('テスト用データ_ユーザー%s', i_tmp),
format('user_id_%s@email.co.jp', i_tmp), 1, clock_timestamp()
FROM generate_series(1,2000000) as i_tmp;

3. レコード更新
2. で追加した200万件のレコードを更新します。

postgres=# UPDATE test_table SET ver = 2, update_time = clock_timestamp();

4. レコード削除
全レコード200万件のうち100万件を削除します。

postgres=# DELETE FROM test_table WHERE (id BETWEEN 1 AND 1000000);

5. テーブルサイズ確認

postgres=# SELECT pg_size_pretty(pg_table_size('test_table')) AS テーブルサイズ,
pg_size_pretty(pg_indexes_size('test_table')) AS インデックスサイズ,
pg_size_pretty(pg_total_relation_size('test_table')) AS 合計サイズ;
テーブルサイズ | インデックスサイズ | 合計サイズ
----------------+--------------------+------------
446 MB | 86 MB | 532 MB
(1 行)

6. pg_repack実行

$ date; pg_repack -n -t test_table; date
2023年 4月 5日水曜日 16:37:46 JST
INFO: repacking table "public.test_table"
2023年 4月 5日水曜日 16:37:50 JST

7. テーブルサイズ確認

postgres=# SELECT pg_size_pretty(pg_table_size('test_table')) AS テーブルサイズ,
pg_size_pretty(pg_indexes_size('test_table')) AS インデックスサイズ,
pg_size_pretty(pg_total_relation_size('test_table')) AS 合計サイズ;
テーブルサイズ | インデックスサイズ | 合計サイズ
----------------+--------------------+------------
112 MB | 21 MB | 133 MB
(1 行)

pg_repackによるテーブルの再編成でテーブルおよびインデックスのサイズが小さくなったことが確認できました。

BluStellar（ブルーステラ）

製品・ソリューション

業種・業務

企業情報

サイト内の現在位置

PostgreSQL