🥇Mga Antipattern ng PostgreSQL: registry navigation

Ngayon ay walang mga kumplikadong kaso at sopistikadong algorithm sa SQL. Ang lahat ay magiging napaka-simple, sa antas ng Captain Obvious - gawin natin ito tinitingnan ang pagpapatala ng kaganapan inayos ayon sa oras.

Ibig sabihin, may sign sa database events, at mayroon siyang bukid ts - eksakto ang oras kung kailan namin gustong ipakita ang mga talang ito sa maayos na paraan:

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

CREATE INDEX ON events(ts DESC);

Malinaw na hindi kami magkakaroon ng isang dosenang talaan doon, kaya kakailanganin namin ng ilang anyo pag-navigate sa pahina.

#0. "Ako ang pogromista ng aking ina"

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

Ito ay halos hindi isang biro - ito ay bihira, ngunit matatagpuan sa ligaw. Minsan, pagkatapos magtrabaho sa ORM, maaaring mahirap lumipat sa "direktang" trabaho sa SQL.

Ngunit lumipat tayo sa mas karaniwan at hindi gaanong halata na mga problema.

#1. OFFSET

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

Saan nagmula ang numero 26? Ito ang tinatayang bilang ng mga entry upang punan ang isang screen. Mas tiyak, 25 ang nagpakita ng mga rekord, kasama ang 1, na nagpapahiwatig na may iba pa sa sample at makatuwirang magpatuloy.

Siyempre, ang halagang ito ay hindi maaaring "tatahiin" sa katawan ng kahilingan, ngunit dumaan sa isang parameter. Ngunit sa kasong ito, ang PostgreSQL scheduler ay hindi makakaasa sa kaalaman na dapat ay medyo kakaunti ang mga talaan - at madaling pumili ng isang hindi epektibong plano.

At habang nasa interface ng application, ang pagtingin sa registry ay ipinatupad bilang paglipat sa pagitan ng mga visual na "pahina," walang nakakapansin ng anumang kahina-hinala sa loob ng mahabang panahon. Eksakto hanggang sa sandali kung kailan, sa pakikibaka para sa kaginhawahan, nagpasya ang UI/UX na gawing "walang katapusang scroll" ang interface - iyon ay, ang lahat ng mga entry sa registry ay iginuhit sa isang listahan na maaaring mag-scroll pataas at pababa ng user.

At kaya, sa susunod na pagsubok, nahuli ka pagdoble ng mga talaan sa rehistro. Bakit, dahil ang talahanayan ay may normal na index (ts), kung saan umaasa ang iyong query?

Eksakto dahil hindi mo iyon isinasaalang-alang ts ay hindi isang natatanging susi sa talahanayang ito. Sa totoo lang, at ang mga halaga nito ay hindi natatangi, tulad ng anumang "oras" sa totoong mga kundisyon - samakatuwid, ang parehong tala sa dalawang magkatabing query ay madaling "tumalon" mula sa pahina patungo sa pahina dahil sa ibang panghuling pagkakasunud-sunod sa loob ng balangkas ng pag-uuri ng parehong key value.

Sa katunayan, mayroon ding pangalawang problema na nakatago dito, na mas mahirap mapansin - ang ilang mga entry ay hindi ipapakita sa lahat! Pagkatapos ng lahat, ang mga "duplicate" na tala ay pumalit sa ibang tao. Ang isang detalyadong paliwanag na may magagandang larawan ay matatagpuan basahin dito.

Pagpapalawak ng index

Nauunawaan ng isang tusong developer na kailangang gawing kakaiba ang index key, at ang pinakamadaling paraan ay palawakin ito sa isang malinaw na kakaibang field, kung saan ang PK ay perpekto para sa:

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

At ang kahilingan ay nagbabago:

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

#2. Lumipat sa "mga cursor"

Makalipas ang ilang oras, may DBA na darating sa iyo at "nalulugod" sa iyong mga kahilingan nilo-load nila ang server na parang impiyerno ng kanilang mga OFFSET na panuntunan, at sa pangkalahatan, oras na para lumipat sa nabigasyon mula sa huling halaga na ipinakita. Muling nag-mutate ang iyong query:

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

Nakahinga ka ng maluwag hanggang sa dumating...

#3. Paglilinis ng mga index

Dahil isang araw nagbasa ang DBA mo artikulo tungkol sa paghahanap ng mga hindi epektibong index at napagtanto iyon Hindi maganda ang "not the latest" timestamp. At muli akong lumapit sa iyo - ngayon sa pag-iisip na dapat pa ring bumalik ang index na iyon (ts DESC).

Ngunit ano ang gagawin sa paunang problema ng "paglukso" ng mga tala sa pagitan ng mga pahina?.. At ang lahat ay simple - kailangan mong pumili ng mga bloke na may hindi naayos na bilang ng mga tala!

Sa pangkalahatan, sino ang nagbabawal sa amin na basahin ang hindi "eksaktong 26", ngunit "hindi bababa sa 26"? Halimbawa, upang sa susunod na bloke ay mayroong mga talaan na may malinaw na magkakaibang kahulugan ts - pagkatapos ay walang magiging problema sa "paglukso" ng mga tala sa pagitan ng mga bloke!

Narito kung paano makamit ito:

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;

Anong nangyayari dito?

Ang hakbang 25 ay nagtatala kami ng "pababa" at nakuha ang halaga ng "hangganan". ts.
Kung wala na doon, pagkatapos ay palitan ang NULL na halaga ng -infinity.
Ibinabawas namin ang buong segment ng mga halaga sa pagitan ng natanggap na halaga ts at ang $1 na parameter ay naipasa mula sa interface (ang dating "huling" na-render na halaga).
Kung ang isang bloke ay ibinalik na may mas mababa sa 26 na mga tala, ito ang huli.

O ang parehong larawan:

Dahil ngayon mayroon na tayo ang sample ay walang anumang partikular na "simula", pagkatapos ay walang pumipigil sa amin na "palawakin" ang kahilingang ito sa kabilang direksyon at ipatupad ang dynamic na paglo-load ng mga bloke ng data mula sa "reference point" sa parehong direksyon - pababa at pataas.

Tandaan:

Oo, sa kasong ito, na-access namin ang index nang dalawang beses, ngunit ang lahat ay "purely by index". Samakatuwid, ang isang subquery ay magreresulta lamang sa sa isang karagdagang Index Only Scan.
Ito ay medyo halata na ang pamamaraan na ito ay magagamit lamang kapag mayroon kang mga halaga ts maaaring tumawid lamang kapag nagkataon, at hindi marami sa kanila. Kung ang iyong karaniwang kaso ay "isang milyong tala sa 00:00:00.000", hindi mo dapat gawin ito. I mean, hindi mo dapat hayaang mangyari ang ganoong kaso. Ngunit kung mangyari ito, gamitin ang opsyon na may pinahabang index.

Pinagmulan: www.habr.com

Mga Antipattern ng PostgreSQL: Pag-navigate sa Registry