PostgreSQLパフォーマンスのボトルネック上位7つとその解決策

PostgreSQLのパフォーマンス作業は、遅いリクエストに対して「インデックスを追加」や「メモリを増やす」という同じ答えが返ってくるとうまくいきません。それが正しい場合もあります。しかし、データベースがロックを待っていたり、ソートをディスクに溢れさせていたり、アイドル接続で溢れていたり、自動バキュームが遅れているために本来より10倍も多くのテーブルページを読み込んでいることもあります。

役立つ習慣は、何かを変更する前にボトルネックを特定することです。遅いAPIエンドポイントは単なる症状です。データベースは通常、時間がスキャン、結合、ソート、ディスクからの読み取り、他のトランザクションの待機、または過剰なセッションのオープンに費やされたかどうかを教えてくれます。

1. 非効率なクエリ実行計画

パフォーマンス低下の最も頻繁な原因の1つは、最適化されていないSQLクエリです。PostgreSQLのクエリプランナーは高度ですが、特に複雑なクエリや古い統計情報がある場合、非効率な実行計画を生成することがあります。

ボトルネックの特定

EXPLAINとEXPLAIN ANALYZEを使用して、PostgreSQLがクエリをどのように実行するかを理解します。EXPLAINは計画された実行を示し、EXPLAIN ANALYZEは実際にクエリを実行し、実際のタイミングと行数を提供します。

-- 実行計画を表示するには:
EXPLAIN SELECT * FROM users WHERE email LIKE 'john.doe%';

-- 計画と実際の実行詳細を表示するには:
EXPLAIN ANALYZE SELECT * FROM users WHERE email LIKE 'john.doe%';

以下の点に注目してください:

インデックスが有効な大規模テーブルでのシーケンシャルスキャン。
実際の行数と比較した大きな行推定誤差。
ハッシュ結合やマージ結合がより適切な場合のネステッドループ結合。

解決策

適切なインデックスを追加する: WHERE、JOIN、ORDER BY、GROUP BY句で使用されるカラムにインデックスが存在することを確認します。先頭にワイルドカード（%）があるLIKE句の場合、B-treeインデックスは効果的でないことが多いため、全文検索やトライグラムインデックスを検討してください。
クエリを書き換える: よりシンプルまたは異なる構造のクエリが、より良い計画につながることがあります。
統計情報を更新する: PostgreSQLは統計情報を使用して述語の選択性を推定します。古い統計情報はプランナーを誤った方向に導く可能性があります。
```
ANALYZE table_name;
-- またはすべてのテーブルに対して:
ANALYZE;
```
クエリプランナーパラメータを調整する: work_memとrandom_page_costはプランナーの選択に影響を与える可能性がありますが、これらは慎重に調整する必要があります。

2. インデックスの欠如または非効率性

インデックスは高速なデータ検索に不可欠です。インデックスがないと、PostgreSQLはシーケンシャルスキャンを実行し、テーブルのすべての行を読み取って一致するデータを見つける必要があり、大規模テーブルでは非常に遅くなります。

ボトルネックの特定

EXPLAIN ANALYZEの出力: クエリ計画で大規模テーブルに対するSeq Scanを探します。
データベース監視ツール: pg_stat_user_tablesのようなツールは、テーブルスキャン回数を表示できます。

解決策

B-treeインデックスを作成する: これらは最も一般的なタイプで、等価（=）、範囲（<、>、<=、>=）、およびLIKE（先頭ワイルドカードなし）操作に適しています。
```
CREATE INDEX idx_users_email ON users (email);
```
他のインデックスタイプを使用する:
- GIN/GiST: 全文検索、JSONB操作、幾何データ型に使用します。
- ハッシュインデックス: 等価チェックに使用します（新しいPostgreSQLバージョンではB-treeの改善によりあまり一般的ではありません）。
- BRIN（ブロック範囲インデックス）: 物理的に相関のあるデータを持つ非常に大規模なテーブルに使用します。
部分インデックス: 行のサブセットのみにインデックスを作成します。クエリが特定の条件を頻繁にターゲットとする場合に便利です。
```
CREATE INDEX idx_orders_pending ON orders (order_date) WHERE status = 'pending';
```
式インデックス: 関数や式の結果にインデックスを作成します。
```
CREATE INDEX idx_users_lower_email ON users (lower(email));
```
冗長なインデックスを避ける: インデックスが多すぎると、書き込み操作（INSERT、UPDATE、DELETE）が遅くなり、ディスク容量を消費します。

