16個のロックがあれば十分なはず

PostgreSQLに関する、長年運用しているユーザーでさえ驚くような事実があります。それは、テーブルに対するすべてのクエリが、そのテーブル上のすべてのインデックスに対してロックを取得するということです。たとえクエリがそれらのインデックスのどれも使用しない場合であってもです。通常、これは無害です。ロックは最も弱いロックであるAccessShareLockであり、ほとんどのものと競合せず、取得コストも低いです。しかし、「安い」は「無料」ではなく、インデックス数とクエリレートの特定の組み合わせでは、この動作は、単一行の主キー検索のような無害なクエリでさえ、CPUを消費するロック競合問題に変わります。 プランナーは参照するすべてをロックします PostgreSQLがクエリを計画する際、クエリが使用する可能性のあるすべてのリレーションに対してAccessShareLockを取得します。これは、プランナーが各インデックスを開いてその有用性を判断する必要があるため、テーブルとそのすべてのインデックスを意味します。最終的にそのうちの1つだけを使用するとしても関係ありません。検討にはロックが必要です。したがって、主キーと20個のセカンダリインデックスを持つテーブルは、クエリごとに22個のリレーションロックを必要とします。プランナーが検討し、破棄したテーブル、主キーインデックス、およびその他の20個のインデックスです。これは実行ごとに発生します。 高速パスとそこからの逸脱 ロックの取得は通常、共有メモリに存在し軽量ロック（16個のパーティションに分割され、それぞれが独自のLWLockを持つ）で保護されている共有ロックテーブルのエントリを意味します。多数のコアを持つマシンでクエリレートが高い場合、これらの16個のLWLock自体が競合のポイントになります。PostgreSQLには、9.2で追加されたこのための最適化があります。それが高速パスロッキングです。各バックエンドは、共有ロックテーブルに触れることなく、弱いリレーションロック（AccessShareLock、RowShareLock、RowExclusiveLock）を記録できる小さなプライベート配列を持っています。PostgreSQL 17まで、その配列には正確に16個のスロットがありました。16個です。インデックスの数を数えてください。クエリが16個を超えるリレーションロックを必要とする場合、オーバーフローはLWLockなどを含む共有ロックテーブルに送られます。これを実行するバックエンドが1つであれば問題ありません。数百のバックエンドが毎秒数千回これを実行すると、pg_stat_activity（PostgreSQL 13より前はlock_managerと表記）のLWLock:LockManager待機、CPUのピン留め、およびクエリ自体が単純なままでスループットが低下するという状況になります。RDSやAuroraで実行している場合、これはPerformance Insightsが警告的な茶色で表示するのが好きなLWLock:LockManager待機イベントです。この障害モードの厄介な特性は、それがまさに間違ったテーブルに集中することです。インデックスが多すぎるテーブルは、すべてのクエリがアクセスするコアテーブルであり、すべてのクエリがその代償を支払うため、競合は総クエリレートとともにスケールします。パーティション化されたテーブルは、プランナーが各パーティションとそのインデックスをロックする必要があるため、さらに早くこの状態になります。 発生の観察 これは簡単に実証できます。主キーと20個の単一列インデックスを持つusersテーブルを作成し（本番環境でこれより悪い例を見たことがありますし、あなたも見たことがあるでしょう）、想像できる最も退屈なクエリを実行します。 ```sql BEGIN; SELECT * FROM users WHERE id = 42; SELECT fastpath, count(*) FROM pg_locks WHERE pid = pg_backend_pid() AND locktype = 'relation' AND relation <> 'pg_locks'::regclass GROUP BY 1; ``` PostgreSQL 16では、単一行の主キー検索が22個のリレーションロックを取得し、高速パスの16個のスロットをすべて満たし、6個のロックを共有テーブルに押し出します。実行ごとに、永遠に。 準備済みステートメント、偶発的な修正 次に、明白ではない部分です。準備済みステートメントは、ロック規則に関する理由ではなく、この問題を解消します。準備済みステートメントは、最初の5回の実行に対してカスタムプランで計画されます。6回目には、PostgreSQLは汎用プランを構築し、キャッシュし（主キー検索の場合のようにコスト比較がうまくいけば）、それ以降再利用します。キャッシュされたプランの再利用はプランナーを完全にスキップし、キャッシュされたプランの実行はプランナーが検討したすべてではなく、実際にプランに含まれるリレーションのみをロックします。 ```sql PREPARE u(bigint) AS SELECT * FROM users WHERE id = $1; -- カスタムプランフェーズを通過するために6回実行し、その後： BEGIN; EXECUTE u(42); SELECT relation::regclass AS relation, fastpath FROM pg_locks WHERE pid = pg_backend_pid() AND locktype = 'relation' AND relation <> 'pg_locks'::regclass; ``` 22個のロックが2つになり、どちらも快適に高速パス上にあり、共有ロックマネージャーはこのクエリから二度と通知を受け取ることはありません。ボーナスとして、クエリの再計画にかかるコストも停止しました。これは、インデックスが過剰なテーブルではそれ自体でかなりのCPU使用量です。 2つの注意点があります。第一に、汎用プランにはよく知られた障害モードがあります。パラメータの分布が偏っていると、特定の値に対して壊滅的に間違ったプランが得られる可能性があります。plan_cache_modeは、これが問題となる場合に利用できますが、正直な答えは、ホットなクエリをテストすることです。第二に、より厄介なのは、準備済みステートメントとコネクションプーラーは複雑な関係を持っているということです。PgBouncerのトランザクションプーリングモードは、1.21以降、max_prepared_statementsを介してプロトコルレベルの準備済みステートメントのみをサポートしています。古いバージョンを使用している場合は、アップグレードしてください。RDS Proxyはさらに悪いです。準備済みステートメントはクライアントセッションをバックエンド接続にピン留めするため、RDS Proxyが存在する理由である多重化が無効になります。アーキテクチャがRDS Proxyに依存している場合、この修正は事実上テーブルから外され、次のセクションに特に興味を持って読むべきです。 PostgreSQL 18はマジックナンバーを削除します PostgreSQL 18では、Tomas Vondra氏の作業のおかげで、高速パス配列はもはや16スロットに固定されなくなりました。サーバー起動時にmax_locks_per_transactionからサイズが決定され、16スロットのグループで構成されるため、デフォルトの64は各バックエンドに64個の高速パススロットを提供します。同じテーブル、同じクエリ、準備済みステートメントなしで： ```sql SHOW max_locks_per_transaction; -- 結果: 64 BEGIN; SELECT * FROM users WHERE id = 42; SELECT fastpath, count(*) FROM pg_locks WHERE pid = pg_backend_pid() AND locktype = 'relation' AND relation <> 'pg_locks'::regclass GROUP BY 1; ``` すべての22個のロックが、デフォルトで高速パス上にあります。（18でmax_locks_per_transaction = 16に設定すると、歴史的な災害を再現するのが好きなら、古い16/6の分割に戻ります。）このための専用ノブはなく、高速パス容量はmax_locks_per_transactionに付随しており、その主な仕事は共有ロックテーブルのサイジングです。スキーマがパーティション化されているか、インデックスが多い場合は、クエリあたりの最悪ケースのリレーション数を超えて引き上げてください。高速パス配列自体はほとんどコストがかかりません。共有ロックテーブルのエントリはより大きいですが、この問題が存在するマシンであれば、メモリはあります。 あるいは、聞いてください：インデックスを減らす 上記の2つの修正は症状を治療します。病気は21個のインデックスを持つテーブルです。過剰なインデックスの標準的なコストはすでに知っています。すべてのINSERTおよび非HOT UPDATEはすべてのインデックスを維持し、VACUUMはすべてのインデックスを処理する必要があり、それぞれがストレージとバッファキャッシュを占有します。この1つをリストに追加してください。PostgreSQL 17まで、ホットテーブル上の15番目のインデックスを超えるすべてのインデックスは、それに対するすべてのクエリをロック高速パスから外し、ペナルティはそれを実行したクエリだけでなく、システム全体での共有メモリ競合で支払われます。pg_stat_all_indexesのidx_scanを確認してください。代表的な期間でそれがゼロであり、インデックスが制約を強制していない場合は、削除してください。ロックマネージャーは、16個のロックを一度に、静かに感謝するでしょう。