パーティショニングとは
パーティショニングについての詳細は 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
-- １件 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 すると速いです。
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 日
今日は誕生日なので４本の Advent Calendar を書きました。よろしければ、このエントリの他に下記のエントリもご覧ください。
誕生日ですが「ウィッシュリストに入れてるからには読めよ」などと言いながら難しい本を送りつけるなどの行為は何卒