⛳
PostgreSQLフルテキスト検索の実装でメール検索を最大71%高速化した話

YoshikiAgatsuma
2025/11/25に公開
 概要メールシステムの検索機能を、従来のLIKE検索からPostgreSQLのフルテキスト検索に移行することで、最大71%のパフォーマンス改善を実現しました。本記事では、26万件の既存データに対してGINインデックスとtsvectorを活用した実装手法と、非ブロッキングマイグレーション戦略について詳しく解説します。

 技術スタックPostgreSQL（フルテキスト検索機能）
Prisma ORM
TypeScript
tsvector / GINインデックス
websearch_to_tsquery()

 背景・課題
 既存の問題点当初、メール検索機能はシンプルなLIKE検索で実装していました：
// 従来のLIKE検索（簡略版）
const emails = await prisma.email.findMany({
  where: {
    OR: [
      { subject: { contains: keyword } },
      { fromName: { contains: keyword } },
      { bodyText: { contains: keyword } }
    ]
  }
});
しかし、データ量が26万件を超えてくると、以下の問題が顕著になりました：

パフォーマンス低下: 複数キーワード検索で1.4秒以上かかるケースも

スケーラビリティの限界: データ量に比例して検索時間が増加

検索の柔軟性不足: 重み付けや関連性スコアリングができない

 具体的な数値Java検索: 1,458ms
エンジニア検索: 968ms
案件検索: 655ms
これらの検索時間を改善する必要がありました。

 実装内容
 1. データベーススキーマの設計まず、emailsテーブルにsearch_vector列を追加しました。重要なのは、件名・送信者名・本文に重み付けを行う点です：
-- search_vectorカラム追加
ALTER TABLE emails ADD COLUMN search_vector tsvector;

-- 重み付けトリガー関数の作成
CREATE OR REPLACE FUNCTION update_search_vector()
RETURNS trigger AS $$
BEGIN
  NEW.search_vector :=
    setweight(to_tsvector('simple', COALESCE(NEW.subject, '')), 'A') ||
    setweight(to_tsvector('simple', COALESCE(NEW."fromName", '')), 'B') ||
    setweight(to_tsvector('simple', COALESCE(NEW."fromAddress", '')), 'C') ||
    setweight(to_tsvector('simple',
      LEFT(COALESCE(NEW."bodyText", ''), 10000)), 'D');
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- トリガーの作成
CREATE TRIGGER emails_search_vector_update
  BEFORE INSERT OR UPDATE ON emails
  FOR EACH ROW
  EXECUTE FUNCTION update_search_vector();
重み付けの意図:
A（件名）: 最も重要な情報源
B（送信者名）: 次に重要
C（メールアドレス）: 補助的な情報
D（本文）: 全体の文脈

 2. GINインデックスの作成テーブルロックを避けるため、CONCURRENTLYオプションを使用しました：
// GINインデックス作成スクリプト（簡略版）
async function createIndex() {
  await prisma.$executeRawUnsafe(`
    CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_emails_search_vector
    ON emails USING GIN(search_vector)
  `);
}
CONCURRENTLY使用の理由:
既存のトラフィックを妨げない
本番環境でも安全に実行可能
インデックス作成中も読み書き可能

 3. 既存データのバックフィル26万件の既存データに対して、エラーハンドリングとリトライロジックを備えたバッチ処理でsearch_vectorを更新しました：
const BATCH_SIZE = 1000;
const MAX_RETRIES = 3;
const RETRY_DELAY = 5000; // 5秒

