パーティショニングPostgreSQLは基本的なテーブルのパーティショニング(分割)をサポートしています。この節では、データベース設計において、なぜそしてどのようにしてパーティショニングを実装するのかを解説します。概要パーティショニングは論理的に1つの大きなテーブルを、物理的に小さなパーティションに分けることを指します。パーティショニングはいくつかの利点があります。特定の状況において、問い合わせのパフォーマンスが劇的に改善されます。具体的には、アクセスが多いテーブル内の行を単一パーティションまたは一部のパーティション内に収めることです。パーティショニングはインデックスの先頭の列を置き換え、インデックスサイズが減少します。このため、インデックス内のよく使用される部分がメモリ内により収まりやすくなります。問い合わせや更新が単一パーティションの大部分にアクセスする場合、インデックススキャンとテーブル全体に渡って散乱したランダムアクセス読み取りの代わりに、そのパーティションのシーケンシャルスキャンとすることができ、性能は改善します。その指定がパーティション設計に合うように計画されていれば、一括でのロードや削除が、パーティションの追加や削除によってなされる可能性があります。TABLEは両方とも一括操作よりもずっと高速です。これらのコマンドは大量のDELETEによって発生するVACUUMのオーバーヘッドを完全に防ぎます。めったに使用されないデータは、安価で遅い記憶メディアに移行できます。この利点は、テーブルのサイズがとても大きくなる場合に価値が出てきます。テーブルのパーティショニングによる利点はアプリケーションに依存しますが、経験的にテーブルのサイズがデータベースサーバの物理メモリを超えるかどうかということがポイントになります。現状ではPostgreSQLは、パーティショニングをテーブルの継承によりサポートしています。それぞれのパーティションは1つの親テーブルの子テーブルとして作成されなくてはいけません。親テーブル自身は通常、空のテーブルとなり、全体のデータを代表するために存在します。パーティショニングを設定する前に、継承(項5.8を参照してください)について詳しく知っておく必要があります。パーティショニングについて次の種類がPostgreSQLに実装されています。範囲分割テーブルは、キーとなる列もしくは列のセットにより定義される"範囲"に分割されます。異なるパーティションに割り当てられた値の範囲は重なることはありません。日付の範囲により分割されたり、特定のビジネスオブジェクトの識別子の範囲により分割されたりします。リスト分割キー値がそれぞれのパーティションに現れるような明示的なリストにより、テーブルが分割されます。パーティショニングの実装テーブルのパーティショニングを実装するには、以下を行ってください。すべてのパーティションが継承することになる、"マスタ"テーブルを作成してください。このテーブルはデータを格納しません。このテーブルにはすべてのパーティションに対して適用されるつもりでなければ検査制約は定義しないでください。同様にインデックスや一意制約を定義することも意味がありません。マスタテーブルから継承された、いくつかの"子"テーブルを作成します。通常、これらの子テーブルはマスタから継承された列以外には列を追加しないようにします。子テーブルは、あらゆる点でPostgreSQLの普通のテーブルですが、子テーブルをパーティションとして参照することになります。それぞれのパーティションでのキー値を定義するために、分割されたテーブルにテーブル制約を追加してください。)制約が、異なるパーティションにおいて許可されているキー値の間で重なりが無いことを保証してください。ある間違いは範囲制約を次のように設定してしまうことです。)キー値の200がどちらのパーティションに属するのかが明確になっていないため、これは間違いになります。範囲分割とリスト分割の間に構文の違いは無いことに注意してください。これらの字句は記述上のものだけです。それぞれのパーティションにおいて、他のインデックスと同様にキーとなる列(列の集合)にインデックスを作成してください。(キーのインデックスは必ずしも必要でありませんが、たいていの場合に役立ちます。キー値が一意であることを意図するのであればいつでも、一意もしくは主キー制約をそれぞれのパーティションに作成してください。)また、マスタテーブルに挿入されたデータを適当なパーティションに中継するためにトリガもしくはルールを定義してください。constraint_exclusion設定パラメータがpostgresql.conf内で無効になっていないことを確認してください。無効であると、問い合わせは思ったとおりに最適化されません。大規模なアイスクリーム会社のデータベースを構築すると仮定してください。会社は、それぞれの地方のアイスクリームの売上と同様に毎日の最高気温を計測しています。概念的に、次のようなテーブルが必要になります。int);ほとんどの問い合わせが先週、先月もしくは半年前のデータを問い合わせるものであることが分かっています。その理由は、このテーブルが経営に関するオンラインのレポートを作成することに主に使用されるからです。必要な過去のデータ量を減らすために、過去3ヶ月のデータのみを保存することにします。月の始めに過去のデータを削除します。このような場合、measurementテーブルに対する異なる要求をすべて満たすようにパーティショニングを利用できます。上記で述べた方法で、パーティショニングを次のように設定します。マスタテーブルは、上記で宣言されたmeasurementテーブルです。次にそれぞれの月に対して1つのパーティションを作成します。(measurement);それぞれのパーティションは、完結したテーブルですがmeasurementテーブルからの定義を継承しています。これは古いデータの削除という問題を解決します。TABLEし新規の月に対しては子テーブルを作成するだけでいいのです。重なりの無いテーブル制約を付ける必要があります。上記のようにパーティションを単に作成するよりも、以下のようにすべきでしょう。(measurement);キーとなる列にインデックスが必要になるでしょう。(logdate);今回は、これ以上のインデックスを付けないことにします。...を実行したら、適切なパーティションテーブルにデータが中継されるようにしたいとします。そのような場合は、適切なトリガ関数をマスタテーブルに設定することにより可能となります。データが最新のパーティションにのみ追加される場合は、非常に簡単なトリガ関数を使用できます。plpgsql;関数を作成した後で、このトリガ関数を呼ぶトリガを作成します。measurement_insert_trigger();毎月、トリガ関数を再定義しなくてはいけません。それにより現在のパーティションをいつも指すことになります。トリガ定義は更新する必要はありません。データを挿入したら、サーバが行を追加すべきパーティションを自動的に決定するようにしたいかもしれません。これは以下のようなもっと複雑なトリガ関数を作成することにより可能です。plpgsql;トリガ定義は前と同じです。それぞれのIFテストをパーティションのCHECK制約と正確に一致させなければならないことに注意してください。この関数は単一月の場合より複雑になりますが、頻繁に更新する必要はありません。条件分岐を前もって追加しておくことが可能だからです。実際には、ほとんどの挿入が一番新しいパーティションに入る場合は、そのパーティションを最初に検査することが最善です。簡単に、この例でのほかの部分と同じ順番でトリガのテストを示しました。以上のように、複雑なパーティション化の計画はたくさんのDDLが必要となります。上記の例では、毎月新しいパーティションを作成することになりますが、必要となるDDLを自動的に生成するスクリプトを書くのが賢明です。テーブルを静的なままにするつもりではない場合、一群のパーティションを作ることになります。古いデータのパーティションの削除や新規データ向けの定期的な新規パーティションの追加という要求はよくあります。したそうでなければ苦痛を伴う作業を、大量のデータを物理的に移動させずに、パーティション構造を操作することでほとんど瞬間的に行うことができます。パーティショニングの最も重要な利点の一つはまさにこの点です。古いデータを削除する最も簡単な方法は、単に不要となったパーティションを削除することです。measurement_y2006m02;レコードごとに削除する必要がありませんので、これは高速に何百万ものレコードを削除することができます。この他、よく使用される方法は、テーブル自体へのアクセス権限をそのまま残したまま、パーティション付けされたテーブルからパーティションを削除することです。measurement;これにより、削除前にデータ操作をさらに行うことができます。COPY、pg_dumpなどのツールを使用してデータをバックアップすることはよくあります。データをより小さな書式に集約したり、他のデータ操作を行ったり、報告を作成したりすることもよくあります。同様に、新しいデータを扱うために新しいパーティションを追加することもできます。上で元々のパーティションを作成した時と同じように、パーティション付けテーブルに空のパーティションを作成することができます。(measurement);この他の方法として、パーティション構造の外部で新しいテーブルを作成し、後で適切にパーティションとすることが便利な場合もあります。これにより、パーティション付けしたテーブルに追加する前に、データをロードし、検査、変換することができます。何らかの準備操作を行うことができます。制約による除外は、上記の方法で定義された分割されたテーブルに対するパフォーマンスを向上させる問い合わせの最適化技術です。'2008-01-01';制約による除外が有効になっているとき、プランナはそれぞれのパーティションの制約を検証し、パーティションが問い合わせのWHEREに一致する行を含んでいないためにスキャンされる必要が無いことを証明しようとします。プランナはこれを証明すると、問い合わせ計画からそのパーティションを除外します。constraint_exclusionを有効とした時の計画と無効にした時の計画の違いを明らかにするため、EXPLAINコマンドを使用できます。この種類のテーブル設定に対する典型的な最適化されない計画は以下のようになります。'2008-01-01'::date)一部のパーティション、もしくはすべてのパーティションで、テーブル全体に対するシーケンシャルスキャンではなく、インデックススキャンが使用される可能性があります。ここでのポイントは、この問い合わせに対する回答のために古いパーティションをスキャンする必要はまったく無いということです。制約による除外を有効にしたとき、大幅により安価な同じ回答を返す計画を得ることができます。'2008-01-01'::date)制約による除外はCHECK制約のみで動作し、インデックスの有無では動作しないことに注意してください。よってキー列のインデックスを定義することは必要ではありません。あるパーティションでインデックスが必要かどうかは、パーティションをスキャンする問い合わせがパーティションの大部分もしくは小さな部分をスキャンするのかによります。前者ではなく後者において、インデックスは役立ちます。実のところ、constraint_exclusionのデフォルト(かつ推奨)の設定は、onでもoffでもなく、partitionという中間の設定です。これによりこの技法は、パーティショニングされたテーブルに対して動作することになる問い合わせのみに適用されるようになります。on設定により、プランナはすべての問い合わせでCHECK制約を検証するようになります。これは単純な問い合わせでも行われ、プラスになりそうでありません。適当なパーティションテーブルにリダイレクトする別の方法は、トリガの替わりにルールをマスターテーブル上に作成することです。(NEW.*);ルールはトリガよりもかなりオーバーヘッドがありますが、行ごとよりもむしろ問い合わせごとのオーバーヘッドとなります。よってこの方法は、一括挿入といった場合に有効となります。多くの場合、トリガを使用する方が、良い性能となります。COPYはルールを無視することに注意してください。データを挿入するのにCOPYを使用したい場合は、マスタテーブルより正しいパーティションテーブルにコピーすることが必要となるでしょう。COPYはトリガを起動します。よってトリガを使用する方法をとれば、普通にCOPYを使用することができます。ルールを使用する方法のもうひとつの欠点は、一連のルールが挿入日付を扱わないときにエラーを強制する簡単な方法がないことです。データは代わりにマスタテーブルに暗黙のうちに挿入されます。ALLビューを使用することによっても使用できます。measurement_y2008m01;しかしビューを再作成することで、データセットの個別のパーティションを追加したり削除したりする余分な手順が増えます。実際には、この方法は継承を使用する方法と比較してお勧めする点はほとんどありません。以下の警告がパーティション付けテーブルに対して適用されます。すべてのCHECK制約が相互に排他であるかどうか自動で確認する方法はありません。パーティションを生成し、関連付けられたオブジェクトを作成もしくは修正するコードを作成する方が、それらをいちいち手書きするよりも安全です。ここで示すスキーマは、行のパーティションキー列が変更しない、もしくは少なくとも他のパーティションに移動することが要求されるほどの変更がないことを仮定しています。別のパーティションに移るようなUPDATEは、CHECK制約により失敗するでしょう。このような場合を扱うには、適切な更新トリガをパーティションテーブルに設定します。これにより管理構造がさらに複雑になります。手動のVACUUMもしくはANALYZEコマンドを使用している場合は、それぞれのパーティションで個別に実行することを忘れないでください。measurement;マスタテーブルのみ処理することになります。以下の警告が制約による除外に適用されます。制約による除外は問い合わせのWHERE句が定数(または外部から供給されたパラメータ)を含んでいたときにのみ動作します。CURRENT_TIMESTAMPのような非immutable関数に対する比較は、関数の結果値がどのパーティションに該当するかを実行時にプランナが知ることが出来ないため、最適化できません。パーティション制約を簡単にしておいてください。しないとプランナは、パーティションを使う必要がないことを立証できません。前述の例で示したとおり、リスト分割のために簡単な等号条件を使用してください。範囲分割のために簡単な範囲テストを使用してください。手っ取り早い良い方法は、パーティショニングの制約がパーティション列とB-treeインデックス作成可能な演算子を用いた定数の比較のみを含んでいることです。マスタテーブルのすべてのパーティションのすべての制約は、制約による除外で試験されます。よってパーティションの数が多くなれば問い合わせ計画の時間がかなり増加します。これらの技術を使用したパーティショニングは、おそらく100個までのパーティションでうまく動作します。何千ものパーティションを使用することは避けてください。