プロホスト > ブログ > 行政 > PostgreSQL アンチパターン: レジストリの移動

PostgreSQL アンチパターン: レジストリの移動

現在、SQL には複雑なケースや高度なアルゴリズムは存在しません。 キャプテン・オブビアスのレベルでは、すべてが非常に簡単になります - やってみましょう イベントレジストリの表示 時間順に並べ替えます。

つまり、データベースにサインがあります events、そして彼女は畑を持っています ts - これらのレコードを順番に表示したい正確な時刻:

CREATE TABLE events(
  id
    serial
      PRIMARY KEY
, ts
    timestamp
, data
    json
);

CREATE INDEX ON events(ts DESC);

そこに十数件のレコードが存在しないことは明らかなので、何らかの形式の ページナビゲーション.

#0。 「私は母のポグロミストです」

cur.execute("SELECT * FROM events;")
rows = cur.fetchall();
rows.sort(key=lambda row: row.ts, reverse=True);
limit = 26
print(rows[offset:offset+limit]);

それはほとんど冗談ではありません。まれですが、野生で発見されます。 ORM を使用した後、SQL を使用した「直接」作業に切り替えるのが難しい場合があります。

しかし、より一般的であまり目立たない問題に移りましょう。

#1. オフセット

SELECT
  ...
FROM
  events
ORDER BY
  ts DESC
LIMIT 26 OFFSET $1; -- 26 - записей на странице, $1 - начало страницы

26という数字はどこから来たのでしょうか？ これは、25 つの画面を満たすエントリのおおよその数です。 より正確には、表示された 1 レコードに XNUMX レコードを加えたもので、サンプル内に少なくともさらに何か別のものがあり、先に進むのが合理的であることを示しています。

もちろん、この値はリクエストの本文に「縫い込む」ことはできませんが、パラメーターを通じて渡されます。 ただし、この場合、PostgreSQL スケジューラは、レコードが比較的少ないはずであるという知識に頼ることができず、簡単に非効率なプランを選択してしまいます。

また、アプリケーション インターフェイスでは、レジストリの表示が視覚的な「ページ」間の切り替えとして実装されていますが、長い間誰も不審な点に気づきません。 利便性との闘いの中で、UI/UX がインターフェイスを「無限スクロール」に作り直すことを決定するその瞬間まで、つまり、すべてのレジストリ エントリが単一のリストに描画され、ユーザーが上下にスクロールできるようになります。

そして、次のテスト中にあなたは捕らえられます 記録の重複 レジストリにあります。 なぜかというと、テーブルには通常のインデックスがあるからです。 (ts)、クエリはどれに依存していますか?

まさにそれを考慮していなかったからだ ts 一意のキーではありません この表では。 実は、そして その値は一意ではありません、実際の状況における他の「時間」と同様に、同じキー値をソートするフレームワーク内の最終順序が異なるため、XNUMX つの隣接するクエリ内の同じレコードがページからページへと簡単に「ジャンプ」します。

実際、ここにはさらに気づきにくい XNUMX つ目の問題が隠されています。 一部のエントリは表示されません 全然！ 結局のところ、「重複」記録が他の誰かの代わりになったのです。 美しい写真付きの詳しい説明が見られます ここをクリック.

インデックスの展開

狡猾な開発者は、インデックス キーを一意にする必要があることを理解しています。最も簡単な方法は、明らかに一意なフィールドでインデックス キーを拡張することです。PK は次の場合に最適です。

CREATE UNIQUE INDEX ON events(ts DESC, id DESC);

そしてリクエストは次のように変化します。

SELECT
  ...
ORDER BY
  ts DESC, id DESC
LIMIT 26 OFFSET $1;

#2. 「カーソル」に切り替えます

しばらくして、DBA があなたのところにやって来て、あなたの要求に「喜んで」くれます。 彼らはOFFSETルールでサーバーに地獄のように負荷をかけますそして一般的には、次のことに切り替える時期が来ました。 最後に表示された値からのナビゲーション。 クエリが再び変化します。

SELECT
  ...
WHERE
  (ts, id) < ($1, $2) -- последние полученные на предыдущем шаге значения
ORDER BY
  ts DESC, id DESC
LIMIT 26;

それが来るまで、あなたは安堵のため息をつきました...

#3. インデックスのクリーニング

なぜなら、ある日、DBA が読んだからです。 効果のないインデックスの検索に関する記事 そして気づいた 「最新ではない」タイムスタンプは良くありません。 そして私は再びあなたのところに来ました - 今、そのインデックスはまだ元に戻るはずだと考えています (ts DESC).

しかし、ページ間でレコードを「ジャンプ」するという最初の問題はどうすればよいでしょうか?.. そしてすべては簡単です - 固定されていない数のレコードを持つブロックを選択する必要があります。

一般に、「ちょうど 26」ではなく「26 未満ではない」と読むことを誰が禁じているでしょうか。 たとえば、次のブロックには次のようになります。 明らかに異なる意味を持つ記録 ts - そうすれば、ブロック間でレコードを「ジャンプ」しても問題はありません。

これを実現する方法は次のとおりです。

SELECT
  ...
WHERE
  ts < $1 AND
  ts >= coalesce((
    SELECT
      ts
    FROM
      events
    WHERE
      ts < $1
    ORDER BY
      ts DESC
    LIMIT 1 OFFSET 25
  ), '-infinity')
ORDER BY
  ts DESC;

何が起きてる？

  1. 25 個のレコードを「ダウン」してステップを実行し、「境界」値を取得します。 ts.
  2. そこにすでに何もない場合は、NULL 値を次の値に置き換えます。 -infinity.
  3. 受信した値の間の値のセグメント全体を減算します ts およびインターフェイスから渡された $1 パラメーター (前回の「最後に」レンダリングされた値)。
  4. 返されたブロックのレコード数が 26 未満の場合、それは最後のブロックです。

または同じ写真:
PostgreSQL アンチパターン: レジストリの移動

なぜなら今私たちは サンプルには特定の「始まり」がありません。そうすると、このリクエストを逆方向に「拡張」し、「参照ポイント」から両方向 (下方向と上方向) にデータ ブロックを動的にロードすることを妨げるものはありません。

注意：

  1. はい、この場合、インデックスに XNUMX 回アクセスしますが、すべては「純粋にインデックスによって」行われます。 したがって、サブクエリの結果は次のとおりです。 追加のインデックスのみのスキャンに XNUMX つ.
  2. この手法は、値がある場合にのみ使用できることは明らかです。 ts 偶然にしか渡れないし、 それらはあまり多くありません。 典型的なケースが「00:00:00.000 に XNUMX 万件のレコード」である場合は、これを行うべきではありません。 つまり、そのような事件が起こることを許すべきではありません。 ただし、このような場合は、拡張インデックスを備えたオプションを使用してください。

出所： habr.com

コメントを追加します