SaaS向けPostgreSQLのインデックス入門:実際のクエリ形状(フィルタ、検索、JSONB、配列)に基づいてB-tree、GIN、GiSTの使い分け方を解説します。

インデックスはPostgreSQLが行を見つける方法を変えます。インデックスがないとデータベースはテーブルの多くを読む(シーケンシャルスキャン)必要があり、大半を捨てることになります。適切なインデックスがあれば、該当する行へ直接飛んで(インデックス検索)、必要なものだけを取得できます。
SaaSではこれを早い段階で実感します。日常の画面はクエリが多く、一回のクリックでリストページ、合計カウント、ダッシュボードのカード、検索ボックスなど複数の読み取りが走ります。テーブルが数千行から数百万行に増えると、以前は瞬時だった同じクエリが遅くなり始めます。
典型的な例は、ステータスと日付でフィルタし、最新順にソートしてページングするOrdersページです。Postgresが過去30日分の有料注文を見つけるためにテーブル全体を走査する必要があると、ページ読み込みごとに余計な作業が発生します。良いインデックスはその部分のデータへ素早くジャンプさせます。
インデックスは無料ではありません。各インデックスは特定のクエリを速くしますが、書き込み(INSERT/UPDATE/DELETE)の遅延とストレージ増(およびキャッシュ圧力)をもたらします。だからこそ、インデックスの種類から始めるのではなく、実際のクエリパターンから出発するべきです。
無駄な作業を防ぐ簡単なルール:それが頻繁に実行される具体的なクエリを指せるときだけインデックスを追加しましょう。チャット駆動のビルダー(Koder.ai)で画面を作っているなら、リストページやダッシュボードの背後で実行されるSQLを収集して、それをインデックスの候補リストにすると便利です。
多くのインデックスに関する混乱は、機能(JSON、検索、配列)で考えるのをやめ、クエリの形で考えると解消します。WHERE句は何をしているか、結果をどう並べたいかを考えてください。
通常の比較やソートがあるクエリにはB-treeを使います。等価、範囲、結合に強い汎用的な選手です。
例:tenant_id = ?、status = 'active'、created_at >= ?、users.id = orders.user_id のような結合、ORDER BY created_at DESCで「最新順」を出すケース。
GIN(Generalized Inverted Index)は、1つのカラムが多数のメンバーを持ち、「それはXを含むか?」と問う場合に向いています。JSONBのキー、配列要素、全文検索のベクトルに典型的です。
例:JSONBでのmetadata @> {'plan':'pro'}、tags @> ARRAY['urgent']、to_tsvector(body) @@ plainto_tsquery('reset password')。
GiST(Generalized Search Tree)は、値が範囲や形のように振る舞う場合、距離や重なりについて問うときに合います。レンジ型、ジオメトリ、近接検索などでよく使われます。
例:レンジカラムによる時間ウィンドウの重なり、トライグラム演算子を使った類似検索、PostGISを使った空間クエリ。
実用的な選び方:
インデックスは読み取りを速くしますが、書き込み時間とディスクを消費します。SaaSではこのトレードオフが、events、sessions、activity logsのようなホットなテーブルで特に重要になります。
多くのSaaSの一覧画面は同じ形をしています:テナント境界、いくつかのフィルタ、予測可能なソート。B-treeはここでのデフォルトで、維持コストも比較的低めです。
一般的なパターンはWHERE tenant_id = ?に、status = ?、user_id = ?、created_at >= ?のようなフィルタが続きます。合成B-treeインデックスでは等価フィルタ(=でマッチする列)を先に置き、次にソートに使う列を置きます。
多くのアプリでうまく働くルール:
tenant_idを先頭にする。=で絞る列(多くの場合statusやuser_id)。ORDER BYに使う列を最後に(多くはcreated_atやid)。INCLUDEでカバーして、キーを広げすぎない。現実的な例:ステータスでフィルタして新しい順に表示するTicketsページ。
-- Query
SELECT id, status, created_at, title
FROM tickets
WHERE tenant_id = $1
AND status = $2
ORDER BY created_at DESC
LIMIT 50;
-- Index
CREATE INDEX tickets_tenant_status_created_at_idx
ON tickets (tenant_id, status, created_at DESC)
INCLUDE (title);
このインデックスはフィルタとソートの両方をサポートするため、Postgresは大きな結果セットをソートせずに済みます。INCLUDE (title)は一覧ページが触るテーブルページを減らすのに役立ちます。
時間範囲のケースでも同じ考え方が使えます:
SELECT id, created_at
FROM events
WHERE tenant_id = $1
AND created_at >= $2
AND created_at < $3
ORDER BY created_at DESC
LIMIT 100;
CREATE INDEX events_tenant_created_at_idx
ON events (tenant_id, created_at DESC);
ページネーションは多くのSaaSアプリが遅くなるポイントです。OFFSET 50000のようなオフセットページネーションではデータベースが多くの行を読み飛ばす必要があります。シーク(キーセット)ページネーションは、最後に見たソートキーを使うことでスケールします。
SELECT id, created_at
FROM tickets
WHERE tenant_id = $1
AND created_at < $2
ORDER BY created_at DESC
LIMIT 50;
適切なB-treeインデックスがあれば、テーブルが大きくなっても高速を保てます。
ほとんどのSaaSはマルチテナントです。すべてのクエリがテナント内に留まる必要があります。インデックスにtenant_idが含まれていないと、Postgresは必要以上に多くのインデックスエントリを走査することがあり得ます。テナント対応のインデックスはインデックス内で各テナントのデータをまとまりにし、一般的な画面を速く予測可能にします。
簡単なルール:クエリが常にテナントでフィルタされるなら、インデックスの最初にtenant_idを置き、その後に最もよくフィルタまたはソートする列を追加します。
効果の高い、地味だけど有用なインデックスの例:
(tenant_id, created_at) — 最近のアイテムリストとカーソルページネーション用(tenant_id, status) — ステータスフィルタ(Open, Paid, Failed)用(tenant_id, user_id) — 「このユーザーの所有するアイテム」画面用(tenant_id, updated_at) — 管理者向け「最近変更」ビュー用(tenant_id, external_id) — Webhookやインポートからのルックアップ用過剰インデックスは、似たような画面ごとに新しいインデックスを追加してしまうと起きます。新しいインデックスを作る前に、既存の合成インデックスが左から何カラムをカバーしているかを確認してください。例えば (tenant_id, created_at) があれば通常 (tenant_id, created_at, id) は、idで追加のフィルタをしない限り不要です。
部分インデックスは、ほとんどの行が対象外の場合にサイズと書き込みコストを削減できます。ソフトデリートや「アクティブのみ」のデータに有効で、例えば deleted_at IS NULL や status = 'active' の場合に有効です。
インデックスが一つ増えるごとに書き込みコストが増えます。挿入は各インデックスを更新する必要があり、更新は一つの列を変えても複数のインデックスに影響することがあります。特にイベントを大量に取り込むアプリ(Koder.aiで素早く作ったアプリを含む)では、インデックスは日々のユーザーが使う限られたクエリ形状に絞ってください。
JSONBは、フィーチャーフラグ、ユーザー属性、テナントごとの設定のような柔軟な追加フィールドに便利です。問題はJSONBの演算子ごとに振る舞いが異なるため、最適なインデックスがクエリの仕方に依存する点です。
特に重要なのは二つの形です:
@> を使って「このJSONは指定したキー・バリューを含むか?」-> / ->> で特定のフィールドの値を取り出し、それを=で比較する形@>で頻繁にフィルタするなら、JSONBカラムに対するGINインデックスは通常有効です。
-- Query shape: containment
SELECT id
FROM accounts
WHERE tenant_id = $1
AND metadata @> '{"region":"eu","plan":"pro"}';
-- Index
CREATE INDEX accounts_metadata_gin
ON accounts
USING GIN (metadata);
JSON構造が予測できてトップレベルのキーでの@>が多いなら、jsonb_path_opsを使うとサイズが小さく高速なことがありますが、サポートする演算子が少ない点に注意してください。
UIが特定のフィールド(例:plan)で繰り返しフィルタをしているなら、そのフィールドを抽出してインデックス化した方が広いGINより速く安価なことが多いです。
SELECT id
FROM accounts
WHERE tenant_id = $1
AND metadata->> 'plan' = 'pro';
CREATE INDEX accounts_plan_expr
ON accounts ((metadata->> 'plan'));
実用的なルール:柔軟で滅多にフィルタしない属性はJSONBに残し、安定してよく使うフィールド(plan, status, created_at)は実際のカラムに昇格させるのが良いです。生成アプリを素早く回しているなら、どのフィルタが毎回出てくるかを見てスキーマを少し修正するだけで大きく改善します。
例:{"tags":["beta","finance"],"region":"us"} のようなJSONBを保存している場合、属性の束でフィルタする(@>)ならGINを使い、一覧表示で多く使われるキー(plan, region)には式インデックスを追加します。
配列は保存が簡単で読みやすく魅力的ですが、主な問いが「この行はある値を含むか?」ならGINが役立ちます。users.roles text[] や projects.labels text[] のようなカラムが該当します。
GINは配列を個々の要素に分解して、要素を含む行への高速なルックアップを作ります。
配列で恩恵があるクエリ形状:
@>(配列が指定集合を含む)&&(いずれかの要素を共有する)= ANY(...)、ただし@>の方が予測しやすい典型例:ロールでユーザーをフィルタする場合。
-- Find users who have the "admin" role
SELECT id, email
FROM users
WHERE roles @> ARRAY['admin'];
CREATE INDEX users_roles_gin ON users USING GIN (roles);
プロジェクトをラベルセットでフィルタ(両方のラベルを含む必要がある)する例:
SELECT id, name
FROM projects
WHERE labels @> ARRAY['billing', 'urgent'];
CREATE INDEX projects_labels_gin ON projects USING GIN (labels);
人々が驚く点:配列を文字列にして array_to_string(labels, ',') のようにして LIKE を使うと、GINは効かなくなります。またラベル内での「先頭一致」やあいまい検索が必要なら、配列のメンバーシップではなくテキスト検索の領域です。
配列はまた、項目ごとのメタデータ(誰がいつ追加したか)やラベルごとの分析が必要になったり頻繁に更新されると維持が難しくなります。その時点では project_labels(project_id, label) のようなジョインテーブルに正規化する方が検証やクエリ、拡張が容易です。
検索ボックスでは、全文検索(トピックに関するレコードを探す)とあいまい一致(タイプミスや部分一致を扱う)という二つのパターンがよく出ます。正しいインデックスを選ぶことが「瞬時」と「10kユーザーでタイムアウト」の差になります。
ユーザーが実際の単語を入力し、関連度で結果を返したいなら全文検索を使います。通常は tsvector を保存(生成列にすることが多い)し、GINでインデックス化します。@@ と tsquery で検索します。
-- Tickets: full-text search on subject + body
ALTER TABLE tickets
ADD COLUMN search_vec tsvector
GENERATED ALWAYS AS (
to_tsvector('simple', coalesce(subject,'') || ' ' || coalesce(body,''))
) STORED;
CREATE INDEX tickets_search_vec_gin
ON tickets USING GIN (search_vec);
-- Query
SELECT id, subject
FROM tickets
WHERE search_vec @@ plainto_tsquery('simple', 'invoice failed');
-- Customers: fuzzy name search using trigrams
CREATE INDEX customers_name_trgm
ON customers USING GIN (name gin_trgm_ops);
SELECT id, name
FROM customers
WHERE name ILIKE '%jon smth%';
ベクトルに何を入れるか:実際に検索するフィールドだけを入れてください。すべて(メモ、内部ログなど)を入れるとインデックスサイズと書き込みコストが増えます。
名前やメールなど短いフレーズに対して部分一致やタイプミス許容が必要な場合はトライグラム類似度を使います。ILIKE '%term%'や類似度演算子にトライグラムが有効です。マッチするかの検索にはGINが速いことが多く、類似度で並べ替える場合はGiSTが適することがあります。
経験則:
tsvectorを使う。ILIKEやタイプミス許容の名前検索にはトライグラムを使う。注意点:
ILIKE '%abc'はトライグラムなしではスキャンを強いる。検索画面を素早く出す場合、インデックスは機能の一部と考えてください:検索UXとインデックスの選択は一緒に設計する必要があります。
まずアプリが実際に実行する正確なクエリから始めてください。遅い画面は通常非常に特定のWHEREとORDER BYを持つ一つのSQL文です。ログやORMのデバッグ出力、既存のクエリキャプチャからコピーしてください。
現実的に使えるワークフロー:
EXPLAIN (ANALYZE, BUFFERS) を実行する。=, >=, LIKE, @>, @@)に注目する。列名だけでなく演算子にも注目する。EXPLAIN (ANALYZE, BUFFERS) を実行する。具体例:Customersページがテナントとステータスでフィルタし、新着順にページングする場合:
SELECT id, created_at, email
FROM customers
WHERE tenant_id = $1 AND status = $2
ORDER BY created_at DESC
LIMIT 50;
EXPLAINでシーケンシャルスキャンとソートが見えるなら、フィルタとソートに合うB-treeインデックスが効くことが多いです:
CREATE INDEX ON customers (tenant_id, status, created_at DESC);
JSONBのmetadata @> '{"plan":"pro"}'のようなフィルタがボトルネックならGINが指し示すサインです。to_tsvector(...) @@ plainto_tsquery(...)のような全文検索もGINに指向します。重なりやオーバーラップ系ならGiSTが合うことが多いです。
インデックス追加後はトレードオフを測ってください。インデックスサイズ、挿入・更新時間、それがトップの遅いクエリのうちの数個に効いているか、あるいは単一のエッジケースだけかを確認します。Koder.aiのような速いプロジェクトでは、この再チェックで使われないインデックスの増殖を防げます。
多くのインデックス問題はB-tree vs GIN vs GiSTの選択より、「アプリのクエリと一致していないインデックスを作る」ことに起因します。
特に害が大きい誤り:
tenant_idとcreated_atで始まるのにインデックスがcreated_at始まりだとプランナはそれを使わないことがある。statusやis_active、booleanだけのインデックスは多くの行にマッチするためあまり効果がない。tenant_idやcreated_atのような選択性のある列と組み合わせるか、作らない方がいいことが多い。ANALYZEが最近走っていないと、正しいインデックスがあってもプランナが悪いプランを選ぶことがある。具体例:Invoices画面がtenant_idとstatusでフィルタし、created_at DESCでソートするなら、statusだけのインデックスはほとんど役に立ちません。tenant_id、status、created_at(フィルタを先、ソートを最後)を含む合成インデックスの方が単一のインデックス変更で大きな効果を出すことが多いです。
すべてのインデックスはコストを伴います。理論上ではなく実際のクエリでそのコストを回収できるかが重要です。
インデックス変更は出すのは簡単でも、書き込みコストを増やしたり多忙なテーブルをロックしてしまうと後戻りが面倒です。マージ前には小さなリリースの扱いにしてください。
何を最適化するかをまず決めます。ログやモニタリングから「最も頻繁に実行されるクエリ」と「高いレイテンシのクエリ」の2つの短いランキングを取り、それぞれについて正確な形(フィルタ列、ソート順、結合、使われる演算子)を書き出します。これが推測を防ぎ、正しいインデックスの種類を選ぶ助けになります。
事前チェックリスト:
追加後に効果を検証する:実際のクエリで EXPLAIN (ANALYZE, BUFFERS) を実行して、導入前後を比較し、本番挙動を1日ほど観察します。
Koder.aiで作っているなら、1~2個の遅い画面の生成SQLを変更と一緒に残しておくと、インデックスが実際にアプリの実行するSQLに合っているか確認しやすくなります。
管理者のUsers一覧を想像してください:テナントスコープ、いくつかのフィルタ、last_activeでソート、検索ボックス。ここでインデックスは理論から現実の時間削減に変わります。
通常見かける三つのクエリ形状:
-- 1) List page with tenant + status filter + sort
SELECT id, email, last_active_at
FROM users
WHERE tenant_id = $1 AND status = $2
ORDER BY last_active_at DESC
LIMIT 50;
-- 2) Search box (full-text)
SELECT id, email
FROM users
WHERE tenant_id = $1
AND to_tsvector('simple', coalesce(name,'') || ' ' || coalesce(email,'')) @@ plainto_tsquery($2)
ORDER BY last_active_at DESC
LIMIT 50;
-- 3) Filter on JSON metadata (plan, flags)
SELECT id
FROM users
WHERE tenant_id = $1
AND metadata @> '{"plan":"pro"}'::jsonb;
この画面に対する小さく意図的なインデックス群:
(tenant_id, status, last_active_at DESC)。tsvector 列に対するGINインデックス。@>を多用するなら GIN (metadata)、主に1つのキーでフィルタするなら ((metadata->> 'plan')) のような式B-tree。複合的なニーズは普通です。1つのページでフィルタ+検索+JSONをやっているなら、すべてを1つの巨大インデックスに詰め込まないでください。B-treeはソート/ページネーション用に保ち、別に最も高コストな部分(多くの場合GIN)を1つ追加するのが現実的です。
次のステップ:1つの遅い画面を選び、その上位2〜3のクエリ形状を書き出して、フィルタ、ソート、検索、JSONの目的ごとにインデックスを見直してください。インデックスが実際のクエリに明確に一致しないなら計画から外しましょう。Koder.aiで素早く反復しているなら、新しい画面を追加するたびにこのレビューを行ってインデックスの増殖を抑えるのが有効です。
インデックスはPostgreSQLが一致する行を見つける方法を変え、テーブル全体を読み飛ばす必要をなくします。リストやダッシュボード、検索のような一般的なSaaS画面では、適切なインデックスによってシーケンシャルスキャンを高速な検索に変え、テーブルが大きくなっても応答を保てます。
まずはB-treeから始めるのが現実的です。=フィルタ、範囲フィルタ、結合、ORDER BYに強いので一般的なアプリクエリで役立ちます。JSONBや配列、全文検索のように「包含」を問う場合はGINを検討し、重なりや距離に関する検索(オーバーラップや近傍)にはGiSTが適することが多いです。
合成B-treeインデックスのカラム順の簡単なルールは、=で絞る列を先に、最後にソート列を置くことです。これによりプランナがインデックスを効率的に辿り、余分なソートを避けられます。
もしすべてのクエリがtenant_idでスコープされるなら、tenant_idを先頭に置くとインデックス内で各テナントの行がまとまり、日常の一覧でタッチするデータ量を減らせます。よってテナント対応のクエリではtenant_idをインデックスに含めるのが一般的に有効です。
INCLUDEは一覧ページで追加の列を取得しつつ、インデックスキー自体を広げたくないときに使います。フィルタとソートに使う少数の列はキーにして、表示用の余分なフィールドをINCLUDEに入れるとインデックスオンリースキャンが増えます。
テーブル全体をインデックス化するより、関心のある行だけを対象にする部分インデックスの方が小さく保てて書き込みコストも下がります。例としてdeleted_at IS NULLやstatus = 'active'のような条件で限定するケースです。
JSONBを@>で頻繁に問い合わせるなら、JSONBカラムに対するGINインデックスが有効です。一方で特定のキー(例:plan)だけをよくフィルタするなら、(metadata ->> 'plan')のような式インデックス(B-tree)が小さくて速くなることが多いです。
配列に対しての主要な質問が「この配列はXを含むか?」ならGINが適しています。@>や&&のような演算子でのメンバーシップやオーバーラップ検索に強いです。ただし、項目ごとにメタデータが必要だったり更新が頻繁だったり、ラベルごとの分析が必要であればジョインテーブルに正規化する方が扱いやすくなります。
全文検索ではtsvector(しばしば生成列)を保持し、GINでインデックス化して@@で検索するのが典型です。ILIKE '%name%'やタイポ許容の部分一致にはトライグラム(gin_trgm_ops)が有効です。検索ボックスの要件に応じて両者を使い分けてください。
遅いクエリからインデックスを決める手順は繰り返し可能です。アプリが実際に発行するSQLを取り、EXPLAIN (ANALYZE, BUFFERS)でどこが重いかを見る。使われている演算子(=, >=, LIKE, @>, @@など)に合わせて最小限のインデックスを追加し、再度EXPLAINして効果を確認します。