backend-db-performance

データベース最適化スキル

自由度：混合。 修正するクエリ／インデックス／N+1 は任意に選択できます［自由度：高］。 既存インデックスの確認と EXPLAIN ANALYZE は、手順どおりに実行してください［自由度：低］。

考え方

1. 観察 — EXPLAIN ANALYZE／pg_stat_statements／既存の pg_indexes
2. 解釈 — シーケンシャルスキャン、N+1、過剰なデータ取得、ページネーションの欠落を判別します
3. 分類 — インデックス追加／先行読み込み／取得項目の絞り込み／ページネーション／変更なし
4. 深刻度 — 書き込みパスのタイムアウトは、200ms の一覧ページより優先します

実例

観察： /feed の p95 は 2.4s。Prisma のログには 81 件のクエリがあり、pg_indexes に idx_posts_user_created はありません。 解釈： findMany で投稿を取得した後、行ごとに user.findUnique を実行しています。つまり N+1 です。また、ORDER BY created_at はシーケンシャルスキャンです。 分類： include: { author } で先行読み込みし、複合インデックス (user_id, created_at DESC) を追加します。 検証： EXPLAIN ANALYZE → Index Scan、クエリ数は 2、p95 は 200ms 未満です。重複インデックスは追加していません。

報告前のセルフチェック

• 既存のものを先に確認 — CREATE INDEX の前に pg_indexes／マイグレーションを確認しました
• EXPLAIN — 改善効果を主張するクエリには、推測ではなく ANALYZE の出力があります
• 重複インデックスなし — 提案する名前を検索し、存在しないことを確認しました
• 適切な担当 — スキーマの一貫性 → audit-db-schema、RLS アクセス → plan-rls-audit

データベースのパフォーマンス問題を特定し、修正するための体系的なアプローチです。

使用するタイミング

• ページの読み込みが遅い（データベースがボトルネック）
• クエリのタイムアウトエラー
• N+1 クエリ
• スキーマ設計のレビュー
• インデックスの最適化
• マイグレーションの計画

まず既存の状態を確認 ［自由度：低 — 手順どおりに実行］

最適化を行う前に、必ず現在の状態を確認してください。

1. 既存のインデックスを確認：

SELECT indexname, indexdef FROM pg_indexes
WHERE schemaname = 'public' AND tablename = 'your_table';

2. 既存のマイグレーションを確認：

ls -la supabase/migrations/ | grep -i "index\|optim\|perf"

3. インデックスがすでに存在するか確認：

SELECT 1 FROM pg_indexes WHERE indexname = 'your_proposed_index';

4. 現在の問題について Supabase のアドバイザーを確認：

• パフォーマンス／セキュリティには get_advisors MCP ツールを使用します
• すでに対処済みの問題を再度修正しないでください

理由： 重複インデックスはストレージを浪費し、書き込みを遅くします。追加する前に必ず確認してください。

パフォーマンスの調査 ［自由度：高］

1. 遅いクエリを特定する

• Prisma：new PrismaClient({ log: [{ emit: 'event', level: 'query' }] }) と db.$on('query', ...) を使用し、所要時間とともに 100 ms を超えるクエリを記録します。
• Postgres／Supabase：CREATE EXTENSION IF NOT EXISTS pg_stat_statements を実行し、total_time DESC で並べ替えて、total_time / calls と rows / calls を確認します。

ログのスニペットと pg_stat_statements のクエリ：references/queries.md §遅いクエリの特定。

2. よくあるパフォーマンスの問題

問題	症状	解決策
N+1 クエリ	小さなクエリが多数実行される	include を使用する／先行読み込みする
インデックスの欠落	WHERE／JOIN が遅い	フィルター対象の列にインデックスを追加する
フルテーブルスキャン	大きなテーブルで遅い	インデックスを追加し、結果を制限する
過剰なデータ取得	レスポンスが遅い	必要なフィールドだけを選択する
ページネーションなし	メモリの問題が発生する	カーソル／オフセットページネーションを追加する

N+1 クエリの修正 ［自由度：高］

• 問題： findMany の後、ループ内で行ごとに findUnique を実行しています。クエリ数は 1 + N です。
• 修正： 関連データを先行読み込みします。ネストした select で対象を絞り、JOIN による過剰なデータ取得を防ぎます。
• Supabase：.select('*, author:users(id, name, avatar)') を 1 回呼び出すと、JOIN を実行できます。

// Good - 2 queries total
const posts = await db.post.findMany({
  include: { author: { select: { id: true, name: true, avatar: true } } },
})

Prisma の悪い例／良い例と Supabase の同等例：references/queries.md §N+1 クエリの修正。

インデックスの最適化 ［自由度：高］

インデックスを追加するタイミング

次の場合はインデックスを追加します：

• 列が WHERE 句（フィルタリング）で使用される
• 列が JOIN 条件で使用される
• 列が ORDER BY 句で使用される
• 列に一意制約がある

次の場合はインデックスを追加しないでください：

• テーブルが小さい（1000 行未満）
• 列のカーディナリティが低い（一意な値が少ない）
• 列がクエリで使われる頻度が低い
• テーブルへの書き込みが多い

