はじめに

エキサイト株式会社バックエンドエンジニアの山縣（@zsp2088dev）です。

エキサイトブログでは、DBコスト削減に取り組んでおり、これまでにいくつかの改善を行ってきました。取り組んだこととその効果については、下記記事をご参照ください。

tech.excite.co.jp

その記事の中で、PostgreSQLのイベントトリガの活用について簡単に触れました。本記事では、イベントトリガのより具体的な活用方法について紹介します。

はじめに
エキサイトブログのタグ機能
PostgreSQLのイベントトリガを採用した理由
実際に作成したテーブルとイベントトリガ
- 追加・更新時のトリガ関数
追加・更新時のトリガー関数をテーブルに適用する
- 削除時のトリガ関数
- 削除時のトリガ関数をテーブルに適用する
おわりに
採用アナウンス

エキサイトブログのタグ機能

エキサイトブログには、1つの記事に最大で3つのタグをつける機能があります。記事にタグをつけると、人気タグランキングに参加できたり、タグ検索をしたりできます。また、ブロガーのトップページには、「タグとタグの個数」を表示する箇所があります。

ここで、タグを扱うDBのテーブルに着目します。一部カラムは省略・改変していますが、ユーザーID、記事ID、1つ目のタグ、2つ目のタグ、3つ目のタグを持つテーブル構成となっています。

以下のクエリーを実行すると、ブロガーの記事に紐づくタグを取得できます。

SELECT
    tag_name_1,
    tag_name_2,
    tag_name_3
FROM
    sample_tag
WHERE
    user_id = 'test_user_1'
    AND article_id = 1

上記のように、1つ目のタグ、2つ目のタグ、3つ目のタグとカラムを持っているため、ユーザーIDと記事IDを指定すると簡単に記事に紐づくタグを取得できます。一方で、「タグとタグの個数」を取得するためには、以下のクエリーを実行しないといけません。

SELECT
    tag_name,
    COUNT(*) AS tag_count
FROM (
    SELECT
        user_id,
        tag_name_1 AS tag_name
    FROM
        sample_tag
    UNION ALL
    SELECT
        user_id,
        tag_name_2 AS tag_name
    FROM
        sample_tag
    UNION ALL
    SELECT
        user_id,
        tag_name_3 AS tag_name
    FROM
        sample_tag) sample_tag_name
WHERE
    user_id = 'test_user_1'
    AND tag_name <> ''
GROUP BY
    tag_name
ORDER BY
    tag_count DESC

これにより、これまでに多くの記事を書き、様々なタグを登録しているブロガー記事を閲覧したときのクエリーの負荷が非常に高くなっていました。キャッシュを利用してDBの参照頻度を減らすようにしていましたが、キャッシュ生存時間が切れてしまうとやはり負荷が増加してしまいます。そのため、何らかの方法で根本的な解決をする必要がありました。

PostgreSQLのイベントトリガを採用した理由

上記の状況のときに、1つ目のタグ、2つ目のタグ、3つ目のタグと持つテーブルに対して正規化を行い、「タグとタグ順序」を持つようなテーブルに切り替えるのが望ましいと思います。実際に、エキサイトブログのブログテーマ機能では、旧テーブルから新テーブルにマイグレーションを行っています。

tech.excite.co.jp

今回はブロガー記事の閲覧に問題が生じていため、迅速な対応が求められていました。タグテーブルの修正による影響範囲は大きく、旧テーブルから新テーブルへのマイグレーションをするには時間がかかるため別の解決方法を模索する必要がありました。

また、今回特に問題となっているのは、ブロガーのトップページに表示するメニューだけであり、この問題の解決が最優先でした。

チームメンバーとの話し合いの結果、PostgreSQLのイベントトリガ機能を利用すれば、この問題を解決できる可能性があるという結論に至りました。

実際に作成したテーブルとイベントトリガ

イベントトリガを活用するにあたり、以下のような「タグとタグ個数」テーブルを用意しました。

このテーブルに対して、以下のクエリーを実行すると、UNION ALLを使用したクエリーと同じ結果*1を得られます。パッと見で何をしたいのかがわかりやすく、クエリー負荷も減らせます。

SELECT
    tag_name,
    count
FROM
    sample_tag_count
WHERE
    user_id = 'test_user_1'
ORDER BY
    count DESC;

