PostgreSQLのタイムスタンプ・パーティション用汎用トリガ関数 #PostgreSQL

はじめに

この記事では、
PostgreSQLのパーティションテーブル自動生成を参考に、timestamp型のtimeカラムを分割キーとするパーティションを自動生成するための汎用トリガ関数を例示します。

パーティション用汎用トリガ関数

このトリガ関数は、参考記事のサンプルと同様に、まずはパーティションの子テーブルにINSERTを試みます。子テーブルが未作成でINSERTが失敗したときには、その子テーブルを自動作成し、再度INSERTを試みます。

このトリガ関数では、パーティション分割の単位(日単位や月単位など)と子テーブル名の添え字フォーマットをCREATE TRIGGER実行時にユーザが指定します。つまり、異なる単位のパーティションを作成するにあたって、別途トリガ関数を用意する必要はありません。このトリガ関数のみで様々な分割単位のパーティションを生成できます。

-- パーティションの子テーブルを作成する関数
CREATE OR REPLACE FUNCTION create_timerange_partition (
  partition_key timestamp,  -- INSERTするレコードの分割キーの値
  parent_table name,        -- パーティションの親テーブルの名前
  range_interval text,      -- パーティションの分割単位(例えば、hour, day, monthなど)
  suffix_format text)       -- 子テーブルの名前の添え字フォーマット
                            -- ※分割単位と添え字フォーマットは整合していること
RETURNS void AS $$
DECLARE
  mintime timestamp := date_trunc(range_interval, $1);
  maxtime timestamp := mintime + ('1 ' || range_interval)::interval;
BEGIN
  -- 引数の値から子テーブルの名前やパーティション範囲を計算して、子テーブルを作成する
  EXECUTE 'CREATE TABLE IF NOT EXISTS ' ||
    parent_table || to_char(partition_key, suffix_format) ||
    ' (LIKE ' || parent_table || ' INCLUDING INDEXES, CHECK(''' ||
    mintime || ''' <= time AND time < ''' || maxtime ||
    ''')) INHERITS (' || parent_table || ')';
END;
$$ LANGUAGE plpgsql;


-- パーティションへのINSERTトリガ関数
CREATE OR REPLACE FUNCTION insert_timerange_partition ()
RETURNS TRIGGER AS $$
DECLARE
  -- 親テーブルの名前はトリガ関数の変数から自動的に取得
  parent_table name := TG_TABLE_SCHEMA || '.' || TG_TABLE_NAME;
  -- パーティションの分割単位は、トリガ作成時に指定
  range_interval text := TG_ARGV[0];
  -- 子テーブル名の添え字フォーマットは、トリガ作成時に指定
  suffix_format text := TG_ARGV[1];
  retry_count integer;
BEGIN
  -- 無限ループに陥らないように、INSERT→子テーブル作成→INSERTの試行は1回のみに制限
  FOR retry_count IN 0 .. 1 LOOP
    BEGIN
      EXECUTE 'INSERT INTO ' || parent_table ||
        to_char(NEW.time, suffix_format) ||
        ' VALUES (($1).*)' USING NEW;
      RETURN NULL;
    EXCEPTION WHEN undefined_table THEN
      -- 無限ループに陥りそうな場合は例外発生
      IF retry_count >= 1 THEN
        RAISE EXCEPTION 'could not insert data into "%" partition',
          parent_table;
      END IF;

      -- 子テーブルが未作成の場合は、作成して再度INSERTを試みる
      PERFORM create_timerange_partition(
        NEW.time, parent_table,
        range_interval, suffix_format);
    END;
  END LOOP;
END;
$$ LANGUAGE plpgsql;

汎用トリガ関数を使ったパーティション化

ここでは、上記の汎用トリガ関数を使い、psql経由でのCPU使用率のテーブル蓄積で利用したcpu_usageテーブルをパーティション化してみます。cpu_usageテーブルの定義は以下のとおりです。

CREATE TABLE cpu_usage (
  time timestamp DEFAULT now(),
  us smallint,
  sy smallint,
  id smallint,
  wa smallint
);

日単位パーティション

cpu_usageテーブルを日単位にパーティション化したい場合は、CREATE TRIGGER実行時に、パーティションの分割単位として'day'、子テーブル名の添え字フォーマットとして(例えば)'_YYYYMMDD'を指定します。

CREATE TRIGGER insert_cpu_usage_partition
  BEFORE INSERT ON cpu_usage FOR EACH ROW
  EXECUTE PROCEDURE
  -- 第１引数に分割単位、第2引数に添え字フォーマットを指定
  insert_timerange_partition('day', '_YYYYMMDD');

これでcpu_usageテーブルのパーティション化は終わりです。cpu_usageテーブルにレコードをINSERTすると、日単位パーティションの子テーブルが自動的に作成されて、そこにレコードがINSERTされます。

-- COPY FROM PROGRAMを使い、vmstatからCPU使用率を取得してテーブルにコピー
COPY cpu_usage(us, sy, id, wa)
  FROM PROGRAM 'vmstat | tail -1 | awk ''{print $13 "," $14 "," $15 "," $16}'''
  WITH (FORMAT csv);

-- 日単位で子テーブルが自動作成されたのを確認
\dt
               List of relations
 Schema |        Name        | Type  |  Owner   
--------+--------------------+-------+----------
 public | cpu_usage          | table | postgres
 public | cpu_usage_20161007 | table | postgres

月単位パーティション

汎用トリガ関数を使って、CREATE TRIGGER実行時の指定を変えるだけで、cpu_usageテーブルを月単位にパーティション化できます。

CREATE TRIGGER insert_cpu_usage_partition
  BEFORE INSERT ON cpu_usage FOR EACH ROW
  EXECUTE PROCEDURE
  insert_timerange_partition('month', '_YYYYMM');