Postgresで日付ごとにテーブルを分散する


Sommersprossen前回に引き続き、アクセス解析に奮闘中。データベースにパラメータの組合せをアクセス日付ごとに登録しているのですが、あまりにも組合せが多すぎて、日付ごとにテーブルを分けて1テーブル当たりの個数を減らす手段を取ろうと思ったわけです。

やったことない~~~!!ので調べてみました。

ほとんど先人様のパクリです。ごめんなさい。すごく助かりました。この場を借りて御礼を。

Creative Commons License photo credit: Niemann-Buuts

postgresは複数の同じ構成をもったテーブルを1つのテーブルに見せる「テーブルパーティショニング」という技術があります。親テーブルを作って、それと同じ構成をもつ子テーブルをたくさん作って、insert する際に、日付でもなんでもキーとなるカラムを判断する条件を作成して、与えられたテーブルへ insert をする(=分散する)わけです。

今回取り組んだ内容はhttp://mlog.euqset.org/archives/pgsql-jp.ml.postgresql.jp/40049.html — 山崎(ゆ)さんのソースをほとんどコピー。ありがとうございます。ただ自分がやりたかったことが、タイムスタンプ型をキーにするのじゃなくてdate型にしたいので、そこを少し補正しなければいけません。

ちなみにすみません、例によって、自己責任でお願いします。責任は負えません。

まずはベースとなるテーブルを作ります。

1
2
3
4
5
6
7
CREATE TABLE tbl_hogehoge (
  hogehoge_param1   text
, hogehoge_date     DATE
, hogehoge_param2   text
, hogehoge_count    INT
, hogehoge_mkdate   TIMESTAMP
);

このテーブルに登録する際、hogehoge_date をキーにして分散します。例えば 2011-10-09 という日付だった場合、tbl_hogehoge201110 というテーブルにinsert したい。もしそのテーブルがなかったら自動作成したい、というわけです。

そのトリガーをこんなようにしました。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
DROP FUNCTION hogehoge_insert_trigger();
CREATE OR REPLACE FUNCTION hogehoge_insert_trigger() returns TRIGGER AS
  $$
    DECLARE
      parent text := 'tbl_hogehoge';  -- 親となるテーブル名
      part text; -- 子テーブル名指定用変数
      t_now DATE;
      t_bgn TIMESTAMP; -- ここだけtimestamp型。そうしないと +1 monthでエラーになる
      t_end DATE;
    BEGIN
      -- パーティション名決め: tbl_hogehogeYYYYMM
      part := parent || TO_CHAR(NEW.hogehoge_date, 'YYYYMM');
      IF NOT EXISTS(
        SELECT * FROM information_schema.tables
         WHERE table_name = part
      ) THEN
        -- 作成するパーティションに振り分ける
        -- 期間を作成する
        t_now := NEW.hogehoge_date;
        t_bgn := TO_TIMESTAMP(TO_CHAR(t_now, 'YYYY-MM-01 00:00:00'), 'YYYY-MM-DD HH24:MI:SS');
        t_end := (t_bgn + '1 month')::DATE;

        EXECUTE 'create table ' ||quote_ident(part)|| ' (like ' ||parent|| '
          including indexes including defaults including constraints, '
||
          'check(' ||quote_literal(t_bgn::DATE)|| ' <= hogehoge_date AND' ||
             ' hogehoge_date < '  ||quote_literal(t_end)||
            ')' ||
          ') inherits(' ||parent|| ')';
      END IF;
    -- new を渡す
    EXECUTE 'insert into '||part||' values(($1).*)' using NEW;
    RETURN NULL;
  END
$$ language plpgsql;

insert文中の hogehoge_date を YYYYMM と変換しテーブルパーティション名を決めます。
そのテーブル名が存在するか information_schemaに問い合わせて、なかったら create table します。
その際、このテーブルに登録する期間を与えておき、inherits(parent)で親と同様の構造にします。

という流れです。このトリガーを、親テーブルにinsertする際に使用するように、もう一個triggerを作成します。

1
2
3
4
DROP TRIGGER hogehoge_insert_trigger_instance ON tbl_hogehoge
CREATE TRIGGER hogehoge_insert_trigger_instance
  BEFORE INSERT ON tbl_hogehoge
  FOR each ROW EXECUTE PROCEDURE hogehoge_insert_trigger();

これで準備は完了です。

1
2
3
4
5
6
7
8
9
10
11
-- 1行作ってみる
$=> INSERT INTO tbl_hogehoge VALUES ('テスト1', '2011-10-09','テスト2', 22, now())
INSERT 01
-- テーブルを見てみる
$=> \d
tbl_hogehoge
tbl_hogehoge201110  -- できている
$=> SELECT * FROM tbl_hogehoge201110
テスト1  | 2011-10-09 | テスト2 | 22 | 2011-10-09 00:00:00 -- 201110側にできている
$=> SELECT * FROM tbl_hogehoge
テスト1  | 2011-10-09 | テスト2 | 22 | 2011-10-09 00:00:00    -- 同様の結果が得られる

となるはずです。すごいですね!

ただ結局自分の作業としては、分散しても1日分が120万件になってしまい、1ヵ月分のテーブルは3600万件になるんだなぁ・・・と思うとうむむ、どうしよう、という状況。

別の分散も考えなきゃなぁ・・・。