そして、イベントトリガを機能を活用すると、以下の操作を自動で実行してくれます。

タグテーブルに追加、更新したときに、「タグとタグ個数」テーブルを更新する
タグテーブルから削除したときに、「タグとタグ個数」テーブルから削除する

実際に使用しているトリガ関数の一例を下記に示します。

追加・更新時のトリガ関数

CREATE OR REPLACE FUNCTION trigger_sample_tag_insert_update ()
    RETURNS TRIGGER
    LANGUAGE plpgsql
    AS $trigger_sample_tag_insert_update$
BEGIN
    IF NEW IS NULL THEN
        RETURN NULL;
    END IF;

    DELETE FROM sample_tag_count WHERE user_id = NEW.user_id;

    INSERT INTO sample_tag_count (user_id, tag_name, count)
    SELECT
        user_id,
        tag_name,
        COUNT(*) AS tag_count
    FROM (
        SELECT
            user_id,
            tag_name_1 AS tag_name
        FROM
            sample_tag
        UNION ALL
        SELECT
            user_id,
            tag_name_2 AS tag_name
        FROM
            sample_tag
        UNION ALL
        SELECT
            user_id,
            tag_name_3 AS tag_name
        FROM
        sample_tag) sample_tag_name
    WHERE
        user_id = NEW.user_id
        AND tag_name <> ''
    GROUP BY
        user_id,
        tag_name;

    RETURN NULL;
END;
$trigger_sample_tag_insert_update$;

追加・更新時のトリガー関数をテーブルに適用する

CREATE TRIGGER trigger_sample_tag_insert_update
    AFTER INSERT OR UPDATE
    ON sample_tag
    FOR EACH ROW
    EXECUTE PROCEDURE trigger_sample_tag_insert_update();

削除時のトリガ関数

レコード削除時の関数を定義する。

CREATE OR REPLACE FUNCTION trigger_sample_tag_delete ()
    RETURNS TRIGGER
    LANGUAGE plpgsql
    AS $trigger_sample_tag_delete$
BEGIN
    IF OLD IS NULL THEN
        RETURN NULL;
    END IF;
        
    DELETE FROM sample_tag_count WHERE user_id = OLD.user_id;

    INSERT INTO sample_tag_count (user_id, tag_name, count)
    SELECT
        user_id,
        tag_name,
        COUNT(*) AS tag_count
    FROM (
        SELECT
            user_id,
            tag_name_1 AS tag_name
        FROM
            sample_tag
        UNION ALL
        SELECT
            user_id,
            tag_name_2 AS tag_name
        FROM
            sample_tag
        UNION ALL
        SELECT
            user_id,
            tag_name_3 AS tag_name
        FROM
            sample_tag) sample_tag_name
    WHERE
        user_id = OLD.user_id
        AND tag_name <> ''
    GROUP BY
        user_id,
        tag_name;

    RETURN NULL;

END;
$trigger_sample_tag_delete$;

削除時のトリガ関数をテーブルに適用する

CREATE TRIGGER trigger_sample_tag_delete
    AFTER DELETE
    ON sample_tag
    FOR EACH ROW
    EXECUTE PROCEDURE trigger_sample_tag_delete();

おわりに

本記事では、エキサイトブログにおけるDBコスト削減の取り組みと、特にPostgreSQLのイベントトリガ機能を活用した活用例を紹介しました。イベントトリガを活用すると、DB内であるテーブルのイベントを元に別テーブルの更新ができるようになります。一方で、トリガ関数の書き方は、見慣れないものであり、複雑なものにするとメンテナンスがしづらいといった問題もあるかと思います。トリガ関数は適切な場面で活用していきたいと考えています。エキサイトブログでは、引き続き既存機能の改修や新機能の開発などに取り組んでいきます。

採用アナウンス

エキサイトではフロントエンジニア、バックエンドエンジニア、アプリエンジニアを随時募集しています。また、長期インターンも歓迎しています。

カジュアル面談からもOKです。少しでもご興味がございましたら、お気軽にご連絡頂ければ幸いです。

▼ 募集職種一覧 ▼ recruit.jobcan.jp

*1:タグ個数が同じ場合、異なる結果の場合があります。

エキサイト TechBlog.

エキサイトブログにおけるPostgreSQLのイベントトリガの活用例