3. 過剰な自動バキュームアクティビティまたは枯渇

PostgreSQLはマルチバージョン同時実行制御（MVCC）システムを使用しており、UPDATEおよびDELETE操作は行をすぐに削除しません。代わりに、それらを廃止済みとしてマークします。VACUUMはこの領域を再利用し、トランザクションIDの周回を防ぎます。自動バキュームはこのプロセスを自動化します。

ボトルネックの特定

高いCPU/IO負荷: 自動バキュームはリソースを大量に消費する可能性があります。
テーブルの肥大化: pg_class.relpagesとpg_class.reltuplesに実際のデータサイズや期待される行数との大きな不一致として現れます。
pg_stat_activity: 長時間実行されているautovacuum workerプロセスを探します。
pg_stat_user_tables: n_dead_tup（デッドタプル数）とlast_autovacuum/last_autoanalyzeの時間を監視します。

解決策

自動バキュームパラメータを調整する: postgresql.confまたはテーブルごとの設定で調整します。
- autovacuum_vacuum_threshold: バキュームをトリガーするデッドタプルの最小数。
- autovacuum_vacuum_scale_factor: バキュームを考慮するテーブルサイズの割合。
- autovacuum_analyze_thresholdとautovacuum_analyze_scale_factor: ANALYZEの同様のパラメータ。
- autovacuum_max_workers: 並列自動バキュームワーカーの数。
- autovacuum_work_mem: 各ワーカーが利用可能なメモリ。
テーブルごとの設定例:
```
ALTER TABLE large_table SET (autovacuum_vacuum_scale_factor = 0.05, autovacuum_analyze_scale_factor = 0.02);
```
手動VACUUM: 即時の領域再利用や、自動バキュームが追いつかない場合に使用します。
```
VACUUM (VERBOSE, ANALYZE) table_name;
```
VACUUM FULLは、テーブルをロックしてテーブル全体を書き換えるため、非常に破壊的になる可能性があるため、絶対に必要な場合にのみ使用してください。
古いトランザクションを監視する: 長時間実行されるトランザクションは、古い行バージョンを開いたまま保持し、クリーンアップを妨げる可能性があります。
トランザクションIDの経過時間を監視する: vacuum_freeze_min_age、autovacuum_freeze_max_age、およびデータベースage(datfrozenxid)を理解することは、周回の緊急事態を防ぐために重要です。

4. 不十分なハードウェアリソース（CPU、RAM、IOPS）

PostgreSQLのパフォーマンスは、基盤となるハードウェアに直接関係しています。不十分なCPU、RAM、または遅いディスクI/Oは、重大なボトルネックを引き起こす可能性があります。

ボトルネックの特定

システム監視ツール: Linuxではtop、htop、iostat、vmstat。Windowsではパフォーマンスモニター。
pg_stat_activity: ロックを待っているクエリを探します（wait_event_type = 'IO'、'LWLock'など）。
高いCPU使用率: 一貫して100%近く。
高いディスクI/O待機時間: ディスク操作を待つことに多くの時間を費やしているシステム。
利用可能なメモリが少ない/スワップ使用量が多い: RAMが不十分であることを示します。

解決策

CPU: 特に同時ワークロードに対して、十分なコア数があることを確認します。PostgreSQLは、並列クエリ実行（新しいバージョン）およびバックグラウンドプロセスに複数のコアを効果的に利用します。
RAM（shared_buffers、work_mem）:
- shared_buffers: データブロックのキャッシュ。一般的な推奨はシステムRAMの25%ですが、ワークロードに基づいて調整します。
- work_mem: ソート、ハッシュ、その他の中間操作に使用されます。不十分なwork_memはディスクへのスピルを強制します。
ディスクI/O:
- SSDを使用する: データベースワークロードにおいてHDDよりも大幅に高速です。
- RAID構成: 読み取り/書き込みパフォーマンスを最適化します（例：RAID 10）。
- WALドライブを分離する: ライトアヘッドログ（WAL）を別の高速ドライブに配置すると、書き込みパフォーマンスが向上します。
ネットワーク: 分散環境では、クライアントとサーバー間の通信に十分な帯域幅と低レイテンシを確保します。

