🥇Mga Antipattern sa PostgreSQL: nabigasyon sa rehistro

Karon wala’y komplikado nga mga kaso ug sopistikado nga mga algorithm sa SQL. Ang tanan mahimong yano ra, sa lebel sa Kapitan Obvious - buhaton naton kini pagtan-aw sa rehistro sa panghitabo gisunod sa panahon.

Sa ato pa, naay sign sa database events, ug siya adunay uma ts - eksakto ang oras kung diin gusto namon ipakita kini nga mga rekord sa hapsay nga paagi:

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

CREATE INDEX ON events(ts DESC);

Klaro nga wala kami usa ka dosena nga mga rekord didto, mao nga kinahanglan namon ang usa ka porma sa panid nabigasyon.

#0. "Ako ang pogromista sa akong inahan"

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

Kini halos dili usa ka komedya - kini talagsaon, apan makita sa ihalas nga mga. Usahay, pagkahuman sa pagtrabaho kauban ang ORM, mahimong lisud ang pagbalhin sa "direkta" nga trabaho sa SQL.

Apan magpadayon kita sa mas komon ug dili kaayo klaro nga mga problema.

#1. OFFSET

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

Diin gikan ang numero 26? Kini ang gibana-bana nga gidaghanon sa mga entri aron pun-on ang usa ka screen. Sa mas tukma, 25 ang gipakita nga mga rekord, dugang ang 1, nga nagsenyas nga adunay labing menos usa ka butang nga labi pa sa sample ug makatarunganon nga magpadayon.

Siyempre, kini nga kantidad dili mahimong "pagtahi" sa lawas sa hangyo, apan gipasa sa usa ka parameter. Apan sa kini nga kaso, ang PostgreSQL scheduler dili makasalig sa kahibalo nga kinahanglan adunay medyo gamay nga mga rekord - ug dali nga makapili usa ka dili epektibo nga plano.

Ug samtang sa interface sa aplikasyon, ang pagtan-aw sa rehistro gipatuman ingon nga pagbalhin tali sa biswal nga "mga panid," walay usa nga nakamatikod sa bisan unsa nga kadudahan sa dugay nga panahon. Eksakto hangtod sa takna kung kanus-a, sa pakigbisog alang sa kasayon, ang UI / UX nakahukom nga himuon pag-usab ang interface sa "walay katapusan nga pag-scroll" - nga mao, ang tanan nga mga entry sa registry gikuha sa usa ka lista nga mahimo’g mag-scroll pataas ug paubos ang user.

Ug busa, sa sunod nga pagsulay, nadakpan ka pagdoble sa mga rekord sa rehistro. Ngano, tungod kay ang lamesa adunay usa ka normal nga indeks (ts), diin nagsalig ang imong pangutana?

Eksakto tungod kay wala nimo kini tagda ts dili talagsaon nga yawe niini nga lamesa. Sa tinuod, ug ang mga bili niini dili talagsaon.

Sa tinuud, adunay usa usab ka ikaduha nga problema nga gitago dinhi, nga labi ka lisud nga mamatikdan - pipila ka mga entry dili ipakita sa tanan! Human sa tanan, ang "duplicate" nga mga rekord mipuli sa laing tawo. Makita ang usa ka detalyado nga katin-awan nga adunay matahum nga mga litrato basaha dinhi.

Pagpalapad sa indeks

Ang usa ka tuso nga developer nakasabut nga ang indeks nga yawe kinahanglan nga himuon nga talagsaon, ug ang labing kadali nga paagi mao ang pagpalapad niini sa usa ka klaro nga talagsaon nga natad, diin ang PK perpekto alang sa:

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

Ug ang hangyo mutate:

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

#2. Pagbalhin sa "cursors"

Paglabay sa pipila ka panahon, usa ka DBA ang moabut kanimo ug "nalipay" sa imong mga hangyo gikarga nila ang server sama sa impyerno sa ilang mga OFFSET nga mga lagda, ug sa kinatibuk-an, panahon na nga mobalhin sa nabigasyon gikan sa katapusang bili nga gipakita. Ang imong pangutana mutate pag-usab:

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

Nakaginhawa ka sa kahupayan hangtod niabot...

#3. Paglimpyo sa mga indeks

Kay usa ka adlaw nagbasa imong DBA artikulo bahin sa pagpangita sa dili epektibo nga mga indeks ug nakaamgo niana Ang "dili ang pinakabag-o" nga timestamp dili maayo. Ug mianhi ako kanimo pag-usab - karon uban ang hunahuna nga kana nga indeks kinahanglan pa nga mobalik (ts DESC).

Apan unsa ang buhaton sa una nga problema sa "paglukso" nga mga rekord sa taliwala sa mga panid?

Sa kinatibuk-an, kinsa ang nagdili kanato sa pagbasa nga dili "eksaktong 26", apan "dili moubos sa 26"? Pananglitan, aron sa sunod nga block adunay mga rekord nga klaro nga lainlain ang kahulugan ts - unya wala'y problema sa "paglukso" nga mga rekord tali sa mga bloke!

Ania kung unsaon pagkab-ot niini:

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;

Unsay nahitabo dinhi?

Ang lakang sa 25 nagrekord sa "down" ug makuha ang "boundary" value ts.
Kung wala na didto, ilisan ang NULL nga kantidad sa -infinity.
Gikuha namon ang tibuuk nga bahin sa mga kantidad tali sa nadawat nga kantidad ts ug ang $1 nga parametro gipasa gikan sa interface (ang miaging “katapusan” gihubad nga bili).
Kung ang usa ka bloke ibalik nga wala’y 26 nga mga rekord, kini ang katapusan.

O parehas nga litrato:

Kay karon naa mi ang sample walay bisan unsang piho nga "sinugdan", unya walay makapugong kanamo sa "pagpalapad" niini nga hangyo sa atbang nga direksyon ug pagpatuman sa dinamikong pagkarga sa mga bloke sa datos gikan sa "reference point" sa duha ka direksyon - paubos ug pataas.

Mubo nga sulat

Oo, sa kini nga kaso kita maka-access sa indeks sa makaduha, apan ang tanan "lunsay pinaagi sa indeks". Busa, ang usa ka subquery moresulta lamang sa sa usa ka dugang nga Index Only Scan.
Klaro kaayo nga kini nga teknik magamit ra kung ikaw adunay mga kantidad ts makatabok lamang sa sulagma, ug dili daghan kanila. Kung ang imong kasagaran nga kaso mao ang "usa ka milyon nga mga rekord sa 00:00:00.000", dili nimo kini buhaton. Buot ipasabot, dili nimo tugotan nga mahitabo ang ingon niini nga kaso. Apan kung mahitabo kini, gamita ang kapilian nga adunay gipadako nga indeks.

Source: www.habr.com

Mga Antipattern sa PostgreSQL: Pag-navigate sa Registry