🥇PostgreSQL Antipatterns: navigace v registru

Dnes v SQL nebudou žádné složité případy a sofistikované algoritmy. Všechno bude velmi jednoduché, na úrovni Captain Obvious – pojďme na to prohlížení registru událostí seřazené podle času.

To znamená, že v databázi je znak eventsa má pole ts - přesně čas, kdy chceme tyto záznamy zobrazit uspořádaným způsobem:

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

CREATE INDEX ON events(ts DESC);

Je jasné, že tam nebudeme mít tucet desek, takže nějakou formu budeme potřebovat navigace na stránce.

#0. "Jsem pogromista své 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]);

Skoro to není vtip – je to vzácné, ale vyskytuje se ve volné přírodě. Někdy po práci s ORM může být obtížné přejít na „přímou“ práci s SQL.

Přejděme ale k běžnějším a méně zjevným problémům.

#1. OFFSET

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

Kde se vzalo číslo 26? Toto je přibližný počet záznamů na vyplnění jedné obrazovky. Přesněji 25 zobrazených záznamů plus 1 signalizující, že dále v ukázce je alespoň něco dalšího a má smysl jít dál.

Tuto hodnotu samozřejmě nelze „všít“ do těla požadavku, ale předat ji přes parametr. Jenže v tomto případě se plánovač PostgreSQL nebude moci spolehnout na vědomí, že záznamů by mělo být relativně málo – a snadno zvolí neefektivní plán.

A zatímco v rozhraní aplikace je prohlížení registru implementováno jako přepínání mezi vizuálními „stránkami“, dlouho si nikdo ničeho podezřelého nevšimne. Přesně do okamžiku, kdy se UI/UX v boji o pohodlí rozhodne předělat rozhraní na „nekonečné posouvání“ – to znamená, že všechny položky registru jsou nakresleny v jediném seznamu, který uživatel může posouvat nahoru a dolů.

A tak jste při dalším testování chyceni duplikace záznamů v registru. Proč, protože tabulka má normální index (ts), na který se váš dotaz opírá?

Právě proto, že jsi to nevzal v úvahu ts není jedinečný klíč v této tabulce. Vlastně a jeho hodnoty nejsou jedinečné, jako každý „čas“ v reálných podmínkách – proto stejný záznam ve dvou sousedních dotazech snadno „přeskakuje“ ze stránky na stránku z důvodu odlišného konečného pořadí v rámci řazení stejné hodnoty klíče.

Ve skutečnosti se zde skrývá i druhý problém, který je mnohem obtížnější si všimnout – některé položky se nezobrazí vůbec! Koneckonců, „duplicitní“ záznamy nahradily někoho jiného. Podrobné vysvětlení s krásnými obrázky naleznete přečtěte si zde.

Rozšíření indexu

Chytrý vývojář chápe, že indexový klíč musí být jedinečný, a nejjednodušším způsobem je rozšířit jej o zjevně jedinečné pole, pro které je PK ideální:

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

A žádost mutuje:

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

#2. Přepnout na „kurzory“

Po nějaké době k vám přijde DBA a je „potěšen“, že vaše požadavky zatěžují server jako čert svými pravidly OFFSETa obecně je čas přejít na navigace od poslední zobrazené hodnoty. Váš dotaz opět mutuje:

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

Vydechl jsi úlevou, dokud to nepřišlo...

#3. Čisticí indexy

Protože jednoho dne bude vaše DBA přečteno článek o hledání neúčinných indexů a uvědomil si to Časové razítko „není nejnovější“ není dobré. A přišel jsem k vám znovu - nyní s myšlenkou, že by se ten index měl ještě změnit (ts DESC).

Co ale dělat s počátečním problémem „přeskakování“ záznamů mezi stránkami?... A vše je jednoduché – je potřeba vybrat bloky s nefixovaným počtem záznamů!

Obecně, kdo nám zakazuje číst ne „přesně 26“, ale „ne méně než 26“? Například tak, že v dalším bloku jsou záznamy se zjevně odlišnými významy ts - pak nebude problém s „přeskakováním“ záznamů mezi bloky!

Jak toho dosáhnout:

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;

Co se tam děje?

Krokujeme 25 záznamů „dolů“ a získáme hodnotu „hranice“. ts.
Pokud tam již nic není, nahraďte hodnotu NULL hodnotou -infinity.
Mezi přijatou hodnotou odečteme celý segment hodnot ts a parametr $1 předaný z rozhraní (předchozí „poslední“ vykreslená hodnota).
Pokud je vrácen blok s méně než 26 záznamy, je to poslední.

Nebo stejný obrázek:

Protože teď máme vzorek nemá žádný konkrétní „začátek“, pak nám nic nebrání tento požadavek „rozšířit“ opačným směrem a implementovat dynamické načítání datových bloků z „referenčního bodu“ oběma směry – dolů i nahoru.

Poznámka:

Ano, v tomto případě přistupujeme k indexu dvakrát, ale vše je „čistě podle indexu“. Výsledkem poddotazu tedy bude pouze na jedno další skenování pouze indexu.
Je zcela zřejmé, že tuto techniku lze použít pouze tehdy, když máte hodnoty ts může přejít jen náhodou, a jen málo z nich. Pokud je váš typický případ „milion záznamů v 00:00:00.000“, neměli byste to dělat. Chci říct, neměli byste dovolit, aby se takový případ stal. Ale pokud k tomu dojde, použijte volbu s rozšířeným indexem.

Zdroj: www.habr.com

PostgreSQL Antipatterns: Navigace v registru