ProHoster > блог > адміністраванне > PostgreSQL Antipatterns: рух па рэестры

PostgreSQL Antipatterns: рух па рэестры

Сёння не будзе ніякіх складаных кейсаў і складаных алгарытмаў на SQL. Усё будзе вельмі проста, на ўзроўні Капітана Відавочна - робім прагляд рэестра падзей з сартаваннем па часе.

Гэта значыць, вось ляжыць у базе таблічка events, а ў яе поле ts - роўна той самы час, па якім мы жадаем гэтыя запісы ўпарадкавана паказваць:

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

CREATE INDEX ON events(ts DESC);

Зразумела, што запісаў у нас там будзе не дзясятак, таму нам спатрэбіцца ў нейкім выглядзе. пастаронкавая рух.

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

Нават амаль не жарт - рэдка, але сустракаецца ў дзікай прыродзе. Часам пасля працы з ORM бывае цяжка перабудавацца на "прамую" працу з SQL.

Але давайце пяройдзем да больш распаўсюджаных і менш відавочных праблем.

#1. OFFSET

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

Адкуль тут узялося лік 26? Гэта прыкладная колькасць запісаў для запаўнення аднаго экрана. Дакладней, 25 паказу запісаў, плюс 1, якая сігналізуе, што далей у выбарцы хоць нешта яшчэ ёсць і мае сэнс рухацца далей.

Вядома, гэтае значэнне можна не «вашываць» у цела запыту, а перадаваць праз параметр. Але ў гэтым выпадку планавальнік PostgreSQL не зможа абаперціся на веданне, што запісаў павінна апынуцца адносна няшмат, – і папросту абярэ неэфектыўны план.

І пакуль у інтэрфейсе прыкладання прагляд рэестра рэалізаваны як пераключэнне паміж візуальнымі "старонкамі", ніхто доўга не заўважае нічога падазронага. Роўна да моманту, калі ў дужанні за выгода UI/UX не вырашаюць перарабіць інтэрфейс на «бясконцы скролл» — гэта значыць усе запісы рэестра малююцца адзіным спісам, які карыстач можа круціць уверх-уніз.

І вось, пры чарговым тэставанні вас ловяць на дубліраванні запісаў у рэестры. Чаму, бо на табліцы ёсць нармальны індэкс (ts), на які абапіраецца ваш запыт?

Роўна таму, што вы не ўлічылі, што ts не з'яўляецца унікальным ключом у гэтай табліцы. Уласна, і значэння ў яго не ўнікальныя, як у любога "часу" ў рэальных умовах - таму адзін і той жа запіс у двух суседніх запытах лёгка "пераскоквае" са старонкі на старонку за кошт іншага канчатковага парадку ў рамках сартавання аднолькавага значэння ключа.

Насамрэч, тут прыхаваная яшчэ і другая праблема, якую заўважыць шмат складаней. некаторыя запісы не будуць паказаны зусім! Бо запісы, якія «здубліраваліся», занялі нечае месца. Падрабязнае тлумачэнне з прыгожымі карцінкамі можна прачытаць тут.

Пашыраем індэкс

Хітры распрацоўнік разумее трэба зрабіць ключ азначніка ўнікальным, а самы просты спосаб пашырыць яго загадзя ўнікальным полем у якасці якога выдатна падыдзе PK:

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

А запыт муціруе:

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

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

Праз некаторы час да вас прыходзіць DBA і «радуе», што вашыя запыты пякельна грузяць сервер сваімі конскімі OFFSET, і наогул, пара б перайсці на навігацыю ад апошняга паказанага значэння. Ваш запыт муціруе зноў:

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

Вы з палёгкай уздыхнулі, пакуль не наступіла…

#3. Чыстка індэксаў

Таму што аднойчы ваш DBA прачытаў артыкул пра пошук неэфектыўных індэксаў і зразумеў, што «неапошні» timestamp – гэта нядобра. І зноў прыйшоў да вас - зараз з думкай, што вось той індэкс павінен усё ж такі ператварыцца назад у (ts DESC).

Але што ж рабіць з першапачатковай праблемай "скакання" запісаў паміж старонкамі?.. А усё проста - трэба выбіраць блокі з нефіксаванай колькасцю запісаў!

Наогул, хто нам забараняе чытаць не "роўна 26", а "не менш за 26"? Напрыклад, так, каб у наступным блоку аказаліся запісы з загадзя іншымі значэннямі ts - тады бо і праблемы з «пераскокваннем» запісаў паміж блокамі не будзе!

Вось як гэтага дабіцца:

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;

Што тут увогуле адбываецца?

  1. Крочаем на 25 запісаў «уніз» і атрымліваем «памежнае» значэнне ts.
  2. Калі там ужо нічога няма, то падмяняем NULL-значэнне на -infinity.
  3. Вычытваем увесь сегмент значэнняў паміж атрыманым значэннем ts і перададзеным з інтэрфейсу параметрам $1 (папярэднім "апошнім" адмаляваным значэннем).
  4. Калі блок вярнуўся менш чым з 26 запісамі - ён апошні.

Або тое ж самае карцінкай:
PostgreSQL Antipatterns: рух па рэестры

Паколькі зараз у нас выбарка не мае нейкага вызначанага «пачатку», то нам нішто не мяшае «разгарнуць» гэты запыт у зваротны бок і рэалізаваць дынамічную падгрузку блокаў дадзеных ад «апорнай кропкі» у абодва бакі – як уніз, так і ўверх.

заўвагу

  1. Так, у такім выпадку мы звяртаемся да індэкса двойчы, але ўсё "чыста па індэксе". Таму ўкладзены запыт прывядзе ўсяго толькі да аднаго дадатковага Index Only Scan.
  2. Досыць відавочна, што гэтай методыкай можна карыстацца, толькі калі ў вас значэння ts могуць перасекчыся толькі выпадкова, і іх няшмат. Калі ж ваш тыповы кейс - "мільён запісаў у 00:00:00.000", так рабіць не варта. У сэнсе не варта дапускаць такога кейса. Але калі ўжо так атрымалася, выкарыстоўвайце варыянт з пашыраным азначнікам.

Крыніца: habr.com

Дадаць каментар