他のバージョンの文書 15 | 14 | 13 | 12 | 11 | 10 | 9.6 | 9.5 | 9.4 | 9.3 | 9.2 | 9.1 | 9.0 | 8.4 | 8.3 | 8.2 | 8.1 | 8.0 | 7.4 | 7.3 | 7.2

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. キー列にインデックスを作成し、またその他のインデックスも必要に応じてパーティションテーブル上に作成します。 (厳密に言えば、キー列のインデックスは必要なわけではありませんが、ほとんどの場合に役に立つでしょう。) これは各パーティション上に1つのインデックスを自動的に作ります。 後から作成もしくは追加したパーティションにもインデックスが含まれます。

    CREATE INDEX ON measurement (logdate);
    

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

上記の例では、毎月、新しいパーティションを作ることになりますから、必要な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が終わった後は不要ですので削除することができます。

前述したとおり、パーティションテーブル上にインデックスを作成することが可能であり、それらは階層全体に自動で適用されます。 既存のパーティションだけではなく将来作成されるパーティションもインデックス付けされるため、これはとても便利です。 一つの制限は、そのようなパーティションのインデックスを作成する場合CONCURRENTLY句を使うことができません。 長いロック時間を克服するためには、パーティションテーブルにCREATE INDEX ON ONLYを使うことが可能です。 そのようなインデックスは無効とマークされ、パーティションは適用するインデックスを自動で取得しません。 パーティション上のインデックスはCONCURRENTLYを使用して個々に作成することができ、後からALTER INDEX .. ATTACH PARTITIONを使用して親のインデックスにattachedできます。 全てのパーティションに対してインデックスがアタッチされた時点で、親のインデックスは、自動で有効とマークされます。 例を示します。

CREATE INDEX measurement_usls_idx ON ONLY measurement (unitsales);

CREATE INDEX measurement_usls_200602_idx
    ON measurement_y2006m02 (unitsales);
ALTER INDEX measurement_usls_idx
    ATTACH PARTITION measurement_usls_200602_idx;
...

この手法は、UNIQUEPRIMARY KEY制約でも使用できます。 制約が作成された際にインデックスは暗黙的に作成されます。 例を示します。

ALTER TABLE ONLY measurement ADD UNIQUE (city_id, logdate);

ALTER TABLE measurement_y2006m02 ADD UNIQUE (city_id, logdate);
ALTER INDEX measurement_city_id_logdate_key
    ATTACH PARTITION measurement_y2006m02_city_id_logdate_key;
...

5.10.2.3. 制限事項

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

  • すべてのパーティションにまたがる排他制約を作成する方法はありません。 それぞれの末端のパーティションについて別々に制約をつけることしかできません。

  • パーティションテーブル上の一意制約は、すべてのパーティションキー列を含んでいなければなりません。 PostgreSQLが各パーティションの一意性を個別に強制するために、この制限は存在します。

  • パーティションテーブルにて主キーがサポートされている一方で、パーティションテーブルを参照する外部キーはサポートされていません。 (パーティションテーブルから他のテーブルの外部キー参照はサポートされています。)

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

  • 一時リレーションと永続的リレーションを同じパーティションツリーに混合することはできません。 ですから、パーティション化されたテーブルが永続的なら、パーティションも永続的でなければなりません。 同様にパーティション化されたテーブルが一時的なら、パーティションも一時的でなければなりません。 一時リレーションを使う場合は、パーティションツリーのすべてのメンバーは同じセッションに由来しなければなりません。

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 FUNCTION 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 enable_partition_pruning = on;                 -- the default
SELECT count(*) FROM measurement WHERE logdate >= DATE '2008-01-01';

パーティション除去がなければ、上記の問い合わせはmeasurementテーブルの各パーティションをスキャンするでしょう。 パーティション除去が有効になっているとき、プランナはそれぞれのパーティションの定義を検証し、パーティションが問い合わせのWHEREに一致する行を含んでいないためにスキャンされる必要が無いことを証明します。 プランナはこれを証明すると、問い合わせ計画からそのパーティションを除外(除去)します。

