Posted inデータベース

PostgreSQLのストアドプロシージャとトリガー：ビジネスロジックの分散を防ぐ

Database tutorial - IT technology blog
Database tutorial - IT technology blog

背景と理由：ロジックをデータに近づける

私は最近、データ整合性の問題に悩まされていた高トラフィックなフィンテックアプリケーションの整理に6ヶ月を費やしました。そのNode.jsバックエンドは、複利計算から監査ログの更新に至るまで、あらゆるビジネスルールを処理していました。

ロジックをアプリケーション層に置くことは柔軟に感じられますが、5つの異なるマイクロサービスが同じデータベースにアクセスし始めたとき、私たちは限界に直面しました。レースコンディション（競合状態）は毎週のようにトラブルを引き起こし、絶え間ないラウンドトリップによるネットワークレイテンシによって、レスポンスタイムは200msを超えて膨れ上がりました。

そこで私は、重い処理をPostgreSQLに移すことに決めました。コアとなるビジネスロジックをデータベースに直接移動させることで、どのサービスがデータに触れてもルールが同一であることを保証しました。これが究極の「信頼できる唯一の情報源（Source of Truth）」となったのです。移行後、コアAPIのレイテンシは60%近く低下し、厄介な「ゴースト」データバグも一晩で解消されました。

ストアドプロシージャは、複雑なSQLを一つの呼び出し可能なユニットにまとめる方法だと考えてください。トリガーは、行が挿入または変更されるたびに自動的にロジックを実行する「静かなヒーロー」です。3つの異なるプログラミング言語でバリデーションロジックを重複して書くことに疲れているなら、このアプローチは最高の味方になるでしょう。

インストール：PostgreSQL環境の準備

PostgreSQLには、PL/pgSQLという独自のプロシージャ言語が標準で搭載されています。ほとんどの現代的なインストール環境では、すぐに使用できる状態になっています。しかし、私は本番環境に複雑なロジックをデプロイする前に、必ず環境を確認するようにしています。

言語が利用可能かどうかを確認するには、psqlまたはお好みのGUIから以下のクエリを実行します：

SELECT * FROM pg_language;

リストにplpgsqlが表示されない場合は、一つのコマンドで有効化できます。これはデータベースごとに一度だけ実行すれば十分です：

CREATE EXTENSION IF NOT EXISTS plpgsql;

ワークフローのヒント：関数のために1万行のCSVをJSONに変換するといったテストデータの準備には、toolcraft.app/ja/tools/data/csv-to-jsonを使用しています。これはブラウザ上で完全に動作するため、データがマシン外に出ることはありません。機密性の高いフィンテックの記録を扱う際のプライバシー確保には必須のツールです。

設定：プロシージャとトリガーの実装

技術的には、関数（Function）とプロシージャ（Procedure）には小さくも重要な違いがあります。関数は計算に適しており、必ず値を返す必要があります。一方、PostgreSQL 11で導入されたプロシージャは、コードブロック内でCOMMITやROLLBACKを使用してトランザクションを制御できます。

1. トランザクションのためのストアドプロシージャ作成

プロシージャはバッチ処理に最適です。例えば、以下は資金移動を処理するために私が書いたプロシージャです。送金側と受取側の両方の更新が、単一のアトミックな単位として確実に行われるようにします。

CREATE OR REPLACE PROCEDURE transfer_funds(
   sender_id INT, 
   receiver_id INT, 
   amount DECIMAL
)
LANGUAGE plpgsql
AS $$
BEGIN
    -- 送金元から差し引き
    UPDATE accounts SET balance = balance - amount WHERE id = sender_id;
    
    -- 送金先へ追加
    UPDATE accounts SET balance = balance + amount WHERE id = receiver_id;

    COMMIT;
END;
$$;

これを実行するには、CALLコマンドを使用します：

CALL transfer_funds(101, 202, 500.00);

2. トリガーによる自動化

トリガーは私たちの監査システムの立役者でした。APIコードにロギングロジックを詰め込むことなく、メールアドレスの変更を追跡する必要がありました. まず、TRIGGERを返す関数を定義し、それをテーブルにバインドします。

-- ロジック用関数
CREATE OR REPLACE FUNCTION log_email_change()
RETURNS TRIGGER AS $$
BEGIN
    IF OLD.email <> NEW.email THEN
        INSERT INTO audit_logs(user_id, old_email, new_email, changed_at)
        VALUES (OLD.id, OLD.email, NEW.email, NOW());
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- トリガーの紐付け
CREATE TRIGGER trigger_email_update
AFTER UPDATE ON users
FOR EACH ROW
EXECUTE FUNCTION log_email_change();

これで、usersテーブルに対してUPDATEが実行されるたびに、PostgreSQLが自動的にログを処理します。バックエンド開発者はJavaScriptを一行も変更する必要がなく、コンプライアンスチームは必要な情報を正確に得ることができました。

検証とモニタリング：スムーズな運用を維持するために

これらを本番環境で運用し始めて気づいたのは、「設定したら終わり」という考え方は危険だということです。パフォーマンスを監視する必要があります。PostgreSQLには、これを驚くほど簡単にする internal ビューが用意されています。

関数のパフォーマンス確認

私はpg_stat_user_functionsビューを使用して、呼び出し回数と実行時間を追跡しています。これにより、登録フローに50msのオーバーヘッドを加えていた低速なバリデーション関数を特定することができました。

SELECT funcname, calls, total_time, self_time 
FROM pg_stat_user_functions 
ORDER BY total_time DESC;

ログによるデバッグ

PL/pgSQLのデバッグは、VS Codeのようにブレークポイントを設定できないため、少しコツがいります。開発中はRAISE NOTICEを使って変数の値を出力しています。これらのメッセージはSQLコンソールやサーバーログに表示されます。

RAISE NOTICE 'ユーザー % から金額 % を送金しています', sender_id, amount;

失敗から学んだ教訓：再帰的なトリガーには注意してください。以前、監視対象と同じテーブルを更新するトリガーを書いてしまったことがあります。それは無限ループを引き起こし、接続をクラッシュさせました。トリガーを本番に適用する前には、必ずBEGIN; ... ROLLBACK;ブロックでテストし、期待通りに動作することを確認してください。

これらのツールを活用することで、バックエンドコードの複雑さを20%削減し、最終的に100%のデータ信頼性を達成しました。複数のアプリにサービスを提供するデータベースを管理しているなら、コアロジックをPostgreSQLに移行することは単なる最適化ではありません。それは平穏な運用のための必然なのです。

Share:
Tags: