🥇PostgreSQL Antipatterns: nawigacja po rejestrze

Dziś nie będzie żadnych skomplikowanych przypadków ani wymyślnych algorytmów w SQL. Będzie bardzo prosto, na poziomie Kapitana Oczywistości — robimy przegląd rejestru zdarzeń z sortowaniem według czasu.

To znaczy, że w bazie leży sobie tabela events, a ma ona pole ts — dokładnie ten czas, według którego chcemy porządkować te rekordy:

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

CREATE INDEX ON events(ts DESC);

Rozumiemy, że rekordów będzie u nas nie dziesiątki, dlatego potrzebujemy w jakiejś formie paginacji.

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

Nawet prawie nie żart — rzadko, ale zdarza się w dzikiej naturze. Czasami po pracy z ORM trudno jest przestawić się na „bezpośrednią” pracę z SQL.

Ale przejdźmy do bardziej powszechnych i mniej oczywistych problemów.

#1. OFFSET

SELECT
  ...
FROM
  events
ORDER BY
  ts DESC
LIMIT 26 OFFSET $1; -- 26 - rekordów na stronie, $1 - początek strony

Skąd wzięła się tutaj liczba 26? To przybliżona liczba rekordów potrzebnych do zapełnienia jednego ekranu. Dokładniej, 25 wyświetlanych rekordów, plus 1, sygnalizująca, że dalej w zbiorze jest coś jeszcze, co ma sens, aby iść dalej.
Oczywiście, tę wartość można nie „wmontowywać” w treść zapytania, a przekazywać przez parametr. Ale w takim przypadku planistka PostgreSQL nie będzie mogła opierać się na wiedzy, że rekordów powinno być stosunkowo niewiele — i łatwo wybierze nieefektywny plan.

I dopóki w interfejsie aplikacji przegląd rejestru realizowany jest jako przełączanie między wizualnymi „stronami”, nikt długo nie zauważa nic podejrzanego. Aż do momentu, gdy w walce o wygodę UI/UX zadecydują o przebudowie interfejsu na „nieskończone przewijanie” — to znaczy wszystkie rekordy rejestru są rysowane jako jedna lista, którą użytkownik może przewijać w górę i w dół.

I oto, przy kolejnych testach łapią cię na duplikowaniu rekordów w rejestrze. Dlaczego, skoro na tabeli jest normalny indeks (ts), na który opiera się twoje zapytanie?

Dokładnie dlatego, że nie wziąłeś pod uwagę, że ts nie jest kluczem unikalnym w tej tabeli. Właściwie, i wartości również nie są unikalne, jak to bywa w przypadku każdego „czasu” w rzeczywistych warunkach — dlatego ta sama wartość w dwóch sąsiednich zapytaniach łatwo „przeskakuje” z jednej strony na drugą z powodu innej końcowej kolejności w ramach sortowania tego samego klucza.

W rzeczywistości kryje się tutaj druga, znacznie trudniejsza do zauważenia problematyka — niektóre rekordy nie będą w ogóle wyświetlane! W końcu „zdublowane” rekordy zajęły czyjeś miejsce. Szczegółowe wyjaśnienie z ładnymi obrazkami można przeczytać tutaj.

Rozszerzamy indeks

Sprytny programista rozumie — należy uczynić klucz indeksu unikalnym, a najprostszym sposobem jest rozszerzenie go o z góry unikalne pole, jako które doskonale nada się PK:

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

A zapytanie mutuje:

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

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

Kilka chwil później przychodzi do Ciebie DBA i „cieszy”, że Twoje zapytania niesamowicie obciążają serwer swoimi ogromnymi OFFSET, a w ogóle, pora na nawigację od ostatniej wyświetlonej wartości. Twoje zapytanie mutuje ponownie:

SELECT
  ...
WHERE
  (ts, id) < ($1, $2) -- ostatnie otrzymane na poprzednim kroku wartości
ORDER BY
  ts DESC, id DESC
LIMIT 26;

Odetchłeś z ulgą, dopóki nie nastał…

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

Ponieważ pewnego razu Twój DBA przeczytał artykuł na temat efektywności indeksów i zrozumiał, że „nienajświeższy” timestamp — to niedobrze. I znów przyszedł do Ciebie — teraz z myślą, że ten indeks powinien jednak zmienić się z powrotem w (ts DESC).

Ale co zrobić z pierwotnym problemem „skakania” rekordów między stronami?.. A to proste — należy wybierać bloki z nieustaloną ilością rekordów!

W ogóle, kto nam zabrania czytać „nie dokładnie 26”, ale „nie mniej niż 26”? Na przykład tak, aby w następnym bloku znalazły się rekordy z z góry innymi wartościami ts — wtedy nie będzie problemów z „przeskakiwaniem” rekordów między blokami!

Oto jak to osiągnąć:

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

Co tu się w ogóle dzieje?

Przechodzimy o 25 rekordów „w dół” i otrzymujemy „graniczne” wartość ts.
Jeśli tam już nic nie ma, to zamieniamy wartość NULL na -infinity.
Odejmujemy cały segment wartości między otrzymaną wartością ts a przekazaną z interfejsu parametrem $1 (poprzednią „ostatnią” wyrysowaną wartością).
Jeśli blok zwrócił mniej niż 26 rekordów — jest to ostatni.

Lub to samo w obrazku:

Ponieważ teraz mamy próbka nie ma jakiegoś określonego „początku”, to nic nie stoi na przeszkodzie, aby „odwrócić” to zapytanie w odwrotną stronę i zrealizować dynamiczne ładowanie bloków danych od „punktu odniesienia” w obie strony — zarówno w dół, jak i w górę.

Uwagi

Tak, w takim przypadku odwołujemy się do indeksu dwukrotnie, ale wszystko „czysto po indeksie”. Dlatego zagnieżdżone zapytanie doprowadzi jedynie do jednego dodatkowego skanowania tylko po indeksie.
Jest oczywiste, że tej metodzie można używać tylko wtedy, gdy masz wartości ts mogą się przypadkowo pokrywać, a jest ich niewiele.Jeśli jednak twój typowy przypadek to „milion rekordów w 00:00:00.000”, lepiej tego nie robić. Mam na myśli, że nie powinieneś dopuszczać do takiego przypadku. Ale jeśli już tak się stało, skorzystaj z opcji z rozszerzonym indeksem.

Źródło: habr.com