ProHoster > Blog > Adminisztráció > PostgreSQL antipatterns: Navigálás a beállításjegyzékben

PostgreSQL antipatterns: Navigálás a beállításjegyzékben

Ma már nem lesznek bonyolult esetek és kifinomult algoritmusok az SQL-ben. Minden nagyon egyszerű lesz, nyilvánvaló kapitány szintjén - tegyük meg az eseménynyilvántartás megtekintése idő szerint rendezve.

Vagyis van egy jel az adatbázisban events, és van egy mezője ts - pontosan az az időpont, amikor ezeket a rekordokat rendezetten szeretnénk megjeleníteni:

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

CREATE INDEX ON events(ts DESC);

Egyértelmű, hogy nem lesz ott egy tucat lemezünk, ezért szükségünk lesz valamilyen formában oldal navigáció.

#0. "Én anyám pogromistája vagyok"

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

Szinte nem vicc – ritka, de a vadonban megtalálható. Néha az ORM-mel végzett munka után nehéz lehet „közvetlen” SQL-munkára váltani.

De térjünk át a gyakoribb és kevésbé nyilvánvaló problémákra.

#1. OFFSET

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

Honnan jött a 26-os szám? Ez az egy képernyő kitöltéséhez szükséges bejegyzések hozzávetőleges száma. Pontosabban 25 megjelenített rekord plusz 1, jelezve, hogy legalább van még valami tovább a mintában, és van értelme továbblépni.

Természetesen ezt az értéket nem lehet „bevarrni” a kérés törzsébe, hanem át lehet adni egy paraméteren. De ebben az esetben a PostgreSQL ütemező nem tud arra a tudásra hagyatkozni, hogy viszonylag kevés rekordnak kell lennie - és könnyen nem hatékony tervet választ.

És míg az alkalmazás felületén a rendszerleíró adatbázis megtekintése vizuális „oldalak” közötti váltásként valósul meg, addig senki sem vesz észre semmi gyanúsat sokáig. Egészen addig a pillanatig, amíg a kényelemért folytatott küzdelem során az UI/UX úgy dönt, hogy a felületet „végtelen görgetésre” alakítja át – vagyis az összes regisztrációs bejegyzés egyetlen listába kerül, amelyet a felhasználó fel-le görgethet.

És így a következő tesztelés során elkapnak iratok megkettőzése a nyilvántartóban. Miért, mert a táblázatnak normális indexe van (ts), amelyre a lekérdezés támaszkodik?

Pontosan azért, mert ezt nem vetted figyelembe ts nem egyedi kulcs ebben a táblázatban. Valójában és értékei nem egyediek, mint minden „időpont” valós körülmények között - ezért két szomszédos lekérdezésben ugyanaz a rekord könnyen „ugrik” oldalról oldalra az eltérő végső sorrend miatt ugyanazon kulcsérték rendezése keretében.

Valójában itt van egy második probléma is, amit sokkal nehezebb észrevenni - egyes bejegyzések nem jelennek meg egyáltalán! Végül is a „másodlagos” rekordok valaki más helyét átvették. Részletes magyarázat gyönyörű képekkel megtalálható olvassa el itt.

Az index bővítése

Egy ravasz fejlesztő megérti, hogy az indexkulcsot egyedivé kell tenni, és a legegyszerűbb módja egy nyilvánvalóan egyedi mezővel bővíteni, amelyre a PK tökéletes:

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

És a kérés változik:

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

#2. Váltás „kurzorokra”

Egy idő után egy DBA érkezik hozzád, és „örül” az Ön kérésének pokolian terhelik a szervert az OFFSET szabályaikkal, és általában, ideje váltani navigáció az utolsó megjelenített értéktől. A lekérdezés ismét megváltozik:

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

Megkönnyebbülten fellélegezted, amíg meg nem jött...

#3. Tisztítási indexek

Mert egy nap a DBA-d olvasott cikk a nem hatékony indexek megtalálásáról és rájött arra A „nem a legújabb” időbélyeg nem jó. És újra eljöttem önhöz – most azzal a gondolattal, hogy ennek az indexnek még vissza kell alakulnia (ts DESC).

De mi a teendő a rekordok oldalak közötti „ugrásának” kezdeti problémájával?.. És minden egyszerű - ki kell választania rögzítetlen számú rekordot tartalmazó blokkokat!

Általában ki tiltja meg, hogy ne „pontosan 26-ot”, hanem „26-nál nem kevesebbet” olvassunk? Például úgy, hogy a következő blokkban vannak nyilvánvalóan eltérő jelentésű rekordok ts - akkor nem lesz probléma a rekordok blokkok közötti „ugrálásával”!

Ezt a következőképpen érheti el:

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;

Mi folyik itt?

  1. 25 rekordot léptetünk „lefelé”, és megkapjuk a „határ” értéket ts.
  2. Ha már nincs ott semmi, akkor cserélje ki a NULL értéket erre -infinity.
  3. A kapott értékből kivonjuk a teljes értékszegmenst ts és a felületről átadott $1 paraméter (az előző „utolsó” megjelenített érték).
  4. Ha egy blokk 26-nál kevesebb rekordot tartalmaz, akkor ez az utolsó.

Vagy ugyanaz a kép:
PostgreSQL antipatterns: Navigálás a beállításjegyzékben

Mert most megvan a mintának nincs konkrét „eleje”, akkor semmi sem akadályoz meg bennünket abban, hogy ezt a kérést az ellenkező irányba „kiterjesszük”, és az adatblokkok dinamikus betöltését a „referenciapontból” mindkét irányban – lefelé és felfelé is – végrehajtsuk.

megjegyzés

  1. Igen, ebben az esetben kétszer érjük el az indexet, de minden „tisztán index alapján”. Ezért egy segédlekérdezés csak azt eredményezi egy további Csak Index szkenneléshez.
  2. Teljesen nyilvánvaló, hogy ez a technika csak akkor használható, ha értékei vannak ts csak véletlenül léphet át, és nem sok van belőlük. Ha az Ön tipikus esete „egymillió rekord 00:00:00.000-kor”, akkor ezt nem szabad megtennie. Úgy értem, nem szabad megengedni, hogy ilyen eset megtörténjen. De ha ez megtörténik, használja a kiterjesztett indexű opciót.

Forrás: will.com

Hozzászólás