ProHoster > Блог > Administracija > PostgreSQL antiobrasci: Kretanje kroz Registry

PostgreSQL antiobrasci: Kretanje kroz Registry

Danas neće biti složenih slučajeva i sofisticiranih algoritama u SQL-u. Sve će biti vrlo jednostavno, na nivou Kapetana Obviousa - uradimo to pregled registra događaja sortirano po vremenu.

To jest, postoji znak u bazi podataka events, i ona ima polje ts - tačno vrijeme u kojem želimo da prikažemo ove zapise na uredan način:

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

CREATE INDEX ON events(ts DESC);

Jasno je da tamo nećemo imati desetak ploča, pa će nam trebati neki oblik navigaciju po stranici.

#0. “Ja sam majčin pogromista”

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

Gotovo da nije šala - rijetko je, ali se može naći u divljini. Ponekad, nakon rada sa ORM-om, može biti teško prebaciti se na „direktan“ rad sa SQL-om.

No, prijeđimo na češće i manje očigledne probleme.

#1. OFFSET

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

Odakle broj 26? Ovo je približan broj unosa za popunjavanje jednog ekrana. Tačnije, 25 prikazanih zapisa, plus 1, što signalizira da postoji još barem nešto dalje u uzorku i da ima smisla ići dalje.

Naravno, ova vrijednost se ne može “ušiti” u tijelo zahtjeva, već proći kroz parametar. Ali u ovom slučaju, PostgreSQL planer neće moći da se osloni na saznanje da bi trebalo da bude relativno malo zapisa – i lako će izabrati neefikasan plan.

I dok je u interfejsu aplikacije gledanje registra implementirano kao prebacivanje između vizuelnih „stranica“, niko dugo ne primećuje ništa sumnjivo. Tačno do trenutka kada, u borbi za praktičnost, UI/UX odluči da prepravi interfejs na „beskrajno skrolovanje“ – to jest, svi unosi u registratoru budu nacrtani na jednoj listi koju korisnik može da skroluje gore-dole.

I tako, tokom sledećeg testiranja, vi ste uhvaćeni umnožavanje zapisa u registru. Zašto, jer tabela ima normalan indeks (ts), na koji se vaš upit oslanja?

Upravo zato što to niste uzeli u obzir ts nije jedinstven ključ u ovoj tabeli. Zapravo, i njegove vrijednosti nisu jedinstvene, kao i svako “vrijeme” u realnim uvjetima – dakle, isti zapis u dva susjedna upita lako “skače” sa stranice na stranicu zbog različitog konačnog redoslijeda u okviru sortiranja iste vrijednosti ključa.

U stvari, tu se krije i drugi problem, koji je mnogo teže uočiti - neki unosi neće biti prikazani uopšte! Na kraju krajeva, "duplikati" zapisa zauzeli su tuđe mjesto. Detaljno objašnjenje sa prekrasnim slikama možete pronaći read here.

Proširivanje indeksa

Lukavi programer razumije da ključ indeksa treba učiniti jedinstvenim, a najlakši način je proširiti ga očito jedinstvenim poljem, za koje je PK savršen:

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

I zahtjev mutira:

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

#2. Prebacite se na "kurzore"

Nešto kasnije dolazi vam DBA i "zadovoljan" je vašim zahtjevom oni opterećuju server kao pakao sa svojim OFFSET pravilima, i općenito, vrijeme je da se prebacite na navigacija od posljednje prikazane vrijednosti. Vaš upit ponovo mutira:

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

Odahnuo si sa olakšanjem dok nije došlo...

#3. Indeksi čišćenja

Jer jednog dana je vaš DBA pročitao članak o pronalaženju neefikasnih indeksa i to shvatio „nije najnovija“ vremenska oznaka nije dobra. I opet sam došao kod vas - sada s mišlju da bi se taj indeks ipak trebao vratiti (ts DESC).

Ali šta učiniti s početnim problemom "skakanje" zapisa između stranica?.. A sve je jednostavno - potrebno je odabrati blokove s nefiksiranim brojem zapisa!

Uopšte, ko nam zabranjuje da čitamo ne „tačno 26“, već „ne manje od 26“? Na primjer, tako da u sljedećem bloku postoje zapisi sa očigledno različitim značenjima ts - tada neće biti problema sa "preskakanjem" zapisa između blokova!

Evo kako to postići:

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;

sta se desava ovde?

  1. Korak 25 zapisa "dolje" i dobijemo "graničnu" vrijednost ts.
  2. Ako tamo već nema ničega, zamijenite vrijednost NULL sa -infinity.
  3. Od primljene vrijednosti oduzimamo cijeli segment vrijednosti ts i parametar $1 koji je proslijeđen iz interfejsa (prethodna „posljednja“ prikazana vrijednost).
  4. Ako se blok vrati s manje od 26 zapisa, on je posljednji.

Ili ista slika:
PostgreSQL antiobrasci: Kretanje kroz Registry

Jer sada imamo uzorak nema nikakav specifičan "početak", onda nas ništa ne sprječava da ovaj zahtjev “proširimo” u suprotnom smjeru i implementiramo dinamičko učitavanje blokova podataka iz “referentne točke” u oba smjera - i prema dolje i prema gore.

Napomena:

  1. Da, u ovom slučaju indeksu pristupamo dva puta, ali sve je „čisto po indeksu“. Stoga će potupit rezultirati samo na jedno dodatno skeniranje samo indeksa.
  2. Sasvim je očigledno da se ova tehnika može koristiti samo kada imate vrijednosti ts može preći samo slučajno, i nema ih mnogo. Ako je vaš tipičan slučaj „milion zapisa u 00:00:00.000“, ne biste trebali ovo raditi. Mislim, ne bi trebalo da dozvolite da se desi takav slučaj. Ali ako se to dogodi, koristite opciju s proširenim indeksom.

izvor: www.habr.com

Dodajte komentar