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

qiita.com

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

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

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

TL;DR

  • 今、自社の業務でトリガーをめっちゃ使ってる。
  • PostgreSQLMySQLをトリガーの観点から比較すると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では SQLC言語以以外の言語でユーザー定義関数を作成する事が出来ます。例えば、ユーザー定義関数の作成の言語としてPythonJavaなどが選べます。

弊社では 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のトリガーの機能として、以下の3パータンがサポートされています。

  • どの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への興味が出てきた方は是非、以下の本をおすすめします!!

[改訂新版]内部構造から学ぶPostgreSQL 設計・運用計画の鉄則 (Software Design plus)

[改訂新版]内部構造から学ぶPostgreSQL 設計・運用計画の鉄則 (Software Design plus)

これからはじめる PostgreSQL入門

これからはじめる PostgreSQL入門

最後に告知です。

2〜3ヶ月に一度、DBの勉強会を 中国地方で行っております。

今回は岡山で行います。 セッションは調整中ですが、今回もDBを取り上げて勉強会を開催しようかと思いますので ご興味ある方はお越しください。

dbstudychugoku.connpass.com

では、これで僕の PostgreSQL Advent Calendar は終了です。

明日は Fujii Masao (@fujii_masao) | Twitter さんです。

文字コード系か何か という事で楽しみに待ちたいと思います。 長い文章お読み頂きありがとうございました!