パーティショニング・概要編

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
■■■■ PostgreSQL スキルアップノート
■■■■
■◆■■ パーティショニング・概要編
■■■■
■■■■
■■■■ 2012/10/28
■■■■ 使用環境：PostgreSQL9.1.4 (CentOS6.2)
                                                                   (C) 2012 ohdb
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
★自己都合により実機確認は9.1、マニュアルへのリンクは9.2としています。ご了承下さい。




■１■　はじめに
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

PostgreSQLのパーティショニングは複数のテーブルと継承・トリガなどを組み合わせて自前で作る。
継承の実装やトリガーのコーディングなど複雑で非常に手間がかかる。
またパーティションを採用したテーブルそれぞれに対してパーティション機能自身のテストなど、Oracleなどとは異なる負担がかかってくる。

制約や注意点も多いので注意。


・パーティショニング→●[マニュアル]
・継承→●[マニュアル]

・Let's Postgres記事 →●[記事]


■２■ 　概要
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━


１．概要と効果
──────────────────────────────

省略（マニュアルを参照のこと）


２．PostgreSQLで対応するパーティションの種類
──────────────────────────────

9.2のマニュアルで正式に対応とされているのは以下の２種類のみ。

範囲分割・・キーとなる列（複数可）をもとに"範囲"で分割
             Oracleでいうレンジパーティション
リスト分割・・キー値を明示的なリストで分割。 
             Oracleでいうリストパーティション


３．仕組み
──────────────────────────────

テーブルの集まりを継承とトリガなどを使って実現

・1つの親テーブルと複数の子テーブル
・テーブルの継承により実現（子テーブルへ継承を実装）
　親テーブル自身は通常空のテーブル
  継承→●[マニュアル]
・トリガなどを使って親に実行されたinsert文の子への中継のロジックを実装する。
・insert文は親に向かって実行する。


４． 　パーティショニングの採用基準は？
──────────────────────────────

サーバの搭載物理メモリよりも大きくなるようなテーブルをおおよその採用の目安とする。

マニュアルには以下のような基準が記載されている。
----------------------------------------------------------------
テーブルのパーティショニングによる利点はアプリケーションに依存しますが、
経験的にテーブルのサイズがデータベースサーバの物理メモリを超えるかどうか
ということがポイントになります。 
-----------------------------------------------------------------
・・かなりアバウトであるが公式見解として明記されている。




■３■　パーティショニング実装方法の概要
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

具体的な実例は、「パーティショニング実装編」のページを参照


Oracleとは比較にならないほど手間がかかる。




１．"マスタ"テーブルを作成
──────────────────────────────

・空のテーブル
・インデックスや一意制約なし
・すべてのパーティションに対して適用されるもの以外の検査制約は定義しない。

 
２．"子"テーブルを作成
──────────────────────────────
 
 ・「継承」の機能により子を作成。INHERITS句
 ・マスタにある列以外に列追加しないこと。

・継承→●[マニュアル]


【参考】継承　もっとも簡単な例

       CRATE TABLE  t1_201301 ( ) INHERITS (t1);
       ・・・t1を継承してt1_201301を作る。

【参考】継承の対象は？・・・
---------------------------------------------------------------------------
親テーブル上の検査制約と非NULL制約はその子テーブルに自動的に継承されます。
他の種類の制約（一意性制約、プライマリキー、外部キー制約）は継承されません。 
---------------------------------------------------------------------------
★継承自体にも注意点が多いため「5.8.1. 警告」の項を中心に十分な理解が必要。


３．分割されたテーブルにテーブル制約を追加
──────────────────────────────

CHECK ( ID >= 100 AND ID < 200 ) 
CHECK ( sosiki IN ( 'Ａ支社', 'Ｂ支社', 'Ｃ支社' )) 

パーティション間で重なりが無いこと。


４．各パーティションのキーの列にインデックス作成
──────────────────────────────

・必須ではないが通常は作成。
・必要に応じて、一意もしくは主キー制約


５．トリガorルールを定義
──────────────────────────────

マスタテーブルに挿入されたデータを適切なパーティションへ中継するためのロジック
ここで作成したロジックによって、INSERT文が各パーティションに振り分けられる。

トリガを使う場合の例
・プロシージャでロジックをコーディング
・これをテーブルにトリガとして実装
 

６．設定
──────────────────────────────

constraint_exclusion設定パラメータがpostgresql.conf内で無効になっていないこと。
無効になっていないとは「on」と「partition」であるが後者のほうがよい。（デフォルト）






■４■　パーティショニング管理方法の概要
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━


例

1)毎月新しい月への子テーブル作成
  CREATE TABLE ・・ CHECK ・・ INHERITS・・

2)毎月最も古い子テーブルをDROP TABLE
  DROP TABLE t1_201201;

3)トリガーのIF文のケースがもし足りなければ追加




■５■　パーティションの制約事項【重要】
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━



１．マニュアルに書かれている問題点
──────────────────────────────
・マニュアル（警告の項）→●[マニュアル]

・定義を間違えてパーティションキーのCHECK制約の重なりがあってもエラー検出してくれない。
・別のパーティションに移るようなUPDATEはできない。
　適切な更新トリガを別に設定すれば可能だが管理構造がさらに複雑に。
・手動のVACUUMやANALYZEはそれぞれのパーティションを個別に実行する必要がある。
  マスタテーブルをアナライズしても子は対象にならない。 
・「制約による除外」（必要のないパーティションへアクセスしないこと）ができ、効率的にアクセスできるのは問い合わせのWHERE句が定数を含んでいたときのみ。
　例）CURRENT_TIMESTAMPのような非immutable関数ではプランナがその値を知らないのでパーティションで効率よく絞り込めない。 
・性能上、パーティション制約を極力簡単にすべき。
・パーティション分割は100個くらいまでに抑える。
 

２．Let's Postgres記事で書かれている問題点
──────────────────────────────
・Let's postgre's 記事→●[記事]

・テーブルあたりの分割数は最大 100 程度 
・パーティションを跨るユニーク制約を定義できない ★重要
・INSERT 性能の低下 
・INSERT 結果行数が 0 になる 
・Prepared Statement と相性が悪い 
・CURRENT_TIMESTAMP と相性が悪い 
・複雑なクエリで性能が劣化する可能性がある 




以上