ProHoster > Blog > Verwaltung > PostgreSQL-Antipatterns: Navigieren in der Registry

PostgreSQL-Antipatterns: Navigieren in der Registry

Heutzutage wird es in SQL keine komplexen Fälle und ausgefeilten Algorithmen mehr geben. Auf der Ebene von Captain Obvious wird alles sehr einfach sein – lass es uns tun Anzeigen der Ereignisregistrierung nach Zeit sortiert.

Das heißt, es gibt ein Zeichen in der Datenbank events, und sie hat ein Feld ts - genau den Zeitpunkt, bis zu dem wir diese Datensätze geordnet anzeigen möchten:

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

CREATE INDEX ON events(ts DESC);

Es ist klar, dass wir dort nicht ein Dutzend Datensätze haben werden, also brauchen wir irgendeine Form von Seitennavigation.

#0. „Ich bin der Pogromist meiner Mutter“

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

Es ist fast kein Scherz – es ist selten, kommt aber in freier Wildbahn vor. Manchmal kann es nach der Arbeit mit ORM schwierig sein, zur „direkten“ Arbeit mit SQL überzugehen.

Aber kommen wir zu häufigeren und weniger offensichtlichen Problemen.

#1. OFFSET

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

Woher kommt die Zahl 26? Dies ist die ungefähre Anzahl von Einträgen, die einen Bildschirm ausfüllen. Genauer gesagt, 25 angezeigte Datensätze plus 1, was darauf hinweist, dass sich in der Stichprobe noch mindestens etwas anderes befindet und es sinnvoll ist, weiterzumachen.

Natürlich kann dieser Wert nicht in den Hauptteil der Anfrage „eingenäht“ werden, sondern über einen Parameter übergeben werden. In diesem Fall kann sich der PostgreSQL-Scheduler jedoch nicht auf das Wissen verlassen, dass relativ wenige Datensätze vorhanden sein sollten – und wählt leicht einen ineffektiven Plan.

Und während in der Anwendungsoberfläche die Anzeige der Registrierung als Wechsel zwischen visuellen „Seiten“ implementiert ist, bemerkt lange Zeit niemand etwas Verdächtiges. Genau bis zu dem Moment, in dem UI/UX im Kampf um Bequemlichkeit beschließt, die Schnittstelle auf „Endless Scroll“ umzustellen – das heißt, alle Registrierungseinträge werden in einer einzigen Liste angezeigt, die der Benutzer nach oben und unten scrollen kann.

Und so werden Sie beim nächsten Test erwischt Vervielfältigung von Aufzeichnungen in der Registry. Warum, weil die Tabelle einen normalen Index hat? (ts), worauf stützt sich Ihre Anfrage?

Eben weil du das nicht berücksichtigt hast ts ist kein eindeutiger Schlüssel in dieser Tabelle. Eigentlich und seine Werte sind nicht eindeutig, wie jede „Zeit“ unter realen Bedingungen – daher „springt“ derselbe Datensatz in zwei benachbarten Abfragen aufgrund einer unterschiedlichen Endreihenfolge im Rahmen der Sortierung desselben Schlüsselwerts leicht von Seite zu Seite.

Tatsächlich verbirgt sich hier noch ein zweites Problem, das viel schwieriger zu erkennen ist – Einige Einträge werden nicht angezeigt überhaupt! Schließlich haben die „doppelten“ Datensätze den Platz einer anderen Person eingenommen. Eine ausführliche Erklärung mit schönen Bildern finden Sie hier hier lesen.

Erweiterung des Indexes

Ein schlauer Entwickler versteht, dass der Indexschlüssel eindeutig gemacht werden muss, und der einfachste Weg besteht darin, ihn um ein offensichtlich eindeutiges Feld zu erweitern, wofür PK perfekt geeignet ist:

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

Und die Anfrage mutiert:

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

#2. Wechseln Sie zu „Cursor“

Einige Zeit später kommt ein DBA zu Ihnen und freut sich über Ihre Anfragen Sie belasten den Server höllisch mit ihren OFFSET-Regeln, und im Allgemeinen ist es Zeit zu wechseln Navigation ab dem zuletzt angezeigten Wert. Ihre Anfrage ändert sich erneut:

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

Du atmetest erleichtert auf, bis es kam...

#3. Reinigungsindizes

Denn eines Tages hat Ihr DBA gelesen Artikel über die Suche nach ineffektiven Indizes und habe das gemerkt Der Zeitstempel „nicht der neueste“ ist nicht gut. Und ich kam wieder zu Ihnen – jetzt mit dem Gedanken, dass sich dieser Index noch einmal ändern sollte (ts DESC).

Aber was tun mit dem anfänglichen Problem des „Springens“ von Datensätzen zwischen Seiten? Und alles ist ganz einfach: Sie müssen Blöcke mit einer nicht festgelegten Anzahl von Datensätzen auswählen!

Wer verbietet uns überhaupt, nicht „genau 26“, sondern „nicht weniger als 26“ zu lesen? Zum Beispiel, damit es im nächsten Block solche gibt Datensätze mit offensichtlich unterschiedlicher Bedeutung ts - dann gibt es kein Problem mit dem „Springen“ von Datensätzen zwischen Blöcken!

So erreichen Sie dies:

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;

Was ist denn hier los?

  1. Wir gehen 25 Datensätze „nach unten“ und erhalten den „Grenzwert“. ts.
  2. Wenn dort bereits nichts vorhanden ist, ersetzen Sie den NULL-Wert durch -infinity.
  3. Wir subtrahieren das gesamte Wertesegment zwischen dem empfangenen Wert ts und der von der Schnittstelle übergebene Parameter $1 (der vorherige „letzte“ gerenderte Wert).
  4. Wenn ein Block mit weniger als 26 Datensätzen zurückgegeben wird, ist es der letzte.

Oder das gleiche Bild:
PostgreSQL-Antipatterns: Navigieren in der Registry

Denn jetzt haben wir es Die Probe hat keinen bestimmten „Anfang“, dann hindert uns nichts daran, diese Anfrage in die entgegengesetzte Richtung zu „erweitern“ und das dynamische Laden von Datenblöcken vom „Referenzpunkt“ in beide Richtungen – sowohl nach unten als auch nach oben – zu implementieren.

Hinweis:

  1. Ja, in diesem Fall greifen wir zweimal auf den Index zu, aber alles „rein per Index“. Daher führt eine Unterabfrage nur zu zu einem zusätzlichen Nur-Index-Scan.
  2. Es ist ziemlich offensichtlich, dass diese Technik nur verwendet werden kann, wenn Werte vorhanden sind ts kann nur durch Zufall überqueren, und wenige davon. Wenn Ihr typischer Fall „eine Million Datensätze um 00:00:00.000“ ist, sollten Sie dies nicht tun. Ich meine, Sie sollten nicht zulassen, dass so ein Fall passiert. Wenn dies jedoch passiert, verwenden Sie die Option mit einem erweiterten Index.

Source: habr.com

Kommentar hinzufügen