ハードウェアの症状には証拠が必要です。CPUが高く、ディスク待機が低い場合は、高価な計画、式の多いクエリ、JSON処理、またはアクティブなワーカーが多すぎることを探します。I/O待機が高い場合は、EXPLAIN (ANALYZE, BUFFERS)のバッファ読み取り、チェックポイント動作、およびホットテーブルがメモリに収まるかどうかを確認します。スワップがアクティブな場合は、クエリの同時実行性を追加する前に、接続負荷またはメモリ設定を減らします。

5. `postgresql.conf`の不適切な設定

PostgreSQLのpostgresql.confファイルには、その動作を制御する数百のパラメータが含まれています。デフォルト設定は控えめであることが多く、特定のワークロードやハードウェアに最適化されていません。

ボトルネックの特定

全体的な動作の鈍さ: 全体的にクエリ時間が遅い。
過剰なディスクI/O: 利用可能なRAMと比較して。
メモリ使用量: メモリ負荷の兆候を示すシステム。
パフォーマンスチューニングガイドの参照: 一般的な最適値の理解。

解決策

考慮すべき主要パラメータ:

shared_buffers: （上記参照）データブロックのキャッシュ。システムRAMの約25%から始めます。
work_mem: ソート/ハッシュ用のメモリ。ディスクスピルを示すEXPLAIN ANALYZEの出力に基づいて調整します。
maintenance_work_mem: VACUUM、CREATE INDEX、ALTER TABLE ADD FOREIGN KEY用のメモリ。値を大きくすると、これらの操作が高速化されます。
effective_cache_size: OSとPostgreSQL自体がキャッシュに利用できるメモリ量をプランナーが推定するのに役立ちます。
wal_buffers: WAL書き込み用のバッファ。書き込み負荷が高い場合は増やします。
checkpoint_completion_target: チェックポイントの書き込みを時間的に分散させ、I/Oスパイクを減らします。
max_connections: 適切に設定します。高すぎるとリソースを枯渇させる可能性があります。
log_statement: デバッグに便利ですが、すべてのステートメントをログに記録するとパフォーマンスに影響を与える可能性があります。

ヒント: pgtuneのようなツールを使用して、ハードウェアに基づいた初期推奨値を取得します。本番環境に適用する前に、必ずステージング環境で変更をテストしてください。

PostgreSQL設定の1つの落とし穴は、すべての設定をスピード調整用のノブとして扱うことです。work_memは良い例です。これはサーバー全体で1回ではなく、操作ごとに割り当てられます。1つのクエリで複数回使用される可能性があり、多くの同時クエリがそれを急速に増幅させる可能性があります。グローバルに4MBから128MBに上げると、1つのレポートクエリには役立つかもしれませんが、トラフィック中にサーバー全体に悪影響を及ぼす可能性があります。既知のクエリをテストするには、まずセッションレベルの変更を使用します:

SET work_mem = '128MB';
EXPLAIN (ANALYZE, BUFFERS)
SELECT ...

計画がディスクへのスピルを停止し、レイテンシが改善された場合、有用なことがわかりました。クエリを書き換えるか、インデックスを追加するか、レポートロール用にメモリを設定するか、グローバル値を変更するかを決定する必要があります。

6. コネクションプーリングの問題

新しいデータベース接続の確立は高コストな操作です。頻繁で短命なデータベースインタラクションを行うアプリケーションでは、接続のオープンとクローズを繰り返すことが、重大なパフォーマンスボトルネックになる可能性があります。

ボトルネックの特定

高い接続数: pg_stat_activityに非常に多くの接続が表示され、その多くがアイドル状態です。
アプリケーションの起動/応答時間が遅い: データベース接続が頻繁に行われる場合。
サーバーリソースの枯渇: 接続管理に起因する高いCPUまたはメモリ使用率。

解決策

