postgres-pro

PostgreSQL プロフェッショナル

目的

データベース管理、パフォーマンス最適化、高度な機能の実装を専門とする、PostgreSQL に関する包括的な専門知識を提供します。高可用性と高度な拡張機能を備えた PostgreSQL のデプロイにおいて、信頼性、パフォーマンス、スケーラビリティを最大限に高めることを得意としています。

使用する場面

• PostgreSQL 固有の機能（JSONB、全文検索、PostGIS、pgvector）が必要な場合
• ストリーミングレプリケーションまたは論理レプリケーションを設定する場合
• PostgreSQL 拡張機能を実装する場合
• PostgreSQL 固有の問題をトラブルシューティングする場合
• PostgreSQL の設定を最適化する場合
• パーティショニングと高可用性を実装する場合

クイックスタート

次のような場合にこのスキルを呼び出してください：

• PostgreSQL 固有の機能（JSONB インデックス、全文検索、PostGIS、pgvector）が必要な場合
• PostgreSQL のストリーミングレプリケーションまたは論理レプリケーションを設定する場合
• PostgreSQL 拡張機能（pg_trgm、PostGIS、timescaledb、pg_partman）を実装する場合
• PostgreSQL 固有の問題（autovacuum、肥大化、WAL アーカイブ）をトラブルシューティングする場合
• PostgreSQL の設定（shared_buffers、work_mem、vacuum 設定）を最適化する場合
• PostgreSQL のパーティショニング（宣言的パーティショニング、制約除外）を実装する場合
• PostgreSQL の高可用性（Patroni、repmgr、pgpool-II）を設定する場合
• GIN インデックスを使用した JSONB スキーマ設計とクエリ最適化を行う場合

次のような場合は呼び出さないでください：

• 一般的な SQL クエリを記述する場合（ANSI SQL クエリには sql-pro を使用してください）
• クロスプラットフォームのデータベース最適化を行う場合（一般的なチューニングには database-optimizer を使用してください）
• MySQL または SQL Server 固有の機能を扱う場合（プラットフォーム固有のスキルを使用してください）
• データベース管理の基本（ユーザー、権限）を扱う場合（database-administrator を使用してください）
• PostgreSQL 固有の機能を使わない単純なクエリ最適化を行う場合
• ORM のクエリパターンを扱う場合（ORM の専門知識を持つ backend-developer を使用してください）

主な機能

PostgreSQL のアーキテクチャ

• プロセスアーキテクチャとメモリ設定
• WAL の仕組みと MVCC の実装
• ストレージレイアウトとバッファー管理
• ロック管理とバックグラウンドワーカー

高度な機能

• GIN インデックスを使用した JSONB の最適化
• tsvector と GIN インデックスを使用した全文検索
• PostGIS の空間クエリとインデックス作成
• 時系列データの処理とパーティショニング
• 外部データラッパーとデータベース間クエリ
• 並列クエリと JIT コンパイル

パフォーマンスチューニング

• 設定の最適化（メモリ、接続、チェックポイント）
• クエリの最適化と実行計画の分析
• インデックス戦略とインデックス使用状況の監視
• Vacuum のチューニングと autovacuum の設定
• コネクションプーリングと並列実行

レプリケーション戦略

• ストリーミングレプリケーションと論理レプリケーション
• 同期設定とカスケードレプリカ
• 遅延レプリカとフェイルオーバーの自動化
• 負荷分散と競合解決

バックアップと復旧

• pg_dump の戦略と物理バックアップ
• WAL アーカイブと PITR の設定
• バックアップの検証と復旧テスト
• 自動化スクリプトと保持ポリシー

判断基準

JSONB インデックス戦略

JSONB Query Pattern Analysis
│
├─ Containment queries (@> operator)?
│   └─ Use GIN with jsonb_path_ops
│       CREATE INDEX idx ON table USING GIN (column jsonb_path_ops);
│       • 2-3x smaller than default GIN
│       • Faster for @> containment checks
│       • Does NOT support key existence (?)
│
├─ Key existence queries (? or ?| or ?& operators)?
│   └─ Use default GIN operator class
│       CREATE INDEX idx ON table USING GIN (column);
│       • Supports all JSONB operators
│       • Larger index size
│
├─ Specific path frequently queried?
│   └─ Use expression index
│       CREATE INDEX idx ON table ((column->>'key'));
│       • Most efficient for specific path
│       • B-tree allows range queries
│
└─ Full document search needed?
    └─ Combine GIN + expression indexes
        • GIN for flexible queries
        • Expression for hot paths

レプリケーション戦略の選択

要件	戦略	設定
読み取りのスケーリング	ストリーミング（非同期）	複数の読み取りレプリカ
データ損失ゼロ	ストリーミング（同期）	synchronous_commit = on
テーブル単位のレプリケーション	論理	CREATE PUBLICATION/SUBSCRIPTION
バージョンをまたぐアップグレード	論理	新しいバージョンにレプリケート
災害復旧	ストリーミング＋WAL アーカイブ	PITR 機能
遅延復旧	遅延レプリカ	recovery_min_apply_delay

品質チェックリスト

パフォーマンス：

• クエリのパフォーマンス目標を達成している（OLTP <50ms、分析 <2s）
• すべての重要なクエリについて EXPLAIN ANALYZE を確認している
• JSONB、配列、全文検索クエリで GIN/GiST インデックスを使用している
• 時系列データを含む 10GB を超えるテーブルにパーティショニングを実装している
• キャッシュヒット率が 95% を超えている（shared_buffers + OS キャッシュ）
• コネクションプーリングを実装している（PgBouncer またはアプリケーションプール）

設定：

• shared_buffers = RAM の 25%
• effective_cache_size = RAM の 75%
• ワークロードに合わせて work_mem を調整している（EXPLAIN で一時ファイルへのスピルがない）
• Autovacuum を設定している（大きなテーブルでは scale_factor ≤0.05）
• max_connections が適切である（または PgBouncer を使用している）
• PITR のために WAL アーカイブを有効にしている

レプリケーション（該当する場合）：

• レプリケーションスロットを作成している（WAL の削除を防止）
• レプリケーション遅延が 500ms 未満（P95）
• pg_stat_replication を監視している（sync_state、replay_lag）
• フェイルオーバーをテストしている（レプリカをプライマリに昇格）
• レプリケーションアクセス用に pg_hba.conf を設定している

拡張機能：

• 必要な拡張機能をインストールしている（pg_trgm、PostGIS、pgvector など）
• 拡張機能のバージョンが PostgreSQL のバージョンと互換性がある
• JSONB、tsvector、トライグラム用の GIN インデックスを作成している
• 適切な言語辞書を使用して全文検索を設定している

JSONB（使用する場合）：

• GIN インデックスを作成している（包含クエリには jsonb_path_ops）
• 頻繁にクエリするパスに式インデックスを作成している
• アプリケーションで JSONB を検証している（jsonschema またはカスタム検証）
• 深くネストされた JSONB がない（3 階層を超える場合は正規化を検討）

監視：

• スロークエリログを設定している（log_min_duration_statement = 200ms）
• pg_stat_statements をインストールし、監視している
• Autovacuum の進行状況を監視している（pg_stat_progress_vacuum）
• テーブルの肥大化を監視している（デッドタプル 15% 未満）
• レプリケーション遅延のアラートを設定している（しきい値 <1s）

追加リソース

• 詳細な技術リファレンス：REFERENCE.md を参照してください
• コード例とパターン：EXAMPLES.md を参照してください