PostgreSQL のパーティションテーブル自動生成

## パーティショニングとは

パーティショニングについての詳細は [Let's postgres パーティショニング : 用途と利点](http://lets.postgresql.jp/documents/technical/partitioning/1)に詳しいですので、ここでは省略します。

## パーティションテーブルの自動生成

> 親テーブルに加えて複数の子テーブルを作成するのに加え、親テーブルにトリガ (8.1 以前ではさらにルール) を定義する必要があります。
> [パーティショニング : 使い方](http://lets.postgresql.jp/documents/technical/partitioning/2)

この記事では、パーティショニングの際に子テーブルを作る手間を省略する方法について紹介します。

## サンプル実装

```sql

-- パーティショニング対象の親テーブル定義
CREATE TABLE logs (
  tag TEXT,
  record JSON NOT NULL,
  time TIMESTAMP NOT NULL,
  CHECK(time IS NULL) NO INHERIT -- 親には直接書き込めない
);
CREATE INDEX "logs_time_idx" ON logs(time);

-- パーティショニングしない普通のテーブル定義（比較のため）
CREATE TABLE logs2 (
  tag TEXT,
  record JSON NOT NULL,
  time TIMESTAMP NOT NULL
);
CREATE INDEX "logs2_time_idx" ON logs2(time);

-- 入力した日付に対応する月毎の logs 子テーブルを作成する関数
CREATE FUNCTION create_table_monthly_logs(IN TIMESTAMP) RETURNS VOID AS
  $$
    DECLARE
      begin_time TIMESTAMP; -- time の開始時刻
      expire_time TIMESTAMP; -- time の終了時刻
    BEGIN
      begin_time := date_trunc('month', $1);
      expire_time := begin_time + '1 month'::INTERVAL;
      EXECUTE 'CREATE TABLE IF NOT EXISTS '
              || 'logs_'
              || to_char($1, 'YYYY"_"MM')
              || '('
              || 'like logs including indexes, '
              || 'CHECK('''
              || begin_time
              || ''' <= time AND time < '''
              || expire_time
              || ''')'
              || ') INHERITS (logs)';
    END;
  $$
  LANGUAGE plpgsql
;

-- 新しいレコードを logs 子テーブルへ書き込む関数
CREATE FUNCTION insert_into_monthly_logs() RETURNS TRIGGER AS
  $$
    BEGIN
      LOOP
        BEGIN
          -- とりあえず、logs 子テーブルに書き込もうとしてみる
          EXECUTE 'INSERT INTO '
                  || 'logs_'
                  || to_char(new.time, 'YYYY"_"MM')
                  || ' VALUES(($1).*)' USING new;
          RETURN NULL;
        EXCEPTION WHEN undefined_table THEN
          -- 対応する logs 子テーブルがなければ作成してリトライ
          PERFORM create_table_monthly_logs(new.time);
        END;
      END LOOP;
    END;
  $$
  LANGUAGE plpgsql
;

-- logs への insert 時に logs 子テーブルへの転送を行うためのトリガー
                 CREATE TRIGGER insert_logs_trigger
               BEFORE INSERT ON logs
 FOR EACH ROW EXECUTE PROCEDURE insert_into_monthly_logs()
;
```

## サンプルデータ生成

```rb
require 'json'
require 'time'

year = ARGV.first.to_i
begin_time = Time.parse("#{year}-01-01")
expire_time = Time.parse("#{year+1}-01-01")

1_000_000.times do
  puts [
    'test',
    {message: 'hogehoge'}.to_json,
    rand(begin_time...expire_time).to_s
  ].join(',')
end

# usage:
# ruby record_gen.rb 2000 | sed -e 's/"/"""/g' > logs.csv
```

## データ投入

```sql
\timing

-- パーティショニングの親テーブル
-- 100 万件コピー
\copy logs from 'logs.csv' with csv
-- Time: 56272.340 ms

-- １件 INSERT
insert into logs values ('test', '{"message": "hoge"}'::JSON, '2018-02-01 00:00:00');
-- Time: 7.344 ms
insert into logs values ('test', '{"message": "hoge"}'::JSON, '2018-03-01 00:00:00');
-- Time: 3.593 ms

-- パーティショニング無しのテーブル
-- 100 万件コピー
\copy logs2 from 'logs.csv' with csv
-- Time: 6259.112 ms

-- １件 INSERT
insert into logs2 values ('test', '{"message": "hoge"}'::JSON, '2018-04-01 00:00:00');
-- Time: 0.547 ms
insert into logs2 values ('test', '{"message": "hoge"}'::JSON, '2018-05-01 00:00:00');
-- Time: 0.275 ms
```

copy だと大体９倍くらい、INSERT だと １４倍くらい遅いですね。
自動振り分けのトリガーは相当なオーバーヘッドになるようです。
下記のように、自動振り分けせず、子テーブルに直接 INSERT すると速いです。

```sql
insert into logs_2013_10 values ('test', '{"message": "hoge"}'::JSON, '2013-10-01 00:00:00');
-- Time: 0.885 ms
```

INSERT のスピードが問われるような使い方には向いていないようです。
ただ、トリガーの書き方を改善することで速くなる余地はありそうな気はします。

## まとめ

パーティショニングはしたいけど、子テーブルを作り忘れたりするの怖いし、アプリ側で適切な子テーブルに振り分けて INSERT するようなコードを書きたくないし、というケースで、今回紹介した自動生成が役に立つと思います。

## 参考資料
[PostgreSQL 9.3.2文書 5.9. パーティショニング](http://www.postgresql.jp/document/9.3/html/ddl-partitioning.html)
[PostgreSQL 9.3.2文書 第 40章PL/pgSQL - SQL手続き言語](http://www.postgresql.jp/document/9.3/html/plpgsql.html)
[テーブルパーティショニングを使って実現するパフォーマンス向上](http://pgsqldeepdive.blogspot.jp/2012/12/postgresql_23.html)
[Let's postgres パーティショニング : 用途と利点](http://lets.postgresql.jp/documents/technical/partitioning/1)
[Let's postgres パーティショニング : 使い方](http://lets.postgresql.jp/documents/technical/partitioning/2)


## 12 月 18 日
今日は誕生日なので４本の Advent Calendar を書きました。よろしければ、このエントリの他に下記のエントリもご覧ください。

- [Refinements のスコープについて](http://qiita.com/yancya/items/b5c8fa2cd53eaa650e65)
- [Rails でシングルじゃないテーブル継承](http://qiita.com/yancya/items/a890ecfce4b29dde5861)
- [誕生日だ](http://upec.jp/archives/592)


誕生日ですが「[ウィッシュリスト](http://www.amazon.co.jp/registry/wishlist/34SGMMW6WQGZM)に入れてるからには読めよ」などと言いながら難しい本を送りつけるなどの行為は何卒