5.10. テーブルのパーティショニング

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

5.10.1. 概要

パーティショニングとは、論理的には一つの大きなテーブルであるものを、物理的により小さな部品に分割することを指します。 パーティショニングによって得られる利点は以下のようにいくつかあります。

  • 特定の条件下で問い合わせのパフォーマンスが劇的に向上することがあります。 特にテーブル内のアクセスが集中する行の殆どが単一または少数のパーティションに存在している場合がそうです。 パーティショニングはインデックスの先頭にある列の代わりになり、インデックスの大きさを小さくして、インデックスの頻繁に使われる部分がメモリに収まりやすくなるようにします。

  • 問い合わせや更新が一つのパーティションの大部分にアクセスする場合、インデックスやランダムアクセスを使用してテーブル全体にまたがる読み取りをする代わりに、そのパーティションへの順次アクセスをすることでパフォーマンスを向上させることができます。

  • 一括挿入や削除について、その要件をパーティションの設計に組み込んでいれば、それをパーティションの追加や削除で実現することが可能です。 ALTER TABLE DETACH PARTITIONを実行する、あるいは個々のパーティションをDROP TABLEで削除するのは、一括の操作をするよりも遥かに高速です。 これらのコマンドはまた、一括のDELETEで引き起こされるVACUUMのオーバーヘッドを完全に回避できます。

  • 滅多に使用されないデータを安価で低速なストレージメディアに移行することができます。

この利益は通常は、そうしなければテーブルが非常に大きくなる場合にのみ価値があります。 テーブルがパーティショニングから利益を得られるかどうかの正確な分岐点はアプリケーションに依存しますが、重要なことはテーブルのサイズがデータベースサーバの物理メモリより大きいことです。

PostgreSQLにはパーティショニングについて以下の形式の組み込み機能があります。

範囲パーティショニング

テーブルはキー列またはキー列の集合で定義される範囲にパーティション分割され、異なるパーティションに割り当てられる値の範囲に重なりがないようになります。 例えば、日付の範囲によってパーティション分割することもあるでしょうし、特定のビジネスオブジェクトの識別子の範囲によって分割することもあるでしょう。

リストパーティション

各パーティションに現れるキーの値を明示的に列挙することでテーブルをパーティションに分割します。

アプリケーションで上記に列挙されていない他の形式のパーティショニングを使用する必要がある場合は、継承やUNION ALLなどの代替方式を代わりに使うことができます。 そのような方式は柔軟性がありますが、組み込みの宣言的パーティショニングによるパフォーマンス上の利益の一部を享受できません。

5.10.2. 宣言的パーティショニング

PostgreSQLはテーブルをパーティションと呼ばれる部品に分割する方法を指定するための方法を提供しています。 分割されたテーブルはパーティションテーブルと呼ばれます。 方法の指定はパーティション方式パーティションキーとして使用される列あるいは式のリストからなります。

パーティションテーブルに挿入されるすべての行は、パーティションキーの値に基づいてパーティションの一つに振り向けられます。 各パーティションはそのパーティション境界によって定義されるデータの部分集合を持ちます。 現在サポートされるパーティション方式には範囲とリストがあり、各パーティションにはそれぞれキーの範囲、あるいはキーのリストが割り当てられます。

サブパーティショニングと呼ばれる方法を使って、パーティションそれ自体をパーティションテーブルとして定義することができます。 パーティションには、他のパーティションとは別に独自のインデックス、制約、デフォルト値を定義できます。 インデックスは各パーティションで別々に作成されなければなりません。 パーティションテーブルおよびパーティションの作成についての更なる詳細についてはCREATE TABLEを参照してください。

通常のテーブルをパーティションテーブルに変更する、およびその逆はできません。 しかし、データのある通常のテーブルやパーティションテーブルをパーティションテーブルのパーティションとして追加する、あるいはパーティションテーブルからパーティションを削除し、それを独立したテーブルにすることは可能です。 ATTACH PARTITIONおよびDETACH PARTITIONのサブコマンドについての詳細はALTER TABLEを参照してください。

