Antipaternuri PostgreSQL: navigarea în registru

Astăzi nu vor fi cazuri complicate și algoritmi pe SQL greu de înțeles. Totul va fi foarte simplu, la nivelul Căpitanului Evident — facem vizualizarea jurnalului de evenimente cu sortare după timp.

Deci, avem o tabelă în baza de date , care este utilizată pentru, iar aceasta are un câmp ts — exact acel moment de timp pe care dorim să-l afișăm ordonat:

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

CREATE INDEX ON events(ts DESC);

Este evident că nu vom avea doar câteva înregistrări, așadar ne va fi necesară într-o formă oarecare navigare paginată.

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

Asta nu este o glumă — rar, dar se întâlnește în natură. Uneori, după ce lucrezi cu ORM, este greu să te adaptezi la munca „directă” cu SQL.

Dar să trecem la probleme mai frecvente și mai puțin evidente.

#1. OFFSET

SELECT
  ...
FROM
  events
ORDER BY
  ts DESC
LIMIT 26 OFFSET $1; -- 26 - înregistrări pe pagină, $1 - începutul paginii

De unde a apărut acest număr 26? Este numărul aproximativ de înregistrări pentru a umple o pagină de ecran. Mai exact, 25 de înregistrări afișate, plus 1, care semnalează că mai există ceva în selecție și are sens să continuăm.

Desigur, această valoare nu trebuie „încapsulată” în corpul cererii, ci poate fi transmisă ca parametru. Dar, în acest caz, planificatorul PostgreSQL nu va putea să se bazeze pe cunoașterea faptului că ar trebui să existe relativ puține înregistrări, — și va alege cu ușurință un plan ineficient.

Și, în timp ce în interfața aplicației vizualizarea jurnalului este implementată ca o comutare între „pagini” vizuale, nimeni nu observă nimic suspect pentru mult timp. Exact până în momentul în care, în lupta pentru confortul UI/UX, se decide să se refacă interfața pentru „scroll infinit” — adică toate înregistrările jurnalului sunt desenate într-un singur listă, pe care utilizatorul o poate derula în sus și în jos.

Și astfel, în timpul unei testări, ești prins în duplicarea înregistrărilor în jurnal. De ce, căci pe tabelă există un index normal (ts), pe care se bazează cererea ta?

Exact din cauză că ts nu este o cheie unică în această tabelă. De fapt, și valorile sale nu sunt unice, ca la orice „timp” în condiții reale - așa că aceeași înregistrare în două interogări vecine sare ușor de pe o pagină pe alta datorită unei altor ordini finale în cadrul sortării valorii cheie identice.

De fapt, aici se ascunde și a doua problemă, pe care este mult mai dificil să o observi - anumite înregistrări nu vor fi afișate deloc! Căci înregistrările „duplicat” au ocupat locul cuiva. O explicație detaliată cu imagini frumoase poate fi citi aici.

Extindem indexul

Dezvoltatorul isteț înțelege - trebuie să facă cheia indexului unică, iar cel mai simplu mod - este să-l extindă cu un câmp cert unic, pentru care PK se potrivește perfect:

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

Și interogarea se schimbă:

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

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

După un timp, vine la tine un DBA și te „bucură” spunând că interogările tale încărcă serverul în mod absurd cu OFFSET-uri enorme, și, în general, ar fi vremea să treci la navigarea de la ultima valoare afișată. Interogarea ta se schimbă din nou:

SELECT
  ...
WHERE
  (ts, id) < ($1, $2) -- ultimele valori primite la pasul anterior
ORDER BY
  ts DESC, id DESC
LIMIT 26;

Ai respirat ușurat, până nu a venit...

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

Pentru că, odată, DBA-ul tău a citit un articol despre căutarea indecșilor ineficienți și a înțeles că timestamp-ul „neultim” - nu este deloc bine. Și a venit din nou la tine - acum cu gândul că acel index ar trebui să se transforme înapoi în (ts DESC).

Dar ce să facem cu problema inițială a „săriturilor” înregistrărilor între pagini?.. Totul e simplu - trebuie să selectăm blocuri cu un număr nespecificat de înregistrări!

În general, cine ne interzice să citim nu „exact 26”, ci „nu mai puțin de 26”? De exemplu, așa încât în blocul următor să fie înregistrări cu valori evident diferite ts — atunci nu vor exista probleme cu „săriturile” înregistrărilor între blocuri!

Iată cum poți realiza asta:

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

Ce se întâmplă aici de fapt?

1. Facem un pas în 25 de înregistrări „în jos” și obținem valoarea „limită” ts.
2. Dacă acolo nu mai este nimic, înlocuim valoarea NULL cu -infinity.
3. Scădem întreaga segment de valori între valoarea obținută ts și parametrul $1 transmis din interfață (predecesorul „ultim” afișat).
4. Dacă blocul s-a întors cu mai puțin de 26 de înregistrări, acesta este ultimul.

Sau același lucru, dar sub formă de imagine:

Deoarece acum avem selecția nu are un «început» definit., nu există nimic care să ne împiedice să „extindem” această interogare în direcția inversă și să realizăm încărcarea dinamică a blocurilor de date de la „punctul de referință” în ambele direcții – atât în jos, cât și în sus.

Observație

1. Da, în acest caz ne adresăm indexului de două ori, dar totul este „pur pe index”. Prin urmare, interogarea încorporată va conduce doar la un singur Index Only Scan..
2. Este evident că această metodă poate fi utilizată doar atunci când valorile ts se pot intersecta numai întâmplător, și sunt puține.. Dacă tipica ta situație este „un milion de înregistrări în 00:00:00.000”, atunci nu ar trebui să procedezi astfel. Adică, nu ar trebui să permiți o astfel de situație. Dar dacă s-a întâmplat așa, folosește varianta cu index extins.

Sursa: habr.com