PostgreSQLにおけるパーティショニングの実装

大規模なテーブルを展開して処理する前処理プログラムを書いている中で，そもそも自機のメモリにデータが全展開するだけの容量がなく，プログラムを複数回に分散して省メモリ化したいと考えたので，今後のためにもテーブルパーティションに関してメモしておく．

実はPostgreSQLではパーティショニングに関する専用の組み込み機能を持たないらしい．．そこでテーブルの継承を利用して機能を実装する．

親テーブル（マスターテーブル）の定義

まず最初に全てのパーティションが継承する親テーブルを宣言する．このテーブルには実データを格納せずに全て子テーブルに分散して格納する．

## サンプルテーブル

CREATE TABLE test (
    id bigint PRIMARY KEY,
    name character varying,
    date timestamp without time zone,
    content character varying
);

子テーブル（サブテーブル）の定義

次に，実データの格納先となる親テーブルを継承した子テーブル定義する．テーブルにはそれぞれCHECKで検査制約を付与する．この検査制約によってデータの格納するパーティションを決定するが，この時に1つのデータが複数のパーティションに共存しないように検査制約を設定する必要がある．

CREATE TABLE test_2012 (
    CHECK ( date >= TIMESTAMP WITHOUT TIME ZONE '2012-01-01' AND date < TIMESTAMP WITHOUT TIME ZONE '2013-01-01')
) INHERITS (test);
CREATE TABLE test_2013 (
    CHECK ( date >= TIMESTAMP WITHOUT TIME ZONE '2013-01-01' AND date < TIMESTAMP WITHOUT TIME ZONE '2014-01-01')
) INHERITS (test);
CREATE TABLE test_2014 (
    CHECK ( date >= TIMESTAMP WITHOUT TIME ZONE '2014-01-01' AND date < TIMESTAMP WITHOUT TIME ZONE '2015-01-01')
) INHERITS (test);
CREATE TABLE test_2015 (
    CHECK ( date >= TIMESTAMP WITHOUT TIME ZONE '2015-01-01' AND date < TIMESTAMP WITHOUT TIME ZONE '2016-01-01')
) INHERITS (test);
CREATE TABLE test_2016 (
    CHECK ( date >= TIMESTAMP WITHOUT TIME ZONE '2016-01-01' AND date < TIMESTAMP WITHOUT TIME ZONE '2017-01-01')
) INHERITS (test);

同時に，この検査制約によってINSERTに遅延が発生するので各子テーブルにインデックスを貼り処理の高速化を計る．

CREATE INDEX test_2012_date_idx ON test_2012 (date);
CREATE INDEX test_2013_date_idx ON test_2013 (date);
CREATE INDEX test_2014_date_idx ON test_2014 (date);
CREATE INDEX test_2015_date_idx ON test_2015 (date);
CREATE INDEX test_2016_date_idx ON test_2016 (date);

トリガ関数の定義

最後に親テーブルにデータがINSERTされた時に，適切なパーティションにデータが中継されるようにトリガ関数を定義する．

CREATE OR REPLACE FUNCTION test_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
    IF ( NEW.date >= TIMESTAMP WITHOUT TIME ZONE '2012-01-01' AND
         NEW.date < TIMESTAMP WITHOUT TIME ZONE '2013-01-01' ) THEN
        INSERT INTO test_2012 VALUES (NEW.*);
    ELSIF ( NEW.date >= TIMESTAMP WITHOUT TIME ZONE '2013-01-01' AND
            NEW.date < TIMESTAMP WITHOUT TIME ZONE '2014-01-01' ) THEN
        INSERT INTO test_2013 VALUES (NEW.*);
    ...
    ELSIF ( NEW.date >= TIMESTAMP WITHOUT TIME ZONE '2016-01-01' AND
            NEW.date < TIMESTAMP WITHOUT TIME ZONE '2017-01-01' ) THEN
        INSERT INTO test_2016 VALUES (NEW.*);
    ELSE
        RAISE EXCEPTION 'Date out of range.  Fix the test_insert_trigger() function.';
    END IF;
    RETURN NULL;
END;
$$
LANGUAGE plpgsql;

この関数を定義した後，上記のトリガ関数をクライアントからのINSERTからよぶトリガをテーブルに定義して完了である．

CREATE TRIGGER insert_test_trigger
    BEFORE INSERT ON test
    FOR EACH ROW EXECUTE PROCEDURE test_insert_trigger();