PostgreSQLのtable partitioning
例えば日付でテーブルを分けたいという時に、制約を使った方法があります。 http://www.postgresql.jp/document/9.3/html/ddl-partitioning.html
ただ、例えば年月でパーティションされたテーブルを作り忘れたり、triggerを更新しなかったりすると悲劇なので、そこら辺を忘れても大丈夫なようにしましょうという話です。
partitioningの例
説明のために以下のようなテーブルを作ります。salesテーブルを分割することを想定します。
CREATE TABLE items (id SERIAL PRIMARY KEY, name TEXT, price numeric CHECK (price > 0)); INSERT INTO items(name, price) VALUES ('hoge', 100); INSERT INTO items(name, price) VALUES ('abc', 200); CREATE TABLE sales (id SERIAL PRIMARY KEY, item_id INT REFERENCES items(id), count INT, ctime TIMESTAMP NOT NULL DEFAULT current_timestamp); CREATE INDEX ctime_idx ON sales (ctime);
子テーブル(例として2013/12, 2014/1)は以下の様なSQLで作成するものとします。LIKE使った場合にインデックスや制約は引き継いでくれますが、外部キー制約は引き継いでくれないので、ちゃんと指定する必要があります。CHECKでこのテーブルに入るデータの制限をかけます。
CREATE TABLE sales_201312 ( LIKE sales INCLUDING ALL, FOREIGN KEY (item_id) REFERENCES items(id), CHECK (ctime >= '2013-12-01 00:00:00' and ctime < '2014-01-01 00:00:00') ) INHERITS (sales); CREATE TABLE sales_201401 ( LIKE sales INCLUDING ALL, FOREIGN KEY (item_id) REFERENCES items(id), CHECK (ctime >= '2014-01-01 00:00:00' and ctime < '2014-02-01 00:00:00') ) INHERITS (sales);
実際に2014/1のデータを入れてEXPLAINすると、ctimeがWHERE句に入っていればうまく枝刈りしてくれます。下の例だと201312のテーブルが枝刈りされています。
hoge=# insert into sales_201401(item_id, count, ctime) values (1, 2, '2014-01-10 10:00:00');
INSERT 0 1
hoge=# analyze;
ANALYZE
hoge=# explain select * from sales where ctime >= '2014-01-10 00:00:00' and ctime < '2014-01-11 00:00:00';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------
Append (cost=0.00..1.01 rows=2 width=20)
-> Seq Scan on sales (cost=0.00..0.00 rows=1 width=20)
Filter: ((ctime >= '2014-01-10 00:00:00'::timestamp without time zone) AND (ctime < '2014-01-11 00:00:00'::timestamp without time zone))
-> Seq Scan on sales_201401 (cost=0.00..1.01 rows=1 width=20)
Filter: ((ctime >= '2014-01-10 00:00:00'::timestamp without time zone) AND (ctime < '2014-01-11 00:00:00'::timestamp without time zone))
(5 rows)
TRIGGER
やっと本題です。どのテーブルにINSERTするかは、アプリでもちろん決めることができますが、めんどくさい場合やCURRENT_TIMESTAMP使っている場合は、親テーブルにトリガー仕込んでおきます。親テーブルにINSERTするとトリガーが勝手に子テーブルのどこかに入れるようにします。
トリガー使う場合に注意したいのは以下の点です。
- 振り分けルールがない
- たとえば、201401ならsales_201401に入れるみたいなルールをそのまま列挙していると、いつかルールを書き忘れて想定しない挙動になってしまうかもしれません
- 振り分けルールはあるが、実際に振り分け先のテーブルがない
- 悲劇ですね
ということで上の2つの問題を解決するために、トリガーの中で動的にテーブル格納先を決めつつ、テーブルなければテーブル作るようなトリガーを作ってみます。
CREATE OR REPLACE FUNCTION sales_insert_trigger_func() RETURNS TRIGGER AS $$ DECLARE partition text; createtable_sql text; startdate text; enddate text; parent_table text; BEGIN parent_table := 'sales'; partition := parent_table || '_' || to_char(new.ctime, 'YYYYMM'); EXECUTE 'INSERT INTO ' || partition || ' VALUES(($1).*)' USING new; RETURN NULL; EXCEPTION WHEN undefined_table THEN startdate := to_char(NEW.ctime, 'YYYY-MM-01'); enddate := to_char(NEW.ctime + interval '1 month', 'YYYY-MM-01'); createtable_sql := 'CREATE TABLE IF NOT EXISTS ' || partition || ' (LIKE '|| parent_table || ' INCLUDING ALL, FOREIGN KEY (item_id) REFERENCES items(id), CHECK (ctime >= ' || quote_literal(startdate) || ' AND ctime < ' || quote_literal(enddate) || ')) INHERITS ( ' || parent_table || ')'; EXECUTE createtable_sql; EXECUTE 'INSERT INTO ' || partition || ' VALUES(($1).*)' USING new; RETURN NULL; END; $$ LANGUAGE plpgsql; CREATE TRIGGER insert_sales_trigger BEFORE INSERT ON sales FOR EACH ROW EXECUTE PROCEDURE sales_insert_trigger_func();
new.ctime(INSERTしようとしている行のctime)からテーブル名を決定し、EXECUTEでSQLを実行します。もしテーブルがない場合は、 undefined_tableというエラーが返ってくるので、テーブル作って再実行するということをやります。
実際にテーブルない状態で2014/2のデータをINSERTしてみた結果です。
hoge=# insert into sales(item_id, count, ctime) values (1, 2, '2014-02-10 10:00:00');
NOTICE: merging column "id" with inherited definition
CONTEXT: SQL statement "CREATE TABLE IF NOT EXISTS sales_201402 (LIKE sales INCLUDING ALL,
FOREIGN KEY (item_id) REFERENCES items(id),
CHECK (ctime >= '2014-02-01' AND ctime < '2014-03-01')) INHERITS ( sales)"
PL/pgSQL function sales_insert_trigger_func() line 20 at EXECUTE statement
NOTICE: merging column "item_id" with inherited definition
CONTEXT: SQL statement "CREATE TABLE IF NOT EXISTS sales_201402 (LIKE sales INCLUDING ALL,
FOREIGN KEY (item_id) REFERENCES items(id),
CHECK (ctime >= '2014-02-01' AND ctime < '2014-03-01')) INHERITS ( sales)"
PL/pgSQL function sales_insert_trigger_func() line 20 at EXECUTE statement
NOTICE: merging column "count" with inherited definition
CONTEXT: SQL statement "CREATE TABLE IF NOT EXISTS sales_201402 (LIKE sales INCLUDING ALL,
FOREIGN KEY (item_id) REFERENCES items(id),
CHECK (ctime >= '2014-02-01' AND ctime < '2014-03-01')) INHERITS ( sales)"
PL/pgSQL function sales_insert_trigger_func() line 20 at EXECUTE statement
NOTICE: merging column "ctime" with inherited definition
CONTEXT: SQL statement "CREATE TABLE IF NOT EXISTS sales_201402 (LIKE sales INCLUDING ALL,
FOREIGN KEY (item_id) REFERENCES items(id),
CHECK (ctime >= '2014-02-01' AND ctime < '2014-03-01')) INHERITS ( sales)"
PL/pgSQL function sales_insert_trigger_func() line 20 at EXECUTE statement
INSERT 0 0
hoge=# select * from sales_201402;
id | item_id | count | ctime
----+---------+-------+---------------------
4 | 1 | 2 | 2014-02-10 10:00:00
(1 row)
なお注意点としては、
- トリガー使った場合はINSERTの戻り値が常に0件になるのと、RETURNINGも動かないので、or mapperが勝手にreturningしている場合はうまく回避する必要があります
- あまりまじめに検証していないので、同時にテーブル作成しようとした場合にどうなるかはちょっと怪しいので、基本的にはcrontabか何かにtable作るような定期ジョブを設定しておいたほうが良いです。といってもテーブル忘れた場合にエラーが出続けるよりはいいとは思います。
といったところでしょうか。
