はじめに
エキサイト株式会社 バックエンドエンジニアの山縣（@zsp2088dev）です。
エキサイトブログでは、DBコスト削減に取り組んでおり、これまでにいくつかの改善を行ってきました。
取り組んだこととその効果については、下記記事をご参照ください。
tech.excite.co.jp
その記事の中で、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の参照頻度を減らすようにしていましたが、キャッシュ生存時間が切れてしまうとやはり負荷が増加してしまいます。
そのため、何らかの方法で根本的な解決をする必要がありました。
上記の状況のときに、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
