PostgreSQL 9.1.5文書
前のページ	巻戻し	第 5章データ定義	早送り	次のページ

5.9. パーティショニング

PostgreSQLは基本的なテーブルのパーティショニング（分割）をサポートしています。この節では、データベース設計において、なぜそしてどのようにしてパーティショニングを実装するのかを解説します。

5.9.1. 概要

パーティショニングは論理的に1つの大きなテーブルを、物理的に小さなパーティションに分けることを指します。パーティショニングはいくつかの利点があります。

特定の状況において、問い合わせのパフォーマンスが劇的に改善されます。具体的には、アクセスが多いテーブル内の行を単一パーティションまたは一部のパーティション内に収めることです。パーティショニングはインデックスの先頭の列を置き換え、インデックスサイズが減少します。このため、インデックス内のよく使用される部分がメモリ内により収まりやすくなります。
問い合わせや更新が単一パーティションの大部分にアクセスする場合、インデックススキャンとテーブル全体に渡って散乱したランダムアクセス読み取りの代わりに、そのパーティションのシーケンシャルスキャンとすることができ、性能は改善します。
その指定がパーティション設計に合うように計画されていれば、一括でのロードや削除が、パーティションの追加や削除によってなされる可能性があります。 ALTER TABLE NO INHERITやDROP TABLEは両方とも一括操作よりもずっと高速です。また、これらのコマンドは大量のDELETEによって発生するVACUUMのオーバーヘッドを完全に防ぎます。
めったに使用されないデータは、安価で遅い記憶メディアに移行できます。

この利点は、テーブルのサイズがとても大きくなる場合に価値が出てきます。テーブルのパーティショニングによる利点はアプリケーションに依存しますが、経験的にテーブルのサイズがデータベースサーバの物理メモリを超えるかどうかということがポイントになります。

現状ではPostgreSQLは、パーティショニングをテーブルの継承によりサポートしています。それぞれのパーティションは1つの親テーブルの子テーブルとして作成されなくてはいけません。親テーブル自身は通常、空のテーブルとなり、全体のデータを代表するために存在します。パーティショニングを設定する前に、継承（項5.8を参照してください）について詳しく知っておく必要があります。

パーティショニングについて次の種類がPostgreSQLに実装されています。

範囲分割: テーブルは、キーとなる列もしくは列のセットにより定義される"範囲"に分割されます。異なるパーティションに割り当てられた値の範囲は重なることはありません。例えば、日付の範囲により分割されたり、特定のビジネスオブジェクトの識別子の範囲により分割されたりします。
リスト分割: キー値がそれぞれのパーティションに現れるような明示的なリストにより、テーブルが分割されます。

5.9.2. パーティショニングの実装

テーブルのパーティショニングを実装するには、以下を行ってください。

すべてのパーティションが継承することになる、"マスタ"テーブルを作成してください。
このテーブルはデータを格納しません。このテーブルにはすべてのパーティションに対して適用されるつもりでなければ検査制約は定義しないでください。同様にインデックスや一意制約を定義することも意味がありません。
マスタテーブルから継承された、いくつかの"子"テーブルを作成します。通常、これらの子テーブルはマスタから継承された列以外には列を追加しないようにします。
子テーブルは、あらゆる点でPostgreSQLの普通のテーブルですが、子テーブルをパーティションとして参照することになります。
それぞれのパーティションでのキー値を定義するために、分割されたテーブルにテーブル制約を追加してください。
典型的な例は、
```
CHECK ( x = 1 )
CHECK ( county IN ( 'Oxfordshire', 'Buckinghamshire', 'Warwickshire' ))
CHECK ( outletID >= 100 AND outletID < 200 )
```
制約が、異なるパーティションにおいて許可されているキー値の間で重なりが無いことを保証してください。よくある間違いは範囲制約を次のように設定してしまうことです。
```
CHECK ( outletID BETWEEN 100 AND 200 )
CHECK ( outletID BETWEEN 200 AND 300 )
```
キー値の200がどちらのパーティションに属するのかが明確になっていないため、これは間違いになります。
範囲分割とリスト分割の間に構文の違いは無いことに注意してください。これらの字句は記述上のものだけです。
それぞれのパーティションにおいて、他のインデックスと同様にキーとなる列（列の集合）にインデックスを作成してください。（キーのインデックスは必ずしも必要でありませんが、たいていの場合に役立ちます。もしキー値が一意であることを意図するのであればいつでも、一意もしくは主キー制約をそれぞれのパーティションに作成してください。）
また、マスタテーブルに挿入されたデータを適当なパーティションに中継するためにトリガもしくはルールを定義してください。
constraint_exclusion設定パラメータがpostgresql.conf内で無効になっていないことを確認してください。無効であると、問い合わせは思ったとおりに最適化されません。

