こんにちは！PostgreSQLを本番運用していると、気づかないうちにパフォーマンスが劣化したり、トランザクションが肥大化したりすることがあります。今回はPostgreSQLを安定稼働させるために押さえておきたいメンテナンスの基本を解説します。
1. VACUUM：死んだタプルを掃除する
PostgreSQLはMVCC（Multi-Version Concurrency Control）という仕組みを採用しており、データを更新・削除しても古いバージョン（死んだタプル）がテーブル内に残り続けます。このまま放置するとテーブルが肥大化し、パフォーマンスが低下します。
-- 手動でVACUUMを実行（他の処理をブロックしない）
VACUUM;
-- 不要領域をOSに返す（テーブルロックがかかる）
VACUUM FULL;
本番環境では autovacuum がデフォルトで有効になっていますが、負荷が高いテーブルでは設定値のチューニングが必要です。
# postgresql.conf のチューニング例
autovacuum_vacuum_scale_factor = 0.01 # 1%の変更でVACUUM実行
autovacuum_vacuum_threshold = 50
autovacuum_naptime = 30s # チェック間隔
2. ANALYZE：統計情報を最新に保つ
クエリオプティマイザはテーブルの統計情報をもとに実行計画を決定します。データが大量に変更されたあとにANALYZEを行わないと、最適でないインデックスが選ばれて低速なクエリが発生します。
-- 統計情報を更新
ANALYZE;
-- すべてのテーブルをまとめて
VACUUM ANALYZE;
autovacuum が有効なら自動で実行されますが、大量データをインポートした後などは手動で実行すると安心です。
3. インデックス戦略を見直す
不要なインデックスを整理する
-- 未使用のインデックスを確認（pg_stat_user_indexes）
SELECT indexrelname, idx_scan, idx_tup_read
FROM pg_stat_user_indexes
WHERE idx_scan = 0;
idx_scan が0のインデックスは一度も使われていない可能性が高いです。ただし、バッチ処理などで月1回だけ使うケースもあるため、削除前に利用状況をしっかり確認しましょう。
インデックスの再構築
長期間運用しているとインデックスが断片化し、パフォーマンスが低下します。
REINDEX INDEX index_name;
REINDEX TABLE table_name;
REINDEX DATABASE database_name;
4. クエリパフォーマンスの監視（pg_stat_statements）
pg_stat_statements 拡張機能を使うと、どのクエリがどれだけの時間を消費しているかを把握できます。
-- 有効化（postgresql.conf に追記）
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all
-- 実行時間の上位クエリを確認
SELECT query, calls, total_exec_time / calls AS avg_time_ms
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;
この情報をもとに、重いクエリに適切なインデックスを追加したり、SQL自体を見直したりします。
5. バックアップ戦略
| 方式 | 特徴 | 推奨頻度 |
|---|---|---|
pg_dump | 論理バックアップ、テーブル単位で可能 | 日次 |
pg_basebackup | 物理バックアップ、PITR対応 | 日次 |
| WALアーカイブ | 継続的なアーカイブ、ポイントインタイムリカバリ | 常時 |
# pg_dumpの例
pg_dump -U postgres mydb > /backup/mydb_$(date +%Y%m%d).sql
可能であれば物理バックアップ＋WALアーカイブの組み合わせが最も安全です。
6. 接続プーリングの検討
PostgreSQLはクライアントからの接続ごとにプロセスをforkするため、大量接続があるとメモリを圧迫します。PgBouncer などのコネクションプーラを導入すると、少数のDB接続を多数のクライアントで効率的に共有できます。
メンテナンススケジュールの例
| 頻度 | 作業内容 |
|---|---|
| 毎日 | VACUUM（autovacuum任せ）、エラーログ確認 |
| 毎週 | クエリパフォーマンス分析（pg_stat_statements） |
| 毎月 | 不要インデックスの棚卸し、ANALYZE手動実行 |
| 四半期 | REINDEX、VACUUM FULL（メンテナンス時間に） |
| 半年 | バックアップからのリストアテスト |
まとめ
PostgreSQLは適切なメンテナンスを行えば非常に安定したデータベースです。「VACUUM」「ANALYZE」「インデックス管理」「バックアップ」の4つを習慣化するだけで、トラブルを大幅に減らせます。まずは pg_stat_statements を導入して、現状のクエリパフォーマンスを把握するところから始めてみてください。
今日も最後までお読みいただき、ありがとうございました！