SaaSアプリのためのPostgreSQLインデックス：B-tree、GIN、GiSTの使い分け

実際のSaaS画面でインデックスが解決する問題

インデックスはPostgreSQLが行を見つける方法を変えます。インデックスがないとデータベースはテーブルの多くを読む（シーケンシャルスキャン）必要があり、大半を捨てることになります。適切なインデックスがあれば、該当する行へ直接飛んで（インデックス検索）、必要なものだけを取得できます。

SaaSではこれを早い段階で実感します。日常の画面はクエリが多く、一回のクリックでリストページ、合計カウント、ダッシュボードのカード、検索ボックスなど複数の読み取りが走ります。テーブルが数千行から数百万行に増えると、以前は瞬時だった同じクエリが遅くなり始めます。

典型的な例は、ステータスと日付でフィルタし、最新順にソートしてページングするOrdersページです。Postgresが過去30日分の有料注文を見つけるためにテーブル全体を走査する必要があると、ページ読み込みごとに余計な作業が発生します。良いインデックスはその部分のデータへ素早くジャンプさせます。

インデックスは無料ではありません。各インデックスは特定のクエリを速くしますが、書き込み（INSERT/UPDATE/DELETE）の遅延とストレージ増（およびキャッシュ圧力）をもたらします。だからこそ、インデックスの種類から始めるのではなく、実際のクエリパターンから出発するべきです。

無駄な作業を防ぐ簡単なルール：それが頻繁に実行される具体的なクエリを指せるときだけインデックスを追加しましょう。チャット駆動のビルダー（Koder.ai）で画面を作っているなら、リストページやダッシュボードの背後で実行されるSQLを収集して、それをインデックスの候補リストにすると便利です。

B-tree vs GIN vs GiST をシンプルに考える

多くのインデックスに関する混乱は、機能（JSON、検索、配列）で考えるのをやめ、クエリの形で考えると解消します。WHERE句は何をしているか、結果をどう並べたいかを考えてください。

B-tree：ソートと比較が得意

通常の比較やソートがあるクエリにはB-treeを使います。等価、範囲、結合に強い汎用的な選手です。

例：tenant_id = ?、status = 'active'、created_at >= ?、users.id = orders.user_id のような結合、ORDER BY created_at DESCで「最新順」を出すケース。

GINとGiST：1行に多数の検索対象があるとき

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を使った空間クエリ。

実用的な選び方：

• 通常のカラムでフィルタやソートをするならB-tree。
• 包含やメンバーシップをチェックするならGINを検討。
• 「どれだけ近いか」「重なっているか」を問うならGiSTが適することが多い。
• クエリが稀であったりテーブルが小さいなら新しいインデックスは不要な場合がある。
• クエリ形状が説明できないなら、追加前に測定（EXPLAIN）する。

インデックスは読み取りを速くしますが、書き込み時間とディスクを消費します。SaaSではこのトレードオフが、events、sessions、activity logsのようなホットなテーブルで特に重要になります。

フィルタ、ソート、ページネーション向けのB-treeパターン

多くのSaaSの一覧画面は同じ形をしています：テナント境界、いくつかのフィルタ、予測可能なソート。B-treeはここでのデフォルトで、維持コストも比較的低めです。

一般的なパターンはWHERE tenant_id = ?に、status = ?、user_id = ?、created_at >= ?のようなフィルタが続きます。合成B-treeインデックスでは等価フィルタ（=でマッチする列）を先に置き、次にソートに使う列を置きます。

多くのアプリでうまく働くルール：

• クエリが毎回テナントでスコープされるならtenant_idを先頭にする。
• 次に=で絞る列（多くの場合statusやuser_id）。
• ORDER BYに使う列を最後に（多くはcreated_atやid）。
• 表示のための列はINCLUDEでカバーして、キーを広げすぎない。
• 深いページではOFFSETよりシークページネーションを推奨。

現実的な例：ステータスでフィルタして新しい順に表示する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のインデックス：GINと式インデックスの使い分け

JSONBは、フィーチャーフラグ、ユーザー属性、テナントごとの設定のような柔軟な追加フィールドに便利です。問題はJSONBの演算子ごとに振る舞いが異なるため、最適なインデックスがクエリの仕方に依存する点です。

特に重要なのは二つの形です：

• 包含：@> を使って「このJSONは指定したキー・バリューを含むか？」
• パス抽出：-> / ->> で特定のフィールドの値を取り出し、それを=で比較する形

GINが合うとき

@>で頻繁にフィルタするなら、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が光る場面

配列は保存が簡単で読みやすく魅力的ですが、主な問いが「この行はある値を含むか？」なら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) のようなジョインテーブルに正規化する方が検証やクエリ、拡張が容易です。

検索インデックス：全文検索とあいまい一致（GINとGiST）

検索ボックスでは、全文検索（トピックに関するレコードを探す）とあいまい一致（タイプミスや部分一致を扱う）という二つのパターンがよく出ます。正しいインデックスを選ぶことが「瞬時」と「10kユーザーでタイムアウト」の差になります。

全文検索：tsvector + GIN

ユーザーが実際の単語を入力し、関連度で結果を返したいなら全文検索を使います。通常は 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%';

ベクトルに何を入れるか：実際に検索するフィールドだけを入れてください。すべて（メモ、内部ログなど）を入れるとインデックスサイズと書き込みコストが増えます。

あいまい一致：トライグラム（GINかGiST）

名前やメールなど短いフレーズに対して部分一致やタイプミス許容が必要な場合はトライグラム類似度を使います。ILIKE '%term%'や類似度演算子にトライグラムが有効です。マッチするかの検索にはGINが速いことが多く、類似度で並べ替える場合はGiSTが適することがあります。

