postgres-tuning

PostgreSQL のチューニング

概要

I/O、クエリ実行、インデックス、メンテナンスにわたって、PostgreSQL 17/18 以降のパフォーマンスを最適化します。最新のストレージでスループットを向上させる、PostgreSQL 18 で導入されたネイティブ AIO サブシステム、EXPLAIN BUFFERS によるクエリプランの詳細な分析（PG18 では自動的に含まれます）、複合インデックスの B-tree スキップスキャン、ネイティブな UUIDv7 の生成、高頻度で更新されるテーブル向けの autovacuum のチューニングを扱います。

使用する場面： 遅いクエリの診断、非同期 I/O の設定、shared_buffers と work_mem のチューニング、書き込み負荷の高いワークロード向けのインデックス最適化、テーブルの肥大化の管理、pgvector HNSW のチューニング。

使用しない場面： スキーマ設計（データモデリングツールを使用してください）、アプリケーションレベルのキャッシュ戦略、データベースの選定、ORM のクエリ生成。

主な監視ビュー：

• pg_stat_statements — 累積実行時間に基づいて、遅いクエリのパターンを特定します
• pg_stat_io — バックエンドの種類、オブジェクト、コンテキスト別に I/O を詳細に分析します（PG16 以降）
• pg_stat_checkpointer — チェックポイントの頻度とタイミングを確認します（PG17 以降。それ以前は pg_stat_bgwriter に含まれていました）
• pg_stat_user_tables — 肥大化の検出と autovacuum の監視に使用するデッドタプル数を確認します
• pg_statio_user_tables — テーブルごとのバッファキャッシュヒット率を確認します
• pg_aios — 進行中の AIO 処理を確認します（PG18 以降）

クイックリファレンス

パターン	設定／クエリ	主なポイント
非同期 I/O	io_method = worker or io_uring	PG18 のデフォルトは worker です。io_uring は Linux 専用です（カーネル 5.1 以降、liburing のビルドフラグが必要です）。
I/O の並行性	io_max_concurrency and io_workers	io_workers のデフォルトは 3 です。io_max_concurrency のデフォルトは -1 です（自動計算）。
フォレンジック EXPLAIN	EXPLAIN (ANALYZE, BUFFERS, SETTINGS)	PG18 では ANALYZE を指定すると BUFFERS が自動的に含まれます。Shared Hit を 95% 超にすることを目指してください。
UUIDv7 主キー	DEFAULT uuidv7()	PG18 に組み込み済みです。時刻順で、セッション内では単調増加し、RFC 9562 に準拠しています。
B-tree スキップスキャン	(a, b) の複合インデックス	PG18 は先頭の列をスキップします。カーディナリティの低いプレフィックスと、後続列の等価条件で最も効果を発揮します。
積極的な autovacuum	autovacuum_vacuum_scale_factor = 0.01	デフォルトの 20% ではなく、行の変更が 1% に達した時点で実行されます。
Shared buffers	RAM の 25% から開始	ベンチマークを行わずに 40% を超えないでください。
work_mem の調整	セッションごとに SET work_mem = '64MB'	ソートのディスクへの書き出しを防ぎます。割り当てはクエリごとではなく、演算子ごとです。
BRIN インデックス	CREATE INDEX USING brin(...)	物理的に順序付けされた時系列データでは、B-tree より 100 倍小さくなります。
HNSW ベクトルインデックス	USING hnsw (col vector_cosine_ops)	再現率と速度のバランスを取るため、m（デフォルト 16）と ef_construction（デフォルト 64）を調整します。
GIN インデックス	CREATE INDEX USING gin(...)	JSONB の包含検索、全文検索、配列演算子に対応します。書き込みは遅くなります。
チェックポイントの調整	checkpoint_timeout = 30min	I/O ストームを避けるため、タイムアウト時間の 90% にわたって書き込みを分散させます。
WAL 圧縮	wal_compression = zstd	PG15 以降で利用できます。書き込み負荷の高いワークロードでは、WAL I/O を 50～70% 削減します。
Bloat の検出	pg_stat_user_tables.n_dead_tup	Bloat が 30% を超えた場合は、並行してインデックスを再構築します。
I/O の監視	SELECT * FROM pg_stat_io	evictions（キャッシュが小さすぎる状態）と extends（急速な増加）を確認してください。
チェックポイントの監視	pg_stat_checkpointer	PG17 以降では、チェックポイント統計が pg_stat_bgwriter から移動しています。

主なバージョン変更点

PostgreSQL 18：

