PostgreSQLは基本的なテーブルのパーティショニング(分割)をサポートしています。 この節では、データベース設計において、なぜそしてどのようにしてパーティショニングを実装するのかを解説します。
パーティショニングとは、論理的には一つの大きなテーブルであるものを、物理的により小さな部品に分割することを指します。 パーティショニングによって得られる利点は以下のようにいくつかあります。
特定の条件下で問い合わせのパフォーマンスが劇的に向上することがあります。 特にテーブル内のアクセスが集中する行のほとんどが単一または少数のパーティションに存在している場合がそうです。 パーティショニングは実質的にインデックスの上位木レベルの代わりになり、インデックスの頻繁に使われる部分がメモリに収まりやすくなるようにします。
問い合わせや更新が一つのパーティションの大部分にアクセスする場合、インデックスを使用してテーブル全体にまたがるランダムアクセス読み取りをする代わりに、そのパーティションへの順次アクセスをすることでパフォーマンスを向上させることができます。
一括挿入や削除について、その使い方のパターンをパーティショニングの設計に組み込んでいれば、それをパーティションの追加や削除で実現することが可能です。
個々のパーティションをDROP TABLE
で削除する、あるいはALTER TABLE DETACH PARTITION
を実行することにより、一括の操作をするよりも遥かに高速です。
これらのコマンドはまた、一括のDELETE
で引き起こされるVACUUM
のオーバーヘッドを完全に回避できます。
めったに使用されないデータを安価で低速なストレージメディアに移行できます。
これらの利益は通常、そうしなければテーブルが非常に大きくなる場合にのみ価値があります。 テーブルがパーティショニングから利益を得られるかどうかの正確な分岐点はアプリケーションに依存しますが、重要なことはテーブルのサイズがデータベースサーバの物理メモリより大きいことです。
PostgreSQLにはパーティショニングについて以下の形式の組み込み機能があります。
テーブルはキー列またはキー列の集合で定義される「範囲」にパーティション分割され、異なるパーティションに割り当てられる値の範囲に重なりがないようになります。
例えば、日付の範囲によってパーティション分割することもあるでしょうし、特定のビジネスオブジェクトの識別子の範囲によって分割することもあるでしょう。
個々の範囲の境界は、下限は境界値を含み、上限は境界値を含まないと理解されています。
たとえば、あるパーティションの境界が1
から10
で、次の範囲が10
から20
なら、値10
は最初ではなく、二番目のパーティションに所属します。
各パーティションに現れるキーの値を明示的に列挙することでテーブルをパーティションに分割します。
各パーティションに対して法と剰余を指定することでテーブルをパーティションに分割します。 各パーティションは、パーティションキーのハッシュ値を指定された法で割った際に指定された剰余となる行を保持します。
アプリケーションで上記に列挙されていない他の形式のパーティショニングを使用する必要がある場合は、継承やUNION ALL
などの代替方式を代わりに使うことができます。
そのような方式は柔軟性がありますが、組み込みの宣言的パーティショニングによるパフォーマンス上の利益の一部を享受できません。
PostgreSQLではテーブルをパーティションに分割すると宣言できます。 分割されたテーブルはパーティションテーブルと呼ばれます。 この宣言は上で述べたパーティショニング方式を含んでおり、加えてパーティションキーとして使用される列あるいは式のリストからなります。
パーティションテーブル自身はストレージを持たない「仮想」テーブルです。 その代わり、ストレージはパーティションテーブルに関連付けられた通常のテーブルであるpartitionsに所属します。 個々のパーティションはパーティション境界によって定義されるデータのサブセットです。 パーティションテーブルに挿入されるすべての行は、パーティションキーの列の値に基づいてパーティションの一つに振り向けられます。 行のパーティションキーを更新し、それが元のパーティション境界を満たさなくなった場合、その行は異なるパーティションに移動されます。
パーティションは自身をパーティション化テーブルであると定義することができ、その結果サブパーティショニングとなります。 すべてのパーティションは親のパーティションと同じ列を持たなければなりませんが、パーティションは他のパーティションとは別の独自のインデックス、制約、デフォルト値を持つことができます。 パーティションテーブルおよびパーティションの作成についてのさらなる詳細についてはCREATE TABLEを参照してください。
通常のテーブルをパーティションテーブルに変更する、およびその逆はできません。
しかし、既存の通常のテーブルやパーティションテーブルをパーティションテーブルのパーティションとして追加する、あるいはパーティションテーブルからパーティションを削除し、それを独立したテーブルにすることは可能です。
これにより多くの保守プロセスを単純化して効率化できます。
ATTACH PARTITION
およびDETACH PARTITION
のサブコマンドについての詳細はALTER TABLEを参照してください。
外部テーブルの内容がパーティション化のルールを満たすようにするのはユーザの責任なので、入念な考慮が必要ではあるものの、パーティションを外部テーブルとすることができます。 他にもいくつか制限事項があります。 詳細はCREATE FOREIGN TABLEを参照してください。
大きなアイスクリーム会社のデータベースを構築している場合を考えましょう。 その会社は毎日の最高気温、および各地域でのアイスクリームの売上を計測します。 概念的には次のようなテーブルが必要です。
CREATE TABLE measurement ( city_id int not null, logdate date not null, peaktemp int, unitsales int );
このテーブルの主な利用目的は経営層向けにオンラインの報告書を作成することであるため、ほとんどの問い合わせは単に直前の週、月、四半期のデータにアクセスするだけであることがわかっています。 保存すべき古いデータの量を削減するため、最近3年分のデータのみを残すことに決めました。 各月のはじめに、最も古い月のデータを削除します。 この場合、計測テーブルについての様々な要求のすべてを、パーティショニングを使って満たすことができます。
この場合に宣言的パーティショニングを使うには、以下の手順に従います。
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);
パーティションを作成します。 各パーティションの定義では、親のパーティショニング方式およびパーティションキーに対応する境界を指定しなければなりません。 新しいパーティションの値が一つ以上の既存のパーティションの値と重なるような境界を指定するとエラーになることに注意してください。 既存のおよびパーティションのどれにも当てはまらないデータを親テーブルに挿入するとエラーになります。 この場合、適切なパーティションを手作業で追加しなければなりません。
こうして作成されたパーティションは、すべての点において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
列に基いてさらにその下のパーティションの一つにリダイレクトされます。
指定するパーティションキーは親のパーティションキーと重なっても構いませんが、サブパーティションの境界を指定するときは、それが受け付けるデータの集合がパーティション自体の境界でできるものの部分集合を構成するように注意してください。
システムは本当にそのようになっているかどうか、検査しようとしません。
既存のパーティションにマップされない親テーブルにデータを挿入しようとするとエラーになります。 手動で適切なパーティションを追加しなければなりません。
パーティションのパーティション境界条件を記述するテーブル制約を手動で作る必要はありません。 そのような制約は自動的に作られます。
キー列にインデックスを作成し、またその他のインデックスも必要に応じてパーティションテーブル上に作成します。 (厳密に言えば、キー列のインデックスが必要なわけではありませんが、ほとんどの場合に役に立つでしょう。) これは個々のパーティションに対応するインデックスを自動的に作るので、作成したすべてのパーティション、あるいは後でアタッチしたパーティションもそのようなインデックスを持ちます。 パーティションテーブルのインデックスあるいは一意制約はパーティションテーブルがそうであるのと同様、「仮想」です。 実際のデータは個々のパーティションテーブル上の子インデックスにあります。
CREATE INDEX ON measurement (logdate);
postgresql.conf
で設定パラメータenable_partition_pruningが無効になっていないことを確認します。
これが無効になっていると、問い合わせが期待通りには最適化されません。
上記の例では、毎月、新しいパーティションを作ることになりますから、必要なDDLを自動的に生成するスクリプトを作るのが賢明かもしれません。
最初にテーブルを定義した時に作成したパーティションの集合は、通常はそのまま静的に残ることを意図したものではありません。 古いデータを持つパーティションを削除し、新しいデータの入った新しいパーティションを定期的に作成したいというのが普通です。 パーティショニングのもっとも重要な利点の一つは、パーティショニングがなければ大変なことになるであろうこの作業を、大量のデータを物理的に動かすのではなく、パーティション構造を操作することにより、ほとんど一瞬にして実行できるという、まさにそのことなのです。
古いデータを削除する最も単純な方法は、次のように、不要になったパーティションを削除することです。
DROP TABLE measurement_y2006m02;
これはすべてのレコードを個別に削除する必要がないため、数百万行のレコードを非常に高速に削除できます。
ただし、上記のコマンドは親テーブルについてACCESS EXCLUSIVE
ロックを取得する必要があることに注意してください。
別の方法で多くの場合に望ましいのは、パーティションテーブルからパーティションを削除する一方で、パーティションそれ自体はテーブルとしてアクセス可能なまま残すことです。 これには2つの形式があります。
ALTER TABLE measurement DETACH PARTITION measurement_y2006m02; ALTER TABLE measurement DETACH PARTITION measurement_y2006m02 CONCURRENTLY;
こうすると、データを削除する前に、そのデータについて追加の操作が実行できます。
例えば、COPY
、pg_dumpや類似のツールを使ってデータのバックアップをする好機となることが多いでしょう。
また、データを集計してより小さな形式にする、その他のデータ操作を実行する、レポート作成を実行するなどのための好機となるかもしれません。
コマンドの最初の形式はACCESS EXCLUSIVE
ロックを親テーブルに必要とします。
二番目の形式のようにCONCURRENTLY
修飾子を追加すると、デタッチ操作の際にSHARE UPDATE EXCLUSIVE
を親テーブルにかけるだけで済みますが、制限の詳細についてはALTER TABLE ... DETACH PARTITION
を参照してください。
同様に、新しいデータを扱うために新しいパーティションを追加できます。 上で元のパーティションを作ったのと全く同じように、パーティションテーブル内に空のパーティションを以下のように作成できます。
CREATE TABLE measurement_y2008m02 PARTITION OF measurement FOR VALUES FROM ('2008-02-01') TO ('2008-03-01') TABLESPACE fasttablespace;
別の方法として、パーティション構造の外で新しいテーブルを作成し、後でパーティションとしてアタッチする方が便利なことがあります。
これにより、パーティションテーブルに現れる前に新しいデータをロードし、チェックし、変換することができます。
さらに、ATTACH PARTITION
操作は、CREATE TABLE ... PARTITION OF
で必要とされるACCESS EXCLUSIVE
ロックとは対照的に、パーティションテーブルに対してはSHARE UPDATE EXCLUSIVE
ロックを必要とするので、パーティションテーブルに対する並行操作に対してよりフレンドリーです。
CREATE TABLE ... LIKE
オプションは、親テーブルの定義を繰り返し書くのを避けるのに役立ちます。
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' -- その他のデータ準備操作を行うこともあります。 ALTER TABLE measurement ATTACH PARTITION measurement_y2008m02 FOR VALUES FROM ('2008-02-01') TO ('2008-03-01' );
上で説明したように、ATTACH PARTITION
コマンドを実行する前に、マッチするパーティション制約を記述するCHECK
制約を、パーティションに追加するテーブルに作成することを推奨します。
こうすることで、システムは、そうしなければ必要になる暗示的なパーティション制約を検証するための走査を、省略できます。
このようなCHECK
制約がなければ、そのパーティションにACCESS EXCLUSIVE
ロック、を保持したままで、パーティション制約を検証するためにテーブルを走査することになります。
もう不要になったこのCHECK
制約はATTACH PARTITION
が終わった後に削除することを推奨します。
アタッチされるテーブル自体がパーティションテーブルなら、適切なCHECK
制約が見つかるか、リーフパーティションに到達するまで個々のサブパーティションは再帰的にロックされ走査されます。
同様に、そのパーティションがDEFAULT
パーティションを持っているなら、アタッチ予定のパーティションの制約を含まないCHECK
制約を作成することをお勧めします。
これをしておかないと、アタッチ予定のパーティション中にレコードがないことを確認するためにDEFAULT
パーティションが走査されます。
この操作の間、ACCESS EXCLUSIVE
ロックがDEFAULT
パーティションに保持されます。
DEFAULT
パーティション自体がパーティションテーブルなら、個々のパーティションは、上で述べたようにアタッチ予定のテーブルと同じ方法で走査されます。
前述のとおり、階層全体に自動で適用されるようにパーティションテーブル上にインデックスを作成することが可能です。
既存のパーティションだけではなく将来作成されるパーティションもインデックス付けされるため、これはとても便利です。
一つの制限は、そのようなパーティションのインデックスを作成する場合CONCURRENTLY
句を使うことができません。
長いロック時間を避けるためには、パーティションテーブルにCREATE INDEX ON ONLY
を使うことが可能です。
そのようなインデックスは無効とマークされ、パーティションは適用するインデックスを自動で取得しません。
パーティション上のインデックスはCONCURRENTLY
を使用して個々に作成することができ、後からALTER INDEX .. ATTACH PARTITION
を使用して親のインデックスにattachedできます。
全てのパーティションに対してインデックスがアタッチされた時点で、親のインデックスは、自動で有効とマークされます。
例を示します。
CREATE INDEX measurement_usls_idx ON ONLY measurement (unitsales); CREATE INDEX CONCURRENTLY measurement_usls_200602_idx ON measurement_y2006m02 (unitsales); ALTER INDEX measurement_usls_idx ATTACH PARTITION measurement_usls_200602_idx; ...
この手法は、UNIQUE
とPRIMARY 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; ...
パーティションテーブルには以下の制限事項があります。
パーティション化テーブルに一意性制約または主キー制約を作成するには、パーティションキーに式または関数呼び出しが含まれないようにし、制約の列にすべてのパーティションキー列が含まれている必要があります。 この制限が存在するのは、制約を構成する個々の索引が、独自のパーティション内でのみ一意性を直接強制できるためです。 したがって、パーティション構造自体が異なるパーティションに重複がないことを保証する必要があります。
すべてのパーティションにまたがる排他制約を作成する方法はありません。 個々のリーフパーティションにそうした制約を設定することだけが可能です。 繰り返しますが、この制限はパーティションをまたがる制限を強制することができないことに起因しています。
INSERT
のBEFORE ROW
トリガーは、どのパーティションが新しい行の最終目的地であるかを変更することはできません。
一時リレーションと永続的リレーションを同じパーティションツリーに混合することはできません。 ですから、パーティション化されたテーブルが永続的なら、パーティションも永続的でなければなりません。 同様にパーティション化されたテーブルが一時的なら、パーティションも一時的でなければなりません。 一時リレーションを使う場合は、パーティションツリーのすべてのメンバは同じセッションに由来しなければなりません。
個々のパーティションは継承を背景にパーティションテーブルに紐付けられています。 しかし、宣言的パーティションテーブルもしくはそれらのパーティションでは継承の一般的な機能の一部(後述)を使用することはできません。 例えば、パーティションテーブルのパーティションは、そのパーティションテーブル以外の親を持つことができませんし、また一般のテーブルはパーティションテーブルをその親にしてパーティションテーブルから継承することはできません。 これはつまり、パーティションテーブルおよびそれらのパーティションは一般のテーブルと継承によって繋がることができないということです。
パーティションテーブルとそのパーティションを構成するパーティションの階層は継承の階層でもあるので、tableoid
と継承におけるすべての通常の規則が5.10で説明したとおりに適用されますが、いくつか例外があります。
最も重要な例外を以下に示します。
パーティションは親に存在しない列を持つことができません。
パーティションをCREATE TABLE
で作成する時に列を指定することはできませんし、作成後にALTER TABLE
でパーティションに列を追加することもできません。
テーブルをALTER TABLE ... ATTACH PARTITION
でパーティションとして追加できるのは、その列が完全に親と一致している場合のみです。
パーティションテーブルのCHECK
制約とNOT NULL
制約はいずれも必ずすべてのパーティションに継承されます。
パーティションテーブルでNO INHERIT
の印を付けたCHECK
制約を作ることはできません。
同じ制約が親テーブルに存在する場合、親テーブルの列に存在するNOT NULL
制約をパーティションの列から削除することはできません。
パーティションテーブルに対してのみ制約を追加または削除する場合、パーティションが存在しない限り、ONLY
を使用することがサポートされています。
ひとたびパーティションが存在すれば、ONLY
を使用すると、UNIQUE
およびPRIMARY KEY
以外の制約に対してエラーが発生します。
その代わりに、パーティション自身の制約を追加することや(親テーブルに存在しない場合)削除することが可能です。
パーティションテーブルは直接データを所有することはないため、TRUNCATE
ONLY
をパーティションテーブルに対して使用しようとすると、必ずエラーが返されます。
組み込みの宣言的パーティショニングは、ほとんどの一般的な利用例に適合しますが、もっと柔軟な方式が便利な状況もあります。 パーティショニングはテーブルの継承を使用して実装することも可能で、これは宣言的パーティショニングではサポートされない以下のような機能が利用できます。
宣言的パーティショニングの場合、パーティションは正確にパーティションテーブルと同じ列の集合を持たなければなりません。 一方テーブルの継承では、子テーブルは親テーブルに存在しない追加の列を持つかもしれません。
テーブルの継承では、複数の継承が可能です。
宣言的パーティショニングではリストパーティショニング、範囲パーティショニングとハッシュパーティショニングしかサポートされませんが、テーブルの継承ではユーザが選択した方法に従ってデータを分割できます。 (ただし、制約による除外が子テーブルを効果的に分離できない場合、問い合わせのパフォーマンスが悪くなるかもしれないことに注意してください。)
この例は、上の宣言的パーティショニングの例と等価な構造のパーティショニングを作成しています。 以下の手順に従います。
「root」テーブルを作成します。
すべての「子」テーブルはこれを継承します。
このテーブルにデータは含まれません。
子テーブルに同じように適用されるのでなければ、このテーブルにチェック制約を定義しないでください。
このテーブル上にインデックスや一意制約を定義することにも意味はありません。
以下の例では、rootテーブルは最初に定義したのと同じmeasurement
テーブルです。
CREATE TABLE measurement ( city_id int not null, logdate date not null, peaktemp int, unitsales int );
いくつかの「子」テーブルを作成し、それぞれrootテーブルを継承するものにします。 通常、これらのテーブルはrootから継承したものに列を追加しません。 宣言的パーティショニングの場合と同じく、これらのテーブルはすべての点で普通の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);
子テーブルに、重なり合わないテーブル制約を追加し、各テーブルに許されるキー値を定義します。
典型的な例は次のようなものです。
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);
各子テーブルについて、キー列にインデックスを作成し、またその他のインデックスも必要に応じて作成します。
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 ...
を実行することができ、そのときにデータが適切な子テーブルにリダイレクトされることが望ましいです。
rootテーブルに適当なトリガー関数を追加することでそのような設定にできます。
データが最後の子テーブルにしか追加されないなら、次のような非常に単純なトリガー関数を使うことができます。
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
制約と正確に一致させなければならないことに注意してください。
この関数は単一月の場合より複雑になりますが、頻繁に更新する必要はありません。なぜなら条件分岐を前もって追加しておくことが可能だからです。
実際には、ほとんどの挿入が一番新しい子テーブルに入る場合は、その子を最初に検査することが最善です。 簡単にするため、この例でのほかの部分と同じ順番でのトリガのテストを示しました。
挿入を適切な子テーブルにリダイレクトする別の方法は、rootテーブルにトリガーではなくルールを設定することです。 例えば次のようにします。
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
を使いたい場合は、rootではなく正しい子テーブルにコピーする必要があります。
トリガーであればCOPY
でも起動されるので、トリガーを使う方法であれば通常通りに使用できます。
ルールを使う方法のもう一つの欠点は、ルールの集合が挿入日付に対応しきれていない場合に、強制的にエラーにする簡単な方法がないことです。 この場合、データは警告などを出すことなくrootテーブルに入ります。
設定パラメータconstraint_exclusionがpostgresql.conf
で無効にされないようにしてください。
他の子テーブルが不要にアクセスされるかもしれません。
以上のように、複雑なテーブルの階層はたくさんのDDLが必要となります。 上記の例では、毎月新しい子テーブルを作成することになりますが、必要となるDDLを自動的に生成するスクリプトを書くのが賢明です。
古いデータを高速に削除するには、不要になった子テーブルを単に削除します。
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;
継承を使用して実装したパーティショニングには以下の注意事項があります。
すべてのCHECK
制約が相互に排他的であることを自動的に確認する手段はありません。
各子テーブルを手作業で作成するよりも、子テーブルを生成し、関連オブジェクトを作成、更新するコードを作成するのが安全でしょう。
インデックスと外部キー制約は継承上の子ではなく、単一テーブルに適用されます。したがってそれらは警告に気を付ける必要があります。
ここで示した方法は、行のキー列の値が変わらないか、あるいは、少なくとも他のパーティションへの移動が必要になるような変更はないということを前提としています。
そのような変更をしようとするUPDATE
はCHECK
制約のためにエラーになります。
このような場合を処理できる必要があるなら、子テーブルに適切なUPDATEトリガーを設定することもできますが、構造の管理がずっと複雑になります。
手作業でVACUUM
あるいはANALYZE
コマンドを実行している場合、それを個々の子テーブルに対して実行する必要があることを忘れないでください。
次のようなコマンドは、
ANALYZE measurement;
rootテーブルしか処理しません。
ON CONFLICT
句のあるINSERT
文は恐らく期待通りに動作しないでしょう。
ON CONFLICT
の動作は対象となる指定リレーション上での一意制約違反の場合にのみ発生するもので、その子リレーションの場合には発生しないからです。
アプリケーションがパーティショニングのスキームについて明示的に意識しているのでなければ、トリガーまたはルールで行を適切な子テーブルに振り向ける必要があります。 トリガーを書くのは複雑であり、また宣言的パーティショニングによって内部的に実行されるタプルの振り向けよりずっと遅いでしょう。
パーティション除去は、宣言的パーティショニングテーブルに対するパフォーマンスを向上させる問い合わせの最適化技術です。 例えば、
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) -> Seq Scan on measurement_y2008m01 (cost=0.00..33.12 rows=617 width=0) Filter: (logdate >= '2008-01-01'::date)
パーティション除去はパーティションキーによって暗黙的に定義された制約のみで動作し、インデックスの有無では動作しないことに注意してください。 よってキー列のインデックスを定義することは必要ではありません。 あるパーティションでインデックスが必要かどうかは、パーティションをスキャンする問い合わせが通常はパーティションの大部分をスキャンするのか、あるいは小さな部分をスキャンするのかによります。 インデックスは後者において役立ちますが、前者では役立ちません。
パーティション除去は与えられた問い合わせの計画時だけでなく、問い合わせの実行時にも可能です。
問い合わせの計画時、句が値のわからない式を含むときにより多くのパーティションを除去できるため便利です。
例えば、PREPARE
文中に定義されたパラメータや、副問い合わせから取得される値の利用、ネステッドループ結合の内側でパラメータ化された値の利用です。
実行中のパーティション除去は、次のいずれかの時点で可能です。
問い合わせ計画の初期化時。
パーティション除去は、パラメータの値が分かる実行の初期化段階時に可能です。
この段階で除去されたパーティションは、問い合わせのEXPLAIN
やEXPLAIN ANALYZE
中に姿を見せることはないでしょう。
EXPLAIN
出力中に「Subplans removed」プロパティを観察することによってこの段階で削除されるパーティションの数を特定することが可能です。
問い合わせ計画の実行時。
パーティション除去では実際に問い合わせの実行をする際にのみ分かる値を用いてパーティションを取り除くことも同様に可能でしょう。
これは、副問い合わせからの値やネステッドループ結合でパラメータ化されたような実行時のパラメータからの値を含みます。
それらのパラメータの値は問い合わせの実行時に何回も変わるかもしれないため、パーティション除去はパーティション除去に使われる実行パラメータの値が変わるたびに行われます。
この段階で除去されたパーティションを特定するには、EXPLAIN ANALYZE
出力中のloops
プロパティの慎重な調査が必要です。
異なるパーティションに対応するサブプランは、それぞれ実行時に除去された回数に応じて異なる値を持っているかもしれません。
毎回パーティションが除去される場合、一部は(never executed)
と表示されるでしょう。
パーティション除去はenable_partition_pruning設定を使うことにより無効化できます。
制約による除外はパーティション除去と同様に問い合わせ最適化技術です。 主に従来の継承方法を使用して実装されたパーティショニングのために使用されると同時に 宣言的パーティショニングを含む他の目的に使うことができます。
各テーブルの名前の付いたCHECK
制約を使用すること(一方でパーティション除去は宣言的パーティショニングの場合にのみ存在するテーブルのパーティション境界を使用します)を除いて、制約による除外はパーティション除去と極めて同様な方法で動作します。
その他の違いは、制約による除外は計画時にのみ適用され実行時にパーティションの削除を試しません。
制約による除外はCHECK
制約を使用しているためパーティション除去と比べて遅いですが、ときどき利点として使うことができます。
なぜなら、内部のパーティション境界に加えて宣言的パーティションテーブルにも制約は定義できるため、制約による除外は問い合わせ計画から追加のパーティションを取り除けるかもしれません。
実のところ、constraint_exclusionのデフォルト(かつ推奨)の設定は、on
でもoff
でもなく、partition
という中間の設定です。
これによりこの技法は、継承パーティションテーブルに対して動作することになる問い合わせのみに適用されるようになります。
on
設定にすると、プランナは、効果のなさそうな単純な問い合わせを含め、すべての問い合わせでCHECK
制約を検証します。
制約による除外には以下の注意事項が適用されます。
問い合わせの実行中にも適用できるパーティション除去とは違い、制約による除外は問い合わせ計画時にのみ適用されます。
制約による除外は問い合わせのWHERE
句が定数(または外部から供給されたパラメータ)を含んでいたときにのみ動作します。例えば、CURRENT_TIMESTAMP
のような非immutable関数に対する比較は、関数の結果値がどの子テーブルに該当するかを実行時にプランナが知ることが出来ないため、最適化できません。
パーティショニングの制約を簡単にしておいてください。そうしないとプランナは、子テーブルを使う必要がないことを立証できないでしょう。 前述の例で示したとおり、リストパーティショニングのために簡単な等号条件を使用してください。また範囲パーティショニングのために簡単な範囲テストを使用してください。 手っ取り早い良い方法は、パーティショニングの制約がパーティショニング列とB-treeインデックス作成可能な演算子を用いた定数の比較のみを含んでいることです。 なぜならパーティションキーにはB-treeでインデックス可能な列だけが使用できるからです。
親テーブルのすべての子テーブルのすべての制約は、制約による除外で試験されます。 よって子テーブルの数が多くなれば問い合わせ計画の時間がかなり増加します。 そのため、従来の継承を基にしたパーティショニングはおそらく100個までの子でうまく動作します。 何千もの子テーブルを使用することは避けてください。
不十分な設計によってクエリ計画および実行性能に負の影響がでる可能性があるため テーブルのパーティション方法の選択は注意して行う必要があります。
最も重要な設計の決定の一つは、データを分割するための一つまたは複数の列です。
大抵最適な選択は、パーティションテーブル上で実行されるクエリのWHERE
句に最もよく現れる列または列の組み合わせによって分割することです。
パーティション範囲制約と一致し互換性があるWHERE
句の項目は、不要なパーティションを取り除く為に使うことができます。
しかしながら、PRIMARY KEY
もしくはUNIQUE
制約の条件により、他の決定を強いられるかもしれません。
不要なデータの削除も同様にパーティショニング戦略を計画する際に考えるべき要素です。
すべてのパーティションはとても早くデタッチすることができるため、一度に削除される全てのデータが単一のパーティション中に設置されるようにパーティション戦略を設計することが有益かもしれません。
テーブルを分割するパーティションの目標数を選択することもまた需要な決定です。
十分なパーティションがないとインデックスは大きくなりデータの局所性が貧しいままであるかもしれず、キャッシュヒット率が低い結果となる可能性があります。
しかしながら非常に多くのパーティションにテーブルを分割することもまた問題の原因となります。
以下に詳しく説明するように、非常に多くのパーティションは、クエリの計画時間が長くなり、クエリの計画および実行の両方の際にメモリ消費が高くなることを意味します。
テーブルを分割する方法を選択するとき、 将来に起こる変化を考慮することもまた重要です。
例えば、顧客毎に一つのパーティションを用意することを選択し、現在大規模な顧客が少数いる場合、数年以内に小規模な顧客を多数代わりに見つける可能性を含めて考慮します。
この場合、LIST
によって分割しデータの分割が実用的な数以上に顧客の数が増加しないことを期待するより、HASH
によって分割し妥当なパーティション数にすることを選択する方が良いかもしれません。
サブパーティショニングは、他のパーティションより巨大になると想定されるパーティションをさらに分割するために役立ちます。 他の選択は、パーティションキー中に複数の列を含む範囲パーティショニングを使うことです。 これらのどちらも容易に大量のパーティション数をもたらす結果になるので、自制することをお勧めします。
クエリの計画および実行時のパーティショニングのオーバーヘッドを考慮することが重要です。 典型的なクエリではクエリプランナが少数のパーティションを除いて残り全てのパーティションを除外できるという前提に立てば、クエリプランナは通常最大数千パーティションのパーティション階層を適切に操作できます。 プランナがパーティション除去を行った後に多くのパーティションが残るほど、計画時間は長くなりメモリ消費は高くなります。 大量のパーティションを持っていることについて考慮するもうひとつの理由は、特に多くのセッションが大量のパーティションを参照する場合、ある期間にサーバのメモリ消費が著しく増加するかもしれないことです。 その理由は、各パーティションは参照される各セッションのローカルメモリにメタデータを読み込む必要があるためです。
データウェアハウスタイプのワークロードでは、OLTPタイプのワークロードより大量のパーティションを使用するのが当然です。 通常、データウェアハウスでは処理時間の大半をクエリ実行に費やすため、クエリ計画時間はあまり問題になりません。 2種類のワークロードのいずれかでも、大量のデータを再パーティショニングすることは非常に遅いため、初期に適切な決定を下すことが重要です。 計画したワークロードのシミュレーションは、パーティショニング戦略を最適化するためにしばしば役立ちます。 単純に多数のパーティションがより少数のパーティションより優れていることや、少数のパーティションが多数のパーティションより優れていることを前提としないでください。