個々のパーティションは継承を背景にパーティションテーブルに紐付けられていますが、前節で説明した継承の機能のうちの一部はパーティションテーブルおよびパーティションでは使用できません。 例えば、パーティションテーブルのパーティションは、そのパーティションテーブル以外の親を持つことができませんし、また一般のテーブルはパーティションテーブルをその親にしてパーティションテーブルから継承することはできません。 これはつまり、パーティションテーブルおよびパーティションは一般のテーブルと継承によって繋がることができないということです。 パーティションテーブルとそのパーティションを構成するパーティションの階層は継承の階層でもあるので、継承におけるすべての通常の規則が5.9で説明したとおりに適用されますが、いくつか例外があります。 最も重要な例外を以下に示します。

  • パーティションテーブルのCHECK制約とNOT NULL制約はいずれも必ずすべてのパーティションに継承されます。 パーティションテーブルでNO INHERITの印を付けたCHECK制約を作ることはできません。

  • ONLYを使ってパーティションテーブルについてのみ制約を追加または削除することは、パーティションが存在しない場合にのみサポートされます。 パーティションが存在する時にパーティションテーブルについてのみ制約を追加または削除することはサポートされないため、一度パーティションが存在すれば、ONLYの使用はエラーになります。 その代わりに、パーティションに直接、制約を追加または削除することは、それが親テーブルに存在するのでなければ可能です。 パーティションテーブルは直接にデータを所有することはまったくないため、TRUNCATE ONLYをパーティションテーブルに対して使用しようとすると、必ずエラーが返されます。

  • パーティションは親に存在しない列を持つことができません。 パーティションをCREATE TABLEで作成する時に列を指定することはできませんし、作成後にALTER TABLEでパーティションに列を追加することもできません。 テーブルをALTER TABLE ... ATTACH PARTITIONでパーティションとして追加できるのは、その列がすべてのoid列も含めて完全に親と一致している場合のみです。

  • 親テーブルの列に存在するNOT NULL制約をパーティションの列から削除することはできません。

パーティションを外部テーブルとすることもできます(CREATE FOREIGN TABLE参照)が、その場合、通常のテーブルにはない制限がいくつかあります。 例えば、パーティションテーブルに挿入されるデータは外部テーブルのパーティションには振り向けられません。

5.10.2.1. 例

大きなアイスクリーム会社のデータベースを構築している場合を考えましょう。 その会社は毎日の最高気温、および各地域でのアイスクリームの売上を計測します。 概念的には次のようなテーブルが必要です。

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

このテーブルの主な利用目的は経営層向けにオンラインの報告書を作成することであるため、ほとんどの問い合わせは単に直前の週、月、四半期のデータにアクセスするだけであることがわかっています。 保存すべき古いデータの量を削減するため、最近3年分のデータのみを残すことに決めました。 各月のはじめに、最も古い月のデータを削除します。 この場合、計測テーブルについての様々な要求のすべてを、パーティショニングを使って満たすことができます。

