PostgreSQLスキルアップノート（自己啓発のための個人サイト）
パーティショニング・実例編（簡単な範囲分割パーティション）
【一覧に戻る】
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
■■■■ PostgreSQL スキルアップノート
■■■■
■◆■■ パーティショニング・実例編（簡単な範囲分割パーティション）
■■■■
■■■■
■■■■ 2012/10/28
■■■■ 使用環境：PostgreSQL9.1.4 (CentOS6.2)
(C) 2012 ohdb
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
★自己都合により実機確認は9.1、マニュアルへのリンクは9.2としています。ご了承下さい。
【マニュアル】
・パーティショニング→●[マニュアル]
・継承→●[マニュアル]
【参考記事】
・Let's Postgres記事 →●[記事]
範囲分割パーティション（レンジパーティション）を実際に作成してみる。
■１■ マスタテーブルの作成
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
CREATE TABLE t1 (
c1 int not null,
c2 date not null,
c3 varchar(10) ,
c4 int
);
■２■ 子テーブル作成
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
ここでは次項と一緒に行うため、次項へスキップ
（作成の流れとして項番のみ残しておく）
■３■ 分割されたテーブルにテーブル制約を追加
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
子テーブル作成とテーブル制約を同時に実施。
範囲の重ならないテーブル制約を定義する必要あり。
（チェックしてくれる訳ではないので注意）
【□】 以下を実行。
-- drop table t1_201301,t1_201302,t1_201303,t1_201304,t1_201305,t1_201306,t1_201307,t1_201308,t1_201309,t1_201310,t1_201311,t1_201312;
CREATE TABLE t1_201301 (
PRIMARY KEY (c1),
CHECK (c2 >= DATE '2013-01-01' AND c2 < DATE '2013-02-01')
) INHERITS (t1);
CREATE TABLE t1_201302 (
PRIMARY KEY (c1),
CHECK (c2 >= DATE '2013-02-01' AND c2 < DATE '2013-03-01')
) INHERITS (t1);
CREATE TABLE t1_201303 (
PRIMARY KEY (c1),
CHECK (c2 >= DATE '2013-03-01' AND c2 < DATE '2013-04-01')
) INHERITS (t1);
CREATE TABLE t1_201304 (
PRIMARY KEY (c1),
CHECK (c2 >= DATE '2013-04-01' AND c2 < DATE '2013-05-01')
) INHERITS (t1);
CREATE TABLE t1_201305 (
PRIMARY KEY (c1),
CHECK (c2 >= DATE '2013-05-01' AND c2 < DATE '2013-06-01')
) INHERITS (t1);
CREATE TABLE t1_201306 (
PRIMARY KEY (c1),
CHECK (c2 >= DATE '2013-06-01' AND c2 < DATE '2013-07-01')
) INHERITS (t1);
CREATE TABLE t1_201307 (
PRIMARY KEY (c1),
CHECK (c2 >= DATE '2013-07-01' AND c2 < DATE '2013-08-01')
) INHERITS (t1);
CREATE TABLE t1_201308 (
PRIMARY KEY (c1),
CHECK (c2 >= DATE '2013-08-01' AND c2 < DATE '2013-09-01')
) INHERITS (t1);
CREATE TABLE t1_201309 (
PRIMARY KEY (c1),
CHECK (c2 >= DATE '2013-09-01' AND c2 < DATE '2013-10-01')
) INHERITS (t1);
CREATE TABLE t1_201310 (
PRIMARY KEY (c1),
CHECK (c2 >= DATE '2013-10-01' AND c2 < DATE '2013-11-01')
) INHERITS (t1);
CREATE TABLE t1_201311 (
PRIMARY KEY (c1),
CHECK (c2 >= DATE '2013-11-01' AND c2 < DATE '2013-12-01')
) INHERITS (t1);
CREATE TABLE t1_201312 (
PRIMARY KEY (c1),
CHECK (c2 >= DATE '2013-12-01' AND c2 < DATE '2014-01-01')
) INHERITS (t1);
■４■ 各パーティションキーにインデックス作成
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
【□】 以下を実行。
CREATE INDEX t1_201301_ix1 ON t1_201301 (c2);
CREATE INDEX t1_201302_ix1 ON t1_201302 (c2);
CREATE INDEX t1_201303_ix1 ON t1_201303 (c2);
CREATE INDEX t1_201304_ix1 ON t1_201304 (c2);
CREATE INDEX t1_201305_ix1 ON t1_201305 (c2);
CREATE INDEX t1_201306_ix1 ON t1_201306 (c2);
CREATE INDEX t1_201307_ix1 ON t1_201307 (c2);
CREATE INDEX t1_201308_ix1 ON t1_201308 (c2);
CREATE INDEX t1_201309_ix1 ON t1_201309 (c2);
CREATE INDEX t1_201310_ix1 ON t1_201310 (c2);
CREATE INDEX t1_201311_ix1 ON t1_201311 (c2);
CREATE INDEX t1_201312_ix1 ON t1_201312 (c2);
※他のインデックスがあるはずなので、ここですべてのインデックスを作成する必要がある。
■５■ トリガ定義
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
親にINSERT すると該当パーティションへデータを中継する設定をトリガーにする。
各IF文とパーティションのCHECK制約を一致させるようにする。
（１）トリガ関数のコーディング
【□】 以下を実行。
CREATE OR REPLACE FUNCTION func_t1_insert()
RETURNS TRIGGER AS $$
BEGIN
IF (
NEW.c2 >= DATE '2013-01-01' AND
NEW.c2 < DATE '2013-02-01'
) THEN
INSERT INTO t1_201301 VALUES (NEW.*);
ELSIF (
NEW.c2 >= DATE '2013-02-01' AND
NEW.c2 < DATE '2013-03-01'
) THEN
INSERT INTO t1_201302 VALUES (NEW.*);
ELSIF (
NEW.c2 >= DATE '2013-03-01' AND
NEW.c2 < DATE '2013-04-01'
) THEN
INSERT INTO t1_201303 VALUES (NEW.*);
ELSIF (
NEW.c2 >= DATE '2013-04-01' AND
NEW.c2 < DATE '2013-05-01'
) THEN
INSERT INTO t1_201304 VALUES (NEW.*);
ELSIF (
NEW.c2 >= DATE '2013-05-01' AND
NEW.c2 < DATE '2013-06-01'
) THEN
INSERT INTO t1_201305 VALUES (NEW.*);
ELSIF (
NEW.c2 >= DATE '2013-06-01' AND
NEW.c2 < DATE '2013-07-01'
) THEN
INSERT INTO t1_201306 VALUES (NEW.*);
ELSIF (
NEW.c2 >= DATE '2013-07-01' AND
NEW.c2 < DATE '2013-08-01'
) THEN
INSERT INTO t1_201307 VALUES (NEW.*);
ELSIF (
NEW.c2 >= DATE '2013-08-01' AND
NEW.c2 < DATE '2013-09-01'
) THEN
INSERT INTO t1_201308 VALUES (NEW.*);
ELSIF (
NEW.c2 >= DATE '2013-09-01' AND
NEW.c2 < DATE '2013-10-01'
) THEN
INSERT INTO t1_201309 VALUES (NEW.*);
ELSIF (
NEW.c2 >= DATE '2013-10-01' AND
NEW.c2 < DATE '2013-11-01'
) THEN
INSERT INTO t1_201310 VALUES (NEW.*);
ELSIF (
NEW.c2 >= DATE '2013-11-01' AND
NEW.c2 < DATE '2013-12-01'
) THEN
INSERT INTO t1_201311 VALUES (NEW.*);
ELSIF (
NEW.c2 >= DATE '2013-12-01' AND
NEW.c2 < DATE '2014-01-01'
) THEN
INSERT INTO t1_201312 VALUES (NEW.*);
ELSE
RAISE EXCEPTION 'ERROR! Date out of range.';
END IF;
RETURN NULL;
END;
$$
LANGUAGE plpgsql;
一番使われる可能性の高いパーティションをIF文の最初に持ってきたほうが性能面でよい。
【参考】別の方法（最新のパーティションにしかデータが入らない場合）
絶対に最新パーティションにしか入らないのであれば以下のように単純化してもよい。
ただし定義は最新が変わるたび（たとえば毎月）作り直す必要がある。
CREATE OR REPLACE FUNCTION ・・()
RETURNS TRIGGER AS $$
BEGIN
INSERT INTO 最新の子テーブル名 VALUES (NEW.*);
RETURN NULL;
END;
$$
LANGUAGE plpgsql;
（２）トリガ関数を呼ぶトリガを作成
【□】 以下を実行。
--drop trigger trig_t1_insert on t1;
CREATE TRIGGER trig_t1_insert
BEFORE INSERT ON t1
FOR EACH ROW EXECUTE PROCEDURE func_t1_insert();
■６■ 設定確認
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
constraint_exclusionの確認をしておく。
constraint_exclusionはテーブル制約の問い合わせプランナの制御・・おもに継承テーブルと分割されたテーブルの性能向上に使用される。
→●[マニュアル]
on 全てのテーブルに対し制約を検査
off 決して制約を検査しない
partition 継承された子テーブルおよびUNION ALL副問い合わせのみ制約を検査 （デフォルト）
事実上、無条件でpartition(デフォルト）にしておけばよいはず。
onよりもpartitionがよい。（マニュアルに記載）
【□】 show constraint_exclusion;
constraint_exclusion
----------------------
partition
■７■ 作成したパーティションの確認
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
１．テーブル
──────────────────────────────
【参考】\dtでの「+」指定はサイズ等の情報付加
【□】 \dt+ t1*
List of relations
Schema | Name | Type | Owner | Size | Description
--------+-----------+-------+----------+---------+-------------
public | t1 | table | postgres | 0 bytes |
public | t1_201301 | table | postgres | 0 bytes |
public | t1_201302 | table | postgres | 0 bytes |
public | t1_201303 | table | postgres | 0 bytes |
public | t1_201304 | table | postgres | 0 bytes |
public | t1_201305 | table | postgres | 0 bytes |
public | t1_201306 | table | postgres | 0 bytes |
public | t1_201307 | table | postgres | 0 bytes |
public | t1_201308 | table | postgres | 0 bytes |
public | t1_201309 | table | postgres | 0 bytes |
public | t1_201310 | table | postgres | 0 bytes |
public | t1_201311 | table | postgres | 0 bytes |
public | t1_201312 | table | postgres | 0 bytes |
(13 rows)
２．インデックス
──────────────────────────────
【参考】\diでの「+」指定はサイズ等の情報付加
【□】 \di+ t1*
postgres=# \di+ t1*
List of relations
Schema | Name | Type | Owner | Table | Size | Description
--------+----------------+-------+----------+-----------+------------+-------------
public | t1_201301_ix1 | index | postgres | t1_201301 | 8192 bytes |
public | t1_201301_pkey | index | postgres | t1_201301 | 8192 bytes |
public | t1_201302_ix1 | index | postgres | t1_201302 | 8192 bytes |
public | t1_201302_pkey | index | postgres | t1_201302 | 8192 bytes |
public | t1_201303_ix1 | index | postgres | t1_201303 | 8192 bytes |
public | t1_201303_pkey | index | postgres | t1_201303 | 8192 bytes |
public | t1_201304_ix1 | index | postgres | t1_201304 | 8192 bytes |
public | t1_201304_pkey | index | postgres | t1_201304 | 8192 bytes |
public | t1_201305_ix1 | index | postgres | t1_201305 | 8192 bytes |
public | t1_201305_pkey | index | postgres | t1_201305 | 8192 bytes |
public | t1_201306_ix1 | index | postgres | t1_201306 | 8192 bytes |
public | t1_201306_pkey | index | postgres | t1_201306 | 8192 bytes |
public | t1_201307_ix1 | index | postgres | t1_201307 | 8192 bytes |
public | t1_201307_pkey | index | postgres | t1_201307 | 8192 bytes |
public | t1_201308_ix1 | index | postgres | t1_201308 | 8192 bytes |
public | t1_201308_pkey | index | postgres | t1_201308 | 8192 bytes |
public | t1_201309_ix1 | index | postgres | t1_201309 | 8192 bytes |
public | t1_201309_pkey | index | postgres | t1_201309 | 8192 bytes |
public | t1_201310_ix1 | index | postgres | t1_201310 | 8192 bytes |
public | t1_201310_pkey | index | postgres | t1_201310 | 8192 bytes |
public | t1_201311_ix1 | index | postgres | t1_201311 | 8192 bytes |
public | t1_201311_pkey | index | postgres | t1_201311 | 8192 bytes |
public | t1_201312_ix1 | index | postgres | t1_201312 | 8192 bytes |
public | t1_201312_pkey | index | postgres | t1_201312 | 8192 bytes |
(24 rows)
３．マスタテーブル定義、トリガの確認
──────────────────────────────
【参考】\dでの「+」でChild tablesのリスト付加
【□】 \d+ t1
postgres=# \d+ t1
Table "public.t1"
Column | Type | Modifiers | Storage | Description
--------+-----------------------+-----------+----------+-------------
c1 | integer | not null | plain |
c2 | date | not null | plain |
c3 | character varying(10) | | extended |
c4 | integer | | plain |
Triggers:
trig_t1_insert BEFORE INSERT ON t1 FOR EACH ROW EXECUTE PROCEDURE func_t1_insert()
★Child tables: t1_201301,
t1_201302,
t1_201303,
t1_201304,
t1_201305,
t1_201306,
t1_201307,
t1_201308,
t1_201309,
t1_201310,
t1_201311,
t1_201312
Has OIDs: no
４．子テーブルの定義・チェック制約・インデックス定義
──────────────────────────────
【□】 \d+ t1_*
Table "public.t1_201301"
Column | Type | Modifiers
--------+-----------------------+-----------
c1 | integer | not null
c2 | date | not null
c3 | character varying(10) |
c4 | integer |
Indexes:
"t1_201301_pkey" PRIMARY KEY, btree (c1)
"t1_201301_ix1" btree (c2)
Check constraints:
"t1_201301_c2_check" CHECK (c2 >= '2013-01-01'::date AND c2 < '2013-02-01'::date)
Inherits: t1
Index "public.t1_201301_ix1"
Column | Type | Definition
--------+------+------------
c2 | date | c2
btree, for table "public.t1_201301"
Index "public.t1_201301_pkey"
Column | Type | Definition
--------+---------+------------
c1 | integer | c1
primary key, btree, for table "public.t1_201301"
:
:
:
:
:
■８■ 実装例・作成したパーティションへのデータ挿入
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
１．親へINSERT
──────────────────────────────
【□】 以下を実行
-- truncate t1;
INSERT INTO t1 values(1,DATE '2013-01-01','abc',0);
INSERT INTO t1 values(2,DATE '2013-01-31','abc',0);
INSERT INTO t1 values(3,DATE '2013-06-01','abc',0);
INSERT INTO t1 values(4,DATE '2013-06-30','abc',0);
INSERT INTO t1 values(5,DATE '2013-12-01','abc',0);
INSERT INTO t1 values(6,DATE '2013-12-31','abc',0);
INSERT INTO t1 values(7,DATE '2014-01-01','out range',0);
INSERT INTO t1 values(8,DATE '2013-02-31','out range',0);
【□】 select * from t1;
postgres=# select * from t1;
c1 | c2 | c3 | c4
----+------------+-----+----
1 | 2013-01-01 | abc | 0
2 | 2013-01-31 | abc | 0
3 | 2013-06-01 | abc | 0
4 | 2013-06-30 | abc | 0
5 | 2013-12-01 | abc | 0
6 | 2013-12-31 | abc | 0
(6 rows)
２．親へUPDATEをかけてみる。
──────────────────────────────
【□】 update t1 set c3='abcdef' where c1=1;
【□】 select * from t1 order by 1 ;
postgres=# select * from t1 order by 1 ;
c1 | c2 | c3 | c4
----+------------+--------+----
1 | 2013-01-01 | abcdef | 0
2 | 2013-01-31 | abc | 0
3 | 2013-06-01 | abc | 0
4 | 2013-06-30 | abc | 0
5 | 2013-12-01 | abc | 0
6 | 2013-12-31 | abc | 0
(6 rows)
３．格納データ確認（どの子テーブルに格納？）
──────────────────────────────
各テーブルのtableoidシステム列（元になったテーブル）とpgclassを結合。
パーティションの格納テストではこの確認は必須。
詳細はマニュアルの継承の項を参照のこと。
tableoid表示の基本形（tableのoidまででテーブル名まではわからない）
【□】 SELECT tableoid,* FROM t1 order by c1;
tableoid | c1 | c2 | c3 | c4
----------+----+------------+--------+----
270395 | 1 | 2013-01-01 | abcdef | 0
270395 | 2 | 2013-01-31 | abc | 0
270425 | 3 | 2013-06-01 | abc | 0
270425 | 4 | 2013-06-30 | abc | 0
270461 | 5 | 2013-12-01 | abc | 0
270461 | 6 | 2013-12-31 | abc | 0
(6 rows)
元テーブルをpgclassと結合させて表示
【□】 SELECT p.relname, t1.* FROM t1,pg_class p WHERE t1.tableoid = p.oid order by c1;
relname | c1 | c2 | c3 | c4
-----------+----+------------+--------+----
t1_201301 | 1 | 2013-01-01 | abcdef | 0
t1_201301 | 2 | 2013-01-31 | abc | 0
t1_201306 | 3 | 2013-06-01 | abc | 0
t1_201306 | 4 | 2013-06-30 | abc | 0
t1_201312 | 5 | 2013-12-01 | abc | 0
t1_201312 | 6 | 2013-12-31 | abc | 0
(6 rows)
４．その他参考・各子テーブルに同じプライマリキーを格納してみる
──────────────────────────────
【□】 以下を実行
truncate t1;
INSERT INTO t1 values(1,DATE '2013-01-01','abc',0);
INSERT INTO t1 values(1,DATE '2013-02-01','abc',0);
INSERT INTO t1 values(1,DATE '2013-03-01','abc',0);
【□】 SELECT p.relname, t1.* FROM t1,pg_class p WHERE t1.tableoid = p.oid order by c1;
relname | c1 | c2 | c3 | c4
-----------+----+------------+-----+----
t1_201301 | 1 | 2013-01-01 | abc | 0
t1_201302 | 1 | 2013-02-01 | abc | 0
t1_201303 | 1 | 2013-03-01 | abc | 0
(3 rows)
・・・パーティションが違うと重複キーでも格納されてしまう。（重要）
■９■ 実装例・作成したパーティションへのアクセス
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
大量データを格納し、パーティション分割時のアクセス内容と性能を実行計画を取得して確認
・・・次の機会に
■１０■ パーティションの管理
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
例
1)毎月新しい月への子テーブル作成
CREATE TABLE ・・ CHECK ・・ INHERITS・・
2)毎月最も古い子テーブルをDROP TABLE
DROP TABLE t1_201201;
3)トリガーのIF文のケースがもし足りなければ追加
以上