例えば、大規模なアイスクリーム会社のデータベースを構築すると仮定してください。会社は、それぞれの地方のアイスクリームの売上と同様に毎日の最高気温を計測しています。概念的に、次のようなテーブルが必要になります。

CREATE TABLE measurement (
    city_id         int not null,
    logdate         date not null,
    peaktemp        int,
    unitsales       int
);

ほとんどの問い合わせが先週、先月もしくは半年前のデータを問い合わせるものであることが分かっています。その理由は、このテーブルが経営に関するオンラインのレポートを作成することに主に使用されるからです。必要な過去のデータ量を減らすために、過去3ヶ月のデータのみを保存することにします。月の始めに過去のデータを削除します。

このような場合、measurementテーブルに対する異なる要求をすべて満たすようにパーティショニングを利用できます。上記で述べた方法で、パーティショニングを次のように設定します。

マスタテーブルは、上記で宣言されたmeasurementテーブルです。
次にそれぞれの月に対して1つのパーティションを作成します。
```
CREATE TABLE measurement_y2006m02 ( ) INHERITS (measurement);
CREATE TABLE measurement_y2006m03 ( ) INHERITS (measurement);
...
CREATE TABLE measurement_y2007m11 ( ) INHERITS (measurement);
CREATE TABLE measurement_y2007m12 ( ) INHERITS (measurement);
CREATE TABLE measurement_y2008m01 ( ) INHERITS (measurement);
```
それぞれのパーティションは、完結したテーブルですがmeasurementテーブルからの定義を継承しています。
これは古いデータの削除という問題を解決します。毎月、最も古い子テーブルをDROP TABLEし新規の月に対しては子テーブルを作成するだけでいいのです。

重なりの無いテーブル制約を付ける必要があります。上記のようにパーティションを単に作成するよりも、以下のようにすべきでしょう。

CREATE TABLE measurement_y2006m02 (
    CHECK ( logdate >= DATE '2006-02-01' AND logdate < DATE '2006-03-01' )
) INHERITS (measurement);
CREATE TABLE measurement_y2006m03 (
    CHECK ( logdate >= DATE '2006-03-01' AND logdate < DATE '2006-04-01' )
) INHERITS (measurement);
...
CREATE TABLE measurement_y2007m11 (
    CHECK ( logdate >= DATE '2007-11-01' AND logdate < DATE '2007-12-01' )
) INHERITS (measurement);
CREATE TABLE measurement_y2007m12 (
    CHECK ( logdate >= DATE '2007-12-01' AND logdate < DATE '2008-01-01' )
) INHERITS (measurement);
CREATE TABLE measurement_y2008m01 (
    CHECK ( logdate >= DATE '2008-01-01' AND logdate < DATE '2008-02-01' )
) INHERITS (measurement);

キーとなる列にインデックスが必要になるでしょう。

CREATE INDEX measurement_y2006m02_logdate ON measurement_y2006m02 (logdate);
CREATE INDEX measurement_y2006m03_logdate ON measurement_y2006m03 (logdate);
...
CREATE INDEX measurement_y2007m11_logdate ON measurement_y2007m11 (logdate);
CREATE INDEX measurement_y2007m12_logdate ON measurement_y2007m12 (logdate);
CREATE INDEX measurement_y2008m01_logdate ON measurement_y2008m01 (logdate);

今回は、これ以上のインデックスを付けないことにします。