この場合に宣言的パーティショニングを使うには、以下の手順に従います。

  1. PARTITION BY句を指定して、measurementテーブルをパーティションテーブルとして作成します。 PARTITION BY句にはパーティション方式(この場合はRANGE)とパーティションキーとして使う列のリストを記述します。

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

    望むなら、範囲パーティショニングのパーティションキーとして複数の列を使うようにすることもできます。 もちろん、こうすると多くの場合、パーティションの数が増え、各パーティションの大きさは小さくなります。 一方で、列の数を少なくすると、パーティショニングの基準の粒度が荒くなり、パーティションの数が少なくなります。 パーティションテーブルにアクセスする問い合わせで、その条件がこれらの列の一部またはすべてを含む場合、より少ない数のパーティションを走査することになります。 例えば、パーティションキーとして列lastnamefirstnameを(この順で)使用して範囲パーティショニングをしたテーブルを考えてみてください。

  2. パーティションを作成します。 各パーティションの定義では、親のパーティショニング方式およびパーティションキーに対応する境界を指定しなければなりません。 新しいパーティションの値が一つ以上の既存のパーティションの値と重なるような境界を指定するとエラーになることに注意してください。 既存のおよびパーティションのどれにも当てはまらないデータを親テーブルに挿入するとエラーになります。 この場合、適切なパーティションを手作業で追加しなければなりません。

    こうして作成されたパーティションは、すべての点においてPostgreSQLの通常のテーブル(あるいは場合によっては外部テーブル)と同じです。 各パーティション毎に別々にテーブル空間や格納パラメータを指定することもできます。

    各パーティションについて、パーティションの境界条件を定義するテーブル制約を作成する必要はありません。 その代わりに、指定した境界条件からパーティション制約が暗黙的に生成され、必要なときにはそれが参照されます。

    CREATE TABLE measurement_y2006m02 PARTITION OF measurement
        FOR VALUES FROM ('2006-02-01') TO ('2006-03-01');
    
    CREATE TABLE measurement_y2006m03 PARTITION OF measurement
        FOR VALUES FROM ('2006-03-01') TO ('2006-04-01');
    
    ...
    CREATE TABLE measurement_y2007m11 PARTITION OF measurement
        FOR VALUES FROM ('2007-11-01') TO ('2007-12-01');
    
    CREATE TABLE measurement_y2007m12 PARTITION OF measurement
        FOR VALUES FROM ('2007-12-01') TO ('2008-01-01')
        TABLESPACE fasttablespace;
    
    CREATE TABLE measurement_y2008m01 PARTITION OF measurement
        FOR VALUES FROM ('2008-01-01') TO ('2008-02-01')
        WITH (parallel_workers = 4)
        TABLESPACE fasttablespace;

    サブパーティショニングを実装するには、例えば以下のように、個々のパーティションを作成するコマンドでPARTITION BY句を指定してください。

    CREATE TABLE measurement_y2006m02 PARTITION OF measurement
        FOR VALUES FROM ('2006-02-01') TO ('2006-03-01')
        PARTITION BY RANGE (peaktemp);

    measurement_y2006m02のパーティションの作成後、measurementに挿入されるデータでmeasurement_y2006m02に振り向けられるもの(あるいはmeasurement_y2006m02に直接挿入されるデータでそのパーティション制約を満たしているもの)はすべて、peaktemp列に基いて更にその下のパーティションの一つにリダイレクトされます。 指定するパーティションキーは親のパーティションキーと重なっても構いませんが、サブパーティションの境界を指定するときは、それが受け付けるデータの集合がパーティション自体の境界でできるものの部分集合を構成するように注意してください。 システムは本当にそのようになっているかどうか、検査しようとしません。

  3. キー列にインデックスを作成し、またその他のインデックスも必要に応じて各パーティションに作成します。 (厳密に言えば、キー列のインデックスは必要なわけではありませんが、ほとんどの場合に役に立つでしょう。 キーの値が一意であることを意図している場合は、各パーティションに一意制約または主キー制約を必ず作成すべきです。)

    CREATE INDEX ON measurement_y2006m02 (logdate);
    CREATE INDEX ON measurement_y2006m03 (logdate);
    ...
    CREATE INDEX ON measurement_y2007m11 (logdate);
    CREATE INDEX ON measurement_y2007m12 (logdate);
    CREATE INDEX ON measurement_y2008m01 (logdate);

  4. postgresql.confで設定パラメータconstraint_exclusionが無効になっていないことを確認します。 これが無効になっていると、問い合わせが期待通りには最適化されません。

上記の例では、毎月、新しいパーティションを作ることになりますから、必要なDDLを自動的に生成するスクリプトを作るのが賢明かもしれません。

5.10.2.2. パーティションの保守

