Help us understand the problem. What is going on with this article?

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

More than 5 years have passed since last update.

パーティショニングとは

パーティショニングについての詳細は 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 を書きました。よろしければ、このエントリの他に下記のエントリもご覧ください。

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

Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away