(14日目) PostgreSQL のトリガーの柔軟さについて調べてみた

qiita.com

この記事は PostgreSQL Advent Calendar 14日目の記事です。

昨日は央 (@sogaoh) | Twitter さんの pg_repack関連 のお話でした。

今日は僕が最近業務でトリガーを使っていった中で PostgreSQLのトリガーは柔軟だな、って思ったのでその辺の話を紹介させてもらおうかな、と思います。

TL;DR

今、自社の業務でトリガーをめっちゃ使ってる。
PostgreSQLとMySQLをトリガーの観点から比較するとPostgreSQLの方が柔軟。
例えば、発動タイミングを柔軟に設定出来る、って点とか。

トリガーを調べた理由

www.ikkitang1211.site

先日のPostgreSQL Conference Japan 2018とかでもお話させて頂いたのですが今、自社の株式会社オミカレで Web・DBのリファクタリングを行っています。

その中でDBリファクタリングの手法として、 AWS の Database Migration Service を使って、「MySQLのデータをPostgreSQLのOLDスキーマへ移行」 -> 「OLDスキーマに設定したトリガーを発動させてリファくタリング済のNEWスキーマへ移行」というような流れで行ってます。

「なぜ、MySQLからPostgreSQLに？」ってのはブログ記事からスライドを追って頂ければ書いていますが、当時の知識で 「MySQLよりPostgreSQLの方がトリガーが柔軟だと思っていたから」 という理由でした。

MySQLよりPostgreSQLのトリガーが柔軟な点

MySQLには複数トリガーが貼れない

※※ 但し、MySQL 側は 5.7以降でちゃんと貼れるようになってる

PostgreSQL側では複数のトリガーをサポートしています。

CREATE TRIGGER hogehoge_on_insert_trigger1
 AFTER INSERT ON hoge FOR EACH ROW hogehoge_insert_function1;

CREATE TRIGGER hogehoge_on_insert_trigger2
 AFTER INSERT ON hoge FOR EACH ROW hogehoge_insert_function2

( hoge テーブルの INSERT トリガーとして
hogehoge_on_insert_trigger1 という名前のトリガーと
hogehoge_on_insert_trigger2 という名前のトリガーを
設定する構文)