最初にテーブルを定義した時に作成したパーティションの集合は、通常はそのまま静的に残ることを意図したものではありません。 古いデータのパーティションを削除し、新しいデータの入った新しいパーティションを定期的に作成したいというのが普通です。 パーティショニングのもっとも重要な利点の一つは、パーティショニングがなければ大変なことになるであろうこの作業を、大量のデータを物理的に動かすのではなく、パーティション構造を操作することにより、ほとんど一瞬にして実行できるという、まさにそのことなのです。

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

DROP TABLE measurement_y2006m02;

これはすべてのレコードを個別に削除する必要がないため、数百万行のレコードを非常に高速に削除できます。 ただし、上記のコマンドは親テーブルについてACCESS EXCLUSIVEロックを取得する必要があることに注意してください。

別の方法で多くの場合に望ましいのは、パーティションテーブルからパーティションを削除する一方で、パーティションそれ自体はテーブルとしてアクセス可能なまま残すことです。

ALTER TABLE measurement DETACH PARTITION measurement_y2006m02;

こうすると、データを削除する前に、そのデータについて追加の操作が実行できます。 例えば、COPYpg_dumpや類似のツールを使ってデータのバックアップをする好機となることが多いでしょう。 また、データを集計してより小さな形式にする、その他のデータ操作を実行する、レポート作成を実行するなどのための好機となるかもしれません。

同様に、新しいデータを扱うために新しいパーティションを追加することができます。 上で元のパーティションを作ったのと全く同じように、パーティションテーブル内に空のパーティションを以下のように作成できます。

CREATE TABLE measurement_y2008m02 PARTITION OF measurement
    FOR VALUES FROM ('2008-02-01') TO ('2008-03-01')
    TABLESPACE fasttablespace;

別の方法として、新しいテーブルをパーティション構造の外部に作成し、その後でそれを適切なパーティションにする方が便利な場合もあります。 こうすると、パーティションテーブル内でデータが見えるようになるより前に、データをロードし、確認し、変換することができます。

CREATE TABLE measurement_y2008m02
  (LIKE measurement INCLUDING DEFAULTS INCLUDING CONSTRAINTS)
  TABLESPACE fasttablespace;

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'
-- possibly some other data preparation work

ALTER TABLE measurement ATTACH PARTITION measurement_y2008m02
    FOR VALUES FROM ('2008-02-01') TO ('2008-03-01' );

ATTACH PARTITIONコマンドを実行する前に、求められるパーティション制約を記述するCHECK制約を、パーティションに追加するテーブルに作成することを推奨します。 こうすることで、システムは暗示的なパーティション制約を検証するための走査を省略することができます。 このような制約がなければ、親テーブルにACCESS EXCLUSIVEロックを保持したままで、パーティション制約を検証するためにテーブルを走査することになります。 この制約はATTACH PARTITIONが終わった後は不要ですので削除することができます。

5.10.2.3. 制限事項

パーティションテーブルには以下の制限事項があります。

  • すべてのパーティションに適合するインデックスを自動的に作成する機能はありません。 インデックスは個々のパーティションについて別々のコマンドで追加しなければなりません。 これは全パーティションにまたがる主キー、一意制約、あるいは排他制約を作成する方法がないということも意味します。 それぞれの末端のパーティションについて別々に制約をつけることしかできません。

  • パーティションテーブルでは主キーがサポートされないため、パーティションテーブルを参照する外部キーはサポートされませんし、パーティションテーブルから他のテーブルを参照する外部キー参照もできません。

  • 一意制約や排他制約は個々のパーティション上にのみ作成可能なため、パーティションテーブルについてON CONFLICT句を使うとエラーになります。 パーティション階層の全体に渡って一意性(あるいは排他制約)を強制する機能はありません。

  • 行をあるパーティションから別のパーティションに移動させることになるUPDATEは失敗します。 行の新しい値が、元のパーティションの暗示的なパーティション制約を満たさないためです。

  • 行トリガーが必要であれば、パーティションテーブルではなく、個々のパーティションに定義されなければなりません。

