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

  • 30
    いいね
  • 3
    コメント
この記事は最終更新日から1年以上が経過しています。

パーティショニングとは

パーティショニングについての詳細は Let's postgres パーティショニング : 用途と利点に詳しいですので、ここでは省略します。

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

親テーブルに加えて複数の子テーブルを作成するのに加え、親テーブルにトリガ (8.1 以前ではさらにルール) を定義する必要があります。
パーティショニング : 使い方

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

サンプル実装


-- パーティショニング対象の親テーブル定義
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()
;

サンプルデータ生成

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

データ投入

\timing

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

-- 1件 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

-- 1件 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 だと大体9倍くらい、INSERT だと 14倍くらい遅いですね。
自動振り分けのトリガーは相当なオーバーヘッドになるようです。
下記のように、自動振り分けせず、子テーブルに直接 INSERT すると速いです。

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. パーティショニング
PostgreSQL 9.3.2文書 第 40章PL/pgSQL - SQL手続き言語
テーブルパーティショニングを使って実現するパフォーマンス向上
Let's postgres パーティショニング : 用途と利点
Let's postgres パーティショニング : 使い方

12 月 18 日

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

誕生日ですが「ウィッシュリストに入れてるからには読めよ」などと言いながら難しい本を送りつけるなどの行為は何卒

この投稿は PostgreSQL Advent Calendar 201418日目の記事です。