EXPLAINコマンドと設定パラメータenable_partition_pruning を使用することによって、パーティションの除去をした計画とそうでない計画の違いを明らかにすることを可能とします。 この種類のテーブル設定に対する典型的な最適化されない計画は以下のようになります。

SET enable_partition_pruning = off;
EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2008-01-01';
                                    QUERY PLAN
-----------------------------------------------------------------------------------
 Aggregate  (cost=188.76..188.77 rows=1 width=8)
   ->  Append  (cost=0.00..181.05 rows=3085 width=0)
         ->  Seq Scan on measurement_y2006m02  (cost=0.00..33.12 rows=617 width=0)
               Filter: (logdate >= '2008-01-01'::date)
         ->  Seq Scan on measurement_y2006m03  (cost=0.00..33.12 rows=617 width=0)
               Filter: (logdate >= '2008-01-01'::date)
...
         ->  Seq Scan on measurement_y2007m11  (cost=0.00..33.12 rows=617 width=0)
               Filter: (logdate >= '2008-01-01'::date)
         ->  Seq Scan on measurement_y2007m12  (cost=0.00..33.12 rows=617 width=0)
               Filter: (logdate >= '2008-01-01'::date)
         ->  Seq Scan on measurement_y2008m01  (cost=0.00..33.12 rows=617 width=0)
               Filter: (logdate >= '2008-01-01'::date)

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

SET enable_partition_pruning = on;
EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2008-01-01';
                                    QUERY PLAN
-----------------------------------------------------------------------------------
 Aggregate  (cost=37.75..37.76 rows=1 width=8)
   ->  Append  (cost=0.00..36.21 rows=617 width=0)
         ->  Seq Scan on measurement_y2008m01  (cost=0.00..33.12 rows=617 width=0)
               Filter: (logdate >= '2008-01-01'::date)

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

パーティション除去は与えられた問い合わせの計画時だけでなく、問い合わせの実行時にも可能です。 問い合わせの計画時、句が値のわからない式を含むときにより多くのパーティションを除去できるため便利です。 例えば、PREPARE文中に定義されたパラメータや、副問い合わせから取得される値の利用、ネステッドループ結合の内側でパラメータ化された値の利用です。 実行中のパーティション除去は、次のいずれかの時点で可能です。

  • 問い合わせ計画の初期化時。 パーティション除去は、パラメータの値が分かる実行の初期化段階時に可能です。 この段階で除去されたパーティションは、問い合わせのEXPLAINEXPLAIN ANALYZE中に姿を見せることはないでしょう。 EXPLAIN出力中にSubplans removedプロパティを観察することによってこの段階で削除されるパーティションの数を特定することが可能です。

  • 問い合わせ計画の実行時。 パーティション除去では実際に問い合わせの実行をする際にのみ分かる値を用いてパーティションを取り除くことも同様に可能でしょう。 これは、副問い合わせからの値やネステッドループ結合でパラメータ化されたような実行時のパラメータからの値を含みます。 それらのパラメータの値は問い合わせの実行時に何回も変わるかもしれないため、パーティション除去はパーティション除去に使われる実行パラメータの値が変るたびに行われます。 この段階で除去されたパーティションを特定するには、EXPLAIN ANALYZE出力中のloopsプロパティの慎重な調査が必要です。 異なるパーティションに対応するサブプランは、それぞれ実行時に除去された回数に応じて異なる値を持っているかもしれません。 毎回パーティションが除去される場合、一部は(never executed)と表示されるでしょう。

パーティション除去はenable_partition_pruning設定を使うことにより無効化できます。

注記

実行時のパーティション除去は現在MergeAppendModifyTableではなくAppendノードタイプに対してのみ発生します。 これはPostgreSQLの将来のリリースで変更される可能性があります。

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

制約による除外はパーティション除去と同様に問い合わせ最適化技術です。 主に従来の継承方法を使用して実装されたパーティショニングのために使用されると同時に 宣言的パーティショニングを含む他の目的に使うことができます。

各テーブルの名前の付いたCHECK制約を使用すること(一方でパーティション除去は宣言的パーティショニングの場合にのみ存在するテーブルのパーティション境界を使用します)を除いて、制約による除外はパーティション除去と極めて同様な方法で動作します。 その他の違いは、制約による除外は計画時にのみ適用され実行時にパーティションの削除を試しません。