5.10.3. 継承を使用した実装

組み込みの宣言的パーティショニングは、ほとんどの一般的な利用例に適合しますが、もっと柔軟な方式が便利な状況もあります。 パーティショニングはテーブルの継承を使用して実装することも可能で、これは宣言的パーティショニングではサポートされない以下のような機能が利用できます。

  • パーティショニングでは、すべてのパーティションが列の集合が親と完全に一致していなければならないという規則が強制されますが、テーブルの継承では、子テーブルは親テーブルに存在しない追加の列を持つことができます。

  • テーブルの継承では、複数の継承が可能です。

  • 宣言的パーティショニングではリストパーティショニングと範囲パーティショニングしかサポートされませんが、テーブルの継承ではユーザが選択した方法に従ってデータを分割することができます。 (ただし、制約の排他がパーティションを効果的に分離できない場合、問い合わせのパフォーマンスが非常に悪くなることに注意してください。)

  • 宣言的パーティショニングを使用すると、テーブルの継承を使用する場合に比べて、一部の走査でより強いロックが要求されます。 例えば、パーティションテーブルにパーティションを追加または削除するには、親テーブルのACCESS EXCLUSIVEロックを取得する必要がありますが、通常の継承の場合にはSHARE UPDATE EXCLUSIVEロックで十分です。

5.10.3.1. 例

上で使用したのと同じmeasurementテーブルを使用します。 継承を使用したパーティションテーブルとして実装するには、以下の手順に従います。

  1. マスターテーブルを作成します。 すべてのパーティションはこれを継承します。 このテーブルにはデータは含まれません。 すべてのパーティションに同じように適用されるのでなければ、このテーブルにチェック制約を定義しないでください。 このテーブル上にインデックスや一意制約を定義することにも意味はありません。 以下の例では、マスターテーブルは最初に定義したのと同じmeasurementテーブルです。

  2. いくつかのテーブルを作成し、それぞれマスターテーブルを継承するものにします。 通常、これらのテーブルはマスターから継承したものに列を追加しません。 宣言的パーティショニングの場合と同じく、これらのパーティションはすべての点で普通のPostgreSQLのテーブル(あるいは外部テーブル)と同じです。

    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);

  3. パーティションテーブルに、重なり合わないテーブル制約を追加し、各パーティションに許されるキー値を定義します。

    典型的な例は次のようなものです。

    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がどちらのパーティションに属するか明らかではないため、これは誤っています。

    その代わりに以下のようにパーティションを作る方が良いでしょう。

    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);

  4. 各パーティションについて、キー列にインデックスを作成し、またその他のインデックスも必要に応じて作成します。

    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);

  5. アプリケーションで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制約と正確に一致させなければならないことに注意してください。

    この関数は単一月の場合より複雑になりますが、頻繁に更新する必要はありません。なぜなら条件分岐を前もって追加しておくことが可能だからです。

    注記

    実際には、ほとんどの挿入が一番新しいパーティションに入る場合は、そのパーティションを最初に検査することが最善です。 簡単にするため、この例でのほかの部分と同じ順番でのトリガのテストを示しました。

    挿入を適切なパーティションテーブルにリダイレクトする別の方法は、マスターテーブルにトリガーではなくルールを設定することです。 例えば次のようにします。

    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でも起動されるので、トリガーを使う方法であれば通常通りに使用することができます。

    ルールを使う方法のもう一つの欠点は、ルールの集合が挿入日付に対応しきれていない場合に、強制的にエラーにする簡単な方法がないことです。 この場合、データは警告などを出すことなくマスターテーブルに入ります。

  6. 設定パラメータconstraint_exclusionpostgresql.confで無効にされないようにしてください。 向こうになっていると、問い合わせが期待通りに最適化されません。

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

5.10.3.2. パーティションの保守

古いデータを高速に削除するには、不要になったパーティションを単に削除します。

