PostgreSQLは基本的なテーブルのパーティショニング（分割）をサポートしています。 この節では、データベース設計において、なぜそしてどのようにしてパーティショニングを実装するのかを解説します。
パーティショニングは論理的には1つの大きなテーブルであるものを、物理的に小さな部分に分けることを指します。 パーティショニングはいくつかの利点があります。
特定の状況において、問い合わせのパフォーマンスが劇的に改善されます。 具体的には、アクセスが多いテーブル内の行が単一パーティションまたは少数のパーティション内に収まっている場合です。 パーティショニングはインデックスの主要な列の代わりとなり、インデックスサイズが減少します。 このため、インデックス内のよく使用される部分がメモリ内により収まりやすくなります。
問い合わせや更新が単一パーティションの大部分にアクセスする場合、インデックススキャンとテーブル全体に渡って散乱したランダムアクセス読み取りの代わりに、そのパーティションのシーケンシャルスキャンとすることで、性能が改善できます。
一括でのロードや削除の要求をパーティション設計に合うように計画していれば、一括ロードや削除をパーティションの追加や削除によって実行可能です。
ALTER TABLE NO INHERITやDROP TABLEは両方とも一括操作よりもずっと高速です。
また、これらのコマンドは大量のDELETEによって発生するVACUUMのオーバーヘッドを完全に防ぎます。
めったに使用されないデータは、安価で遅い記憶メディアに移行できます。
パーティショニングの利点は、通常はテーブルのサイズがとても大きくなる場合にのみ得られます。 テーブルのパーティショニングによって利益が得られる境目はアプリケーションに依存しますが、経験的にはテーブルのサイズがデータベースサーバの物理メモリを超えている場合です。
現状ではPostgreSQLは、パーティショニングをテーブルの継承によりサポートしています。 それぞれのパーティションは1つの親テーブルの子テーブルとして作成されなくてはいけません。 親テーブル自身は通常、空のテーブルとなり、全体のデータを代表するために存在します。 パーティショニングを設定する前に、継承（5.9. 継承を参照してください）について詳しく知っておく必要があります。
パーティショニングについて次の種類がPostgreSQLに実装されています。
テーブルは、キーとなる列もしくは列の組み合わせにより定義される「範囲」に分割されます。異なるパーティションに割り当てられた値の範囲は重なることはありません。 例えば、日付の範囲により分割されたり、特定のビジネスオブジェクトの識別子の範囲により分割されたりします。
どのキー値がそれぞれのパーティションに現れるかを明示的に列挙することにより、テーブルが分割されます。
テーブルのパーティショニングを実装するには、以下を行ってください。
すべてのパーティションが継承することになる、「マスタ」テーブルを作成してください。
このテーブルはデータを格納しません。 このテーブルにはすべてのパーティションに対して適用されるつもりでなければ検査制約は定義しないでください。 インデックスや一意性制約を定義することも意味がありません。
マスタテーブルから継承された、いくつかの「子」テーブルを作成します。 通常、これらの子テーブルはマスタから継承された列以外には列を追加しないようにします。
これらの子テーブルは、あらゆる点で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;
制約による除外は、上記の方法で定義されたパーティションテーブルに対するパフォーマンスを向上させる問い合わせの最適化技術です。例えば、
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制約を検証するようになります。
適当なパーティションテーブルに中継する別の方法は、トリガの替わりにルールをマスターテーブル上に作成することです。例えば
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;しかしビューを再作成する必要があることで、データセットの個別のパーティションを追加したり削除したりする余分な手順が増えます。 実際には、この方法は継承を使用する方法と比較してお勧めする点はほとんどありません。
以下の警告がパーティション付けテーブルに対して適用されます。
すべてのCHECK制約が相互に排他であるかどうか自動で確認する方法はありません。
パーティションを生成し、関連付けられたオブジェクトを作成もしくは修正するコードを作成する方が、それらをいちいち手書きするよりも安全です。
ここで示す方式は、行のパーティションキー列が変更しない、もしくは少なくとも他のパーティションに移動することが要求されるような変更がないことを仮定しています。
別のパーティションに移るようなUPDATEは、CHECK制約により失敗するでしょう。
このような場合を扱う必要があるなら、適切な更新トリガをパーティションテーブルに設定することができますが、そうすると管理構造がさらに複雑になります。
もし手動のVACUUMもしくはANALYZEコマンドを使用している場合は、それぞれのパーティションで個別に実行することを忘れないでください。
以下のようなコマンドは
ANALYZE measurement;
マスタテーブルのみ処理することになります。
ON CONFLICT句のあるINSERT文は期待通りに動作しない可能性が高いです。
なぜなら、ON CONFLICTの動作は指定した対象のリレーション上で一意性制約違反が発生した場合にのみ起きるのであって、その子のリレーションでは起きないからです。
以下の警告が制約による除外に適用されます。
制約による除外は問い合わせのWHERE句が定数(または外部から供給されたパラメータ)を含んでいたときにのみ動作します。例えば、CURRENT_TIMESTAMPのような非immutable関数に対する比較は、関数の結果値がどのパーティションに該当するかを実行時にプランナが知ることが出来ないため、最適化できません。
パーティション制約を簡単にしておいてください。そうしないとプランナは、パーティションを使う必要がないことを立証できません。 前述の例で示したとおり、リスト分割のために簡単な等号条件を使用してください。また範囲分割のために簡単な範囲テストを使用してください。 手っ取り早い良い方法は、パーティショニングの制約がパーティション列とB-treeインデックス作成可能な演算子を用いた定数の比較のみを含んでいることです。
マスタテーブルのすべてのパーティションのすべての制約は、制約による除外で試験されます。 よってパーティションの数が多くなれば問い合わせ計画の時間がかなり増加します。 これらの技術を使用したパーティショニングは、おそらく100個までのパーティションでうまく動作します。 何千ものパーティションを使用することは避けてください。