Postgres レイテンシーを削減するプロダクションチューニング

postgresql.confは、デフォルト状態では、インストールに失敗せず、トラフィックを吸収しないように設計されています。 shared_buffersの過去の値である 128 MB により、Postgres は重要なリソースを予約せずに最小限のマシンで起動できます。 100 の max_connections は小規模な共有サーバーに適合します。どちらも実際の負荷に対して選択されていません。

したがって、問題は、Postgres のデフォルト設定が不十分であることではなく、Postgres がユーザー向けに設定されていないことです。次のセクションでは、最も一般的なボトルネック (接続) から最も遅いボトルネック (WAL) まで、効果が最も高い順に設定を説明します。

最初のプロジェクトは記憶ではなく、つながりです。各 Postgres 接続は、アイドル状態であっても RAM とコンテキスト CPU 時間を消費する専用のサーバー プロセスを開きます。 「接続が多すぎる」タイプのエラーを回避するために max_connections を増やすと、問題が変わります。同時にアクティブな接続が一定数を超えると、CPU 競合により、最速のリクエストを含むすべてのリクエストのレイテンシが低下します。

正しいアプローチは通常の順序を逆にします。実際のサーバーの同時実行性で max_connections をスケールし、次に PgBouncer のようなプーラーを使用してアプリケーション側の同時実行性を pool_mode=transactionに吸収します。プーラーは、数百のクライアント接続を実際にアクティブな少数のサーバー接続に多重化します。私たちの専用記事では、トランザクション モード メカニズムとその制限 (準備されたステートメント、LISTEN/NOTIFY)、および実装を選択するための PgBouncer、Supavisor、および PgCat の比較について詳しく説明しています。

max_connections は「ポストマスター」コンテキスト パラメーターです。これを変更するには、単純なリロードではなく、サーバーを完全に再起動する必要があります。 Aurabase 専用 Postgres インスタンス (Pro および Enterprise プラン、プロジェクトごとに 1 つの CNPG クラスター) では、この設定はデフォルト値のままではなく、プラン層ごとに構成されます。再起動は運用環境で繰り返す簡単な操作ではないため、固定値ではなく段階的にこの選択を行うことが正当化されます。独自の値を選択する式とその制限については、別の記事で説明します: size max_connections。

shared_buffers、 effective_cache_size、 work_mem 、 maintenance_work_memという 4 つのメモリ設定は、他のすべての設定を組み合わせたものよりも重みが高くなります。最初の 3 つは、Postgres がディスクに戻る前にメモリに保持するデータ量を決定します。 4 番目の値は、VACUUM またはインデックス作成の速度を決定します。

shared_buffers は、すべての接続で共有される内部キャッシュを設定します。 PostgreSQL プロジェクトで一般的に文書化されているベンチマークは、専用データベース サーバーで利用可能な RAM の約 25% です。それを超えると、ゲインは減少し、オペレーティング システムのディスク キャッシュが引き継ぎます。 effective_cache_size は何も割り当てません。これは、クエリ プランナーに与えられる、キャッシュに使用できる合計メモリ (Postgres と OS を合わせたもの) の推定値です。サイズを小さくすると、インデックスの大部分がキャッシュされるのに対し、スケジューラは順次スキャンを行うようになります。現在のベンチマークは RAM の 50 ～ 75% です。

work_mem は最も一般的なトラップです。これは世界的な制限ではありません。クエリ内の各ソートまたはハッシュ操作は、その独自のシェアを消費する可能性があり、複数の結合を含むクエリは独自のシェアを複数回予約することができます。値が大きすぎると、高い max_connections を組み合わせると、たとえ各リクエストが個別に取得されるのが合理的であるように見えても、同時負荷の下でサーバー RAM を使い果たす可能性があります。 逆に、 maintenance_work_memは、大幅に寛大なままにすることができます。つまり、互いに同時実行されることはほとんどないメンテナンス操作 (VACUUM、CREATE INDEX) にのみ適用されます。

shared_buffers のみ再起動が必要です。他の 3 つは、グローバル設定に影響を与えることなく、単一の貪欲なリクエストの間、分離セッション SET work_mem = '64MB'; を含めてホット リロードされます。

本番環境では、負荷のピーク時に「リソースを解放する」ために一時的にであっても自動バキュームを無効にしないでください。 Postgres は MVCC を使用します。各 UPDATE と各 DELETE には、バキュームのみが回復できるデッドラインが残されます。これがないと、テーブルが肥大化し、インデックスが劣化し、実行計画が徐々に悪化し、遅くなるまでエラーが表示されなくなります。

デフォルト値のautovacuum_vacuum_scale_factor (トリガー前に 20% のデッド行) は、書き込みが集中する数百万行のテーブルではなく、小さなテーブルに適しています。 1,000 万行のテーブルでは、この 20% は、最初のパスの前に蓄積された 200 万行の無効行を表します。データベース全体の全体的な値を変更するのではなく、テーブルごとにこのしきい値を下げます。

-- 全体ではなく、書き込みの多いテーブルのしきい値を下げる
ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.05,
  autovacuum_vacuum_cost_limit = 2000
);

Aurabase Studio に統合されたアドバイザは、主キーやインデックスのない外部キーのないテーブルと同じ方法で、プロジェクト分析のたびにこの設定をチェックします。これは、発見が遅すぎたサイレントな劣化ではなく、明示的なシグナルです。