コネクションプーリングを実装する: PgBouncerやOdysseyなどのコネクションプーラーを使用します。これらのツールは、オープンなデータベース接続のプールを維持し、受信するクライアントリクエストにそれらを再利用します。
- PgBouncer: 軽量で高性能なコネクションプーラー。トランザクション、セッション、またはステートメントプーリングモードで動作できます。
- Odyssey: SCRAM-SHA-256などのプロトコルをサポートする、より現代的で機能豊富なコネクションプーラー。
プーラーを適切に設定する: プールサイズ、タイムアウト、プーリングモードをアプリケーションのニーズとデータベース容量に基づいて調整します。
アプリケーション側のプーリング: 一部のアプリケーションフレームワークは、組み込みのコネクションプーリング機能を提供します。これらが正しく設定されていることを確認します。

コネクションプーリングの問題は、デプロイメントのスケールアウト後に現れることがよくあります。20接続のプールを持つ1つのアプリケーションインスタンスは問題ないかもしれません。同じプール設定を持つ30のインスタンスは、実際のトラフィックが到着する前に600のデータベースセッションを作成する可能性があります。PostgreSQLは接続ごとにプロセスを使用するため、アイドルセッションは無料ではありません。アプリケーションプールを小さく保ち、多くの短命なリクエストが予想される場合はPgBouncerを前面に配置し、アプリケーション名でpg_stat_activityを監視して、誰がセッションを所有しているかを把握します。

7. ロック競合

複数のトランザクションが同時に同じデータにアクセスして変更しようとすると、競合するロックを取得する場合に互いに待機する必要があります。過剰なロック競合は、アプリケーションを停止状態に陥らせる可能性があります。

ボトルネックの特定

pg_stat_activity: wait_event_typeがLockである行を探します。
アプリケーションパフォーマンスの低下: 特定の操作が非常に遅くなります。
デッドロック: トランザクションが互いに無限に待機する状態。
長時間実行トランザクション: ロックを長時間保持します。

解決策

トランザクションを最適化する: トランザクションを短く簡潔に保ちます。できるだけ早くコミットまたはロールバックします。
アプリケーションロジックをレビューする: 潜在的な競合状態や非効率なロックパターンを特定します。
適切なロックレベルを使用する: PostgreSQLはさまざまなロックレベルを提供します（例：ACCESS EXCLUSIVE、ROW EXCLUSIVE、SHARE UPDATE EXCLUSIVE）。必要な最小限の制限的なロックを理解して使用します。
SELECT ... FOR UPDATE / SELECT ... FOR NO KEY UPDATE: トランザクションが完了する前に他のトランザクションが行を変更するのを防ぐために、行をロックする必要がある場合に慎重に使用します。
定期的にVACUUMを実行する: 前述のように、VACUUMはデッドタプルのクリーンアップに役立ち、長時間のVACUUM操作を防ぐことで、間接的にロック競合を減らすことができます。

pg_locksを確認する: pg_locksをクエリして、どのプロセスが他のプロセスをブロックしているかを確認します。

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_statement,
       blocking_activity.query AS current_statement_in_blocking_process
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.page IS NOT DISTINCT FROM blocked_locks.page
    AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
    AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
    AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
    AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
    AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
    AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
    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;

PostgreSQLが遅くなった場合、システムを変更する前に証拠を収集します：ワークロード形状のためのpg_stat_statements、クエリパスのためのEXPLAIN (ANALYZE, BUFFERS)、待機と接続のためのpg_stat_activity、CPU、メモリ、I/Oのためのホストメトリクス。時間が実際にどこに費やされているかがわかれば、修正方法ははるかに明確になります。

PostgreSQLパフォーマンスのボトルネック上位7つとその解決策

1. 非効率なクエリ実行計画

ボトルネックの特定

解決策

2. インデックスの欠如または非効率性

ボトルネックの特定

解決策

3. 過剰な自動バキュームアクティビティまたは枯渇

ボトルネックの特定

解決策

4. 不十分なハードウェアリソース（CPU、RAM、IOPS）

ボトルネックの特定

解決策

5. postgresql.confの不適切な設定

ボトルネックの特定

解決策

6. コネクションプーリングの問題

ボトルネックの特定

解決策

7. ロック競合

ボトルネックの特定

解決策

5. `postgresql.conf`の不適切な設定