アプリケーションでINSERT INTO measurement ...を実行したら、適切なパーティションテーブルにデータが中継されるようにしたいとします。そのような場合は、適切なトリガ関数をマスタテーブルに設定することにより可能となります。もしデータが最新のパーティションにのみ追加される場合は、非常に簡単なトリガ関数を使用できます。
```
CREATE OR REPLACE FUNCTION measurement_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO measurement_y2008m01 VALUES (NEW.*);
    RETURN NULL;
END;
$$
LANGUAGE plpgsql;
```
関数を作成した後で、このトリガ関数を呼ぶトリガを作成します。
```
CREATE TRIGGER insert_measurement_trigger
    BEFORE INSERT ON measurement
    FOR EACH ROW EXECUTE PROCEDURE measurement_insert_trigger();
```
毎月、トリガ関数を再定義しなくてはいけません。それにより現在のパーティションをいつも指すことになります。しかし、トリガ定義は更新する必要はありません。
データを挿入したら、サーバが行を追加すべきパーティションを自動的に決定するようにしたいかもしれません。これは以下のようなもっと複雑なトリガ関数を作成することにより可能です。
```
CREATE OR REPLACE FUNCTION measurement_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
    IF ( NEW.logdate >= DATE '2006-02-01' AND
         NEW.logdate < DATE '2006-03-01' ) THEN
        INSERT INTO measurement_y2006m02 VALUES (NEW.*);
    ELSIF ( NEW.logdate >= DATE '2006-03-01' AND
            NEW.logdate < DATE '2006-04-01' ) THEN
        INSERT INTO measurement_y2006m03 VALUES (NEW.*);
    ...
    ELSIF ( NEW.logdate >= DATE '2008-01-01' AND
            NEW.logdate < DATE '2008-02-01' ) THEN
        INSERT INTO measurement_y2008m01 VALUES (NEW.*);
    ELSE
        RAISE EXCEPTION 'Date out of range.  Fix the measurement_insert_trigger() function!';
    END IF;
    RETURN NULL;
END;
$$
LANGUAGE plpgsql;
```
トリガ定義は前と同じです。それぞれのIFテストをパーティションのCHECK制約と正確に一致させなければならないことに注意してください。
この関数は単一月の場合より複雑になりますが、頻繁に更新する必要はありません。なぜなら条件分岐を前もって追加しておくことが可能だからです。
注意: 実際には、ほとんどの挿入が一番新しいパーティションに入る場合は、そのパーティションを最初に検査することが最善です。簡単に、この例でのほかの部分と同じ順番でトリガのテストを示しました。

以上のように、複雑なパーティション化の計画はたくさんのDDLが必要となります。上記の例では、毎月新しいパーティションを作成することになりますが、必要となるDDLを自動的に生成するスクリプトを書くのが賢明です。

通常、初期定義でテーブルを静的なままにするつもりではない場合、一群のパーティションを作ることになります。古いデータのパーティションの削除や新規データ向けの定期的な新規パーティションの追加という要求はよくあります。こうしたそうでなければ苦痛を伴う作業を、大量のデータを物理的に移動させずに、パーティション構造を操作することでほとんど瞬間的に行うことができます。パーティショニングの最も重要な利点の一つはまさにこの点です。

古いデータを削除する最も簡単な方法は、単に不要となったパーティションを削除することです。

DROP TABLE measurement_y2006m02;

レコードごとに削除する必要がありませんので、これは高速に何百万ものレコードを削除することができます。

この他、よく使用される方法は、テーブル自体へのアクセス権限をそのまま残したまま、パーティション付けされたテーブルからパーティションを削除することです。

ALTER TABLE measurement_y2006m02 NO INHERIT measurement;

これにより、削除前にデータ操作をさらに行うことができます。例えば、COPY、pg_dumpなどのツールを使用してデータをバックアップすることはよくあります。また、データをより小さな書式に集約したり、他のデータ操作を行ったり、報告を作成したりすることもよくあります。

同様に、新しいデータを扱うために新しいパーティションを追加することもできます。上で元々のパーティションを作成した時と同じように、パーティション付けテーブルに空のパーティションを作成することができます。

CREATE TABLE measurement_y2008m02 (
    CHECK ( logdate >= DATE '2008-02-01' AND logdate < DATE '2008-03-01' )
) INHERITS (measurement);

この他の方法として、パーティション構造の外部で新しいテーブルを作成し、後で適切にパーティションとすることが便利な場合もあります。これにより、パーティション付けしたテーブルに追加する前に、データをロードし、検査、変換することができます。

