specsfy-specialist-postgres

PostgreSQL

使用する場面

• スキーマの設計、SQL の作成やレビュー、インデックスの選定、実行計画（EXPLAIN）の分析、ロックやデッドロックの診断、マイグレーションの計画、Postgres のバックアップやリストアの規模の見積もりを行う場合に使用します。
• ORM（Eloquent、Prisma、Drizzle）が非効率な SQL を生成しており、根本原因が ORM の API ではなく、データモデリングやインデックスにある場合にも使用します。
• Supabase 固有の判断（RLS、Auth、Realtime、Edge Functions）には使用しないでください。その場合は $specsfy-specialist-supabase を使用します。Postgres をベースに、これらの追加レイヤーを適用します。
• エントリーポイントが Eloquent の場合は $specsfy-specialist-laravel と、ボトルネックがデータベース以外（アプリケーション、ネットワーク、キャッシュ）にも及ぶ場合は $specsfy-specialist-performance-engineering と組み合わせます。

ワークフロー

1. 推奨する前に、Postgres のバージョン、インストール済みの拡張機能、現在のデータ量、予想される増加量、ワークロード（OLTP、分析、混合）、データのオーナーを確認します。
2. ユースケースに適した正確な型、NOT NULL、CHECK、UNIQUE、外部キー、正規化を用いて不変条件をモデル化します。
3. ルールを表す最もシンプルなクエリを作成し、空のテーブルや開発用テーブルではなく、代表的なデータを使って EXPLAIN (ANALYZE, BUFFERS) で実際の実行計画を測定します。
4. 観測されたワークロードの条件（WHERE の述語、ORDER BY、JOIN）に基づいてインデックスを選定します。「この列がクエリされる」という理由だけで選定してはいけません。
5. 実際の負荷を想定して、分離レベル、トランザクションの長さ、ロック取得の順序、予想される同時実行性を分析します。
6. デプロイ中に旧バージョンと新バージョンのアプリケーションの互換性を維持できるようにマイグレーションを計画し、テスト可能なロールバック手順を用意します。
7. 実行に [MIGRATION] のラベルを付け、バージョン管理されたファイルを示し、テスト用データベースに適用して、完了前にマイグレーターの履歴を確認します。
8. 変更を本番環境向けに完了とする前に、対象環境でバックアップ、リストア、監視、キャパシティを検証します。

パターン

• 常に満たす必要がある不変条件には、データベース制約（NOT NULL、CHECK、UNIQUE、FK、EXCLUDE）を優先します。アプリケーションだけで検証すると、別の書き込み経路から不整合なデータが入り込む可能性があります。
• 本番コードでは SELECT *、不正確な型（値の種類が固定された列に text を使う、金額に float を使う）、同じプレフィックスを持つ既存のインデックスと重複するインデックスを避けます。
• 書き込みパターン、テーブルのサイズ、述語の選択性、追加前後の実行計画を確認せずにインデックスを追加してはいけません。選定を誤ったインデックスは読み取りを速めずに書き込みを遅くします。
• トランザクションを短く保ち（外部 I/O、ユーザーの応答待ち、ネットワーク呼び出しを内部で行わない）、デッドロックを防ぐために、すべてのコード経路でロック取得の順序を統一します。
• EXPLAIN (ANALYZE, BUFFERS) は、クエリを実際に実行しても安全な環境でのみ実行します（ROLLBACK やレプリカを使わずに本番環境で実行してはいけません）。
• 互換性のないスキーマ変更や大容量テーブルでは、expand/contract を適用します。読み取り中の列を、追加と同じ手順で名前変更または削除してはいけません。
• 必要最小限の権限を付与し、マイグレーション（DDL）、アプリケーション（DML）、読み取り（SELECT のみ）のロールを分離します。アプリケーションは DROP TABLE を実行できるロールで接続してはいけません。

アンチパターン

• 「このカラムは WHERE に出てくる」という理由だけで、選択性を無視してインデックスを作成すること。カーディナリティの低いカラム（ブール値や値の種類が少ないステータス）では、プランナーはシーケンシャルスキャンを選ぶことが多く、インデックスは書き込みコストを増やすだけです。
• 古い Postgres のバージョン（< 11）で ALTER TABLE ... ADD COLUMN ... NOT NULL DEFAULT を実行し、ロック中にテーブル全体を書き換えること。現在のバージョンでは定数の DEFAULT は最適化されていますが、揮発性関数を使う DEFAULT では引き続き書き換えが発生します。
• ネットワーク応答やユーザーの確認を待つ間、長時間のトランザクションでロックを保持すること。autovacuum による不要なタプルのクリーンアップを妨げ、肥大化を招きます。
• 増加し続けるテーブルで大きな OFFSET を使ってページネーションすること。コストは offset に比例して増加するため、keyset ページネーション（WHERE id > :cursor ORDER BY id LIMIT :n）を優先してください。
• 自動バックアップを一度もリストアしていないこと。完全なリストアをテストせずに「バックアップがある」と言っても、検証されていない前提にすぎず、保証にはなりません。

検証

• 整合性（制約違反で想定したエラーが発生すること）、並行性（2 つの writer が同時に処理しても不変条件が壊れないこと）、およびホットパスの重要なクエリをテストしてください。
• 開発環境の空のテーブルではなく、現実的なカーディナリティを使って EXPLAIN (ANALYZE, BUFFERS) を変更前後で比較してください。
• デプロイウィンドウを設定する前に、本番のテーブルサイズに照らして、あらゆる DDL のロックと書き換えにかかる時間を見積もってください。
• 各 [MIGRATION] タスクについて、ファイル、適用コマンド、マイグレーターの履歴照会を確認し、すべての終了コードが 0 であることを記録してください。
• 実際のバックアップから定期的にリストアを検証し、処理にかかる時間（RTO）も確認してください。リストアをテストしていないバックアップは、復旧の保証にはなりません。
• 比較したプランなしに「パフォーマンスへの影響はない」と宣言しないでください。また、上記の制約と並行性のテストなしに、スキーマが「整合している」と宣言しないでください。

関連スキル

• $specsfy-specialist-data-modeling は、Postgres 固有の詳細に入る前に、エンティティ、関係、ライフサイクルを定義します。
• $specsfy-specialist-application-security は、制約、ロール、RLS としてデータベースに反映される脅威、認可、分離を定義します。
• Postgres を Supabase で管理している場合は、$specsfy-specialist-supabase を使用してください（RLS、Auth、Realtime、固有のプーリング）。
• エントリーポイントが Eloquent であり、修正を migration/model に反映する必要がある場合は、$specsfy-specialist-laravel を使用してください。
• ボトルネックがインデックスや実行プランだけでは解決しない場合（ネットワーク、キャッシュ、アプリケーション）は、$specsfy-specialist-performance-engineering を使用してください。
• 本番環境のデータベースのメトリクスとアラート（接続、ロック、レプリケーション、ラグ）には、$specsfy-specialist-observability を使用してください。
• 参照される状態の一部がデータベース外のキャッシュにある場合は、$specsfy-specialist-redis を使用してください。Postgres は引き続き信頼できる唯一の情報源であり、Redis が置き換えることはありません。
• Postgres サーバーをコンテナにパッケージ化して運用する場合（公式イメージ、データボリューム、ヘルスチェック）は、$specsfy-specialist-docker を使用してください。スキーマ、インデックス、実行プランの判断はこちらで行います。

型、インデックス、並行性、セキュリティ、migrations、運用、公式情報については、references/standards.md をお読みください。