🥇PostgreSQL Antipatterns: navigácia v registri

Dnes v SQL nebudú žiadne zložité prípady a sofistikované algoritmy. Všetko bude veľmi jednoduché, na úrovni kapitána Obviousa - poďme na to prezeranie registra udalostí zoradené podľa času.

To znamená, že v databáze je znak events, a ona má pole ts - presný čas, kedy chceme tieto záznamy zobraziť usporiadaným spôsobom:

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

CREATE INDEX ON events(ts DESC);

Je jasné, že tam nebudeme mať tucet záznamov, takže budeme potrebovať nejakú formu navigácia po stránke.

#0. "Som pogromista svojej matky"

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

Takmer to nie je vtip - je to zriedkavé, ale vyskytuje sa vo voľnej prírode. Niekedy po práci s ORM môže byť ťažké prejsť na „priamu“ prácu s SQL.

Prejdime však k bežnejším a menej zjavným problémom.

#1. OFFSET

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

Odkiaľ pochádza číslo 26? Toto je približný počet záznamov na vyplnenie jednej obrazovky. Presnejšie, 25 zobrazených záznamov plus 1 signalizujúce, že ďalej v ukážke je aspoň niečo iné a má zmysel ísť ďalej.

Samozrejme, túto hodnotu nemožno „všiť“ do tela požiadavky, ale preniesť ju cez parameter. No v tomto prípade sa plánovač PostgreSQL nebude môcť spoľahnúť na vedomie, že záznamov by malo byť relatívne málo – a ľahko zvolí neefektívny plán.

A zatiaľ čo v rozhraní aplikácie je prezeranie registra implementované ako prepínanie medzi vizuálnymi „stránkami“, nikto si dlho nevšimne nič podozrivé. Presne do momentu, keď sa UI/UX v boji o pohodlie rozhodne prerobiť rozhranie na „nekonečné posúvanie“ – to znamená, že všetky položky registra sú nakreslené v jednom zozname, ktorý môže používateľ posúvať nahor a nadol.

A tak ste pri ďalšom testovaní chytení duplikácia záznamov v registri. Prečo, veď tabuľka má normálny index (ts), na ktorý sa váš dopyt spolieha?

Presne preto, že si to nebral do úvahy ts nie je jedinečný kľúč v tejto tabuľke. V skutočnosti a jeho hodnoty nie sú jedinečné, ako každý „čas“ v reálnych podmienkach – preto ten istý záznam v dvoch susedných dopytoch ľahko „skáče“ zo strany na stranu z dôvodu rozdielneho konečného poradia v rámci triedenia rovnakej hodnoty kľúča.

V skutočnosti je tu skrytý aj druhý problém, ktorý je oveľa ťažšie postrehnúť - niektoré záznamy sa nezobrazia vôbec! Koniec koncov, „duplicitné“ záznamy nahradili niekoho iného. Podrobné vysvetlenie s krásnymi obrázkami nájdete Prečítajte si tu.

Rozšírenie indexu

Prefíkaný vývojár chápe, že indexový kľúč musí byť jedinečný a najjednoduchším spôsobom je rozšíriť ho o očividne jedinečné pole, na ktoré je PK ideálny:

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

A žiadosť sa mení:

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

#2. Prepnúť na „kurzory“

O nejaký čas neskôr k vám príde DBA a je „potešený“, že vaše požiadavky načítajú server pekelne svojimi pravidlami OFFSETa vo všeobecnosti je čas prejsť na navigácia od poslednej zobrazenej hodnoty. Váš dopyt sa opäť zmení:

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

Vydýchol si, kým to neprišlo...

#3. Indexy čistenia

Pretože jedného dňa bude váš DBA čítať článok o hľadaní neúčinných indexov a uvedomil si to Časová pečiatka „nie je najnovšia“ nie je dobrá. A znova som k vám prišiel - teraz s myšlienkou, že by sa ten index mal ešte zmeniť (ts DESC).

Ale čo robiť s počiatočným problémom „preskakovania“ záznamov medzi stránkami?... A všetko je jednoduché - musíte vybrať bloky s nepevným počtom záznamov!

Vo všeobecnosti, kto nám zakazuje čítať nie „presne 26“, ale „nie menej ako 26“? Napríklad tak, že v ďalšom bloku sú záznamy so zjavne odlišným významom ts - potom nebude problém s „preskakovaním“ záznamov medzi blokmi!

Tu je postup, ako to dosiahnuť:

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;

Čo sa tu deje?

Krok 25 záznamov „nadol“ a získame „hraničnú“ hodnotu ts.
Ak tam už nič nie je, nahraďte hodnotu NULL -infinity.
Odčítame celý segment hodnôt medzi prijatou hodnotou ts a parameter $1 odovzdaný z rozhrania (predchádzajúca „posledná“ vykreslená hodnota).
Ak sa vráti blok s menej ako 26 záznamami, je posledný.

Alebo ten istý obrázok:

Pretože teraz máme vzorka nemá žiadny konkrétny „začiatok“, potom nám už nič nebráni túto požiadavku „rozvinúť“ opačným smerom a implementovať dynamické načítanie dátových blokov z „referenčného bodu“ oboma smermi – dole aj hore.

poznámka

Áno, v tomto prípade pristupujeme k indexu dvakrát, ale všetko je „čisto podľa indexu“. Výsledkom poddotazu teda bude iba na jedno dodatočné skenovanie iba indexu.
Je celkom zrejmé, že táto technika sa dá použiť len vtedy, keď máte hodnoty ts môže prejsť len náhodou, a nie je ich veľa. Ak je váš typický prípad „milión záznamov o 00:00:00.000“, nemali by ste to robiť. Myslím, že by ste nemali dovoliť, aby sa takýto prípad stal. Ak sa to však stane, použite možnosť s rozšíreným indexom.

Zdroj: hab.com

PostgreSQL Antipatterns: Navigácia v registri