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

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

More than 3 years have passed since last update.

はじめに

この記事では、
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
  -- 第1引数に分割単位、第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');
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
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  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
ユーザーは見つかりませんでした