テーブルパーティショニング - あられねこのめも

PostgreSQLのテーブルパーティショニングめも。

PostgreSQLでは、テーブル継承を利用してテーブルパーティショニングを行うことができます。
例えば、三ヶ月以上前のデータを一ヶ月単位で削除するようなシステムの場合、月ごとにテーブルを分けることで削除処理をスムーズに行うことができます。

月ごとにテーブルパーティショニングを行う例

概要

・データを格納しない親テーブルを作成する。
・月ごとのデータを格納する継承テーブルを作成する。
・INSERT時、継承テーブルにデータが挿入されるようなテーブルルールを作成する。
・親テーブルにINSERTトリガとしてテーブルルールを設定する。

実装例

親テーブル

CREATE TABLE sample
(
  id serial NOT NULL,  --ID
  name text NOT NULL,  --名前
  status smallint,  --ステータス
  ins_date timestamp without time zone,  --作成日時
  CONSTRAINT sample_pkey PRIMARY KEY (id)
)
WITH (OIDS=FALSE);

継承テーブル

CREATE TABLE sample_00() INHERITS (sample);
CREATE TABLE sample_01() INHERITS (sample);
CREATE TABLE sample_02() INHERITS (sample);
CREATE TABLE sample_03() INHERITS (sample);

テーブルルール

CREATE OR REPLACE FUNCTION sample_insert_func()
RETURNS TRIGGER AS $$
    DECLARE
      suffix int8 ;
BEGIN
    suffix := cast(date_part('month', NEW.ins_date) as integer) % 4 ;
    
    IF ( suffix = 1 ) THEN
        INSERT INTO sample_01 VALUES (NEW.*);
    ELSIF ( suffix = 2 ) THEN
        INSERT INTO sample_02 VALUES (NEW.*);
    ELSIF ( suffix = 3 ) THEN
        INSERT INTO sample_03 VALUES (NEW.*);
    ELSE
        INSERT INTO sample_00 VALUES (NEW.*);
    END IF;
    
    RETURN NEW;
END;
$$
LANGUAGE plpgsql;

INSERTトリガ

CREATE TRIGGER sample_insert_trig
    BEFORE INSERT ON sample
    FOR EACH ROW EXECUTE PROCEDURE sample_insert_func();

注意点

問い合わせ

親テーブル（ここでは、sampleテーブル）に対してクエリを発行すると、継承テーブル全てに対して問い合わせが行われます。

explainのイメージ

                                                    QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
 Result  (cost=0.00..411.04 rows=203 width=38)
   ->  Append  (cost=0.00..411.04 rows=203 width=38)
         ->  Index Scan using sample_00_id on sample_00  sample  (cost=0.00..165.86 rows=84 width=38)
               Index Cond: (id = 10)
         ->  Index Scan using sample_01_id on sample_01  sample  (cost=0.00..68.86 rows=34 width=38)
               Index Cond: (id = 10)
         ->  Index Scan using sample_02_id on sample_02  sample  (cost=0.00..73.57 rows=36 width=38)
               Index Cond: (id = 10)
         ->  Index Scan using sample_03_id on sample_03  sample  (cost=0.00..38.69 rows=18 width=38)
               Index Cond: (id = 10)

特定のテーブルのみに問い合わせを行いたい場合は、onlyを付けます。

-- sample(親テーブル)のみ検索。この例の場合は、データは格納されていません。
select * from only sample where id = 10;

-- sample_00、sample_01のみ検索
select * from only sample_00 sample_01 where id = 10;

インデックス

親テーブルに作成したインデックスは、継承テーブルに対して有効ではありません。
以下のように、継承テーブルごとにインデックスを作成する必要があります。

CREATE INDEX sample_00_ins_date ON sample_00 (ins_date);
CREATE INDEX sample_01_ins_date ON sample_01 (ins_date);
CREATE INDEX sample_02_ins_date ON sample_02 (ins_date);
CREATE INDEX sample_03_ins_date ON sample_03 (ins_date);

トランザクション

8.3.x以前のバージョンでは、継承テーブルの悲観的ロックがサポートされていません。
ですので、以下のようなクエリはエラーとなってしまいます。
（ERROR: SELECT FOR UPDATE/SHARE is not supported for inheritance queries）
8.4以降ではサポートされています。

begin;
select id from sample where id = 100 for update;

update sample set status = 2 where id = 100;
commit;

チェック制約

チェック制約を付けることで、親テーブルの問い合わせ効率が改善することがあります。

CREATE TABLE sample_01 (
    CHECK ( ins_date >= DATE '2011-01-01' AND ins_date < DATE '2011-02-01' )
) INHERITS (sample);