async function backfillSearchVector() {
  const totalCount = await prisma.email.count();
  console.log(`Total emails to process: ${totalCount}`);

  let successCount = 0;
  let failureCount = 0;

  for (let offset = 0; offset < totalCount; offset += BATCH_SIZE) {
    let retryCount = 0;
    let success = false;

    while (retryCount < MAX_RETRIES && !success) {
      try {
        const startTime = Date.now();

        // CTE（Common Table Expression）を使用した効率的な更新
        await prisma.$executeRaw`
          WITH batch AS (
            SELECT id, subject, "fromName", "fromAddress", "bodyText"
            FROM emails
            ORDER BY id
            LIMIT ${BATCH_SIZE} OFFSET ${offset}
          )
          UPDATE emails
          SET search_vector =
            setweight(to_tsvector('simple', COALESCE(batch.subject, '')), 'A') ||
            setweight(to_tsvector('simple', COALESCE(batch."fromName", '')), 'B') ||
            setweight(to_tsvector('simple', COALESCE(batch."fromAddress", '')), 'C') ||
            setweight(to_tsvector('simple',
              LEFT(COALESCE(batch."bodyText", ''), 10000)), 'D')
          FROM batch
          WHERE emails.id = batch.id
        `;

        const elapsed = Date.now() - startTime;
        successCount += BATCH_SIZE;
        console.log(`✓ Processed ${offset + BATCH_SIZE}/${totalCount} (${elapsed}ms)`);
        success = true;

      } catch (error) {
        retryCount++;
        console.error(`✗ Batch failed (attempt ${retryCount}/${MAX_RETRIES}):`, error);

        if (retryCount < MAX_RETRIES) {
          console.log(`Retrying in ${RETRY_DELAY}ms...`);
          await new Promise(resolve => setTimeout(resolve, RETRY_DELAY));
        } else {
          failureCount += BATCH_SIZE;
          console.error(`✗ Batch permanently failed after ${MAX_RETRIES} attempts. Skipping...`);
        }
      }
    }
  }

  console.log(`\nBackfill complete:`);
  console.log(`- Success: ${successCount} records`);
  console.log(`- Failure: ${failureCount} records`);

  if (failureCount > 0) {
    throw new Error(`Backfill completed with ${failureCount} failures`);
  }
}
処理結果:
26万件のデータを約53分で完了
1バッチあたり平均12秒
エラー発生時は最大3回リトライ
失敗バッチはログに記録してスキップ

 4. 検索クエリの実装websearch_to_tsquery()を使用し、エラーハンドリングを備えた安全な検索機能を実装：
export async function findManyWithFullTextSearch(params: {
  keywords: string[];
  accountId: string;
  category?: string;
  skip?: number;
  take?: number;
}): Promise<Email[]> {
  const { keywords, accountId, category, skip = 0, take = 50 } = params;

  // 入力検証: キーワード数の制限（DoS対策）
  if (keywords.length > 10) {
    throw new Error('Too many keywords. Maximum 10 keywords allowed.');
  }

  // 入力検証: 各キーワードの長さ制限
  const invalidKeywords = keywords.filter(k => k.length > 100);
  if (invalidKeywords.length > 0) {
    throw new Error('Keyword too long. Maximum 100 characters per keyword.');
  }

  // キーワードをtsquery形式に変換（AND結合）
  // Prisma.sqlのパラメータ化により、SQLインジェクション対策は自動的に行われる
  const searchQuery = keywords.join(' & ');

  try {
    const results = await prisma.$queryRaw<Email[]>`
      SELECT e.*
      FROM emails e
      WHERE e.search_vector @@ websearch_to_tsquery('simple', ${searchQuery})
        AND e."accountId" = ${accountId}
        ${category ? Prisma.sql`AND EXISTS (
          SELECT 1 FROM "EmailClassificationResult" ecr
          WHERE ecr."emailId" = e.id AND ecr.category = ${category}
          LIMIT 1
        )` : Prisma.empty}
      ORDER BY ts_rank(e.search_vector, websearch_to_tsquery('simple', ${searchQuery})) DESC
      LIMIT ${take} OFFSET ${skip}
    `;

    return results;

  } catch (error) {
    // PostgreSQL構文エラー（エラーコード42601）の場合は空配列を返す
    if (error instanceof Error && 'code' in error && error.code === '42601') {
      console.warn('Invalid tsquery syntax, returning empty results:', searchQuery);
      return [];
    }

    // その他のエラーはログを出力して再スロー
    console.error('Full-text search error:', error);
    throw error;
  }
}
重要なポイント:

セキュリティ対策:

Prisma.sqlのパラメータ化により、SQLインジェクションを防止
キーワード数と長さの制限でDoS攻撃を防止


エラーハンドリング:
tsquery構文エラーは空配列を返してアプリケーション継続
その他のエラーは適切にログ出力して再スロー


クエリ最適化:

