パフォーマンス状態
今何が起きているか(リアルタイム)
pg_stat_activity — 現在実行中のクエリ、接続数、ロック待ちなどを確認
SELECT pid, usename, state, query, now() - query_start AS duration FROM pg_stat_activity WHERE state != 'idle' ORDER BY duration DESC;
- 長時間実行中のクエリを見つける
SELECT pid, now() - pg_stat_activity.query_start AS duration, query FROM pg_stat_activity WHERE (now() - pg_stat_activity.query_start) > interval '5 minutes';
- ロック待ちの確認
SELECT * FROM pg_locks WHERE NOT granted;
- クエリ単位の統計(拡張機能が必要)
pg_stat_statements — どのクエリが遅い/頻繁に呼ばれているかを集計。デフォルトでは無効なので postgresql.conf で shared_preload_libraries = 'pg_stat_statements' を設定して再起動が必要です。
CREATE EXTENSION IF NOT EXISTS pg_stat_statements; SELECT query, calls, total_exec_time, mean_exec_time, rows FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;
- テーブル・インデックスの使用状況
pg_stat_user_tables — シーケンシャルスキャンが多発していないかなど
SELECT relname, seq_scan, idx_scan, n_live_tup, n_dead_tup FROM pg_stat_user_tables ORDER BY seq_scan DESC;
pg_stat_user_indexes — 使われていないインデックスの発見
SELECT relname, indexrelname, idx_scan FROM pg_stat_user_indexes WHERE idx_scan = 0;
- 個別クエリの実行計画
EXPLAIN (ANALYZE, BUFFERS) SELECT ...;
BUFFERS を付けるとキャッシュヒット率(shared hit/read)も見えるので、実際にディスクI/Oが発生しているか判断できます。
- データベース全体のヒット率
SELECT datname,
blks_hit::float / nullif(blks_hit + blks_read, 0) AS cache_hit_ratio
FROM pg_stat_database;
99%前後が目安。低い場合は shared_buffers の見直しなどを検討します。
- デッドタプル系（バキュームの目安）
デッドタプルの割合が10〜20%を超えてきたら要注意。更新頻度の高いテーブルは自動バキュームの頻度を調整する。
SELECT schemaname, relname, n_dead_tup, n_live_tup,
ROUND((n_dead_tup::numeric / NULLIF(n_live_tup + n_dead_tup, 0)) * 100, 2) AS dead_tuple_percent,
last_autovacuum, autovacuum_count
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;
レスポンス調査
- いつ・どこで悪化したかを特定 急激な悪化か、じわじわの悪化かで原因が変わります 急激: デプロイ、統計情報の陳腐化、ロック競合、突発的な負荷増加、インデックス破損など じわじわ: テーブル肥大化(bloat)、インデックス肥大化、autovacuum不足、データ量増加による実行計画の変化など
-- pg_stat_database でリセット以降の傾向を確認(定期的にスナップショットを取っておくと比較しやすい)
SELECT datname, xact_commit, xact_rollback, blks_read, blks_hit,
tup_returned, tup_fetched, deadlocks, temp_files, temp_bytes
FROM pg_stat_database;
temp_files / temp_bytes が増えていたら、work_mem 不足でソートやハッシュがディスクに溢れている可能性があります。
- 今まさに何が起きているか
-- 実行中クエリとその経過時間
SELECT pid, usename, state, wait_event_type, wait_event,
now() - query_start AS duration, query
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY duration DESC;
wait_event が埋まっていれば、CPUではなく「何かを待っている」状態です。よくあるパターン:
Lock 系 → ロック競合(ステップ3へ) IO 系 → ディスクI/Oがボトルネック Client 系 → アプリ側がクエリ結果を受け取れていない(ネットワークやアプリ側の問題)
- ロック競合の確認
SELECT blocked_locks.pid AS blocked_pid,
blocked_activity.query AS blocked_query,
blocking_locks.pid AS blocking_pid,
blocking_activity.query AS blocking_query
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks
ON blocking_locks.locktype = blocked_locks.locktype
AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;
長時間トランザクションが居座っていると、他のクエリを巻き込んで全体が遅くなることがよくあります。
- 特定のクエリが遅いのか、全体が遅いのか
pg_stat_statements が有効なら、直近で mean_exec_time が上がっているクエリを探します。
SELECT query, calls, mean_exec_time, total_exec_time FROM pg_stat_statements ORDER BY mean_exec_time DESC LIMIT 20;
特定クエリが遅い場合は EXPLAIN (ANALYZE, BUFFERS) で実行計画を確認 → インデックスが使われていない、統計が古い(ANALYZE していない)、行数見積もりが大きくズレている、などをチェック。
- bloat(肥大化)・autovacuumの状態
SELECT relname, n_live_tup, n_dead_tup,
round(n_dead_tup::numeric / nullif(n_live_tup + n_dead_tup, 0) * 100, 2) AS dead_ratio,
last_autovacuum, last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;
dead tupleの比率が高い、または last_autovacuum が古い場合は、autovacuumが追いついていない可能性があります(頻繁なUPDATE/DELETEが多いテーブルで起きやすい)。
- リソース面(OS/インフラ側)
DB内部だけでなく、サーバー側も確認:
CPU使用率、ディスクI/O(iostatなど) メモリ、swap発生の有無 ディスク容量(残り少ないと突然遅くなることがある) コネクション数が max_connections に近づいていないか