CREATE TABLE measurement_y2008m02
  (LIKE measurement INCLUDING DEFAULTS INCLUDING CONSTRAINTS);
ALTER TABLE measurement_y2008m02 ADD CONSTRAINT y2008m02
   CHECK ( logdate >= DATE '2008-02-01' AND logdate < DATE '2008-03-01' );
\copy measurement_y2008m02 from 'measurement_y2008m02'
-- 何らかの準備操作を行うことができます。
ALTER TABLE measurement_y2008m02 INHERIT measurement;

5.9.4. パーティショニングと制約による除外

制約による除外は、上記の方法で定義された分割されたテーブルに対するパフォーマンスを向上させる問い合わせの最適化技術です。例えば、

SET constraint_exclusion = on;
SELECT count(*) FROM measurement WHERE logdate >= DATE '2008-01-01';

制約による除外が有効になっているとき、プランナはそれぞれのパーティションの制約を検証し、パーティションが問い合わせのWHEREに一致する行を含んでいないためにスキャンされる必要が無いことを証明しようとします。プランナはこれを証明すると、問い合わせ計画からそのパーティションを除外します。

constraint_exclusionを有効とした時の計画と無効にした時の計画の違いを明らかにするため、EXPLAINコマンドを使用できます。この種類のテーブル設定に対する典型的な最適化されない計画は以下のようになります。

SET constraint_exclusion = off;
EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2008-01-01';

                                          QUERY PLAN
-----------------------------------------------------------------------------------------------
 Aggregate  (cost=158.66..158.68 rows=1 width=0)
   ->  Append  (cost=0.00..151.88 rows=2715 width=0)
         ->  Seq Scan on measurement  (cost=0.00..30.38 rows=543 width=0)
               Filter: (logdate >= '2008-01-01'::date)
         ->  Seq Scan on measurement_y2006m02 measurement  (cost=0.00..30.38 rows=543 width=0)
               Filter: (logdate >= '2008-01-01'::date)
         ->  Seq Scan on measurement_y2006m03 measurement  (cost=0.00..30.38 rows=543 width=0)
               Filter: (logdate >= '2008-01-01'::date)
...
         ->  Seq Scan on measurement_y2007m12 measurement  (cost=0.00..30.38 rows=543 width=0)
               Filter: (logdate >= '2008-01-01'::date)
         ->  Seq Scan on measurement_y2008m01 measurement  (cost=0.00..30.38 rows=543 width=0)
               Filter: (logdate >= '2008-01-01'::date)

一部のパーティション、もしくはすべてのパーティションで、テーブル全体に対するシーケンシャルスキャンではなく、インデックススキャンが使用される可能性があります。しかしここでのポイントは、この問い合わせに対する回答のために古いパーティションをスキャンする必要はまったく無いということです。制約による除外を有効にしたとき、大幅により安価な同じ回答を返す計画を得ることができます。

SET constraint_exclusion = on;
EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2008-01-01';
                                          QUERY PLAN
-----------------------------------------------------------------------------------------------
 Aggregate  (cost=63.47..63.48 rows=1 width=0)
   ->  Append  (cost=0.00..60.75 rows=1086 width=0)
         ->  Seq Scan on measurement  (cost=0.00..30.38 rows=543 width=0)
               Filter: (logdate >= '2008-01-01'::date)
         ->  Seq Scan on measurement_y2008m01 measurement  (cost=0.00..30.38 rows=543 width=0)
               Filter: (logdate >= '2008-01-01'::date)

制約による除外はCHECK制約のみで動作し、インデックスの有無では動作しないことに注意してください。よってキー列のインデックスを定義することは必要ではありません。あるパーティションでインデックスが必要かどうかは、パーティションをスキャンする問い合わせがパーティションの大部分もしくは小さな部分をスキャンするのかによります。前者ではなく後者において、インデックスは役立ちます。

実のところ、constraint_exclusionのデフォルト（かつ推奨）の設定は、onでもoffでもなく、partitionという中間の設定です。これによりこの技法は、パーティショニングされたテーブルに対して動作することになる問い合わせのみに適用されるようになります。 on設定により、プランナはすべての問い合わせでCHECK制約を検証するようになります。これは単純な問い合わせでも行われ、プラスになりそうでありません。

5.9.5. 他のパーティショニング方法