@@演算子: tsvectorとtsqueryのマッチング

websearch_to_tsquery(): 自然な検索構文サポート（"Java AND Python"など）

ts_rank(): 関連性スコアでソート
WHERE句の順序最適化: search_vector → accountId → category


 パフォーマンス改善結果
 検索時間の比較

検索キーワード
改善前
改善後
改善率


Java
1,458ms
596ms
59%改善

エンジニア
968ms
279ms
71%改善

案件
655ms
203ms
69%改善


 なぜこれほど高速化できたのかインデックススキャンの効率化
LIKE検索: O(N) - 全行スキャン
GINインデックス: O(log N) - インデックススキャンのみ
クエリプランナーの最適化
PostgreSQLのクエリプランナーがGINインデックスを効率的に使用
WHERE句の順序を最適化（選択性の高い条件を優先）
重み付けによる関連性スコアリング
件名マッチを優先的に返すことで、ユーザー体験向上
ts_rank()による自然な順序付け

 学んだこと
 意外だった落とし穴文字数制限の重要性
tsvectorは1MBまでの制限がある
bodyTextを10,000文字に制限することで安全性を確保
GINインデックスのディスク使用量
26万件のデータで約1.2GBのインデックスサイズ
元のテーブルサイズの約30-40%に相当
ストレージコストとパフォーマンスのトレードオフを考慮
'simple'設定の選択と日本語検索の課題
日英混在テキストでは、言語固有の設定（'english', 'japanese'）よりも'simple'が適切
ステミング処理が不要なケースも多い

日本語形態素解析との比較:
MeCab + pg_bigm: 精度は高いが、セットアップが複雑
'simple'設定: セットアップ簡単だが、単語境界の認識が不完全
本プロジェクトでは、開発速度を優先して'simple'を選択

CONCURRENTLYの落とし穴
トランザクション内では使用できない
インデックス作成に予想以上の時間（26万件で約45分）
エラー時の再実行に注意が必要（部分的に作成されたインデックスが残る）

 今後使えそうな知見段階的マイグレーション戦略
ステップ1: スキーマ変更（search_vectorカラム追加）
ステップ2: トリガー設定（新規データは自動更新）
ステップ3: GINインデックス作成（CONCURRENTLY）
ステップ4: 既存データのバックフィル
ステップ5: アプリケーションコード切り替え
パフォーマンスチューニングのベストプラクティス
EXPLAIN ANALYZEで実行計画を確認
WHERE句の順序を選択性の高い順に配置
EXISTS句にLIMIT 1を追加して早期終了
バッチ処理の最適化
CTEを使用した効率的な一括更新
進捗ログによる透明性確保
エラーハンドリングとリトライロジック

 もっと良い書き方の発見改善前（複数OR条件）:
where: {
  OR: [
    { subject: { contains: 'Java' } },
    { fromName: { contains: 'Java' } },
    { bodyText: { contains: 'Java' } }
  ]
}
改善後（フルテキスト検索）:
where: {
  search_vector: {
    search: 'Java',
    mode: 'websearch'
  }
}
シンプルで高速、かつ柔軟性の高い実装になりました。

 運用上の注意点
 モニタリング設定// インデックス使用状況の監視
const indexStats = await prisma.$queryRaw`
  SELECT
    schemaname,
    tablename,
    indexname,
    idx_scan as index_scans,
    idx_tup_read as tuples_read,
    idx_tup_fetch as tuples_fetched,
    pg_size_pretty(pg_relation_size(indexrelid)) as index_size
  FROM pg_stat_user_indexes
  WHERE indexname = 'idx_emails_search_vector'
`;

 アラート閾値検索レスポンスタイム: 500ms以上で警告、1秒以上でアラート
インデックススキャン数: 想定トラフィックの80%以下で警告
GINインデックスサイズ: ディスク容量の30%超過で警告

 ログ出力設計// 構造化ログの例
