PostgreSQL Antipatternid: registri navigeerimine

Täna ei tule mingeid keerulisi juhtumeid ja nutikaid SQL algoritme. Kõik on väga lihtne, Kapitani Ilmsuse tasemel — teeme sündmuste registri vaatamine aja järgi sortimisega.

Ehk siis siin on andmebaasis tabel events, ja tal on väli ts — just see aeg, mille järgi me soovime neid kirjeid korrektselt kuvada:

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

CREATE INDEX ON events(ts DESC);

On arusaadav, et meil on seal mitte kümme kirjet, seega vajame mingis vormis leheküljelist navigeerimist.

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

Peaaegu mitte naljaks — harva, aga esineb looduslikus keskkonnas. Mõnikord pärast tööd ORM-iga on raske ümber harjuda „otsesele“ SQL-iga töötamisele.

Kuid liikume edasi levinumate ja vähem ilmselgete probleemide juurde.

#1. OFFSET

SELECT
  ...
FROM
  events
ORDER BY
  ts DESC
LIMIT 26 OFFSET $1; -- 26 - kirjeid lehe peal, $1 - lehe algus

Kust tuli number 26? See on umbkaudne arv kirjeid ühe ekraani täitmiseks. Täpsemalt, 25 kuvatavat kirjete ning 1, mis näitab, et valikus on midagi veel ja tasub edasi liikuda.

Muidugi, seda väärtust ei pea "sissetegema" päringu kehas, vaid saab edastada parameetrina. Kuid sel juhul ei saa PostgreSQL-i planeerija tugineda teadmisele, et kirjeid peaks olema suhteliselt vähe, — ja võib lihtsalt valida ebaefektiivse plaani.

Ja seni, kuni rakenduse liideses toimub registri vaatamine visuaalsete "lehtede" vahel vahetamisena, ei märka keegi pikka aega midagi kahtlast. Just kuni hetkeni, mil UI/UX mugavuse nimel otsustatakse liides ümber kujundada "lõputuks kerimiseks" — see tähendab, et kõik registri kirjed kuvatakse ühe loendina, mida kasutaja saab üles-alla kerida.

Ja nii, järgmise testimise käigus tabatakse teid kirjete dubleerimisel registris. Miks, kui tabelis on normaalne indeks (ts), millele teie päring tugineb?

Täpsemalt seetõttu, et te ei arvestanud, et ts ei ole unikaalne võtme selles tabelis. Tegelikult on tema väärtused ei ole unikaalsed, nagu iga «aeg» reaalses elus — seetõttu võib sama kirje kahe kõrvutise päringu puhul kergesti «hüppata» ühelt lehelt teisele, kuna sama võtme sorteerimise raames on järjestus erinev.

Tegelikkuses peitub siin veel teine probleem, mida on palju keerulisem märgata — mõned kirjed ei kuvata üldse! Lõppude lõpuks on «dubleeritud» kirjed kelleltki koha ära võtnud. Üksikasjalikku seletust ilusa pildimaterjaliga võib lugeda siit.

Laiena indekseed

Kaval arendaja mõistab — tuleb teha indeksivõti unikaalseks, ja kõige lihtsam viis — laiendada seda tõendatud unikaalse väljana, milleks sobib PK:

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

Ja päring muutub:

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

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

Mõne aja pärast tuleb teie juurde DBA ja "rõõmustab", et teie päringud koormavad serverit oma hiiglaslike OFFSET'itega, ja üldiselt, oleks paslik minna navigatsioonile viimasest kuvatud väärtusest. Teie päring muutub jälle:

SELECT
  ...
WHERE
  (ts, id) < ($1, $2) -- viimased saadud väärtused eelmisest sammust
ORDER BY
  ts DESC, id DESC
LIMIT 26;

Te hingatsed kergendatult, kuni...

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

sest kord luges su DBA artiklit ebaefektiivsete indeksite leidmisest ja taipas, et «mitte viimase» ajatempli olemasolu on halvad uudised.Ja ta tuli sinu juurde tagasi — nüüd mõttega, et see indeks peaks ikkagi muutuma tagasi (ts DESC).

Aga mis teha algse probleemiga «lehekülgede vahel hüppamise» kohta?.. Kõik on lihtne — tuleb valida blokk, millel pole fikseeritud arvu kirjeid!

Tegelikult, kes meil keelab lugeda mitte «just 26», vaid «vähemalt 26»? Näiteks nii, et järgmises plokis on kirjed, millel on teadaolevalt erinevad väärtused ts — siis ei ole ju ka probleem «kirjete vahel hüppamise» osas!

Nii saavutad seda:

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

Mis siin üldse toimub?

1. Astume 25 kirje «alla» ja saame «piirväärtuse» ts.
2. Kui seal pole enam midagi, siis asendame NULL-väärtuse -infinity.
3. Lugedes välja kogu väärtuste segmenti, mis on saadud väärtuse vahel. ts ja ja antud liidese parameetri $1 (eelmine «viimane» joonistatud väärtus).
4. Kui plokk tagastati vähem kui 26 kirjeid — see on viimane.

Või sama pildina:

Kuna nüüd on meil valim ei oma mingit kindlat «algust», ei takista meid miski «pöörata» seda päringut tagurpidi ja rakendada dünaamilist andmeplokkide allalaadimist «toetuspunktilt» mõlemas suunas — nii alla kui üles.

Märkus

1. Jah, sel juhul pöördume indeksi poole kaks korda, kuid kõik on «puhtalt indeksi» kaudu. Seega toob sisemine päring kaasa vaid ühe täiendava Index Only Scan.
2. On ilmselge, et seda tehnikat saab kasutada vaid siis, kui teie väärtused ts võivad vaid juhuslikult üksteisega kokku langeda, ja neid on vähe.. Kui aga teie tüüpiline juhtum on «miljon kirjet kell 00:00:00.000», siis nii ei tohiks teha. Ehkki sellist juhtumit ei tohiks lubada. Kuid kui olukord on nii valmis saanud, kasutage laiendatud indeksi varianti.

Allikas: habr.com