運用環境におけるレイテンシの問題のほとんどは、CPU や RAM に起因するものではなく、インデックスが存在しないか、インデックスが適切に選択されていないことが原因です。 postgresql.confの単一パラメータを操作する前に、問題のクエリに対する EXPLAIN (ANALYZE, BUFFERS) が最も信頼できる診断です。リソースを追加する前に、Postgres リクエストのレイテンシが大幅に短縮されます。

-- 構成に触れる前に遅いクエリを診断する
EXPLAIN (ANALYZE, BUFFERS)
SELECT o.id, o.total
FROM orders o
WHERE o.customer_id = '…'
ORDER BY o.created_at DESC
LIMIT 20;

Index Scan が予期されていた数百万行のテーブル上の Seq Scan は、ほとんどの場合、インデックスの問題を示します。最もよく考えられる原因は 3 つあります。それは、インデックスの欠落、既存のインデックスと互換性のない列タイプ、または ANALYZEを使用しない大量のインポート後の古い統計です。 RAM を追加するか work_mem を増やすと、少量のデータではこの症状が隠れる場合があります。テーブルが大きくなるとすぐに問題が再発します。

これらのリクエストを 1 つずつ検索せずに見つけるために、pg_stat_statements はすべてのサーバーのリクエストの実行統計を集計します。よくある落とし穴: 拡張機能は、最初に shared_preload_libraries(再起動が必要な「ポストマスター」コンテキスト パラメーター) にリストされている必要があります。この手順を行わないと、CREATE EXTENSION pg_stat_statements; は静かに成功しますが、何も収集しません。

# 完全な再起動が必要です (「postmaster」パラメータ)
shared_preload_libraries = 'pg_stat_statements'

-- サーバーが再起動したら、各データベースで以下を監査します。
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

SELECT query, calls, mean_exec_time, max_exec_time
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

これはまさに、この拡張機能が存在しない場合に Aurabase バックエンドが返すエラーです。サイレントな空のリストではなく明示的なメッセージであり、「低速クエリなし」と混同される可能性があります。 Studio Advisor はさらに進んで、平均時間が 150 ミリ秒を超えるリクエストを警告として、500 ミリ秒を超えるリクエストをクリティカルとして自動的に分類します。これらのしきい値は、同じ pg_stat_statements統計に基づいています。

チェックポイントにより、Postgres は、前のページ以降にメモリ内で変更されたすべてのページをディスクに書き込むように強制されます。デフォルトでは、この記事はあまりにも短い時間枠に焦点を当てている可能性があります。その結果、アプリケーション側のディスク遅延が顕著に急増します。これは、特定のリクエストに関連付けることが難しい、周期的な速度低下の一種です。

checkpoint_completion_target は、2 つのチェックポイント間の間隔にわたるこの書き込みの広がりを制御します。古いチェックリストに欠けている詳細: 2021 年にリリースされた PostgreSQL 14 では、デフォルト値が 0.5 から 0.9 に変更されました。したがって、専用の Aurabase テナントクラスタなどの PostgreSQL 16 インスタンスでは、この設定はデフォルトですでに正しい設定になっています。手動で調整することは、14 より前のバージョンでのみ意味があります。チューニングに影響する他のバージョンの変更については、PostgreSQL 16 対 17 対 18 の比較を参照してください。

max_wal_size も同じ方向に動作します。値が低すぎると、checkpoint_timeout にまだ達していない場合でも、予想よりも頻繁にチェックポイントがトリガーされます。この値を増やすと、チェックポイントの頻度が減りますが、再生する WAL の数が増えるため、クラッシュ後の回復時間が長くなります。妥協点は、普遍的な価値ではなく、利用できないことに対する許容度に応じて決定される必要があります。

このチェックリストは、本番環境に入る前に一度だけチェックを入れる 1 回限りの監査ではありません。ベースのボリュームが 2 倍になったり、トラフィックが 3 倍になったりすると、起動時に選択されたベンチマークが時代遅れになり、多くの場合、明示的なエラーはなく、p95 レイテンシが徐々に低下するだけです。

3 つの信号は継続的に監視する価値があります。 pg_stat_statements は、時間の経過とともに低下するリクエストを識別します。 pg_stat_activity は、ブロックされたクエリまたは異常に長時間実行されているクエリを報告し、max_connections に対するアクティブな接続の比率は、アプリケーション側のエラーが発生する前に飽和を予測します。

Studio の Observability タブは、Aurabase プロジェクトのこの基盤の一部をカバーしており、サードパーティのツールをインストールする必要はありません。遅いリクエストをリストし、PID によってアクティブなリクエストをキャンセルまたは終了できるようにし、使用率が 80% を超えると警告となるプール飽和インジケーターを表示します。自己ホスト型インスタンスでは、これと同じ監視が手動で構築され、pg_stat_statements がアクティブ化され、外部監視ツールが接続されます。

8 つの設定を、効果が最も高い順に示し、設定する前に知っておくべきことを説明します。

Aurabase Studio Advisor が監視する 150 ミリ秒や 80% のプール飽和など、ここで挙げたしきい値はコード検証済みの出発点であり、普遍的な真実ではありません。あなたの実際の告発は依然として唯一の最終的な裁判官です。一般的なルールに従うのではなく、max_connections のサイズを正確に設定するには、専用の記事 で公式とその制限を詳しく説明します。

チェックリストを初めて適用すると、系統的に現れる 3 つの質問。