logger.info('full_text_search', {
  keywords: keywords.join(','),
  resultCount: results.length,
  duration: Date.now() - startTime,
  accountId,
  category
});

 テスト戦略
 ユニットテストdescribe('findManyWithFullTextSearch', () => {
  it('should return emails matching single keyword', async () => {
    const results = await findManyWithFullTextSearch({
      keywords: ['Java'],
      accountId: 'acc_123'
    });

    expect(results).toHaveLength(10);
    expect(results[0].subject).toContain('Java');
  });

  it('should handle invalid tsquery syntax gracefully', async () => {
    const results = await findManyWithFullTextSearch({
      keywords: ['test & | invalid'],
      accountId: 'acc_123'
    });

    expect(results).toEqual([]); // エラーではなく空配列を返す
  });

  it('should reject too many keywords', async () => {
    await expect(
      findManyWithFullTextSearch({
        keywords: new Array(11).fill('test'),
        accountId: 'acc_123'
      })
    ).rejects.toThrow('Too many keywords');
  });
});

 パフォーマンステストdescribe('Full-text search performance', () => {
  it('should complete search within 500ms', async () => {
    const startTime = Date.now();

    await findManyWithFullTextSearch({
      keywords: ['エンジニア'],
      accountId: 'acc_123'
    });

    const duration = Date.now() - startTime;
    expect(duration).toBeLessThan(500);
  });
});

 トラブルシューティング
 よくある問題と解決策検索結果が0件になる
原因: search_vectorがnullの可能性
確認: SELECT COUNT(*) FROM emails WHERE search_vector IS NULL
解決: バックフィルスクリプトを再実行
検索が遅い
原因: GINインデックスが使用されていない
確認: EXPLAIN ANALYZEで実行計画を確認
解決: VACUUM ANALYZE emailsでインデックス統計を更新
tsvectorサイズ制限エラー
原因: bodyTextが10,000文字を超えている
確認: SELECT id, LENGTH("bodyText") FROM emails WHERE LENGTH("bodyText") > 10000
解決: トリガー関数の文字数制限を確認・適用

 デバッグ方法-- 特定のメールのsearch_vectorを確認
SELECT
  id,
  subject,
  search_vector,
  ts_rank(search_vector, websearch_to_tsquery('simple', 'Java')) as rank
FROM emails
WHERE id = 'email_123';

-- インデックスが使用されているか確認
EXPLAIN ANALYZE
SELECT * FROM emails
WHERE search_vector @@ websearch_to_tsquery('simple', 'Java')
LIMIT 10;

 終わりにPostgreSQLのフルテキスト検索機能は、適切に実装すれば大規模データでも高速な検索を実現できる強力なツールです。今回の実装では、以下のポイントが成功の鍵でした：

非ブロッキングマイグレーション: 本番環境への影響を最小化

重み付け検索: ユーザー体験を重視した関連性スコアリング

段階的な移行: リスクを分散した実装戦略
特に、CONCURRENTLY オプションとバッチ処理による段階的な移行は、サービス停止なしで大規模データのマイグレーションを実現する上で非常に効果的でした。
読者の皆さんも、データ量が増えてきたら、早めにフルテキスト検索の導入を検討することをお勧めします。LIKE検索の限界を感じたら、それが移行のタイミングです。
この記事で紹介したコードは、実際のプロダクションコードを簡略化したものです。エラーハンドリングやセキュリティチェックなど、実際の実装では追加の考慮事項があります。
関連技術: PostgreSQL, Prisma ORM, TypeScript, フルテキスト検索, GINインデックス, tsvector, パフォーマンス最適化, データベース設計
筆者: 91works開発チーム
検索キーワード	改善前	改善後	改善率
Java	1,458ms	596ms	59%改善
エンジニア	968ms	279ms	71%改善
案件	655ms	203ms	69%改善
91works Tech BlogPublication
91worksのTech Blogです。
概要

技術スタック

背景・課題

既存の問題点

具体的な数値

実装内容

1. データベーススキーマの設計

2. GINインデックスの作成

3. 既存データのバックフィル

4. 検索クエリの実装

パフォーマンス改善結果

検索時間の比較

なぜこれほど高速化できたのか

学んだこと

意外だった落とし穴

今後使えそうな知見

もっと良い書き方の発見

運用上の注意点

モニタリング設定

アラート閾値

ログ出力設計

テスト戦略

ユニットテスト

パフォーマンステスト

トラブルシューティング

よくある問題と解決策

デバッグ方法

終わりに

Discussion
