🥇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;

Що тут взагалі відбувається?

Крокуємо на 25 записів «вниз» та отримуємо «граничне» значення ts.
Якщо там уже нічого немає, то підмінюємо NULL-значення на -infinity.
Вичитуємо весь сегмент значень між отриманим значенням ts та переданим з інтерфейсу параметром $1 (попереднім «останнім» відмальованим значенням).
Якщо блок повернувся менше ніж із 26 записами — він останній.

Або те саме картинкою:

Бо тепер у нас вибірка не має якогось певного «початку», то нам ніщо не заважає "розгорнути" цей запит у зворотний бік і реалізувати динамічне підвантаження блоків даних від "опорної точки" в обидві сторони - як вниз, так і вгору.

зауваження

Так, у такому разі ми звертаємось до індексу двічі, але все «чисто за індексом». Тому вкладений запит наведе лише до одного додаткового Index Only Scan.
Досить очевидно, що цією методикою можна користуватися лише тоді, коли у вас значення ts можуть перетнутися лише випадково, і їх не багато. Якщо ваш типовий кейс — «мільйон записів о 00:00:00.000», так робити не варто. У сенсі не варто допускати такого кейсу. Але якщо так вийшло, використовуйте варіант з розширеним індексом.

Джерело: habr.com

PostgreSQL Antipatterns: навігація по реєстру