DROP TABLE measurement_y2006m02;

パーティションをパーティションテーブルから削除するものの、それ自体をテーブルとしてアクセスできるようにするには、次のようにします。

ALTER TABLE measurement_y2006m02 NO INHERIT measurement;

新しいデータを扱う新しいパーティションを追加するには、上で最初のパーティションを作成したときと同じように空のパーティションを作成します。

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.10.3.3. 注意事項

継承を使用して実装したパーティションテーブルには以下の注意事項があります。

  • すべてのCHECK制約が相互に排他的であることを自動的に確認する手段はありません。 各パーティションを手作業で作成するよりも、パーティションを生成し、関連オブジェクトを作成、更新するコードを作成するのが安全でしょう。

  • ここで示した方法は、パーティションのキー列の値が変わらないか、あるいは、少なくとも他のパーティションへの移動が必要になるような変更はないということを前提としています。 そのような変更をしようとするUPDATECHECK制約のためにエラーになります。 このような場合を処理できる必要があるなら、パーティションテーブルに適切なUPDATEトリガーを設定することもできますが、構造の管理がずっと複雑になります。

  • 手作業でVACUUMあるいはANALYZEコマンドを実行している場合、それを個々のパーティションに対して実行する必要があることを忘れないで下さい。 次のようなコマンドはマスターテーブルしか処理しません。

    ANALYZE measurement;

  • ON CONFLICT句のあるINSERT文は恐らく期待通りには動作しないでしょう。 ON CONFLICTの動作は対象となる指定リレーション上での一意制約違反の場合にのみ発生するもので、その子リレーションの場合には発生しないからです。

  • アプリケーションがパーティショニングのスキームについて明示的に意識しているのでなければ、トリガーまたはルールで行を適切なパーティションに振り向ける必要があります。 トリガーを書くのは複雑であり、また宣言的パーティショニングによって内部的に実行されるタプルの振り向けよりずっと遅いでしょう。

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

制約による除外は、上記の方法で定義された(宣言的パーティショニングと継承によって実装されたものの両方の)パーティションテーブルに対するパフォーマンスを向上させる問い合わせの最適化技術です。 例えば、

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

制約による除外がなければ、上記の問い合わせはmeasurementテーブルの各パーティションをスキャンするでしょう。 制約による除外が有効になっているとき、プランナはそれぞれのパーティションの制約を検証し、パーティションが問い合わせの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制約を検証するようになります。

制約による除外には以下の注意事項があり、これは継承とパーティションテーブルの両方に当てはまります。

  • 制約による除外は問い合わせのWHERE句が定数(または外部から供給されたパラメータ)を含んでいたときにのみ動作します。例えば、CURRENT_TIMESTAMPのような非immutable関数に対する比較は、関数の結果値がどのパーティションに該当するかを実行時にプランナが知ることが出来ないため、最適化できません。

  • パーティション制約を簡単にしておいてください。そうしないとプランナは、パーティションを使う必要がないことを立証できません。 前述の例で示したとおり、リスト分割のために簡単な等号条件を使用してください。また範囲分割のために簡単な範囲テストを使用してください。 手っ取り早い良い方法は、パーティショニングの制約がパーティション列とB-treeインデックス作成可能な演算子を用いた定数の比較のみを含んでいることです。 これはパーティションテーブルにも当てはまります。 なぜならパーティションキーにはB-treeでインデックス可能な列だけが使用できるからです。 (宣言的パーティショニングを使用する場合は、これは問題にはなりません。 なぜなら、自動的に生成される制約は非常に単純で、プランナが理解できるものだからです。)

  • マスタテーブルのすべてのパーティションのすべての制約は、制約による除外で試験されます。 よってパーティションの数が多くなれば問い合わせ計画の時間がかなり増加します。 これらの技術を使用したパーティショニングは、おそらく100個までのパーティションでうまく動作します。 何千ものパーティションを使用することは避けてください。