経験則：

• 関連度ベースのテキスト検索にはGIN + tsvectorを使う。
• ILIKEやタイプミス許容の名前検索にはトライグラムを使う。

注意点：

• 先頭ワイルドカードのみのILIKE '%abc'はトライグラムなしではスキャンを強いる。
• 非常に短い検索語（1〜2文字）はトライグラムで使いにくい。
• ストップワードやステミングは全文検索でユーザーを驚かせることがあるので、プロダクト言語に合わせた設定を選ぶ。

検索画面を素早く出す場合、インデックスは機能の一部と考えてください：検索UXとインデックスの選択は一緒に設計する必要があります。

ステップバイステップ：遅いクエリから正しいインデックスへ

まずアプリが実際に実行する正確なクエリから始めてください。遅い画面は通常非常に特定のWHEREとORDER BYを持つ一つのSQL文です。ログやORMのデバッグ出力、既存のクエリキャプチャからコピーしてください。

現実的に使えるワークフロー：

• WHERE、ORDER BY、LIMITを含む完全なSQLを取得する。
• 同じクエリで 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の選択より、「アプリのクエリと一致していないインデックスを作る」ことに起因します。

特に害が大きい誤り：

• 使われないインデックス。クエリがインデックスのサポートする演算子と異なる、または合成インデックスのカラム順が間違っている場合。WHERE句がtenant_idとcreated_atで始まるのにインデックスがcreated_at始まりだとプランナはそれを使わないことがある。
• 低カードinalityな列だけをインデックスにする。statusやis_active、booleanだけのインデックスは多くの行にマッチするためあまり効果がない。tenant_idやcreated_atのような選択性のある列と組み合わせるか、作らない方がいいことが多い。
• 重複・重なり合うインデックスで肥大化し書き込みを遅くする。似たようなインデックスがあるとストレージが倍増し、挿入・更新が遅くなる。
• ページネーションの盲点。OFFSETのみをサポートするようなインデックス設計は罠。キーセットページネーションを使うなら、ソートと最後に見たフィルタに合うインデックスが必要。
• 統計が古かったりテーブルが雑然としていること。autovacuumが間に合っていなかったりANALYZEが最近走っていないと、正しいインデックスがあってもプランナが悪いプランを選ぶことがある。

具体例：Invoices画面がtenant_idとstatusでフィルタし、created_at DESCでソートするなら、statusだけのインデックスはほとんど役に立ちません。tenant_id、status、created_at（フィルタを先、ソートを最後）を含む合成インデックスの方が単一のインデックス変更で大きな効果を出すことが多いです。

すべてのインデックスはコストを伴います。理論上ではなく実際のクエリでそのコストを回収できるかが重要です。

インデックス変更をリリースする前の簡単チェックリスト

インデックス変更は出すのは簡単でも、書き込みコストを増やしたり多忙なテーブルをロックしてしまうと後戻りが面倒です。マージ前には小さなリリースの扱いにしてください。

何を最適化するかをまず決めます。ログやモニタリングから「最も頻繁に実行されるクエリ」と「高いレイテンシのクエリ」の2つの短いランキングを取り、それぞれについて正確な形（フィルタ列、ソート順、結合、使われる演算子）を書き出します。これが推測を防ぎ、正しいインデックスの種類を選ぶ助けになります。

事前チェックリスト：

• 対象クエリがテナントスコープになっているか確認する。
• インデックスを演算子に合わせる：B-treeは等価/範囲/ソート、GINはメンバーシップ（配列、JSONB、全文検索）、GiSTは重なりや距離系。
• よく使うフィルタ＋ソートに合う1つの合成インデックスを好む。単一カラムの複数インデックスをたくさん作らない。
• インデックスは狭く保つ：本当にクエリで使う列だけを含める。
• 作成のロールアウトを計画する：インデックス作成が書き込みをブロックするか、閑散時間にスケジュールする必要があるか。

追加後に効果を検証する：実際のクエリで EXPLAIN (ANALYZE, BUFFERS) を実行して、導入前後を比較し、本番挙動を1日ほど観察します。

• 対象画面の読み取りレイテンシは下がったか？
• 書き込みレイテンシ（挿入/更新）は増えたか？
• 新しいインデックスでCPUやストレージが急増したか？
• インデックスは実際に使われているか？ ただの重りになっていないか？

Koder.aiで作っているなら、1～2個の遅い画面の生成SQLを変更と一緒に残しておくと、インデックスが実際にアプリの実行するSQLに合っているか確認しやすくなります。

例：典型的なSaaSワークフローのインデックス設計と次の一手

管理者の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;

この画面に対する小さく意図的なインデックス群：

• 一覧用のB-tree合成：(tenant_id, status, last_active_at DESC)。
• 検索用のGIN：生成された tsvector 列に対するGINインデックス。
• JSONBは使用状況に応じて：@>を多用するなら GIN (metadata)、主に1つのキーでフィルタするなら ((metadata->> 'plan')) のような式B-tree。

複合的なニーズは普通です。1つのページでフィルタ＋検索＋JSONをやっているなら、すべてを1つの巨大インデックスに詰め込まないでください。B-treeはソート／ページネーション用に保ち、別に最も高コストな部分（多くの場合GIN）を1つ追加するのが現実的です。

次のステップ：1つの遅い画面を選び、その上位2〜3のクエリ形状を書き出して、フィルタ、ソート、検索、JSONの目的ごとにインデックスを見直してください。インデックスが実際のクエリに明確に一致しないなら計画から外しましょう。Koder.aiで素早く反復しているなら、新しい画面を追加するたびにこのレビューを行ってインデックスの増殖を抑えるのが有効です。