PostgreSQL Antipatterns: navigācija pa reģistru

Šodien nebūs nekādu sarežģītu gadījumu un muļķīgu algoritmu SQL. Viss būs ļoti vienkārši, Kapteiņa Acīmredzamība līmenī — darām notikumu reģistra apskate sacensībām pa laiku.

Tātad šajā datubāzē ir tabula events, un tai ir lauks ts — tieši tas pats laiks, pēc kura mēs vēlamies šos ierakstus kārtot parādīt:

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

CREATE INDEX ON events(ts DESC);

Ir skaidrs, ka ierakstu tur būs ne desmit, tādēļ mums kaut kādā veidā lappušu navigācija.

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

Patiesībā tā nav pat joks — tas notiek reti, bet dažreiz sastopams brīvā dabā. Dažreiz pēc darba ar ORM ir grūti pāriet uz "tiešo" darbu ar SQL.

Bet pāriesim pie izplatītākām un mazāk acīmredzamām problēmām.

#1. OFFSET

SELECT
  ...
FROM
  events
ORDER BY
  ts DESC
LIMIT 26 OFFSET $1; -- 26 - ierakstu skaits lapā, $1 - lappuses sākums

Kāpēc šeit ir skaitlis 26? Tas ir aptuvenais ierakstu skaits, kas nepieciešams viena ekrāna aizpildīšanai. Precīzāk, 25 attēlotie ieraksti, plus 1, kas signalizē, ka tālāk izvēlē ir kaut kas cits, un ir jēga turpināt.

Protams, šo vērtību varētu nepievienot jautājumam, bet nodot to kā parametru. Bet šajā gadījumā PostgreSQL plānotājs nevarēs paļauties uz zināšanām, ka ierakstu vajadzētu būt salīdzinoši maz, — un bildētu neefektīvu plānu.

Un kamēr lietojumprogrammas saskarnē notikumu reģistra apskate tiek īstenota kā pārslēgšanās starp vizuālām "lappusēm", neviens ilgi nemanīs neko aizdomīgu. Tieši līdz brīdim, kad cīņā par ērtumu UI/UX nolemj pārveidot saskarni uz "bezgalīgo ritināšanu" — tas ir, visi reģistra ieraksti tiek parādīti vienā sarakstā, ko lietotājs var ritināt uz augšu un uz leju.

Un, veicot kārtējo testēšanu, jūs sastopat ierakstu dublēšanu reģistrā. Kāpēc, ja tabulai ir normāls indekss (ts), uz kuru balstās jūsu pieprasījums?

Tieši tāpēc, ka jūs neesat ņēmuši vērā, ka ts nav unikāls atslēga šajā tabulā. Patiesībā arī tā vērtības nav unikālas, kā jebkurā “laikā” reālajos apstākļos — tāpēc viena un tā pati ieraksts divās blakus esošās pieprasījumos viegli “lēc” no lapas uz lapu, pateicoties citam galīgajam secībā tādas pašas atslēgas vērtības kārtībā.

Patiesībā šeit slēpjas vēl otra problēma, kuru pamanīt ir daudz grūtāk — daži ieraksti vispār netiks parādīti! Jo “duplikāti” ieraksti aizņēma kāda cita vietu. Detalizētu skaidrojumu ar skaistām bildēm var izlasīt šeit.

Paplašinām indeksu

Viltīgs izstrādātājs saprot — jāpadara indeksa atslēga unikāla, un vienkāršākais veids ir to paplašināt ar iepriekš noteiktu unikālu lauku, kā tam lieliski piemērots būs PK:

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

Un pieprasījums mutē:

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

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

Pēc kāda laika pie jums nāk DBA un “priecē”, ka jūsu pieprasījumi brutāli noslogo serveri ar saviem milzīgajiem OFFSET, un vispār, ir laiks pāriet uz navigāciju no pēdējā rādītā vērtība. Jūsu pieprasījums atkal mutē:

SELECT
  ...
WHERE
  (ts, id) < ($1, $2) -- pēdējās iegūtās vērtības iepriekšējā solī
ORDER BY
  ts DESC, id DESC
LIMIT 26;

Jūs atviegloti ieelpojat, līdz nebeidzās...

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

Tāpēc, ka kādu dienu jūsu DBA izlasīja rakstu par neefektīvu indeksu meklēšanu un saprata, ka “ne pēdējais” timestamps — tas nav labi.Un atkal atnāca pie jums — tagad ar domu, ka šim indeksam tomēr vajadzētu atgriezties (ts DESC).

Bet ko darīt ar sākotnējo problēmu “lēciena” ierakstiem starp lapām?.. Viss ir vienkārši — ir jāizvēlas bloki ar nefixētu ierakstu skaitu!

Vispār, kurš mums aizliedz lasīt ne “precīzi 26”, bet “ne mazāk kā 26”? Piemēram, tā, lai nākamajā blokā būtu ieraksti ar noteikti citādām vērtībām ts — tad jau nebūs arī problēmu ar “lēcieniem” ierakstiem starp blokiem!

Šādi to var panākt:

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

Kas šeit vispār notiek?

1. Izsoļojam par 25 ierakstiem “uz leju” un iegūstam “robežu” vērtību ts.
2. Ja tur vairs nav neko, tad nomainām NULL vērtību uz -infinity.
3. Izslēdzam visu vērtību segmentu starp iegūto vērtību ts un nodoto no saskarnes parametru $1 (iepriekšējo “pēdējo” uzzīmēto vērtību).
4. Ja, ja, blokam, kas atgriežas ar mazāk nekā 26 ierakstiem — tas ir pēdējais.

Vai tas pašas parādās attēlā:

Tagad mums ir paraugs nav noteikts "sākums", tāpēc nekas neļauj mums "pagriezt" šo pieprasījumu pretējā virzienā un realizēt dinamisko datu bloku ielādēšanu no "atsauces punkta" abos virzienos — gan uz leju, gan uz augšu.

Piezīme

1. Jā, šādā gadījumā mēs piekļūstam indeksam divreiz, taču viss "tīri pēc indeksa". Tāpēc iegultais pieprasījums radīs tikai vienu papildu Index Only Scan.
2. Pietiekami acīmredzami, ka šī metodika ir izmantojama tikai tad, ja jums ir vērtības ts var pārsniegt tikai nejauši, un to ir maz. Ja jūsu tipisks gadījums ir "miljons ierakstu 00:00:00.000", tad tā nedrīkst darīt. Proti, nedrīkst pieļaut šādu gadījumu. Bet, ja tā nu ir sanācis, izmantojiet paplašinātā indikatora variantu.

Avots: habr.com