🥇PostgreSQL Antipatterns: Navigation durch das Protokoll

Heute gibt es keine komplizierten Fälle oder verwirrenden SQL-Algorithmen. Es wird ganz einfach, auf dem Niveau von Kapitän Offensichtlich – wir machen es. Ereignisprotokoll anzeigen sortiert nach Zeit.

Es gibt also eine Tabelle in der Datenbank, eventsund sie hat ein Feld ts – genau das Zeitfeld, nach dem 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 eine Handvoll Einträge haben werden, daher benötigen wir in irgendeiner Form Seitenavigation..

#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 sogar fast kein Scherz – es ist selten, kommt aber in freier Wildbahn vor. Manchmal ist es schwer, nach der Arbeit mit ORM wieder 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 - Startseite

Woher kommt die Zahl 26? Dies ist die ungefähre Anzahl der Einträge, die einen Bildschirm füllen. Genauer gesagt handelt es sich um 25 angezeigte Einträge plus 1, der signalisiert, dass in der Auswahl noch etwas vorhanden ist und es sinnvoll ist, weiterzulesen.
Natürlich kann dieser Wert nicht fest in den Anfrage-Body integriert, sondern über einen Parameter übermittelt werden. In diesem Fall kann der PostgreSQL-Planer jedoch nicht darauf vertrauen, dass nur relativ wenige Einträge vorhanden sind, und könnte leicht einen ineffizienten Plan auswählen.

Solange die Darstellung des Registers in der Anwendung als Wechsel zwischen visuellen „Seiten“ realisiert wird, bemerkt niemand lange etwas Verdächtiges. Genau bis zu dem Moment, an dem die Entscheidung getroffen wird, das UI/UX-Design für unendliches Scrollen zu überarbeiten — das heißt, alle Einträge des Registers werden als eine einzige Liste dargestellt, die der Benutzer nach oben und unten scrollen kann.

Und während eines weiteren Tests werden Sie ertappt beim Duplizieren von Einträgen im Register. Warum, schließlich gibt es einen ordentlichen Index auf der Tabelle (ts), auf den Ihre Anfrage aufbaut?

Genau deshalb, weil Sie nicht bedacht haben, dass ts kein eindeutiger Schlüssel in dieser Tabelle ist. Eigentlich auch die Werte sind nicht einzigartig, wie bei jeder „Zeit“ unter realen Bedingungen — deshalb springt derselbe Datensatz in zwei benachbarten Abfragen aufgrund einer anderen Endreihenfolge innerhalb der Sortierung des gleichen Schlüsselwerts leicht von Seite zu Seite.

Tatsächlich verbirgt sich hier noch ein zweites Problem, das viel schwerer zu erkennen ist — einige Datensätze werden überhaupt nicht angezeigt! Denn „duplizierte“ Einträge haben jemandes Platz eingenommen. Eine detaillierte Erklärung mit schönen Bildern finden Sie Index erweitern hier lesen..

Der clevere Entwickler versteht — man muss den Indexschlüssel einzigartig machen, und die einfachste Möglichkeit ist, ihn mit einem von Natur aus einzigartigen Feld zu erweitern, für das 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;

Einige Zeit später kommt ein DBA zu Ihnen und „freut“ Sie damit, dass Ihre Abfragen

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

den Server mit ihren enormen OFFSETs höllisch belasten , und überhaupt, es ist an der Zeit, aufNavigation vom letzten angezeigten Wert zu wechseln. Ihre Abfrage mutiert erneut:SELECT ... WHERE (ts, id) < ($1, $2) -- zuletzt erhaltene Werte vom vorherigen Schritt ORDER BY ts DESC, id DESC LIMIT 26;

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

Sie atmeten erleichtert auf, bis...

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

weil einst Ihr DBA einen Artikel über die Suche nach ineffizienten Indizes gelesen hat und erkannte, dass ein »nicht letzter« Zeitstempel — das ist nicht gut.Und wieder kam er zu Ihnen — jetzt mit der Idee, dass jener Index doch wieder zurückverwandelt werden sollte in (ts DESC).

Aber was tun mit dem ursprünglichen Problem des »Sprunges« zwischen den Seiten?.. Ganz einfach — man muss Blöcke mit einer variablen Anzahl von Einträgen auswählen!

Im Grunde genommen, wer hindert uns daran, nicht »genau 26«, sondern »mindestens 26« zu lesen? Beispielsweise so, dass im nächsten Block Einträge mit ganz anderen Werten auftauchen ts — dann treten die Probleme mit dem »Springen« der Einträge zwischen den Blöcken nicht auf!

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 eigentlich?

Wir gehen 25 Einträge »nach unten« und erhalten den »Grenzwert«. ts.
Wenn da bereits nichts mehr ist, ersetzen wir den NULL-Wert durch -infinity.
Wir lesen den gesamten Wertebereich zwischen dem erhaltenen Wert aus. ts und über den übergebenen Parameter $1 aus der Schnittstelle (dem vorherigen „letzten“ gerenderten Wert).
Wenn der Block weniger als 26 Einträge zurückgegeben hat, ist er der letzte.

Oder dasselbe in Bildform:

Da wir jetzt keinen bestimmten „Start“ für die Abfrage haben,, steht uns nichts im Weg, diese Anfrage umzukehren und eine dynamische Nachladung von Datenblöcken von einem „Referenzpunkt“ in beide Richtungen zu implementieren — sowohl nach unten als auch nach oben.

Hinweis

Ja, in diesem Fall greifen wir zweimal auf den Index zu, aber alles erfolgt „rein indexbasiert“. Daher führt die verschachtelte Abfrage lediglich zu einem zusätzlichen Index Only Scan..
Es ist offensichtlich, dass diese Methode nur verwendet werden kann, wenn die Werte ts nur zufällig überschneiden können, und es nicht viele sind.. Wenn jedoch Ihr typischer Anwendungsfall „eine Million Einträge um 00:00:00.000“ ist, sollten Sie das nicht tun. Im Sinne von, solche Fälle sollten vermieden werden. Aber wenn es bereits so gekommen ist, verwenden Sie die Variante mit dem erweiterten Index.

Quelle: habr.com