• io_method パラメーターによるネイティブの非同期 I/O（読み取りのみ。書き込みは引き続き同期処理）
• セッション内で単調増加する順序を持つ組み込みの uuidv7() 関数（RFC 9562）
• gen_random_uuid() のエイリアスである uuidv4() と、UUIDv7 用の uuid_extract_timestamp()
• 複合インデックス向けの B-tree スキップスキャン（後続列の等価条件、カーディナリティの低い先頭列）
• EXPLAIN ANALYZE で、BUFFERS を指定しなくてもバッファー統計が自動的に含まれるように変更
• pg_stat_io にバイト単位の列（read_bytes、write_bytes、extend_bytes）を追加し、op_bytes を削除
• effective_io_concurrency のデフォルト値を 1 から 16 に変更
• 進行中の I/O 操作を確認するためのシステムビュー pg_aios による AIO の監視

PostgreSQL 17：

• チェックポイント統計を pg_stat_bgwriter から pg_stat_checkpointer に移動
• 列名を変更：checkpoints_timed から num_timed、checkpoints_req から num_requested
• buffers_backend と buffers_backend_fsync を pg_stat_bgwriter から削除（現在は pg_stat_io にあります）

PostgreSQL 15：

• wal_compression をブール値から拡張し、pglz、lz4、zstd アルゴリズムをサポート

よくある間違い

間違い	正しいパターン
順序付きキーに uuid_generate_v7() または gen_random_uuid() を使う	PG18 では時間順 UUID 用の組み込み関数 uuidv7() が利用できます。PG18 より前では pg_uuidv7 拡張機能を使います
設定パラメーターとして max_async_ios を使う	正しい PG18 パラメーターは io_max_concurrency です（プロセスごとの最大同時 I/O 操作数）
PG17 以降でチェックポイント統計を取得するために pg_stat_bgwriter を照会する	チェックポイント統計は PG17 で pg_stat_checkpointer に移動し、列名も変更されています（num_timed、num_requested）
高頻度クエリで SELECT * を使う	必要な列だけを選択し、I/O を減らしてキャッシュヒット率を向上させます
1 万行を超えるテーブルでシーケンシャルスキャンを無視する	WHERE、ORDER BY、JOIN 句で使われる列に適切なインデックスを追加します
テストせずに shared_buffers を RAM の 40% より大きく設定する	25% から始めてベンチマークします。割り当てが過剰だと OS のページキャッシュとの競合が発生します
更新頻度の高いテーブルで autovacuum をデフォルト設定のままにする	UPDATE/DELETE が頻繁なテーブルでは autovacuum_vacuum_scale_factor を 0.01 に調整します
クエリでほとんど使わない列にインデックスを付けすぎる	インデックスを追加するたびに UPDATE/INSERT が遅くなり、HOT（Heap Only Tuple）更新が妨げられます
B-tree スキップスキャンが範囲述語でも機能すると想定する	PG18 のスキップスキャンは、後続列に対する等価演算子でのみ機能します
クエリプランの「External Merge Disk」を無視する	特定のセッションで work_mem を増やします。これはソート結果がディスクにスピルしていることを示します
ビルドフラグを確認せずに io_method = io_uring を設定する	PostgreSQL は --with-liburing を付けてビルドする必要があり、Linux カーネル 5.1 以降が必要です
PG18 の AIO が書き込みを高速化すると想定する	PG18 の AIO は読み取り（シーケンシャルスキャン、ビットマップヒープスキャン、VACUUM）のみを対象とし、書き込みは引き続き同期処理です

ワークフローのチューニング

1. pg_stat_statementsから遅いクエリを特定します（total_exec_timeで並べ替えます）
2. EXPLAIN (ANALYZE, BUFFERS, SETTINGS)で実行計画を分析します
3. pg_statio_user_tablesでバッファーヒット率を確認します（目標は99%超です）
4. pg_stat_ioでI/Oパターンを監視します（エビクションとディスク読み取りに注意します）
5. 対象を絞ったインデックス、work_memの調整、またはクエリの書き換えで最適化します
6. EXPLAINを再実行してコストを比較し、改善を確認します
7. 更新頻度の高いテーブルには積極的なautovacuum設定を適用して保守します

委任

• 遅いクエリとI/Oのボトルネックを特定する：Exploreエージェントを使用して、pg_stat_statements、pg_stat_io、遅いクエリのログを分析します
• クエリプランの分析とインデックスの最適化を実行する：Taskエージェントを使用して、EXPLAIN ANALYZEの実行、インデックスの作成、パフォーマンス改善の確認を行います
• データベースのスケーリングとパーティショニング戦略を設計する：Planエージェントを使用して、シャーディング、パーティショニング、レプリケーショントポロジーを設計します

参考資料

• 非同期I/Oの設定とストレージのチューニング
• クエリプランの分析とオペレーターのフォレンジック
• インデックス戦略と肥大化の管理
• コネクションプーリング、パーティショニング、クエリパターン