PostgreSQL Antipatterns: Навигация в регистъра

Днес няма да има сложни случаи и сложни алгоритми в SQL. Всичко ще бъде много просто, на нивото на Captain Obvious - нека го направим преглед на регистъра на събитията сортирани по време.

Тоест има знак в базата данни 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. ИЗМЕСТВАНЕ

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 прочете статия за намиране на неефективни индекси и разбра това „не най-новото“ времево клеймо не е добро. И аз отново дойдох при вас - сега с мисълта, че този индекс все пак трябва да се върне обратно в (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 записа, той е последният.

Или същата снимка:

Защото сега имаме извадката няма конкретно „начало“, тогава нищо не ни пречи да „разширим“ тази заявка в обратна посока и да реализираме динамично зареждане на блокове данни от „референтната точка“ в двете посоки - и надолу, и нагоре.

забележка

1. Да, в този случай имаме достъп до индекса два пъти, но всичко е „чисто по индекс“. Следователно подзаявката ще доведе само до към едно допълнително сканиране само на индекс.
2. Съвсем очевидно е, че тази техника може да се използва само когато имате ценности ts може да премине само случайно и не са много от тях. Ако вашият типичен случай е „милион записа на 00:00:00.000“, не трябва да правите това. Искам да кажа, че не трябва да допускате такъв случай. Но ако това се случи, използвайте опцията с разширен индекс.

Източник: www.habr.com