パーティショニング : 使い方
NTT オープンソースソフトウェアセンタ 板垣 貴裕
パーティショニングの実際の使い方を解説します。PostgreSQL では、パーティショニングを継承と CHECK制約の組み合わせで実現しています。WHERE 句に指定した絞り込み条件を制約に照らし合わせ、検索対象が含まれないテーブルは処理の早期の段階で検索候補から除外します。この処理により、パーティションの効率的な検索ができるようになっています。パーティショニング機能は 8.1 以降のバージョンで利用できますが、最近のバージョンではさらに手間がかからなくなっています。
この記事ではパーティショニングの具体的な使い方について説明しています。概要については『パーティショニング : 用途と利点』を参照してください。
パーティショニングを構成する要素
PostgreSQLのパーティショニングは専用の組み込み機能ではなく、「継承」「CHECK制約」「トリガ」を組み合わせて実現します。若干手間はかかりますが、そのぶん柔軟な構成が可能になっています。最初にそれらの構成要素について簡単に紹介します。
継承
オブジェクト関係データベース管理システム (ORDBMS) である PostgreSQL はテーブルの『継承』という機能を持っています。テーブルに親子関係を定義し、子は親の列構成を引き継ぎます（列の追加もできます）。親テーブルへのクエリは子テーブルも含むように自動的に展開されます。オブジェクト指向を取り入れたスキーマ設計のための機能ですが、最近ではもっぱらテーブル・パーティショニングのために利用されているようです。
CHECK制約
パーティションに含まれるデータの範囲の定義には『CHECK制約』を使用します。PostgreSQLでは分割方法について、特に「レンジ」「リスト」「ハッシュ」などの区別をせず、単純に WHERE 句の条件が制約に適合するかのみで判定します。柔軟性はあるものの、判定が順番に行われるためパーティション数に比例する処理コストがかかるのが難点です。分割数は100個に留めたほうが良いでしょう。
トリガ (TRIGGER)
PostgreSQL 8.2 以降であれば、親テーブルに対する SELECT, UPDATE, DELETE は自動的に子テーブルを展開し、絞込みを行ってくれます。ただし INSERT については何もしないので、親テーブルに『トリガ』を定義し、パーティション・キーの値に基づいて挿入先を振り分ける必要があります。
パーティショニングの使い方 : 概要
最初にパーティショニングの使い方の概要を示します。実際に必要な操作は PostgreSQL のバージョンごとに異なるため、詳細を参照してください。
初期設定
親テーブルに加えて複数の子テーブルを作成するのに加え、親テーブルにトリガ (8.1 以前ではさらにルール) を定義する必要があります。
- 親テーブルを作成します。
- 子テーブルを作成します。
- (8.2 以前) 子テーブルにインデックスを定義します。
- INSERT トリガ用の関数を定義します。
- 親テーブルに INSERT トリガを定義します。
- (8.1 以前) 親テーブルに UPDATE, DELETE ルールを定義します。
- 設定パラメータ constraint_exclusion に on または partition を設定します。
データの投入は親テーブルに対して INSERT および COPY FROM を使って行います。ただ、もしデータが予めパーティションごとに分かれているのであれば、子テーブルを直接指定して投入する方が振り分け処理が要らなくなるので高速です。
パーティションの追加
パーティショニングを追加する手順は以下のようになります。削除の場合は逆順の操作です。
- 子テーブルを作成します。
- (8.2 以前) 子テーブルにインデックスを定義します。
- (8.3 以前) INSERT 用の関数を更新します。
パーティションの変更はオンラインのまま行えますが、追加や削除の対象のパーティションをテーブル本体から切り離して操作したい場合には継承関係を後から変更する『ALTER TABLE [NO] INHERIT』文を使うこともできます。特にある程度の初期データを投入した上でパーティションを追加したい場合に役立ちます。
パーティショニングの使い方 : 詳細
バージョンを追うごとに少しずつ機能が追加されているため、バージョンに手順が異なります。当然ながら新しいバージョンほど必要な手順は少なく手間がかからないので、できるかぎり新しいバージョンを使ってください。
| ver. | SELECT | UPDATE | DELETE | INSERT | インデックス定義 |
|---|---|---|---|---|---|
| 8.1 | 自動 (2) | 手動 (1) | 手動 (1) | 静的SQL (3) | 手動 (5) |
| 8.2 | 自動 (2) | 自動 (2) | |||
| 8.3 | 自動 (6) | ||||
| 8.4 | 動的SQL (4) |
1. UPDATE, DELETE へのルールの定義
8.1 以前では UPDATE, DELETE に対して『ルール (RULE)』の定義が必要です。古いバージョンでは検索条件による自動的な絞込みが行われないため、検索を効率化するためにルールを用いて不要なパーティションを除外してやる必要があるのです。 この作業には手間がかかると思いますので、作業が自動化された 8.2 以降を使うことをお勧めします。
2. constraint_exclusion による SELECT, UPDATE, DELETE の自動化
設定パラメータ constraint_exclusion が追加され、SELECT は 8.1 以降で、UPDATE, DELETE は 8.2 以降で、絞込みが自動的に行われるようになりました。constraint_exclusion = on を設定します。ただし、デフォルトの設定は off になっていますので、パーティショニングを使う場合には on に変更しておいてください。
8.4 では設定値に partition が追加されており、常に constraint_exclusion = partition で運用できます。この設定がデフォルトになっています。
3. 静的SQLによる INSERT の分配
8.3 以前では、IF 文を羅列した関数を作成します。具体的な PL/pgSQL の書式は『8.3 文書 パーティショニングの実装』を参考にしてください。ただし、パーティションの追加／削除／定義変更をした際には、登録したトリガ関数を書き直す必要があります。書き直しの回数を減らすため、将来必要になるパーティションを予め作成しておくほうが良いかもしれません。
また、8.2 以前のマニュアルでは INSERT の分配に ルールを使うよう例示されていますが、以下の利点があるためトリガを使うことをお勧めします。マニュアルも 8.3 以降はトリガを使うよう変更されています。
- トリガのほうがルールよりも 2～5 倍ほど高速です。特にパーティション数が増えると顕著です。
- 親テーブルへの COPY FROM は、ルールでは対応できませんが、トリガならば対応できます。
4. 動的SQLによる INSERT の分配
8.4 以降は PL/pgSQL に EXECUTE USING 構文が追加されたため、これを使うと楽ができます。マニュアルにある例を EXECUTE USING を使って書き直すと以下のようになります。このトリガはパーティションを追加／削除しても変更する必要が無いので、メンテナンスの手間が軽減できます。
=# CREATE FUNCTION measurement_insert_trigger() RETURNS TRIGGER AS
$$
DECLARE
part text; -- パーティション・テーブルの名前
BEGIN
-- キー値から計算 : measurement_y[年]m[月]
part := 'measurement_' || to_char(new.logdate, '"y"YYYY"m"MM');
-- new を渡す
EXECUTE 'INSERT INTO ' || part || ' VALUES(($1).*)' USING new;
RETURN NULL;
END;
$$
LANGUAGE plpgsql;
5. 追加パーティションのインデックスは別定義
8.2 以前では子テーブルの作成後、子テーブルのインデックスを別に定義する必要があります。CREATE TABLE INHERITS は列の定義のみをコピーし、インデックスの定義は複製してくれません。子テーブルの作成後、インデックスは「ALTER TABLE ADD PRIMARY KEY」や「CREATE INDEX」で別に定義する必要があります。
WITH 句で指定するパラメータ (fillfactor) はコピーされないことに注意してください。子テーブルを定義する際に明記する必要があります。
=# CREATE TABLE partition (
CHECK (下限 <= partkey AND partkey < 上限)
)
INHERITS (parent)
WITH (fillfactor=...);
=# ALTER TABLE partition ADD PRIMARY KEY (id);
=# CREATE INDEX partition_key ON partition (...);
6. CREATE TABLE LIKE 構文によるインデックス定義の自動化
8.3 以降は CREATE TABLE LIKE 構文が追加されため、列の定義と共にインデックスの定義も半自動でコピーできるようになりました。親テーブルにもインデックスを定義しておき、それをテンプレートのように使うことで定義をコピーできます。インデックス定義のほかにも列のデフォルト値や制約もコピーできます。一般的な用途ではすべてをコピーして構わないと思います。
WITH 句で指定するパラメータは相変わらずコピーされませんが、INCLUDING INDEX で複製したインデックスの WITH パラメータは親テーブルでの定義を引き継ぎます。
=# CREATE TABLE partition (
LIKE parent INCLUDING INDEXES
INCLUDING DEFAULTS
INCLUDING CONSTRAINTS,
CHECK (下限 <= partkey AND partkey < 上限)
)
INHERITS (parent)
WITH (fillfactor=...);
このとき以下の NOTICE が出力される場合がありますが無視して構いません。LIKE と INHERITS の両方で列定義がコピーされ、同じ名前なので1つにまとめた旨が報告されています。
NOTICE: merging column "列名" with inherited definition
まとめ
テーブル・パーティショニングの効果と使い方を紹介しました。性能や管理の面でメリットがありますので、大規模なデータを扱う機会があれば採用を検討してみてください。定期的に一括削除されるデータに利用するだけであっても、削除後の VACUUM が不要になるなど、大きな利点があります。
パーティショニングをサポートする機能はバージョンを追うごとに少しずつ改善されていますが、手順を簡略化するアイデアや現状での不満などありましたら、ぜひコミュニティに報告してください。今後の改良の参考にしてもらえると思います。
外部リンク
- 日本語ドキュメント: パーティショニング (英語)
- PostgreSQL Wiki: Table partitioning
- "PostgreSQL Partitioning", Robert Treat, PGCon 2007
- Wikipedia: 分割 (データベース)