大規模データセットにおける過剰なインデックス作成の問題
WHERE句に登場するすべてのカラムにB-treeインデックスを追加し、パフォーマンスが上がるのを祈る――これはよくある落とし穴です。私自身も経験があります。最初はうまくいきます。しかし、テーブルが50GBを超えたり、レコード数が1億件に達したりすると、これら「標準的」なインデックスが牙を剥き始めます。ディスク容量を消費し、バックアップを肥大化させ、インデックスツリーのバランスを保とうとするエンジンの負荷により、すべてのINSERTやUPDATEが低速化します。
PostgreSQLは、多くの開発者が認識しているよりもはるかにきめ細かな制御が可能です。すべての行にインデックスを貼ったり、カラムの生の値をそのまま保存したりする必要は必ずしもありません。部分インデックス（Partial Index）と関数インデックス（Functional Index）を使用することで、メモリに収まりやすく、より軽量で高速なデータベースを構築できます。デフォルトの設定を超えた、一歩先の設計を見ていきましょう。
コアコンセプト：標準的なB-Treeの先を考える
標準的なインデックス作成は、テーブルの1行に対してインデックスのエントリを1つ作成するという単純なルールに従います。1億行あれば、インデックスも1億件のエントリを持ちます。これは多くの場合、無駄です。もしクエリの95%がデータの特定のサブセットのみを対象としているなら、残りの5%にインデックスを貼る意味はあるでしょうか？
部分インデックス（Partial Index）とは？
部分インデックスは「フィルタリングされた地図」のようなものです。WHERE句を使用して、どの行をインデックスに含めるかを正確に定義します。PostgreSQLは条件を満たさない行を無視します。その結果、インデックスファイルは非常に小さくなり、スキャンが速くなるだけでなく、書き込み時のメンテナンスコストも大幅に削減されます。
関数インデックス（Functional Index）とは？
ボトルネックの原因がデータの量ではなく、データの「検索方法」にある場合があります。例えば、WHERE LOWER(email) = '[email protected]'を実行する場合、emailカラムに貼られた標準的なインデックスは役に立ちません。インデックスには元のケース（大文字・小文字）が保存されているため、データベースは低速なシーケンシャルスキャンを実行せざるを得ません。関数インデックス（または式インデックス）は、関数の計算結果をあらかじめ保存しておくため、こうした検索をほぼ瞬時に完了させることができます。
実践：現実世界のシナリオ
私の経験上、これら2つの手法は、読み取り負荷の高いアプリケーションにおけるパフォーマンス・ボトルネックの大部分を解決します。本番環境での具体的な例を見てみましょう。
シナリオ1：「論理削除（ソフトデリート）」パターン
最近の多くのアプリでは、行を物理削除せず、deleted_atタイムスタンプやis_activeフラグを設定します。ユーザーの90%がアクティブで、コンプライアンスのために数百万件の古いレコードを保持している場合、usernameに対する標準的なインデックスは、決して検索されないデータで肥大化してしまいます。
-- 標準的なインデックス：全1,000万ユーザーをインデックス化
CREATE INDEX idx_users_username ON users(username);
-- 部分インデックス：アクティブな100万ユーザーのみをインデックス化
CREATE INDEX idx_users_username_active ON users(username)
WHERE is_active IS TRUE;その効果は絶大です。ある最近のプロジェクトでは、アクティブなレコードに対して部分インデックスに切り替えたところ、インデックスサイズが4.2GBからわずか180MBに縮小しました。インデックスがRAMのバッファキャッシュに完全に収まるサイズになったため、クエリのレイテンシは150msから4ms未満にまで低下しました。
シナリオ2：大文字・小文字を区別しない検索
ユーザーが入力する大文字・小文字は一貫していません。ログイン時のトラブルを防ぐために、クエリでLOWER()を使用することが多いでしょう。関数インデックスがないと、データベースは不必要な処理を大量に行うことになります。
-- このクエリは 'email' カラムの標準インデックスを無視します
SELECT * FROM users WHERE LOWER(email) = '[email protected]';
-- 関数インデックスを使用してボトルネックを解消
CREATE INDEX idx_users_email_lower ON users (LOWER(email));これにより、PostgreSQLは小文字化された文字列をB-treeに直接保存します。クエリプランナがLOWER()関数を検出すると、このインデックスに直接マッピングします。これは、1,000万行をスキャンするのと、ダイレクトなポインタ検索を行うほどの差があり、深刻なボトルネックを解消します。
シナリオ3：効率的なJSONBクエリ
PostgreSQLはドキュメントの保存に優れていますが、JSONBカラム全体に対するGINインデックスは非常に巨大になり、時にはテーブル本体よりも大きくなることがあります。特定のキーのみが必要な場合は、代わりに関数インデックスを使用します。
-- metadata: { "source": "mobile", "priority": "high" }
CREATE INDEX idx_metadata_source ON orders ((metadata->>'source'));
-- このクエリは非常に高速になります
SELECT * FROM orders WHERE metadata->>'source' = 'mobile';ここでの関数インデックスは、フルGINインデックスよりも10倍から20倍小さくなることが多く、IOPSを大幅に節約できます。
シナリオ4：Nullを許容するユニーク制約
標準的なユニークインデックスは、NULLを個別の値として扱います。これにより、複数の行がNULLの電話番号を持つことが可能になりますが、通常はこれで問題ありません。しかし、「アクティブなレコードに対してのみ」ユニーク制約を適用したい場合はどうすればよいでしょうか？部分インデックスを使えば簡単に解決できます。
-- アクティブなユーザーに対してのみ、電話番号の重複を防止する
CREATE UNIQUE INDEX idx_unique_phone_active
ON users(phone_number)
WHERE is_active IS TRUE;本番環境でのベストプラクティス
これらの手法は強力ですが、正確さが求められます。本番クラスターを管理する際に私が留意している点は以下の通りです。
- クエリを正確に一致させる： 部分インデックスを機能させるには、クエリの
WHERE句がインデックスの句のサブセットである必要があります。WHERE status = 'shipped'でインデックスを作成した場合、WHERE status = 'pending'のクエリは低速なシーケンシャルスキャンに戻ります。 - 関数の揮発性（Volatility）を確認する： インデックスを作成できるのは
IMMUTABLE（不変）な関数のみです。これらは、LOWER()やUPPER()のように、同じ入力に対して常に同じ出力を返す関数です。now()はミリ秒ごとに値が変わるため、インデックスを作成できません。 - 使用状況を監視する：
pg_stat_user_indexesを使用して「デッドインデックス」を見つけます。クエリプランナが一度も選択しないような複雑な部分インデックスのために、書き込み時のペナルティを払い続ける意味はありません。 - 組み合わせて使う： これらの機能は組み合わせ可能です。
WHERE is_verified IS TRUEのみを含むLOWER(email)インデックスは、非常に効率的です。
結果の測定
推測せず、測定してください。変更の前後に必ずEXPLAIN ANALYZEを実行しましょう。出力に「Index Scan」が表示され、「execution time（実行時間）」メトリクスが大幅に低下していることを確認してください。
EXPLAIN ANALYZE
SELECT * FROM users
WHERE is_active IS TRUE AND username = 'backend_pro';もしプランナが新しいインデックスを使用しない場合は、ANALYZE users;を実行してテーブル統計を更新してください。これにより、オプティマイザが正しい direction へ導かれることがよくあります。
最後に
PostgreSQLを使用すると、外科手術のような精密さで最適化を行うことができます。部分インデックスはデータの規模に対処し、関数インデックスはクエリの複雑さに対処します。これらを選択的に適用することで、データが増大してもデータベースの高速性を維持し、インフラコストを低く抑えることができます。ただ何にでもインデックスを貼るのではなく、意図を持ってインデックスを設計しましょう。
