PostgreSQLは基本的なテーブルのパーティショニング（分割）をサポートしています。 この節では、データベース設計において、なぜそしてどのようにしてパーティショニングを実装するのかを解説します。
パーティショニングは論理的に1つの大きなテーブルを、物理的に小さなパーティションに分けることを指します。 パーティショニングはいくつかの利点があります。
特定の状況において、問い合わせのパフォーマンスが劇的に改善されます。 具体的には、アクセスが多いテーブル内の行を単一パーティションまたは一部のパーティション内に収めることです。 パーティショニングはインデックスの先頭の列を置き換え、インデックスサイズが減少します。 このため、インデックス内のよく使用される部分がメモリ内により収まりやすくなります。
問い合わせや更新が単一パーティションの大部分にアクセスする場合、インデックススキャンとテーブル全体に渡って散乱したランダムアクセス読み取りの代わりに、そのパーティションのシーケンシャルスキャンとすることができ、性能は改善します。
その仕様がパーティション設計に合うように計画されていれば、大量のロードや削除が、パーティションの追加や削除によってなされる可能性があります。 ALTER TABLEは大量操作よりもずっと高速です。 また、大量のDELETEによって発生するVACUUMのオーバーヘッドを完全に防ぎます。
めったに使用されないデータは、安価で遅い記憶メディアに移行できます。
この利点は、テーブルのサイズがとても大きくなる場合に価値が出てきます。 テーブルのパーティショニングによる利点はアプリケーションに依存しますが、経験的にテーブルのサイズがデータベースサーバの物理メモリを超えるかどうかということがポイントになります。
現状ではPostgreSQLは、パーティショニングをテーブルの継承によりサポートしています。 それぞれのパーティションは1つの親テーブルの子テーブルとして作成されなくてはいけません。 親テーブル自身は通常、空のテーブルとなり、全体のデータを代表するために存在します。 パーティショニングを設定する前に、継承（項5.8を参照してください）について詳しく知っておく必要があります。
パーティショニングについて次の種類が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;
レコードごとに削除する必要がありませんので、これは高速に100万レコードを削除することができます。
この他、よく使用される方法は、テーブル自体へのアクセス権限をそのまま残したまま、パーティション付けされたテーブルからパーティションを削除することです。
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制約のみで動作し、インデックスの有無では動作しないことに注意してください。よってキー列のインデックスを定義することは必要ではありません。 あるパーティションでインデックスが必要かどうかは、パーティションをスキャンする問い合わせがパーティションの大部分もしくは小さな部分をスキャンするのかによります。前者ではなく後者において、インデックスは役立ちます。
適当なパーティションテーブルにリダイレクトする別の方法は、トリガの替わりにルールをマスターテーブル上に作成することです。例えば
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はトリガを起動します。よってトリガを使用する方法をとれば、トリガを使用することができます。
ルールを使用する方法のもうひとつの欠点は、一連のルールが挿入日付を扱わないときにエラーを強制する簡単な方法がないことです。データは替わりにマスターテーブルに暗黙のうちに挿入されます。
パーティショニングは、テーブルの継承の替わりに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;
マスターテーブルのみ処理することになります。
以下の警告が制約による除外に適用されます。
制約の除外は問い合わせのWHERE句が定数を含んでいたときのみに動作します。パラメータ化された問い合わせは最適化されません。その理由はプランナは実行時に、パラメータ値がどのパーティションを選択するか知り得ないためです。
同様の理由で、CURRENT_DATEのような"安定"関数は避けなくてはいけません。
パーティション制約を簡単にしておいてください。そうしないとプランナは、パーティションを使う必要がないことを立証できません。 前述の例で示したとおり、リスト分割のために簡単な等号条件を使用してください。また範囲分割のために簡単な範囲テストを使用してください。 手っ取り早い良い方法は、パーティショニングの制約がパーティション列とB木索引作成可能な演算子を用いた定数の比較のみを含んでいることです。
マスターテーブルのすべてのパーティションのすべての制約は、制約による除外で試験されます。よってパーティションの数が多くなれば実行計画の時間がかなり増加します。 これらの技術を使用したパーティショニングは、おそらく100個までのパーティションでうまく動作します。何千ものパーティションを使用することは避けてください。