PostgreSQL アンチパターン: 「死者」の大群との戦い

PostgreSQL の内部メカニズムの特殊性により、ある状況では非常に高速になることも、別の状況では「あまり高速ではない」こともあります。 今日は、DBMS の動作方法と開発者が DBMS を使用して行うことの間の矛盾の典型的な例に焦点を当てます。 UPDATE と MVCC の原則.

からの簡単なストーリー 素晴らしい記事:

UPDATE コマンドによって行が変更されると、DELETE と INSERT という XNUMX つの操作が実際に実行されます。 で 文字列の現在のバージョン xmax は、UPDATE を実行したトランザクションの数に等しく設定されます。 それから作成されます новаяверсия 同じ行。 その xmin 値は、以前のバージョンの xmax 値と一致します。

このトランザクションが完了してからしばらくすると、古いバージョンまたは新しいバージョンが、状況に応じて COMMIT/ROOLBACK、認識されます 「死んだ」（死んだタプル） 通過するとき VACUUM 表に従ってクリアしました。

しかし、これはすぐに起こるわけではありませんが、「死者」に関する問題は、繰り返したり、繰り返したりすることで、非常にすぐに発生する可能性があります。 記録の大量更新 大きなテーブルで、少し後に同じ状況に遭遇するでしょう VACUUMでは役に立ちません.

#1: 動かすのが好き

メソッドがビジネス ロジックで動作していて、あるレコードの X フィールドを更新する必要があることに突然気付いたとしましょう。

UPDATE tbl SET X = <newX> WHERE pk = $1;

次に、実行が進むにつれて、Y フィールドも更新する必要があることがわかります。

UPDATE tbl SET Y = <newY> WHERE pk = $1;

...そして、Z - なぜ些細なことで時間を無駄にするのでしょうか?

UPDATE tbl SET Z = <newZ> WHERE pk = $1;

現在、データベースにはこのレコードのバージョンがいくつありますか? はい、4個です！ これらのうち 3 つは関連しており、XNUMX つは実行後に [auto]VACUUM によってクリーンアップする必要があります。

こんなことはしないでください！ 使用 XNUMX つのリクエストですべてのフィールドを更新する — ほとんどの場合、メソッドのロジックは次のように変更できます。

UPDATE tbl SET X = <newX>, Y = <newY>, Z = <newZ> WHERE pk = $1;

#2: 使用はルークとは異なります!

それで、あなたはまだ望んでいた テーブル内の非常に多くのレコードを更新する (たとえば、スクリプトまたはコンバータの使用中)。 そして、次のようなものがスクリプトに組み込まれます。

UPDATE tbl SET X = <newX> WHERE pk BETWEEN $1 AND $2;

ほぼこの形式のリクエストは非常に頻繁に発生しますが、ほとんどの場合、空の新しいフィールドに入力するのではなく、データ内のいくつかのエラーを修正することが目的です。 同時に彼女自身も 既存のデータの正確性はまったく考慮されていません -しかし無駄です！ つまり、必要なものが正確に含まれていたとしても、レコードは書き換えられるのですが、なぜでしょうか? それを修正しましょう:

UPDATE tbl SET X = <newX> WHERE pk BETWEEN $1 AND $2 AND X IS DISTINCT FROM <newX>;

このような素晴らしいオペレーターの存在を知らない人も多いので、ここにチートシートを掲載します。 IS DISTINCT FROM およびその他の論理演算子も役立ちます。

...そして複雑な操作について少し説明します ROW()-式:

#3: 私は...ブロックすることで恋人を認識します

打ち上げられています XNUMX つの同一の並列プロセス、それぞれがエントリを「進行中」であるとマークしようとします。

UPDATE tbl SET processing = TRUE WHERE pk = $1;

これらのプロセスが実際には互いに独立して動作する場合でも、同じ ID 内では、最初のトランザクションが完了するまで、XNUMX 番目のクライアントはこのリクエストに対して「ロック」されます。

決定第1号: タスクは前のタスクに縮小されます

もう一度追加しましょう IS DISTINCT FROM:

UPDATE tbl SET processing = TRUE WHERE pk = $1 AND processing IS DISTINCT FROM TRUE;

この形式では、XNUMX 番目のリクエストは単にデータベース内の何も変更せず、すべてがすでにあるべき状態になっているため、ブロッキングは発生しません。 次に、適用されたアルゴリズムでレコードが「見つからない」という事実を処理します。

決定第2号: 勧告ロック

大きなトピックについては別の記事で説明します。 推奨ブロックの適用方法と「レイク」.

決定第3号: 愚かな電話

しかし、これはまさにあなたに起こるべきことです 同じレコードの同時作業? それとも、たとえばクライアント側でビジネス ロジックを呼び出すためのアルゴリズムを間違えたのでしょうか? で、よく考えたら？

出所： habr.com