ProHoster > Blog > uprava > PostgreSQL Antipatterns: Navigacija registrom

PostgreSQL Antipatterns: Navigacija registrom

Danas neće biti složenih slučajeva i sofisticiranih algoritama u SQL-u. Sve će biti vrlo jednostavno, na razini Captain Obvious - let's do it pregled registra događaja poredano po vremenu.

Odnosno, postoji znak u bazi podataka events, a ona ima njivu ts - točno vrijeme u kojem želimo prikazati 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 navigacija stranicom.

#0. “Ja sam mamin pogromist”

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 - rijedak je, ali ga ima u divljini. Ponekad, nakon rada s ORM-om, može biti teško prebaciti se na "izravan" rad sa SQL-om.

Ali prijeđimo na češće i manje očite 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 zaslona. Točnije, 25 prikazanih zapisa, plus 1, koji signalizira da postoji barem još nešto dalje u uzorku i da ima smisla krenuti dalje.

Naravno, ova se vrijednost ne može “ušiti” u tijelo zahtjeva, već se propušta kroz parametar. Ali u ovom slučaju, PostgreSQL planer se neće moći osloniti na saznanje da bi trebalo biti relativno malo zapisa - i lako će izabrati neučinkovit plan.

I dok je u sučelju aplikacije pregled registra implementiran kao prebacivanje između vizualnih "stranica", nitko dugo ne primjećuje ništa sumnjivo. Točno do trenutka kada, u borbi za praktičnošću, UI/UX odluči preraditi sučelje na “beskrajni scroll” – odnosno, svi unosi u registru su iscrtani u jednu listu koju korisnik može scrollati gore-dolje.

I tako, tijekom sljedećeg testiranja, uhvaćeni ste umnožavanje zapisa u registru. Zašto, zato što tablica ima normalan indeks (ts), na što se oslanja vaš upit?

Upravo zato što to niste uzeli u obzir ts nije jedinstven ključ u ovoj tablici. Zapravo, i njegove vrijednosti nisu jedinstvene, kao i svako “vrijeme” u stvarnim 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.

Zapravo, tu se krije i drugi problem koji je mnogo teže uočiti - neki unosi neće biti prikazani uopće! Uostalom, "duplikati" zapisa zauzeli su tuđe mjesto. Možete pronaći detaljno objašnjenje s prekrasnim slikama pročitajte ovdje.

Proširenje indeksa

Lukav 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. Prijeđi na "pokazivače"

Nešto kasnije, DBA dolazi do vas i "zadovoljan" je vašim zahtjevima opterećuju server kao vrag svojim OFFSET pravilima, i općenito, vrijeme je za prijelaz na navigacija od posljednje prikazane vrijednosti. Vaš upit opet mutira:

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

Odahnuo si dok nije došlo...

#3. Čišćenje indeksa

Jer jednog dana vaš DBA čita članak o pronalaženju neučinkovitih indeksa i shvatio da Vremenska oznaka "nije najnovija" nije dobra. I opet sam ti došao - sada s mišlju da se taj indeks ipak treba vratiti (ts DESC).

Ali što učiniti s početnim problemom "skakanja" zapisa između stranica?.. I sve je jednostavno - trebate odabrati blokove s nefiksiranim brojem zapisa!

Općenito, tko nam zabranjuje čitati ne "točno 26", već "ne manje od 26"? Na primjer, tako da u sljedećem bloku postoje zapisa s očito različitim značenjima ts - tada neće biti problema s "skakanjem" 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;

Što se ovdje događa?

  1. Koračamo 25 zapisa "dolje" i dobivamo "graničnu" vrijednost ts.
  2. Ako tamo već nema ničega, zamijenite NULL vrijednost s -infinity.
  3. Oduzimamo cijeli segment vrijednosti između primljene vrijednosti ts i parametar $1 proslijeđen iz sučelja (prethodna "posljednja" prikazana vrijednost).
  4. Ako se blok vrati s manje od 26 zapisa, to je zadnji.

Ili ista slika:
PostgreSQL Antipatterns: Navigacija registrom

Jer sada imamo uzorak nema nikakav određeni "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.

primjedba

  1. Da, u ovom slučaju indeksu pristupamo dva puta, ali sve je “čisto po indeksu”. Stoga će podupit rezultirati samo s na jedno dodatno skeniranje samo indeksa.
  2. Sasvim je očito da se ova tehnika može koristiti samo ako imate vrijednosti ts može prijeći samo slučajno, i nema ih mnogo. Ako je vaš tipični slučaj "milijun zapisa na 00:00:00.000", ne biste to trebali učiniti. Mislim, ne biste smjeli dopustiti da se dogodi takav slučaj. Ali ako se to dogodi, upotrijebite opciju s proširenim indeksom.

Izvor: www.habr.com

Dodajte komentar