ProHoster > Blog > Administration > PostgreSQL Antipatterns: Navigation im Register

PostgreSQL Antipatterns: Navigation im Register

Heute gibt es keine komplizierten Fälle oder ausgeklügelte Algorithmen in SQL. Alles wird sehr einfach sein, auf dem Niveau von Kapitän Offensichtlich – wir machen eine Ansicht des Ereignisprotokolls mit einer zeitlichen Sortierung.

Das heißt, hier liegt eine Tabelle in der Datenbank events, und sie hat das Feld ts — genau zu der Zeit, nach der wir diese Einträge 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 nur ein Dutzend Einträge haben werden, deshalb brauchen wir in irgendeiner Form Paging-Navigation.

#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]);

Das ist fast kein Scherz – es kommt selten vor, aber es gibt es in der Wildnis. Manchmal ist es nach der Arbeit mit ORM schwierig, auf die "direkte" Arbeit mit SQL umzuschalten.

Aber lassen Sie uns zu häufigeren und weniger offensichtlichen Problemen übergehen.

#1. OFFSET

SELECT
  ...
FROM
  events
ORDER BY
  ts DESC
LIMIT 26 OFFSET $1; -- 26 - Einträge pro Seite, $1 - Beginn der Seite

Woher kommt hier die Zahl 26? Das ist die ungefähre Anzahl der Einträge, um einen Bildschirm zu füllen. Genauer gesagt sind es 25 sichtbare Einträge plus 1, der signalisiert, dass es in der Auswahl noch etwas gibt und es sinnvoll ist, weiterzugehen.

Natürlich kann dieser Wert nicht in den Anfragekörper "eingebaut" werden, sondern als Parameter übergeben werden. Aber in diesem Fall kann der Planner von PostgreSQL nicht darauf vertrauen, dass es relativ wenige Einträge geben sollte — und wird möglicherweise einen ineffizienten Plan wählen.

Und solange die Ansicht des Protokolls in der Anwendung als Wechseln zwischen visuellen "Seiten" umgesetzt ist, bemerkt niemand lange etwas Verdächtiges. Genau bis zu dem Zeitpunkt, an dem in der Suche nach benutzerfreundlichem UI/UX entschieden wird, die Schnittstelle auf "unendliches Scrollen" umzustellen — das heißt, alle Einträge des Protokolls werden als eine Liste angezeigt, die der Benutzer nach oben und unten scrollen kann.

Und bei einem weiteren Test werden Sie dabei ertappt, dass Sie Einträge im Protokoll duplizieren. Warum, weil die Tabelle einen ordentlichen Index hat (ts), auf den Ihre Anfrage sich stützt?

Genau deshalb, weil Sie nicht berücksichtigt haben, dass ts kein eindeutiger Schlüssel in dieser Tabelle ist. Tatsächlich sind die Werte dafür nicht einzigartig, wie bei jeder „Zeit“ unter realen Bedingungen - daher kann ein und derselbe Eintrag in zwei benachbarten Abfragen leicht von Seite zu Seite „springen“, aufgrund einer anderen Endreihenfolge innerhalb der Sortierung des gleichen Schlüssels.

Tatsächlich verbirgt sich hier ein weiteres Problem, das viel schwieriger zu erkennen ist - einige Einträge werden nicht angezeigt überhaupt! Denn „duplizierte“ Einträge haben einen anderen Platz eingenommen. Eine detaillierte Erklärung mit schönen Bildern finden Sie hier lesen.

Index erweitern

Ein schlauer Entwickler versteht - der Schlüssel des Indexes muss eindeutig sein, und die einfachste Möglichkeit ist, ihn um ein bereits eindeutiges Feld zu erweitern, wobei sich PK hervorragend eignet:

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

Und die Abfrage mutiert:

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

#2. Переход на «курсоры»

Einige Zeit später kommt ein DBA zu Ihnen und „freut“ sich darüber, dass Ihre Abfragen den Server mit ihren enormen OFFSETs belastend, und überhaupt, es wird Zeit, auf die Navigation vom zuletzt angezeigten Wert umzuschwenken.Ihre Abfrage mutiert erneut:

SELECT
  ...
WHERE
  (ts, id) < ($1, $2) -- die zuletzt erhaltenen Werte aus dem vorherigen Schritt
ORDER BY
  ts DESC, id DESC
LIMIT 26;

Sie atmen erleichtert auf, bis es…

#3. Чистка индексов

Denn eines Tages las Ihr DBA einen Artikel über die Suche nach ineffizienten Indizes und erkannte, dass ein „nicht letzter“ Zeitstempel - das ist nicht gut.. Und er kam erneut zu Ihnen - jetzt mit dem Gedanken, dass dieser Index doch wieder zurückverwandelt werden sollte in (ts DESC).

Aber was ist mit dem ursprünglichen Problem des „Springens“ der Einträge zwischen den Seiten?.. Alles ganz einfach - man muss Blöcke mit einer nicht festen Anzahl von Einträgen auswählen!

Eigentlich, wer verbietet uns, nicht „genau 26“, sondern „mindestens 26“ zu lesen? Beispielsweise so, dass im nächsten Block Einträge mit eindeutig anderen Werten erscheinen, ts dann wird es nämlich auch keine Probleme mit dem „Springen“ von Einträgen zwischen Blöcken geben!

So erreichen Sie das:

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

Was passiert hier überhaupt?

  1. Wir gehen 25 Einträge „nach unten“ und erhalten den „Grenzwert“ ts.
  2. Wenn dort bereits nichts mehr ist, ersetzen wir den NULL-Wert durch -infinity.
  3. Wir ziehen den gesamten Wertebereich zwischen dem erhaltenen Wert ts und dem übergebenen Parameter $1 (dem vorherigen „letzten“ dargestellten Wert) ab.
  4. Wenn der Block mit weniger als 26 Einträgen zurückgekehrt ist, ist er der letzte.

Oder dasselbe als Bild:
PostgreSQL Antipatterns: Navigation im Register

Da wir jetzt nicht über einen bestimmten "Start" verfügen, steht uns nichts im Wege, diese Anfrage umzukehren und eine dynamische Nachladung von Datenblöcken von einem "Fixpunkt" in beide Richtungen zu realisieren – sowohl nach unten als auch nach oben.

Hinweis

  1. Ja, in diesem Fall greifen wir zweimal auf den Index zu, aber alles "rein nach Index". Daher führt die verschachtelte Abfrage nur zu einem zusätzlichen Index Only Scan..
  2. Es ist offensichtlich, dass diese Methode nur dann verwendet werden kann, wenn Sie Werte haben, ts die sich nur zufällig überschneiden, und es nicht viele sind.. Wenn Ihr typischer Fall jedoch "eine Million Einträge um 00:00:00.000" ist, sollten Sie das nicht tun. Das heißt, Sie sollten einen solchen Fall vermeiden. Aber falls es doch so gekommen ist, verwenden Sie die Variante mit dem erweiterten Index.

Quelle: habr.com

60GB SSD 8Gb DDR4