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ヶ月のデータのみを保存することにします。月の始めに過去のデータを削除します。
このような場合、measurementsテーブルに対する異なる要求をすべて満たすようにパーティショニングを利用できます。上記で述べた方法で、パーティショニングを次のように設定します。
マスターテーブルは、上記で宣言されたmeasurementテーブルです。
次にそれぞれの月に対して1つのパーティションを作成します。
CREATE TABLE measurement_y2004m02 ( ) INHERITS (measurement); CREATE TABLE measurement_y2004m03 ( ) INHERITS (measurement); ... CREATE TABLE measurement_y2005m11 ( ) INHERITS (measurement); CREATE TABLE measurement_y2005m12 ( ) INHERITS (measurement); CREATE TABLE measurement_y2006m01 ( ) INHERITS (measurement);
それぞれのパーティションは、完結したテーブルですがmeasurementテーブルからの定義を継承しています。
これはデータの削除という問題を解決します。毎月、最も古い子テーブルをDROP TABLEし新規の月に対しては子テーブルを作成するだけでいいのです。
重ならないようなテーブル制約を追加する必要があります。よってテーブル作成のスクリプトは以下のようになります。
CREATE TABLE measurement_y2004m02 (
CHECK ( logdate >= DATE '2004-02-01' AND logdate < DATE '2004-03-01' )
) INHERITS (measurement);
CREATE TABLE measurement_y2004m03 (
CHECK ( logdate >= DATE '2004-03-01' AND logdate < DATE '2004-04-01' )
) INHERITS (measurement);
...
CREATE TABLE measurement_y2005m11 (
CHECK ( logdate >= DATE '2005-11-01' AND logdate < DATE '2005-12-01' )
) INHERITS (measurement);
CREATE TABLE measurement_y2005m12 (
CHECK ( logdate >= DATE '2005-12-01' AND logdate < DATE '2006-01-01' )
) INHERITS (measurement);
CREATE TABLE measurement_y2006m01 (
CHECK ( logdate >= DATE '2006-01-01' AND logdate < DATE '2006-02-01' )
) INHERITS (measurement);
キーとなる列にインデックスが必要になるでしょう。
CREATE INDEX measurement_y2004m02_logdate ON measurement_y2004m02 (logdate); CREATE INDEX measurement_y2004m03_logdate ON measurement_y2004m03 (logdate); ... CREATE INDEX measurement_y2005m11_logdate ON measurement_y2005m11 (logdate); CREATE INDEX measurement_y2005m12_logdate ON measurement_y2005m12 (logdate); CREATE INDEX measurement_y2006m01_logdate ON measurement_y2006m01 (logdate);
今回は、これ以上のインデックスをつけないことにします。
もしデータが最新のパーティションに追加されるのであれば、データの挿入に対して非常に簡単なルールを設定できます。 月がいつも現在のパーティションを指すように定義しなおす必要があります。
CREATE OR REPLACE RULE measurement_current_partition AS
ON INSERT TO measurement
DO INSTEAD
INSERT INTO measurement_y2006m01 VALUES ( NEW.city_id,
NEW.logdate,
NEW.peaktemp,
NEW.unitsales );データ挿入と行が追加されるべきパーティションを自動的にサーバに見つけてもらうことが必要になります。これは以下のようなもっと複雑なルールを作成することにより可能です。
CREATE RULE measurement_insert_y2004m02 AS
ON INSERT TO measurement WHERE
( logdate >= DATE '2004-02-01' AND logdate < DATE '2004-03-01' )
DO INSTEAD
INSERT INTO measurement_y2004m02 VALUES ( NEW.city_id,
NEW.logdate,
NEW.peaktemp,
NEW.unitsales );
...
CREATE RULE measurement_insert_y2005m12 AS
ON INSERT TO measurement WHERE
( logdate >= DATE '2005-12-01' AND logdate < DATE '2006-01-01' )
DO INSTEAD
INSERT INTO measurement_y2005m12 VALUES ( NEW.city_id,
NEW.logdate,
NEW.peaktemp,
NEW.unitsales );
CREATE RULE measurement_insert_y2006m01 AS
ON INSERT TO measurement WHERE
( logdate >= DATE '2006-01-01' AND logdate < DATE '2006-02-01' )
DO INSTEAD
INSERT INTO measurement_y2006m01 VALUES ( NEW.city_id,
NEW.logdate,
NEW.peaktemp,
NEW.unitsales );それぞれのルールにおいて、WHERE句は、そのパーティションのCHECK制約に正確に一致することに注意してください。
以上のように、複雑なパーティション化の計画はたくさんのDDLが必要となります。上記の例では、毎月新しいパーティションを作成することになりますが、必要となるDDLを自動的に生成するスクリプトを書くのが賢明です。
パーティションはUNION ALLビューを使用し整理することが可能です。
CREATE VIEW measurement AS
SELECT * FROM measurement_y2004m02
UNION ALL SELECT * FROM measurement_y2004m03
...
UNION ALL SELECT * FROM measurement_y2005m11
UNION ALL SELECT * FROM measurement_y2005m12
UNION ALL SELECT * FROM measurement_y2006m01;しかし、ビューを再作成するにはデータセットの個々のパーティションを追加したり削除したりするような余分な手順が必要になります。
通常、初期のテーブル定義で静的状態を意図しない場合に、パーティション群は確立します。 古いデータのパーティションの削除や新規データ向けの定期的な新規パーティションの追加という要求はよくあります。 パーティショニングの最も重要な利点は正確性です。 このため、こうしたどちらかといえば危険を伴う作業を、大量のデータを移動させずに、パーティション構造を操作することでほとんど瞬間的に行うことができます。
古いデータを削除する最も簡単な方法は、単に不要となったパーティションを削除することです。
DROP TABLE measurement_y2003m02;
レコードごとに削除する必要がありませんので、これは高速に100万レコードを削除することができます。
この他、よく使用される方法は、テーブル自体へのアクセス権限をそのまま残したまま、パーティション付けされたテーブルからパーティションを削除することです。
ALTER TABLE measurement_y2003m02 NO INHERIT measurement;
これにより、削除前にデータ操作をさらに行うことができます。 たとえば、COPY、pg_dumpなどのツールを使用してデータをバックアップすることはよくあります。 また、データをより小さな書式に集約したり、他のデータ操作を行ったり、報告を作成したりすることもよくあります。
同様に、新しいデータを扱うために新しいパーティションを追加することもできます。 上で元々のパーティションを作成した時と同じように、パーティション付けテーブルに空のパーティションを作成することができます。
CREATE TABLE measurement_y2006m02 (
CHECK ( logdate >= DATE '2006-02-01' AND logdate < DATE '2006-03-01' )
) INHERITS (measurement);この他の方法として、パーティション構造の外部で新しいテーブルを作成し、後で適切にパーティションとすることが便利な場合もあります。 これにより、パーティション付けしたテーブルに追加する前に、データをロードし、検査、変換することができます。
CREATE TABLE measurement_y2006m02 (LIKE measurement INCLUDING DEFAULTS INCLUDING CONSTRAINTS); ALTER TABLE measurement_y2006m02 ADD CONSTRAINT y2006m02 CHECK ( logdate >= DATE '2006-02-01' AND logdate < DATE '2006-03-01' ); \copy measurement_y2006m02 from 'measurement_y2006m02' -- 何らかの準備操作を行うことができます。 ALTER TABLE measurement_y2006m02 INHERIT measurement;
制約による除外は、上記の方法で定義されたパーティショニングされたテーブルに対するパフォーマンスを向上させる問い合わせの最適化技術です。例えば、
SET constraint_exclusion = on; SELECT count(*) FROM measurement WHERE logdate >= DATE '2006-01-01';
制約による除外が無い場合、上記の問い合わせはmeasurementテーブルのパーティションをスキャンするでしょう。 制約による除外が有効になっているとき、プランナはそれぞれのパーティションの制約を調べて、パーティションが問い合わせのWHEREに一致する行を含んでいないためにスキャンされる必要が無いと分析しようとします。
constraint_exclusionを有効とした計画と無効にした計画の違いを見るために、EXPLAINコマンドを使用できます。 この型のテーブル設定に対する典型的なデフォルトの計画は以下のようになります。
SET constraint_exclusion = off;
EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2006-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 >= '2006-01-01'::date)
-> Seq Scan on measurement_y2004m02 measurement (cost=0.00..30.38 rows=543 width=0)
Filter: (logdate >= '2006-01-01'::date)
-> Seq Scan on measurement_y2004m03 measurement (cost=0.00..30.38 rows=543 width=0)
Filter: (logdate >= '2006-01-01'::date)
...
-> Seq Scan on measurement_y2005m12 measurement (cost=0.00..30.38 rows=543 width=0)
Filter: (logdate >= '2006-01-01'::date)
-> Seq Scan on measurement_y2006m01 measurement (cost=0.00..30.38 rows=543 width=0)
Filter: (logdate >= '2006-01-01'::date)一部のパーティション、もしくはすべてのパーティションで、テーブル全体に対するシーケンシャルスキャンではなく、インデックススキャンが使用される可能性があります。 しかしここでのポイントは、この問い合わせに対する回答のために古いパーティションをスキャンする必要はまったく無いということです。 制約による除外を有効にしたとき、大幅に小さくなった同じ回答を返す計画を得ることができます。
SET constraint_exclusion = on;
EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2006-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 >= '2006-01-01'::date)
-> Seq Scan on measurement_y2006m01 measurement (cost=0.00..30.38 rows=543 width=0)
Filter: (logdate >= '2006-01-01'::date)
制約による除外はCHECK制約のみで動作し、インデックスの有無では動作しないことに注意してください。よってキー列のインデックスを定義することは必要ではありません。 あるパーティションでインデックスが必要かどうかは、パーティションをスキャンする問い合わせがパーティションの大部分もしくは小さな部分をスキャンするのかによります。前者ではなく後者において、インデックスは役立ちます。
以下の警告がパーティション付けテーブルに対して適用されます。
現在、すべてのCHECK制約が互いに排他的であることを検査する方法はありません。 データベース設計者が注意しなければなりません。
現在、行がマスタテーブルに確実に挿入されないことを指定する簡単な方法はありません。 マスタテーブル上のCHECK (false)制約は、すべての子テーブルで継承されます。 そのため、この目的のためには使用することはできません。 マスタテーブル上に常にエラーを生成するON INSERTトリガを設定する方法を取ることで実現できます。 （他にも、こうしたトリガを使用して、上で提案したルールを使用するのではなく、適切な子テーブルにデータを転送することもできます。）
以下の警告が制約による除外に適用されます。
制約の除外は問い合わせのWHERE句が定数を含んでいたときのみに動作します。パラメータ化された問い合わせは最適化されません。その理由はプランナは実行時に、パラメータ値がどのパーティションを選択するか知り得ないためです。
同様の理由で、CURRENT_DATEのような"安定"関数は避けなくてはいけません。
CHECK制約内でデータ型を跨ぐような比較はしないでください。現状でプランナはそのような条件を偽と分析することに失敗します。 例えば次の制約はxがinteger列であればうまく動作しますが、xがbigintであれば動作しません。
CHECK ( x = 1 )
bigint列の場合は以下のように定数を使用しなくてはいけません
CHECK ( x = 1::bigint )
この問題はbigintのデータ型に限ったことではありません。—定数のデフォルトのデータ型が比較される列のデータ型に一致していないときにはいつでも発生します。 問い合わせにおいてデータ型を跨ぐ比較はたいていの場合は大丈夫ですが、CHECK条件の場合はよくありません。
マスターテーブルのすべてのパーティションのすべての制約は、制約による除外で考慮されます。よってパーティションの数が多くなれば実行計画の時間が増加します。
それぞれのパーティションに対してANALYZEコマンドが必要です。
ANALYZE measurement;
このコマンドはマスターテーブルのみを処理します。