PostgreSQL では、上記のSQLは正常に実行されますが、 MySQL(5.6まで）ではどちらか一方しか設定する事が出来ませんでした。

また、合わせて PostgreSQL ではトリガーの発動タイミングを一斉に指定する事も出来ます。(この機能は標準SQLに対するPostgreSQLの拡張です.)

例えば、

CREATE TRIGGER hogehoge_on_insert_trigger1
 AFTER INSERT OR UPDATE OR DELETE ON hoge FOR EACH ROW hogehoge_insert_function1;

AFTER INSERT OR UPDATE OR DELETE として INSERT・UPDATE・DELETE の各タイミングで発動するトリガーを設定する事も出来ます。（関数内で INSERTで呼ばれたかとかは判別出来るようになっている。）

例えば、オミカレでは一個のトリガーの中に各ステートメント毎の記述を書いてます。

if (TG_OP === "INSERT") {
  /* 新テーブルへのINSERT処理 */
} else if (TG_OP === "UPDATE") {
  /* 新テーブルへのUPDATE処理 */
} else if (TG_OP === "DELETE") {
  /* 新テーブルのDELETE処理 */
}

というのも リファクタリングが終わればトリガーは削除する ってのとトリガーの中身もリファクタリング後のNEWスキーマへINSERT / UPDATE / DELETE する処理しかやってないからです。

手続き型言語が使用出来る。

第41章手続き言語

PostgreSQLでは SQLやC言語以以外の言語でユーザー定義関数を作成する事が出来ます。例えば、ユーザー定義関数の作成の言語としてPythonやJavaなどが選べます。

弊社では JavaScript でユーザー定義関数を実装しています。 RDSに pl/Python がサポートされてない、ってのも大きな理由なのと、 pgsql とかよりも速いみたいですね。フィボナッチ数を計算する関数を pl/pgSQL と pl/v8 (JavaScript) で比較した所、 0.66ms と 0.39ms などと違いが出てます。

github.com

行レベルだけでなく文レベルのトリガーを作成出来る。

基本的には ROW レベルでトリガーを設定するかと思いますが、PostgreSQLでは STATEMENT ベースでトリガーを設定する事が出来ます。

例えば、 TRUNCATE 文が流れた時のタイミングで一度だけ発動するトリガーをしかける事も出来ます。例えば、下記のようなトリガーを設定しておいて、Truncateを許可しない、とかって使いみちもありそうですね。

CREATE FUNCTION on_hoge_trigger() RETURNS OPAQUE AS '
    BEGIN
      RAISE EXCEPTION ''not allowed truncate this table'';
    END;
' LANGUAGE 'plpgsql';

CREATE TRIGGER on_hoge_trigger AFTER TRUNCATE ON hoges FOR EACH STATEMENT EXECUTE PROCEDURE on_hoge_trigger();

こちらも標準SQLに対してのPostgreSQLの拡張です。

UPDATE トリガーの発動条件を設定出来る。

PostgreSQLでは UPDATEのトリガーの機能として、以下の３パータンがサポートされています。

どのUPDATEトリガーでも発動させる。
SET句に指定カラムが設定された時に発動させる。
SET句に指定カラムが設定されてかつその値が更新された時のみに発動させる。

不要な時にトリガーが発動しないようにしたりとかそもそもの速度の向上であったりとか、使い方が色々広がりそうです。

ここで、

- 条件有りの時と無しの時で速度にどれ位差が出るか。
- 条件の比較自体に速度がどれくらいかかるか。

みたいな疑問点がわいたので、ちょっと調べてみたいと思います。

ベンチマークのとり方が分からなかった ので適当に・・・はかってみました。（普通、同条件ではかりますもんね・・・ちゃんとしたとり方じゃなさそう。。。）

進め方

create table hoges
(
    id serial not null
        constraint hoges_pkey
            primary key,
    name varchar(64) not null
);

-- 100万件位INSERTした。

-- 100万レコードのデータに対して name を更新するUPDATE文を流す。
UPDATE hoges SET name = 'hoge_name' || trunc(random() * 9 + 1);

100万件のレコードに対して hoges.name を10% の確率で同じ値で更新する UPDATEを流す。

上記を行って以下の予測の元で比較してみたいと思います。

- 速度比較した時に 条件付きトリガーの方が条件無しトリガーより10%程 実行速度が速い。

発動する関数

CREATE FUNCTION on_hoge_trigger() RETURNS trigger AS $$

    /* 更新されたIDをログとして吐く. */
    plv8.elog(NOTICE, "UPDATED id = ", NEW.id);

$$ LANGUAGE "plv8";

トリガー有り(条件無し)

CREATE TRIGGER on_hoge_trigger
  AFTER UPDATE
  ON hoges FOR EACH ROW
  EXECUTE PROCEDURE on_hoge_trigger();

トリガー有り(条件有り)

CREATE TRIGGER on_hoge_trigger
  AFTER UPDATE OF name
  ON hoges FOR EACH ROW
  WHEN (OLD.name <> NEW.name)
  EXECUTE PROCEDURE on_hoge_trigger();

結果

5回試してみて、平均を取りました。

パターン	5回の平均 ※()内は中央値
トリガー無し	7.559s (7.819s)
トリガー有り(条件無し)	3m42.583s (3m38.93)
トリガー有り(変更時のみ)	3m15.528s (3m16.18)

まあ、トリガーの有無で大分差が出る、ってのはご覧の通りではありますが、当初の予想どおり大体 10%弱処理時間が短くなってるな、って感じです。

また、1件だけの処理速度も比較してみましたがトリガー無しが 6.1ms、条件付きトリガーが 5.3ms という結果も出ました。(何故か、条件付きトリガーの方が速い。)

条件比較 の速度はUPDATEの速度にそこまで影響は出ない、と思ってても良さそうです。

トリガーの速度で困ってる方とかは是非条件を上手く使って、トリガーの速度向上を行ってみてもらっては？と思いますm( )m

まとめ

何かトリガーでお困りの際は是非参考になれば、と思い今回はPostgreSQL Advent Calendar にトリガーの話を書きました。

トリガーって僕も今の職場に来るまで使った事無い、得体のしれない物 だったのですが、使い方次第で安全に出来たり ( TRUNCATE の STATEMENT は例外を吐いて落とす, ) とか、色々な使いみちがあるな〜、って感じですね。

僕自身も初めての PostgreSQL Advent Calendar だったので中々緊張の面持ちではありますが・・・何か参考になれば幸いです。

(宣伝)

今回のまとめにあたって PostgreSQL の日本語ドキュメントには非常にお世話になりました。 PostgreSQLの日本語ドキュメントは僕も所属している日本PostgreSQLユーザ会 | 日本PostgreSQLユーザ会の活動として行われています。

現在、PostgreSQLに興味ある方や OSSの活動に貢献したい方など翻訳活動にご協力いただける方を募集しております。詳しくは以下ツイートをっ＼(^o^)／

PostgreSQL 11のマニュアル翻訳がスタートするします。今回もいろいろ変わってますが、JIT https://t.co/Y6Sis6yWxo
とbtreehttps://t.co/iKigy8a2S7
の章が追加されてるけど、盛り上がったところで https://t.co/grWSrmFuFM
が残念なことに。ソース内のREADMEは翻訳対象外なんだ。
— Saito Noboru (@noborus) November 20, 2018