PostgreSQLのJSONB検索を高速化するGINインデックスの正しい貼り方と注意点

PostgreSQL Database Performance SQL
結論

包含検索（@>）特化なら jsonb_path_ops を指定してGINを作成し、書き込みスパイク事故防止には gin_pending_list_limit をチューニングします。

-- PostgreSQL公式仕様：jsonb_path_ops インデックスの作成と包含クエリ
CREATE INDEX idx_users_data_path ON users USING gin (data jsonb_path_ops);

-- GINインデックスが適用される検索
SELECT id, data FROM users WHERE data @> '{"status": "active"}';

PostgreSQL公式仕様：2つの演算子クラス比較

PostgreSQL公式ドキュメント（Section 8.14 / 65.4）に基づき、GIN（Generalized Inverted Index）の2種類の演算子クラスのトレードオフを整理します。

項目jsonb_ops (デフォルト)jsonb_path_ops
サポートする演算子@>, @?, @@, ?, `?, ?&`
インデックス構造キー・値を個別項目として全生成パス全体の32bitハッシュ値を1項目生成
インデックスサイズ大小（サイズが約半減し検索速度最速）
空オブジェクトの扱いインデックス化される{"a": {}} 等の値無しハッシュはインデックス非生成

実際に起こるトラブル：fastupdate による突然のCPU100%スパイク事故

Reddit（r/postgresql）や本番運用で頻繁に報告される有名なトラブルとして、GINインデックス付きテーブルへのINSERT/UPDATEが突然数十秒フリーズし、CPU使用率が100%に跳ね上がる現象があります。

原因：Pending List の同期マージ（クリーンアップ）

GINインデックスはデフォルトで FASTUPDATE = ON に設定されています。 書き込み要求を一度 pending list（メモリ上のバッファ、デフォルト上限 4MB）に溜め込み、その上限（gin_pending_list_limit）を超えた瞬間に、クエリをロックしてメインのGINインデックスへ同期マージ（クリーンアップ）を開始するためレイテンシスキャンが発生します。

トラブル回避・チューニング手順

  1. 検索が @> 中心であれば jsonb_path_ops でインデックスサイズ自体を小さく抑える
  2. 書き込み頻度の高いテーブルでは、バッファ上限を拡大して同期マージの頻度を下げる
  3. 突然のスパイクを許容できず一定のレイテンシを保ちたい場合は FASTUPDATE をOFFにする
-- 対策1: pending list 上限を 4MB から 128MB へ拡大してマージ頻度を下げる
ALTER INDEX idx_users_data_path SET (gin_pending_list_limit = '128MB');

-- 対策2: スパイクを排除し、書き込みごとに分散処理させる（FASTUPDATE無効化）
ALTER INDEX idx_users_data_path SET (fastupdate = OFF);

失敗境界

  • 存在演算子 ? を使いたい場合
    data ? 'email'（特定キーが存在するか）という検索を行う場合、jsonb_path_ops ではインデックスがスキップされて Seq Scan に落ちます。この場合は jsonb_ops を指定する必要があります。