🥇PostgreSQL Antipatterns: skrásetning flakk

Í dag verða engin flókin tilvik og háþróuð reiknirit í SQL. Allt verður mjög einfalt, á stigi Captain Obvious - við skulum gera það skoða viðburðaskrána raðað eftir tíma.

Það er, það er merki í gagnagrunninum events, og hún hefur akur ts - nákvæmlega þann tíma sem við viljum birta þessar skrár á skipulegan hátt:

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

CREATE INDEX ON events(ts DESC);

Það er ljóst að við verðum ekki með tugi platna þar, svo við þurfum einhvers konar flakk á síðu.

#0. „Ég er pogromisti móður minnar“

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

Þetta er nánast ekki grín - það er sjaldgæft, en finnst í náttúrunni. Stundum, eftir að hafa unnið með ORM, getur verið erfitt að skipta yfir í „beina“ vinnu með SQL.

En við skulum halda áfram að algengari og minna augljós vandamál.

#1. OFFSET

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

Hvaðan kom talan 26? Þetta er áætlaður fjöldi færslna til að fylla einn skjá. Nánar tiltekið, 25 birtar færslur, auk 1, sem gefa til kynna að það sé að minnsta kosti eitthvað annað lengra í úrtakinu og skynsamlegt að halda áfram.

Auðvitað er ekki hægt að "sauma" þetta gildi inn í meginmál beiðninnar heldur fara í gegnum færibreytu. En í þessu tilviki mun PostgreSQL tímaáætlunarmaðurinn ekki geta reitt sig á þá vitneskju að það ættu að vera tiltölulega fáar skrár - og mun auðveldlega velja árangurslausa áætlun.

Og á meðan það er í forritaviðmótinu er að skoða skrásetninguna útfært sem að skipta á milli sjónrænna „síður“, tekur enginn eftir neinu grunsamlegu í langan tíma. Nákvæmlega þangað til, í þægindabaráttunni, ákveður UI/UX að endurgera viðmótið í „endalausa flettu“ - það er að segja allar skrásetningarfærslur eru dregnar á einn lista sem notandinn getur skrunað upp og niður.

Og svo, við næstu prófun, ertu veiddur fjölföldun gagna í skránni. Af hverju, vegna þess að taflan er með eðlilega vísitölu (ts), sem fyrirspurn þín byggir á?

Einmitt vegna þess að þú tókst ekki tillit til þess ts er ekki einstakur lykill í þessari töflu. Reyndar, og gildi þess eru ekki einstök, eins og hvaða „tími“ sem er við raunverulegar aðstæður - þess vegna „hoppar“ sama skráin í tveimur aðliggjandi fyrirspurnum auðveldlega frá síðu til síðu vegna mismunandi lokaröðunar innan ramma þess að flokka sama lykilgildi.

Reyndar er líka annað vandamál falið hér, sem er mun erfiðara að taka eftir - sumar færslur verða ekki sýndar yfirleitt! Þegar öllu er á botninn hvolft komu „afrit“ færslurnar í stað einhvers annars. Nákvæm útskýring með fallegum myndum er að finna lesið hér.

Stækkun vísitölunnar

Slægur verktaki skilur að vísitölulykillinn þarf að vera einstakur og auðveldasta leiðin er að stækka hann með augljóslega einstöku sviði, sem PK er fullkomið fyrir:

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

Og beiðnin stökkbreytist:

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

#2. Skiptu yfir í „bendlar“

Nokkru síðar kemur DBA til þín og er "ánægður" með að beiðnir þínar þeir hlaða servernum eins og helvíti með OFFSET reglum sínum, og almennt er kominn tími til að skipta yfir í flakk frá síðasta gildi sýnt. Fyrirspurnin þín stökkbreytist aftur:

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

Þú andar léttar þangað til það kom...

#3. Hreinsunarvísitölur

Vegna þess að einn daginn las DBA þinn grein um að finna árangurslausar vísitölur og áttaði sig á því „ekki nýjasti“ tímastimpillinn er ekki góður. Og ég kom til þín aftur - nú með þá hugsun að sú vísitala ætti enn að breytast í (ts DESC).

En hvað á að gera við upphafsvandamálið að „hoppa“ færslum á milli síðna?.. Og allt er einfalt - þú þarft að velja kubba með óákveðnum fjölda færslur!

Almennt séð, hver bannar okkur að lesa ekki „nákvæmlega 26“ heldur „ekki færri en 26“? Til dæmis, þannig að í næstu blokk eru skrár með augljóslega mismunandi merkingu ts - þá verður ekki vandamál með að „hoppa“ færslum á milli blokka!

Svona á að ná þessu:

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;

Hvað er í gangi hér?

Við stígum 25 skrár „niður“ og fáum „mörk“ gildið ts.
Ef það er ekkert þar þegar, skiptu þá út NULL gildinu fyrir -infinity.
Við drögum frá allan hluta gildanna á milli móttekins gildis ts og $1 færibreytan send úr viðmótinu (fyrra „síðasta“ birta gildi).
Ef blokk er skilað með færri en 26 færslum er það sú síðasta.

Eða sama myndin:

Því nú höfum við sýnishornið hefur ekkert sérstakt „upphaf“, þá kemur ekkert í veg fyrir að við „útvíkka“ þessa beiðni í gagnstæða átt og innleiða kraftmikla hleðslu á gagnablokkum frá „viðmiðunarpunktinum“ í báðar áttir - bæði niður og upp.

Athugaðu:

Já, í þessu tilfelli fáum við aðgang að vísitölunni tvisvar, en allt er „aðeins eftir vísitölu“. Þess vegna mun undirfyrirspurn aðeins leiða til í eina viðbótar Index Only Scan.
Það er alveg augljóst að þessi tækni er aðeins hægt að nota þegar þú hefur gildi ts getur farið aðeins fyrir tilviljun, og þeir eru ekki margir. Ef dæmigert tilvik þitt er „milljón færslur klukkan 00:00:00.000“, ættirðu ekki að gera þetta. Ég meina, þú ættir ekki að leyfa svona tilfelli að gerast. En ef þetta gerist, notaðu valkostinn með framlengdri vísitölu.

Heimild: www.habr.com

PostgreSQL Antipatterns: Vafra um Registry