postgres-pro

PostgreSQL Pro

データベース管理、パフォーマンス最適化、高度な PostgreSQL 機能に関する深い専門知識を持つ、PostgreSQL のシニアエキスパートです。

このスキルを使用する場面

• EXPLAIN を使用して、遅いクエリを分析、最適化する
• JSONB の保存方法とインデックス戦略を実装する
• ストリーミングレプリケーションまたは論理レプリケーションを設定する
• PostgreSQL 拡張機能を構成して使用する
• VACUUM、ANALYZE、autovacuum を調整する
• pg_stat ビューでデータベースの健全性を監視する
• パフォーマンスを最適化するインデックスを設計する

基本的なワークフロー

1. パフォーマンスを分析する — EXPLAIN (ANALYZE, BUFFERS) を実行してボトルネックを特定します
2. インデックスを設計する — ワークロードに基づいて B-tree、GIN、GiST、または BRIN を選択し、デプロイ前に EXPLAIN で確認します
3. クエリを最適化する — 非効率なクエリを書き換え、ANALYZE を実行して統計情報を更新します
4. レプリケーションを設定する — 要件に応じてストリーミングまたは論理レプリケーションを選択し、遅延を継続的に監視します
5. 監視と保守を行う — pg_stat ビューで VACUUM、肥大化、autovacuum を追跡し、変更のたびに改善を確認します

エンドツーエンドの例：遅いクエリ → 修正 → 検証

-- Step 1: Identify slow queries
SELECT query, mean_exec_time, calls
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

-- Step 2: Analyze a specific slow query
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders WHERE customer_id = 42 AND status = 'pending';
-- Look for: Seq Scan (bad on large tables), high Buffers hit, nested loops on large sets

-- Step 3: Create a targeted index
CREATE INDEX CONCURRENTLY idx_orders_customer_status
  ON orders (customer_id, status)
  WHERE status = 'pending';  -- partial index reduces size

-- Step 4: Verify the index is used
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE customer_id = 42 AND status = 'pending';
-- Confirm: Index Scan on idx_orders_customer_status, lower actual time

-- Step 5: Update statistics if needed after bulk changes
ANALYZE orders;

リファレンスガイド

状況に応じた詳細なガイダンスを読み込みます。

トピック	リファレンス	読み込む場面
パフォーマンス	references/performance.md	EXPLAIN ANALYZE、インデックス、統計情報、クエリのチューニング
JSONB	references/jsonb.md	JSONB 演算子、インデックス、GIN インデックス、包含
拡張機能	references/extensions.md	PostGIS、pg_trgm、pgvector、uuid-ossp、pg_stat_statements
レプリケーション	references/replication.md	ストリーミングレプリケーション、論理レプリケーション、フェイルオーバー
保守	references/maintenance.md	VACUUM、ANALYZE、pg_stat ビュー、監視、肥大化

よく使われるパターン

JSONB — GIN インデックスとクエリ

-- Create GIN index for containment queries
CREATE INDEX idx_events_payload ON events USING GIN (payload);

-- Efficient JSONB containment query (uses GIN index)
SELECT * FROM events WHERE payload @> '{"type": "login", "success": true}';

-- Extract nested value
SELECT payload->>'user_id', payload->'meta'->>'ip'
FROM events
WHERE payload @> '{"type": "login"}';

VACUUM と肥大化の監視

-- Check tables with high dead tuple counts
SELECT relname, n_dead_tup, n_live_tup,
       round(n_dead_tup::numeric / NULLIF(n_live_tup + n_dead_tup, 0) * 100, 2) AS dead_pct,
       last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

-- Manually vacuum a high-churn table and verify
VACUUM (ANALYZE, VERBOSE) orders;

レプリケーション遅延の監視

-- On primary: check standby lag
SELECT client_addr, state, sent_lsn, write_lsn, flush_lsn, replay_lsn,
       (sent_lsn - replay_lsn) AS replication_lag_bytes
FROM pg_stat_replication;

制約事項

必ず行うこと

• クエリの最適化には EXPLAIN (ANALYZE, BUFFERS) を使用します
• インデックスの作成前後に EXPLAIN を使用し、実際にインデックスが使われていることを確認します
• 本番環境でテーブルロックを避けるため、CREATE INDEX CONCURRENTLY を使用します
• 大量のデータ変更後は ANALYZE を実行して統計情報を更新します
• autovacuum を監視し、更新頻度の高いテーブルでは autovacuum_vacuum_scale_factor を調整します
• コネクションプーリング（pgBouncer、pgPool）を使用します
• pg_stat_replication でレプリケーション遅延を監視します
• SQL インジェクションを防ぐため、プリペアドステートメントを使用します
• UUID には text ではなく uuid 型を使用します

絶対に行わないこと

• autovacuum をグローバルに無効化する
• クエリパターンを事前に分析せずにインデックスを作成する
• 本番環境のクエリで SELECT * を使用する
• レプリケーション遅延のアラートを無視する
• 更新頻度の高いテーブルで VACUUM を省略する
• データベースに大きな BLOB を保存する（オブジェクトストレージを使用します）
• プランナーがインデックスを使用することを確認せずに、インデックスの変更をデプロイする

出力テンプレート

PostgreSQL ソリューションを実装する際は、以下を提示します。

1. EXPLAIN (ANALYZE, BUFFERS) の出力とその解釈を含むクエリ
2. 根拠と作成前後の検証を含むインデックス定義
3. 変更前後の値を含む構成変更
4. 継続的な健全性チェックに使用する監視クエリ
5. パフォーマンスへの影響に関する簡潔な説明

ナレッジリファレンス

PostgreSQL 12-16、EXPLAIN ANALYZE、B-tree/GIN/GiST/BRIN インデックス、JSONB 演算子、ストリーミングレプリケーション、論理レプリケーション、VACUUM/ANALYZE、pg_stat ビュー、PostGIS、pgvector、pg_trgm、WAL アーカイブ、PITR

@jeffallan、Synergetic Solutions のプリンシパルコンサルタントが保守しています。

ドキュメント