【2026年版】PostgreSQLパフォーマンスチューニング実践ガイド:クエリ最適化からインデックス戦略まで

Tech Trends AI
- 6 minutes read - 1170 wordsはじめに:PostgreSQLパフォーマンスの重要性
PostgreSQLは、世界で最も先進的なオープンソースリレーショナルデータベースとして、スタートアップから大企業まで幅広く採用されています。2026年現在、PostgreSQL 17がリリースされ、パフォーマンス面でも大幅な改善が行われていますが、本来の性能を引き出すにはアプリケーション特性に応じたチューニングが不可欠です。
本記事では、PostgreSQLのパフォーマンスチューニングを4つの柱――クエリ最適化、インデックス戦略、サーバー設定、監視・分析――に分けて、実践的な手法を解説します。
パフォーマンス問題の診断フレームワーク
問題の特定アプローチ
パフォーマンス改善の第一歩は、ボトルネックの正確な特定です。
| 症状 | 疑うべきポイント | 診断ツール |
|---|---|---|
| 特定クエリが遅い | クエリプラン、インデックス不足 | EXPLAIN ANALYZE |
| 全体的にレスポンスが遅い | サーバー設定、リソース不足 | pg_stat_activity |
| 書き込みが遅い | WAL設定、チェックポイント | pg_stat_bgwriter |
| 接続数が上限に達する | コネクションプーリング | pg_stat_activity |
| ディスクI/O高負荷 | shared_buffers、ワーキングメモリ | iostat + pg_statio |
| ロック待ちが頻発 | トランザクション設計 | pg_stat_locks |
pg_stat_statementsの有効化
パフォーマンス分析の最重要ツールであるpg_stat_statementsを有効にします。
-- postgresql.confに追加
-- shared_preload_libraries = 'pg_stat_statements'
-- 拡張機能のインストール
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
-- 最も時間がかかっているクエリのTop10
SELECT
query,
calls,
total_exec_time / 1000 AS total_time_sec,
mean_exec_time AS avg_time_ms,
rows,
shared_blks_hit + shared_blks_read AS total_blocks,
ROUND(100.0 * shared_blks_hit / NULLIF(shared_blks_hit + shared_blks_read, 0), 2) AS cache_hit_ratio
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;
クエリ最適化
EXPLAIN ANALYZEの読み方
クエリ最適化の基本はEXPLAIN ANALYZEの出力を正しく読み解くことです。
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT u.name, COUNT(o.id) AS order_count, SUM(o.amount) AS total_amount
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.created_at >= '2026-01-01'
GROUP BY u.name
ORDER BY total_amount DESC
LIMIT 20;
実行プランの主要ノード
| ノード | 説明 | コスト傾向 |
|---|---|---|
| Seq Scan | テーブル全走査 | 高(大テーブル時) |
| Index Scan | インデックスによる検索 | 低〜中 |
| Index Only Scan | インデックスのみで完結 | 最低 |
| Bitmap Index Scan | ビットマップによるインデックス検索 | 中 |
| Nested Loop | ネストループ結合 | 小テーブル同士で効率的 |
| Hash Join | ハッシュ結合 | 中〜大テーブルで効率的 |
| Merge Join | ソートマージ結合 | ソート済みデータで効率的 |
| Sort | ソート操作 | メモリ依存 |
| Aggregate | 集約操作 | データ量依存 |
アンチパターンと改善例
アンチパターン1:SELECT *の乱用
-- 悪い例:不要なカラムも取得
SELECT * FROM orders WHERE user_id = 123;
-- 良い例:必要なカラムのみ取得
SELECT id, amount, status, created_at
FROM orders
WHERE user_id = 123;
アンチパターン2:N+1クエリ
-- 悪い例:ユーザーごとにクエリを発行
-- アプリケーション側で for user in users: SELECT * FROM orders WHERE user_id = user.id
-- 良い例:JOINまたはサブクエリで一括取得
SELECT u.id, u.name, o.id AS order_id, o.amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.id = ANY(ARRAY[1, 2, 3, 4, 5]);
アンチパターン3:関数によるインデックス無効化
-- 悪い例:関数適用でインデックスが使えない
SELECT * FROM orders WHERE DATE(created_at) = '2026-02-01';
-- 良い例:範囲条件でインデックスを活用
SELECT * FROM orders
WHERE created_at >= '2026-02-01 00:00:00'
AND created_at < '2026-02-02 00:00:00';
アンチパターン4:不適切なOR条件
-- 悪い例:OR条件でインデックスが使えないケース
SELECT * FROM products
WHERE category = 'electronics' OR price < 1000;
-- 良い例:UNIONで分割
SELECT * FROM products WHERE category = 'electronics'
UNION
SELECT * FROM products WHERE price < 1000;
インデックス戦略
インデックスの種類と使い分け
PostgreSQLは多様なインデックスタイプを提供しています。適切な種類を選ぶことが重要です。
| インデックス種別 | 用途 | 適用場面 |
|---|---|---|
| B-tree | 等値検索、範囲検索 | 一般的な検索(デフォルト) |
| Hash | 等値検索のみ | 完全一致のみの高速検索 |
| GiST | 幾何データ、全文検索 | PostGIS、近傍検索 |
| GIN | 配列、JSONB、全文検索 | 複数値カラムの検索 |
| BRIN | 大テーブルの範囲検索 | 時系列データ(created_at等) |
| Bloom | 複数カラムの等値検索 | 多数カラムのAND条件 |
複合インデックスの設計原則
-- 基本原則:選択性の高いカラムを先に配置
-- クエリ: WHERE status = 'active' AND created_at >= '2026-01-01' AND user_id = 123
-- user_idが最も選択性が高い場合
CREATE INDEX idx_orders_user_status_date
ON orders (user_id, status, created_at);
-- カバリングインデックス(Index Only Scanを実現)
CREATE INDEX idx_orders_covering
ON orders (user_id, status, created_at)
INCLUDE (amount, product_id);
部分インデックス(Partial Index)
テーブルの一部のみにインデックスを作成して、サイズと更新コストを削減します。
-- アクティブな注文のみにインデックス
CREATE INDEX idx_orders_active
ON orders (user_id, created_at)
WHERE status = 'active';
-- 未処理タスクのみにインデックス
CREATE INDEX idx_tasks_pending
ON tasks (priority, created_at)
WHERE completed_at IS NULL;
式インデックス(Expression Index)
-- メールアドレスの小文字検索用
CREATE INDEX idx_users_email_lower
ON users (LOWER(email));
-- JSONB内のフィールド検索用
CREATE INDEX idx_events_type
ON events ((metadata->>'event_type'));
-- 日付の年月検索用
CREATE INDEX idx_orders_year_month
ON orders (DATE_TRUNC('month', created_at));
インデックスの健全性チェック
-- 未使用インデックスの検出
SELECT
schemaname,
tablename,
indexname,
idx_scan,
pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
AND schemaname = 'public'
ORDER BY pg_relation_size(indexrelid) DESC;
-- 重複インデックスの検出
SELECT
a.indexrelid::regclass AS index1,
b.indexrelid::regclass AS index2,
pg_size_pretty(pg_relation_size(a.indexrelid)) AS size1,
pg_size_pretty(pg_relation_size(b.indexrelid)) AS size2
FROM pg_index a
JOIN pg_index b ON a.indrelid = b.indrelid
AND a.indexrelid != b.indexrelid
AND a.indkey::text LIKE b.indkey::text || '%'
WHERE a.indrelid::regclass::text NOT LIKE 'pg_%';
-- インデックスの膨張率チェック
SELECT
schemaname,
tablename,
indexname,
pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
idx_scan,
idx_tup_read,
idx_tup_fetch
FROM pg_stat_user_indexes
WHERE schemaname = 'public'
ORDER BY pg_relation_size(indexrelid) DESC
LIMIT 20;
サーバー設定チューニング
メモリ関連パラメータ
PostgreSQLのパフォーマンスに最も影響するのがメモリ設定です。
| パラメータ | デフォルト | 推奨値(32GB RAM) | 説明 |
|---|---|---|---|
| shared_buffers | 128MB | 8GB(RAM の25%) | 共有バッファキャッシュ |
| effective_cache_size | 4GB | 24GB(RAM の75%) | OS キャッシュを含む推定値 |
| work_mem | 4MB | 64MB | ソート・ハッシュ操作のメモリ |
| maintenance_work_mem | 64MB | 2GB | VACUUM、CREATE INDEX用 |
| wal_buffers | -1(自動) | 64MB | WAL バッファサイズ |
| temp_buffers | 8MB | 32MB | 一時テーブル用バッファ |
work_memの最適化
work_memはクエリごと・操作ごとに割り当てられるため、慎重な設定が必要です。
-- 現在のwork_mem設定の確認
SHOW work_mem;
-- セッション単位での一時変更(テスト用)
SET work_mem = '256MB';
-- 大量ソートが必要な特定クエリのみ一時的に増加
SET LOCAL work_mem = '512MB';
SELECT * FROM large_table ORDER BY complex_column;
RESET work_mem;
WAL(Write-Ahead Logging)設定
| パラメータ | デフォルト | 推奨値 | 説明 |
|---|---|---|---|
| wal_level | replica | replica | WALの詳細度 |
| max_wal_size | 1GB | 4GB | チェックポイント間のWAL上限 |
| min_wal_size | 80MB | 1GB | WALの最小保持サイズ |
| checkpoint_completion_target | 0.9 | 0.9 | チェックポイントの分散率 |
| wal_compression | off | on | WALデータの圧縮 |
接続管理と並列処理
# postgresql.conf
# 接続数の設定
max_connections = 200
# 並列クエリの設定
max_parallel_workers_per_gather = 4
max_parallel_workers = 8
max_parallel_maintenance_workers = 4
parallel_tuple_cost = 0.01
parallel_setup_cost = 500
# JITコンパイルの設定
jit = on
jit_above_cost = 100000
jit_inline_above_cost = 500000
jit_optimize_above_cost = 500000
PgBouncerによるコネクションプーリング
# pgbouncer.ini
[databases]
myapp = host=localhost port=5432 dbname=myapp
[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
# プーリングモード
pool_mode = transaction
# 接続数の設定
max_client_conn = 1000
default_pool_size = 25
min_pool_size = 5
reserve_pool_size = 5
# タイムアウト
server_idle_timeout = 600
client_idle_timeout = 0
VACUUMとテーブルメンテナンス
MVCC とデッドタプル
PostgreSQLのMVCC(Multi-Version Concurrency Control)では、UPDATEやDELETEの結果として「デッドタプル」が蓄積します。これがテーブルの肥大化(bloat)を引き起こします。
autovacuumの最適化
-- テーブル単位のautovacuum設定
ALTER TABLE orders SET (
autovacuum_vacuum_threshold = 1000,
autovacuum_vacuum_scale_factor = 0.05,
autovacuum_analyze_threshold = 500,
autovacuum_analyze_scale_factor = 0.02
);
-- 高更新テーブルの設定例
ALTER TABLE sessions SET (
autovacuum_vacuum_threshold = 100,
autovacuum_vacuum_scale_factor = 0.01,
autovacuum_vacuum_cost_delay = 10
);
テーブル肥大化の確認
-- テーブルの肥大化率を確認
SELECT
schemaname,
tablename,
pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) AS total_size,
pg_size_pretty(pg_relation_size(schemaname || '.' || tablename)) AS table_size,
pg_size_pretty(pg_indexes_size(schemaname || '.' || tablename::regclass)) AS index_size,
n_live_tup,
n_dead_tup,
ROUND(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_ratio
FROM pg_stat_user_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname || '.' || tablename) DESC
LIMIT 20;
パーティショニング戦略
レンジパーティショニング(時系列データ)
-- パーティションテーブルの作成
CREATE TABLE events (
id BIGSERIAL,
event_type VARCHAR(50),
payload JSONB,
created_at TIMESTAMPTZ NOT NULL
) PARTITION BY RANGE (created_at);
-- 月別パーティションの作成
CREATE TABLE events_2026_01 PARTITION OF events
FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');
CREATE TABLE events_2026_02 PARTITION OF events
FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');
-- パーティション自動作成用の関数
CREATE OR REPLACE FUNCTION create_monthly_partition()
RETURNS void AS $$
DECLARE
next_month DATE := DATE_TRUNC('month', NOW()) + INTERVAL '1 month';
partition_name TEXT;
start_date TEXT;
end_date TEXT;
BEGIN
partition_name := 'events_' || TO_CHAR(next_month, 'YYYY_MM');
start_date := TO_CHAR(next_month, 'YYYY-MM-DD');
end_date := TO_CHAR(next_month + INTERVAL '1 month', 'YYYY-MM-DD');
EXECUTE format(
'CREATE TABLE IF NOT EXISTS %I PARTITION OF events FOR VALUES FROM (%L) TO (%L)',
partition_name, start_date, end_date
);
END;
$$ LANGUAGE plpgsql;
パーティショニングの効果比較
| 指標 | パーティションなし | パーティションあり | 改善率 |
|---|---|---|---|
| 月次データクエリ | 12.5秒 | 0.3秒 | 97.6% |
| VACUUM時間 | 45分 | 3分(対象パーティション) | 93.3% |
| インデックスサイズ | 8GB | 800MB(月あたり) | 90.0% |
| INSERT性能 | 5,000行/秒 | 8,000行/秒 | 60.0% |
監視とアラート
重要メトリクスダッシュボード
-- キャッシュヒット率(95%以上が目標)
SELECT
'buffer cache hit ratio' AS metric,
ROUND(100.0 * sum(blks_hit) / NULLIF(sum(blks_hit) + sum(blks_read), 0), 2) AS value
FROM pg_stat_database;
-- トランザクション実行状況
SELECT
datname,
numbackends AS active_connections,
xact_commit AS commits,
xact_rollback AS rollbacks,
ROUND(100.0 * xact_rollback / NULLIF(xact_commit + xact_rollback, 0), 2) AS rollback_ratio
FROM pg_stat_database
WHERE datname = current_database();
-- ロック待ちの検出
SELECT
blocked_locks.pid AS blocked_pid,
blocked_activity.usename AS blocked_user,
blocking_locks.pid AS blocking_pid,
blocking_activity.usename AS blocking_user,
blocked_activity.query AS blocked_query,
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.database IS NOT DISTINCT FROM blocked_locks.database
AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
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;
Prometheus + Grafana監視
# docker-compose.yml(監視スタック)
services:
postgres-exporter:
image: prometheuscommunity/postgres-exporter:latest
environment:
DATA_SOURCE_NAME: "postgresql://monitor:password@postgres:5432/myapp?sslmode=disable"
ports:
- "9187:9187"
prometheus:
image: prom/prometheus:latest
volumes:
- ./prometheus.yml:/etc/prometheus/prometheus.yml
ports:
- "9090:9090"
grafana:
image: grafana/grafana:latest
ports:
- "3000:3000"
environment:
GF_SECURITY_ADMIN_PASSWORD: admin
主要監視項目
| メトリクス | 閾値(警告) | 閾値(危険) | 確認クエリ |
|---|---|---|---|
| キャッシュヒット率 | < 95% | < 90% | pg_stat_database |
| コネクション使用率 | > 70% | > 90% | pg_stat_activity |
| デッドタプル率 | > 10% | > 20% | pg_stat_user_tables |
| ロック待ち数 | > 5 | > 20 | pg_locks |
| レプリケーション遅延 | > 1秒 | > 10秒 | pg_stat_replication |
| ディスク使用率 | > 70% | > 85% | OS level |
PostgreSQL 17の新パフォーマンス機能
主要な改善点
| 機能 | 説明 | パフォーマンス影響 |
|---|---|---|
| 増分バックアップ | 変更ブロックのみバックアップ | バックアップ時間短縮 |
| 並列VACUUM改善 | B-treeインデックスの並列VACUUM | メンテナンス時間短縮 |
| クエリプランナー改善 | 統計情報の精度向上 | プラン選択の最適化 |
| MERGE文の拡張 | RETURNING句サポート | UPSERT操作の効率化 |
| JSON機能の強化 | JSON_TABLE関数 | JSON処理の高速化 |
まとめ
PostgreSQLのパフォーマンスチューニングは、単一の設定変更ではなく、複数のレイヤーにわたる総合的なアプローチが求められます。
- クエリ最適化: EXPLAIN ANALYZEを活用し、アンチパターンを排除する
- インデックス戦略: B-tree、GIN、BRINを適材適所で使い分け、部分インデックスやカバリングインデックスを活用する
- サーバー設定: shared_buffers、work_mem、WAL設定をハードウェアに合わせて調整する
- メンテナンス: autovacuumを適切に設定し、テーブル肥大化を防ぐ
- パーティショニング: 大規模テーブルはパーティション分割で性能を維持する
- 監視: pg_stat_statements、Prometheus + Grafanaで継続的に性能を可視化する
チューニングは一度で完了するものではなく、データ量の増加やクエリパターンの変化に応じて継続的に見直すことが重要です。まずはpg_stat_statementsを有効にして、現状のボトルネックを把握するところから始めましょう。