たった1つのデータベースの工夫でメール処理を18倍高速化した方法

メール同期の問題

ファイルからデータベースへメールアドレスを同期するアプリを構築しているとします。タスクは、ファイル内のすべてのメールを有効（active）にし、データベース内にあってもファイル内になくなったメールを無効化し、監査のためにすべての変更履歴を記録することです。単純な処理に思えますが、10万件のアドレスを扱う場合、その単純なアプローチがボトルネックとなります。

開発者のYaroslav Podorvanov氏は最近、この問題の解決にまさに90秒かかっていたものの、あるPostgreSQLのテクニックを使って書き直したことでわずか5秒に短縮できた経験を共有しました。2026年7月8日時点で、このアプローチはデータベースの仕組みに関する重要な事実を伝えているため、理解する価値があります。本当のコストは通常、重い処理自体ではなく、コードとデータベースの間のやり取り（会話）にあるのです。

低速版の仕組み

元のソリューションでは、処理を別々のステップに分割していました。

1. ファイルから新しいメールアドレスを挿入または更新する
2. ファイルに存在しないメールアドレスを非アクティブ化する
3. 監査のために各変更を履歴テーブルに記録する

各ステップは独立したデータベースクエリでした。つまり、コードは以下を行う必要がありました。

• メールリストをデータベースに送信する
• レスポンスを待つ
• 変更されたもののIDを取得する
• それらのIDをデータベースに送り返す
• 再び待つ
• 非アクティブ化のために繰り返す
• 履歴記録のために再度繰り返す

10万件のアドレスでは、これらの待ち時間が積み重なり、合計90秒になりました。

CTEによる解決策

共通テーブル式（CTE）は、複雑なクエリを名前付きの再利用可能なチャンクに分割できるようにするPostgreSQLの機能であり、すべて単一のデータベース操作内で行われます。データベースへの往復を5回行う代わりに、Podorvanov氏はすべてを1つのクエリにまとめました。

WITH
  data (email) AS (
    SELECT UNNEST(@emails::VARCHAR[])
  ),
  deactivated (id) AS (
    UPDATE rtt_emails
    SET active = FALSE, updated_at = @now::TIMESTAMP
    WHERE email NOT IN (SELECT email FROM data)
    AND active = TRUE
    RETURNING id
  ),
  activated (id) AS (
    INSERT INTO rtt_emails (email, active, created_at, updated_at)
    SELECT email, TRUE, @now::TIMESTAMP, @now::TIMESTAMP
    FROM data
    ON CONFLICT (email) DO UPDATE
    SET active = EXCLUDED.active, updated_at = EXCLUDED.updated_at
    WHERE rtt_emails.active IS DISTINCT FROM EXCLUDED.active
    RETURNING id
  ),
  deactivated_history AS (
    INSERT INTO rtt_email_history (email_id, active, file_id, created_at)
    SELECT id, FALSE, @file_id::BIGINT, @now::TIMESTAMP
    FROM deactivated
  ),
  activated_history AS (
    INSERT INTO rtt_email_history (email_id, active, file_id, created_at)
    SELECT id, TRUE, @file_id::BIGINT, @now::TIMESTAMP
    FROM activated
  )
SELECT
  (SELECT COUNT(*) FROM activated) AS activated_count,
  (SELECT COUNT(*) FROM deactivated) AS deactivated_count;

一見複雑に見えますが、実際には個別のクエリが行っていたことと同じことを、データベースとの1回の会話の中で行っています。CTEは各処理を連結します。入力データが有効化ステップに渡され、それが履歴記録ステップに渡される、といった具合です。

高速化の結果

結果は一目瞭然です。

• 10,000件のアドレス： 約5秒（低速版） → 約3秒（CTE）
• 100,000件のアドレス： 約90秒（低速版） → 約5秒（CTE）
• 1,000,000件のアドレス： 約30秒（CTE）

これは、10万件の場合で18倍の高速化であり、データベースの負荷を増やすのではなく、データフローの構成を見直してスマートに処理させることだけで達成されました。

これが重要である理由

ここでの教訓は直感に反するかもしれません。データベースにおいては、ラウンドトリップのコスト（データの送受信にかかる時間）が実際の計算処理時間よりも圧倒的に大きくなることがよくあります。5回の往復を1回に統合することで、Podorvanov氏は時間を短縮しただけでなく、サーバー負荷を軽減し、操作のアトミック性（途中の不完全な状態が存在せず、すべてが成功するか何も成功しないかのいずれかになること）を高めました。

これは魔法ではなく、あらゆる場所に適用できる原則です。無駄なやり取りを最小限に抑えれば、システムは高速化します。このテクニックは、メール同期、バッチインポート、複数の読み込み・書き込みを連結するあらゆる操作で機能します。

まとめ

データベース操作が遅く感じられる場合、原因はクエリ自体ではなく、実行している個別クエリの数にあることがよくあります。CTEを使用すると、複数の操作を1つのアトミックなステップに統合でき、ラウンドトリップのオーバーヘッドを大幅に削減してスループットを飛躍的に向上させることができます。メール同期やバッチアップロードのようなワークロードにおいて、この種の最適化は、処理時間が90秒かかるか5秒で済むかの決定的な差となり得ます。

メリット

• データベースのラウンドトリップを激減させ、レイテンシを削減
• データの整合性を維持—すべての変更が1つのトランザクション内でアトミックに行われる
• アプリケーションコードの簡素化（独立した関数呼び出しが減少）
• リニアにスケール；1万件でも100万件のアドレスでも同じクエリで動作
• データベースの強みに逆らうのではなく、有効活用できる

デメリット

• CTE構文はより複雑で、デバッグが難しくなる可能性がある
• 高度なSQLの知識が必要—データベースに不慣れなチームには不向き
• データベースログの粒度が低くなる（どのステップが遅かったかを正確に特定しにくくなる）
• すべてのデータベースが同様に良好なCTEサポートを提供しているわけではない
• オーバーヘッドが問題にならない小規模なデータセットにはオーバースペックになる可能性がある

注意点

この記事は教育目的であり、実世界の事例に基づいています。同様の最適化を実装する際は、プレースホルダー値（@file_id や @now など）をアプリケーションの実際の変数に置き換え、独自のデータ量で十分にテストし、本番環境で導入する前に元のソースと照らし合わせて検証してください。データベースのパフォーマンスはコンテキストに大きく依存します。ここで有効な方法も、特定のスキーマ、インデックス、ハードウェアに合わせて調整が必要になる場合があります。

よくある質問

• PostgreSQLにおけるCTEとは何ですか？また、クエリのパフォーマンスをどのように向上させますか？
• CTEはメール同期以外の操作にも使用できますか？
• 問題が発生した場合、CTEクエリをどのようにデバッグしますか？
• CTEとストアドプロシージャの違いは何ですか？
• すべてのSQLデータベースが同じようにCTEをサポートしていますか？
• 個別クエリを実行するのと比べて、どのような場合にCTEが適切なツールとなりますか？
• CTEはインデックスの使用やクエリ計画にどのように影響しますか？
• CTE操作が途中で失敗した場合はどうなりますか？

タグ

#postgres #database #performance #cte #sql #optimization #batch-processing #postgresql