PostgreSQLで実装する効率的なシーケンス管理

こんにちは。ミツモアエンジニアの加藤です。

※こちらはミツモアAdvent Calendar 2024の12/13の記事です

この記事では、シーケンス管理の実装方法として、PostgreSQLを利用した具体例を紹介します。
ミツモアのプロダクト「ProOne」では、ユーザー設定に基づいた採番方法で、ジョブやキャンペーンの管理番号を生成しています。

以下は月毎に採番した場合の例です。
本例では月単位でカウントをリセットしていますが、年単位でカウントリセットしたり、管理番号の中に仕事の付帯情報などを含めるようにしたりユーザがカスタマイズできるようにしています。

従来の実装方法では、ジョブやキャンペーンごとにシーケンス管理用のテーブルを個別に準備する必要があったほか、以下のデメリットも伴っていました。

テーブルの冗長性：採番が必要な各テーブル(jobやcampaign)に対して、専用のシーケンス管理テーブルが必要
コード量の増加：採番処理・シーケンスの取得に、1000行以上のコードが必要であり、可読性・保守性の低下
DBへの余分なアクセス
デッドロックのリスク

これらの課題に対応するために、私たちは以下の解決方法でpostgreSQLを用いて実装しました。

解決策とその効果

シーケンス管理テーブルの定義
→複数のテーブルを用意する必要がなくなった
トリガー関数の実装
　→コード行数 1000行~から200行へ削減した
　→DBへの余分なアクセスがなくなった
シーケンス進行関数の作成
　 →ON CONFLICT句を用いることで、競合を回避でき、デットロックが起きなくなった

では実際にコードを見ていきましょう。
まずは、シーケンス管理用のテーブル定義を以下のように定義します。
nameはシーケンス名(例:foo:202404)、trackにはリセット単位(月 or 年例:202404)、valueには現在のシーケンスが挿入されます。

1. 管理番号生成のトリガー設定

Foo テーブルにデータが挿入される際に自動的に管理番号を生成するトリガーを以下のように設定します。

CREATE FUNCTION foo_set_slug()
RETURNS TRIGGER
LANGUAGE plpgsql AS $$
  NEW."slug" :=　生成した管理番号
  RETURN NEW;
END;
$$;

CREATE TRIGGER foo_set_slug_before_insert
BEFORE INSERT ON "Foo"
FOR EACH ROW EXECUTE PROCEDURE foo_set_slug();

2. シーケンス生成

こちらが実際にシーケンスを生成し、返す関数となっています。

実際の管理番号は 2024-04-01-0001のような形式ですが、ここでは末尾のシーケンス(1→2→3)を、生成しています。(2024-04-01部分の生成は割愛します)

引数：_trackには現在の日付とリセットする単位を以下のように表示したものを渡します。例)
case1) 現在：2024/04/01 , リセット単位：月
_track= 202404
case2) 現在：2024/04/01 , リセット単位：年
_track= 2024

引数；_nameには tableName:YYYYMM を渡します。 tableNameにはjobのシーケンスだったらjob、Fooのシーケンスだったらfooというようにシーケンス管理するテーブル名を入れています。
これによって複数のテーブルのシーケンスを一つのテーブルで管理できるようになります。

CREATE  FUNCTION public.get_next_sequence_value(
    _name TEXT, -- foo:202404
    _track TEXT -- 202404
) RETURNS INT
LANGUAGE plpgsql 
AS $$
DECLARE _value INT;
BEGIN
    INSERT INTO "ManagementNumberSequence" ("name", "track", "value")
    VALUES (_name, _track, 0) -- データがない場合は0をセット
    
    -- 他のトランザクションとの競合を避ける
    ON CONFLICT ON CONSTRAINT "ManagementNumberSequence_pkey" DO UPDATE
    SET "value" = "ManagementNumberSequence"."value" -- only to lock the row
    WHERE "ManagementNumberSequence"."name" = EXCLUDED.name;

    -- シーケンスを1進める
    UPDATE "ManagementNumberSequence"
       SET "value" = (
               SELECT MAX("value")
                 FROM "ManagementNumberSequence"
                WHERE ("name" = _name)
                   OR ("name" LIKE SPLIT_PART(_name, ':', 1) || ':'
                                || SPLIT_PART(_name, ':', 2) || ':'
                                || _track || '%' AND
                       "track" = _track)
           ) + 1
     WHERE "name" = _name
       AND "track" = _track
    RETURNING "value" INTO _value;

    RETURN _value;
END $$;

この関数によって、指定されたリセット単位でシーケンスが効率的に生成されます。以下は具体的な実行例です。