インデックスの種類

• 1つのフィルターには単一列インデックスを使用します。WHERE a = ? ORDER BY b のような場合は複合インデックスを使用します（列の順序が重要です。等価条件の列を先に、ソート列を後にします）。
• 制約にはユニークインデックスを使用します。頻繁に使われる一部のデータには部分インデックス（WHERE published = true）、JSONB、配列、to_tsvector 検索には GIN を使用します。
• Prisma：@@index([userId, createdAt(sort: Desc)])、@@unique([...]) は SQL に対応します。

CREATE INDEX idx_posts_user_created ON posts(user_id, created_at DESC);  -- composite, order matters
CREATE INDEX idx_posts_published ON posts(created_at) WHERE published = true;  -- partial

6種類すべてのインデックス形式と Prisma モデルについては、references/schema-and-indexes.md §インデックスの種類、§Prisma のインデックス構文を参照してください。

クエリ最適化のパターン [自由度：高]

• 必要なフィールドのみを選択する — 行全体ではなく、select: { id, name, email } を使用します。
• ページネーション — 小さなページにはオフセット（skip／take）で問題ありません。大規模なリストや無限スクロールにはカーソル（cursor: { id }、skip: 1）を使用し、最後の行から nextCursor を返します。
• バッチ処理 — 作成処理のループではなく createMany({ skipDuplicates }) を使用し、関連する書き込みには $transaction([...]) を使用します。
• 件数取得 — db.post.count({ where }) を使用します。ページ取得クエリと合わせて1つの $transaction 内で実行します。

const posts = await db.post.findMany({
  take: limit,
  skip: cursor ? 1 : 0,                        // skip the cursor row itself
  cursor: cursor ? { id: cursor } : undefined,
  orderBy: { createdAt: 'desc' },
})
const nextCursor = posts.length === limit ? posts[posts.length - 1].id : null

必要なフィールドのみの選択、2種類のページネーション、バッチ処理、件数取得については、references/queries.md §必要なフィールドのみを選択する から §件数取得の最適化までを参照してください。

スキーマ設計のベストプラクティス [自由度：高]

正規化と非正規化

正規化する場合：

• データが頻繁に変更される
• データの整合性が重要である
• ストレージが懸念事項である

非正規化する場合：

• 読み取り性能が重要である

• データがほとんど変更されない

• 複雑な結合が遅い

• 正規化：post_stats(post_id PK → posts, view_count, like_count) テーブル。非正規化：posts の列としてカウンターを保持します。

• データ型 — ID には UUID、長さに上限のある文字列には VARCHAR(n)、金額には DECIMAL(10,2)、常に TIMESTAMPTZ、固定の値集合には ENUM を使用します。

• 論理削除 — @@index([deletedAt]) を設定した、NULL 許容の deletedAt を使用します。すべての読み取りで deletedAt: null をフィルタリングします。

両方の形式の SQL、データ型の一覧、論理削除モデルについては、references/schema-and-indexes.md §正規化、§効率的なデータ型、§論理削除を参照してください。

Supabase 固有の最適化 [自由度：高]

• RLS — 行ごとの関数呼び出しやサブセレクト（get_user_team()）は遅いため、user_id = auth.uid() を優先します。ルール上必要な場合は、メンバーシップテーブルに対する EXISTS 結合を使用します。
• Edge Functions — 複数のクエリによる集計は、クライアントとの複数回の往復ではなく、Deno.serve の背後で supabase.rpc('get_dashboard_stats') を1回呼び出す形に移します。

CREATE POLICY "fast_policy" ON posts FOR SELECT USING (user_id = auth.uid());

低速／高速／結合ポリシーと Edge Function については、references/schema-and-indexes.md §RLS のパフォーマンス、§Edge Functions を参照してください。

クエリ分析 [自由度：低 — そのまま実行]

EXPLAIN ANALYZE

EXPLAIN ANALYZE SELECT * FROM posts WHERE user_id = 'abc123' ORDER BY created_at DESC LIMIT 20;

確認項目：Seq Scan（大規模テーブルでは不適切）、Index Scan（適切）、Nested Loop（N+1 がないか確認）、実測時間が長い箇所。注釈付きバージョンについては、references/queries.md §EXPLAIN ANALYZE を参照してください。

主な指標

指標	目標	超過した場合の対応
クエリ時間	< 100ms	インデックスを追加し、最適化する
スキャンされた行数	< 返却行数の10倍	インデックスを追加する
メモリ使用量	< 256MB	LIMIT とページネーションを追加する
接続数	< プールサイズ	コネクションプーリングを使用する

最適化チェックリスト [自由度：低 — 省略しないこと]

• クエリのログ記録と監視を実施している
• フィルターや結合に使う列にインデックスがある
• N+1 クエリがない（イーガーローディング）
• すべてのリストエンドポイントでページネーションを使用している
• 必要なフィールドのみを選択している
• 可能な場合はバッチ処理を使用している
• コネクションプーリングを設定している
• RLS ポリシーを最適化している
• 遅いクエリに EXPLAIN ANALYZE を実行している
• 適切なデータ型を使用している