制約による除外はCHECK制約を使用しているためパーティション除去と比べて遅いですが、ときどき利点として使うことができます。 なぜなら、内部のパーティション境界に加えて宣言的パーティションテーブルにも制約は定義することができるため、制約による除外は問い合わせ計画から追加のパーティションを取り除けるかもしれません。

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

制約による除外には以下の注意事項が適用されます。

  • 問い合わせの実行中にも適用できるパーティション除去とは違い、制約による除外は問い合わせ計画時にのみ適用されます。

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

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

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

5.10.6. 宣言的パーティショニングのベストプラクティス

不十分な設計によってクエリ計画および実行性能に負の影響がでる可能性があるため テーブルのパーティション方法の選択は注意して行う必要があります。

最も重要な設計の決定の一つは、データを分割するための一つまたは複数の列です。 大抵最適な選択は、パーティションテーブル上で実行されるクエリのWHERE句に最もよく現れる列または列の組み合わせによって分割することです。 パーティションキーと一致し互換性があるWHERE句の項目は、不要なパーティションを取り除く為に使うことができます。 しかしながら、PRIMARY KEYもしくはUNIQUE制約の条件により、他の決定を強いられるかもしれません。 不要なデータの削除も同様にパーティショニング戦略を計画する際に考えるべき要素です。 すべてのパーティションはとても早くデタッチすることができるため、一度に削除される全てのデータが単一のパーティション中に設置されるようにパーティション戦略を設計することが有益かもしれません。

テーブルを分割するパーティションの目標数を選択することもまた需要な決定です。 十分なパーティションがないとインデックスは大きくなりデータの局所性が貧しいままであるかもしれず、キャッシュヒット率が低い結果となる可能性があります。 しかしながら非常に多くのパーティションにテーブルを分割することもまた問題の原因となります。 非常に多くのパーティションは、クエリの計画時間が長くなり、クエリの計画および実行の両方の際にメモリ消費が高くなることを意味します。 テーブルを分割する方法を選択するとき、 将来に起こる変化を考慮することもまた重要です。 例えば、顧客毎に一つのパーティションを用意することを選択し、現在大規模な顧客が少数いる場合、数年以内に小規模な顧客を多数代わりに見つける可能性を含めて考慮します。 この場合、LISTによって分割しデータの分割が実用的な数以上に顧客の数が増加しないことを期待するより、HASHによって分割し妥当なパーティション数にすることを選択する方が良いかもしれません。

サブパーティショニングは、他のパーティションより巨大になると想定されるパーティションを更に分割するために役立ちますが、過度なサブパーティショニングは大量のパーティションを容易に引き起こし、前の段落で言及したのと同じ問題の原因となります。

クエリの計画および実行時のパーティショニングのオーバーヘッドを考慮することもまた重要です。 典型的なクエリではクエリプランナが少数のパーティションを除いて残り全てのパーティションを除外できるという前提に立てば、クエリプランナは通常最大数百パーティションのパーティション階層を適切に操作することができます。 パーティションが追加されることにより、計画時間は長くなりメモリ消費は高くなります。 これはUPDATEDELETEコマンドに特に当てはまります。 大量のパーティションを持っていることについて考慮するもうひとつの理由は、特に多くのセッションが大量のパーティションを参照する場合、ある期間にサーバのメモリ消費が著しく増加するかもしれないことです。 その理由は、各パーティションは参照される各セッションのローカルメモリにメタデータを読み込む必要があるためです。

データウェアハウスタイプのワークロードでは、OLTPタイプのワークロードより大量のパーティションを使用するのが当然です。 通常、データウェアハウスでは処理時間の大半をクエリ実行に費やすため、クエリ計画時間はあまり問題になりません。 2種類のワークロードのいずれかでも、大量のデータを再パーティショニングすることは非常に遅いため、初期に適切な決定を下すことが重要です。 計画したワークロードのシミュレーションは、パーティショニング戦略を最適化するためにしばしば役立ちます。 多数のパーティションがより少数のパーティションより優れていることや、少数のパーティションが多数のパーティションより優れていることを前提としないでください。