PostgreSQL Antipatterns: navigacija kroz registar

Danas neće biti nikakvih složenih slučajeva ili kompliciranih SQL algoritama. Sve će biti vrlo jednostavno, na nivou Kapitana Očiglednost — radimo pregled registra događaja s sortiranjem prema vremenu.

Dakle, ovdje imamo tabelu u bazi events, a ona ima polje ts — to je tačno ono vreme po kojem želimo da ove zapise prikazujemo sortirane:

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

CREATE INDEX ON events(ts DESC);

Jasno je da neće biti samo deset zapisa, zato će nam biti potrebna na neki način stranica navigacije.

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

Čak i skoro ne šala — retko, ali se sreće u divljini. Ponekad, nakon rada sa ORM-om, teško je prebaciti se na 'direktni' rad sa SQL-om.

Ali hajde da pređemo na uobičajenije i manje očigledne probleme.

#1. OFFSET

SELECT
  ...
FROM
  events
ORDER BY
  ts DESC
LIMIT 26 OFFSET $1; -- 26 - zapisa na stranici, $1 - početak stranice

Odakle ovde broj 26? To je okvirna količina zapisa koja popunjava jedan ekran. Tačnije, 25 prikazanih zapisa, plus 1, koja signalizira da u pretrazi ima još nečega i ima smisla nastaviti dalje.

Naravno, ovu vrednost ne treba 'ukalupljivati' u telo upita, već je predavati kao parametar. Ali u tom slučaju, planirnik PostgreSQL-a neće moći da se oslanja na saznanje da će biti relativno malo zapisa — i lako može izabrati neefikasan plan.

I dok je u interfejsu aplikacije pregled registra implementiran kao prebacivanje između vizuelnih 'stranica', niko dugo ne primećuje ništa sumnjivo. Tačno do trenutka kada se, u borbi za praktičnost UI/UX, odluče da redešavaju interfejs na 'beskonačno skrolovanje' — tj. svi zapisi registra se prikazuju kao jedinstvena lista koju korisnik može da pomera gore-dole.

I tako, prilikom još jednog testiranja uhvate vas na dupliranju zapisa u registru. Zašto, kada na tabeli postoji normalan indeks (ts), na koji se oslanja vaš upit?

Tačno zato što niste uzeli u obzir da ts nije jedinstveni ključ u ovoj tabeli. U suštini, ni vrednosti nisu jedinstvene, kao i kod svake "vrijeme" u realnim uvjetima — zato se ista stavka u dva susjedna upita lako "preskoči" sa stranice na stranicu zbog drugačijeg konačnog poretka unutar sortiranja iste vrijednosti ključa.

U stvari, ovdje se skriva još jedan problem koji je puno teže primijetiti — neke stavke se neće prikazati uopće! Jer su "duplicirane" stavke zauzele nečije mjesto. Detaljno objašnjenje s lijepim slikama možete pročitati ovdje.

Proširujemo indeks

Snalažljiv programer shvata — potrebno je učiniti ključ indeksa jedinstvenim, a najjednostavniji način je proširiti ga unaprijed jedinstvenim poljem, za što je PK odličan:

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

A upit se mutira:

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

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

Nakon nekog vremena dolazi do vas DBA i "raduje" vas činjenicom da vaši upiti strašno opterećuju server svojim konjskim OFFSET, i uopće, vrijeme je da pređete na navigaciju od posljednje prikazane vrijednosti. Vaš upit se opet mutira:

SELECT
  ...
WHERE
  (ts, id) < ($1, $2) -- posljednje primljene u prethodnom koraku vrijednosti
ORDER BY
  ts DESC, id DESC
LIMIT 26;

Olakšano ste uzdahnuli, sve dok nije došlo...

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

Zato što je jednom vaš DBA pročitao članak o pronalaženju neefikasnih indeksa i shvatio da "neposljednji" timestamp — to nije dobro. I opet je došao do vas — sada s mišlju da taj indeks ipak treba da se vrati u (ts DESC).

Ali šta da radimo s prvim problemom "skakanja" stavki između stranica?.. Sve je jednostavno — potrebno je birati blokove s nefixiranim brojem stavki!

Uopće, ko nam zabranjuje da čitamo ne "tačno 26", već "ne manje od 26"? Na primjer, tako da se u sljedećem bloku nađu stavke s unaprijed drugim vrijednostima ts — tada neće biti problema s "preskakanjem" stavki između blokova!

Evo kako to postići:

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

Šta se ovdje uopće dešava?

  1. Idemo na 25 stavki "dole" i dobijamo "graničnu" vrijednost ts.
  2. Ako tamo već nema ništa, zamjenjujemo NULL-vrijednost sa -infinity.
  3. Oduzimamo cijeli segment vrijednosti između dobijene vrijednosti ts i proslijeđene iz interfejsa parametra $1 (prethodne "posljednje" prikazane vrijednosti).
  4. Ako blok vrati manje od 26 zapisa, onda je to poslednji.

Isto to u slici:
PostgreSQL Antipatterns: navigacija kroz registar

Budući da sada imamo uzorak koji nema određeni 'početak', ništa nam ne preprečuje da 'okrenemo' ovaj upit unazad i implementiramo dinamičko učitavanje blokova podataka od 'referentne tačke' u obe strane — kako naniže, tako i naviše.

Napomena

  1. Da, u tom slučaju pristupamo indeksu dva puta, ali sve je 'čisto preko indeksa'. Stoga, ugnježdeni upit će dovesti do samo jednog dodatnog Index Only Scan-a.
  2. Izgleda prilično očigledno da se ovom metodom može koristiti samo kada imate vrednosti ts koje se mogu preklapati samo slučajno, i ima ih malo. Međutim, ako je vaš tipični slučaj 'milion zapisa u 00:00:00.000', tako ne treba raditi. U tom smislu, ne treba dopuštati takve slučajeve. Ali ako se već tako dogodilo, koristite varijantu s proširenim indeksom.

Izvor: habr.com

Kupite pouzdan hosting za sajtove uz zaštitu od DDoS, VPS VDS servere 🔥 Kupite pouzdan hosting za sajtove uz zaštitu od DDoS, VPS VDS servere | ProHoster