適当なパーティションテーブルにリダイレクトする別の方法は、トリガの替わりにルールをマスターテーブル上に作成することです。例えば

CREATE RULE measurement_insert_y2006m02 AS
ON INSERT TO measurement WHERE
    ( logdate >= DATE '2006-02-01' AND logdate < DATE '2006-03-01' )
DO INSTEAD
    INSERT INTO measurement_y2006m02 VALUES (NEW.*);
...
CREATE RULE measurement_insert_y2008m01 AS
ON INSERT TO measurement WHERE
    ( logdate >= DATE '2008-01-01' AND logdate < DATE '2008-02-01' )
DO INSTEAD
    INSERT INTO measurement_y2008m01 VALUES (NEW.*);

ルールはトリガよりもかなりオーバーヘッドがありますが、行ごとよりもむしろ問い合わせごとのオーバーヘッドとなります。よってこの方法は、一括挿入といった場合に有効となります。しかし多くの場合、トリガを使用する方が、良い性能となります。

COPYはルールを無視することに注意してください。もしデータを挿入するのにCOPYを使用したい場合は、マスタテーブルより正しいパーティションテーブルにコピーすることが必要となるでしょう。 COPYはトリガを起動します。よってトリガを使用する方法をとれば、普通にCOPYを使用することができます。

ルールを使用する方法のもうひとつの欠点は、一連のルールが挿入日付を扱わないときにエラーを強制する簡単な方法がないことです。データは代わりにマスタテーブルに暗黙のうちに挿入されます。

パーティショニングは、テーブルの継承の代わりにUNION ALLビューを使用することによっても使用できます。例えば、

CREATE VIEW measurement AS
          SELECT * FROM measurement_y2006m02
UNION ALL SELECT * FROM measurement_y2006m03
...
UNION ALL SELECT * FROM measurement_y2007m11
UNION ALL SELECT * FROM measurement_y2007m12
UNION ALL SELECT * FROM measurement_y2008m01;

しかしビューを再作成することで、データセットの個別のパーティションを追加したり削除したりする余分な手順が増えます。実際には、この方法は継承を使用する方法と比較してお勧めする点はほとんどありません。

5.9.6. 警告

以下の警告がパーティション付けテーブルに対して適用されます。

すべてのCHECK制約が相互に排他であるかどうか自動で確認する方法はありません。パーティションを生成し、関連付けられたオブジェクトを作成もしくは修正するコードを作成する方が、それらをいちいち手書きするよりも安全です。
ここで示すスキーマは、行のパーティションキー列が変更しない、もしくは少なくとも他のパーティションに移動することが要求されるほどの変更がないことを仮定しています。別のパーティションに移るようなUPDATEは、CHECK制約により失敗するでしょう。このような場合を扱うには、適切な更新トリガをパーティションテーブルに設定します。しかしこれにより管理構造がさらに複雑になります。
もし手動のVACUUMもしくはANALYZEコマンドを使用している場合は、それぞれのパーティションで個別に実行することを忘れないでください。以下のようなコマンドは
```
ANALYZE measurement;
```
マスタテーブルのみ処理することになります。

以下の警告が制約による除外に適用されます。

制約による除外は問い合わせのWHERE句が定数を含んでいたときのみに動作します。パラメータ化された問い合わせは最適化されません。その理由はプランナは実行時に、パラメータ値がどのパーティションを選択するか知り得ないためです。同様の理由で、CURRENT_DATEのような"安定"関数は避けなくてはいけません。
パーティション制約を簡単にしておいてください。そうしないとプランナは、パーティションを使う必要がないことを立証できません。前述の例で示したとおり、リスト分割のために簡単な等号条件を使用してください。また範囲分割のために簡単な範囲テストを使用してください。手っ取り早い良い方法は、パーティショニングの制約がパーティション列とB-treeインデックス作成可能な演算子を用いた定数の比較のみを含んでいることです。
マスタテーブルのすべてのパーティションのすべての制約は、制約による除外で試験されます。よってパーティションの数が多くなれば問い合わせ計画の時間がかなり増加します。これらの技術を使用したパーティショニングは、おそらく100個までのパーティションでうまく動作します。何千ものパーティションを使用することは避けてください。

前のページ	ホーム	次のページ
継承	上に戻る	外部データ