Пошук уроків, статей та іншого контенту
Реалізуйте швидку пагінацію за значеннями ключів зі стабільним сортуванням і коректними межами.
Keyset pagination, або пагінація за курсором, переходить на наступну сторінку не за номером сторінки, а за значеннями ключів останнього отриманого рядка.
Замість:
OFFSET 100000 LIMIT 20використовується умова на значення останнього елемента попередньої сторінки:
WHERE (created_at, id) < ($1, $2)
ORDER BY created_at DESC, id DESC
LIMIT 20Це дає дві важливі переваги:
PostgreSQL може перейти безпосередньо до потрібного місця індексу;
час виконання не зростає пропорційно до кількості пропущених рядків.
Keyset pagination особливо корисна для:
стрічок подій;
списків повідомлень;
журналів;
каталогів із великою кількістю записів;
нескінченного прокручування.
OFFSET стає повільнимРозглянемо запит:
SELECT id, created_at, title
FROM articles
ORDER BY created_at DESC, id DESC
OFFSET 100000
LIMIT 20;Навіть якщо існує відповідний індекс, PostgreSQL має знайти або прочитати щонайменше перші 100020 рядків, щоб пропустити 100000 із них.
Чим далі сторінка, тим більше роботи виконує база даних.
Keyset-пагінація передає значення останнього рядка попередньої сторінки:
SELECT id, created_at, title
FROM articles
WHERE (created_at, id) < ($1, $2)
ORDER BY created_at DESC, id DESC
LIMIT 20;Тепер база даних шукає записи після конкретної позиції в індексі, а не послідовно пропускає попередні рядки.
Для коректної пагінації порядок сортування має бути:
детермінованим;
стабільним;
таким, що однозначно визначає кожен рядок.
Сортування лише за created_at недостатнє:
ORDER BY created_at DESCКілька рядків можуть мати однакове значення created_at. Тоді PostgreSQL не зобов’язаний повертати їх у певному порядку. Між двома запитами порядок таких рядків може змінитися.
Використовуйте унікальний ключ як додаткове поле сортування:
ORDER BY created_at DESC, id DESCТут:
created_at визначає основний порядок;
id розв’язує конфлікти;
комбінація (created_at, id) однозначно визначає позицію рядка.
Зазвичай як останній ключ використовують первинний ключ.
PostgreSQL підтримує порівняння кортежів:
(created_at, id) < ($1, $2)Це означає:
created_at < $1
АБО
created_at = $1 І id < $2Наприклад, нехай останній рядок сторінки має:
created_at = '2026-01-20 12:00:00+00'
id = 42Для сортування за спаданням наступна сторінка повинна містити:
рядки з меншою датою;
або рядки з тією самою датою, але з меншим id.
Саме це виражає умова:
WHERE (created_at, id) < ('2026-01-20 12:00:00+00', 42)Важливо використовувати строге порівняння <, а не <=. Рядок курсора вже був показаний на попередній сторінці, тому він не повинен потрапити на наступну.
Індекс має відповідати фільтрації та сортуванню.
Приклад таблиці:
CREATE TABLE articles (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
tenant_id bigint NOT NULL,
created_at timestamptz NOT NULL,
title text NOT NULL
);Для пагінації статей одного клієнта:
CREATE INDEX articles_tenant_created_id_idx
ON articles (tenant_id, created_at DESC, id DESC);Порядок колонок важливий:
спочатку tenant_id, бо він використовується у фільтрі;
потім created_at та id, бо вони використовуються для сортування і курсора.
Для першої сторінки курсор ще відсутній:
SELECT id, created_at, title
FROM articles
WHERE tenant_id = 7
ORDER BY created_at DESC, id DESC
LIMIT 21;Часто запитують на один рядок більше, ніж потрібно користувачу.
Якщо розмір сторінки дорівнює 20, використовується LIMIT 21:
перші 20 рядків повертаються користувачу;
21-й рядок показує, що існує наступна сторінка;
курсором наступної сторінки стає останній із показаних 20 рядків.
Такий підхід не потребує окремого COUNT(*).
Нехай на першій сторінці останнім був рядок:
created_at = '2026-01-20 12:00:00+00'
id = 42Запит наступної сторінки:
SELECT id, created_at, title
FROM articles
WHERE tenant_id = 7
AND (created_at, id) < ('2026-01-20 12:00:00+00', 42)
ORDER BY created_at DESC, id DESC
LIMIT 21;У прикладі нижче показано повний сценарій, який можна виконати в PostgreSQL:
DROP TABLE IF EXISTS articles;
CREATE TABLE articles (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
tenant_id bigint NOT NULL,
created_at timestamptz NOT NULL,
title text NOT NULL
);
CREATE INDEX articles_tenant_created_id_idx
ON articles (tenant_id, created_at DESC, id DESC);
INSERT INTO articles (tenant_id, created_at, title)
VALUES
(7, '2026-01-20 12:05:00+00', 'Стаття 1'),
(7, '2026-01-20 12:04:00+00', 'Стаття 2'),
(7, '2026-01-20 12:03:00+00', 'Стаття 3'),
(7, '2026-01-20 12:02:00+00', 'Стаття 4'),
(7, '2026-01-20 12:01:00+00', 'Стаття 5'),
(7, '2026-01-20 12:00:00+00', 'Стаття 6'),
(7, '2026-01-20 11:59:00+00', 'Стаття 7'),
(7, '2026-01-20 11:58:00+00', 'Стаття 8'),
(8, '2026-01-20 12:10:00+00', 'Інший клієнт');
-- Перша сторінка: просимо 3 рядки плюс один додатковий.
SELECT id, created_at, title
FROM articles
WHERE tenant_id = 7
ORDER BY created_at DESC, id DESC
LIMIT 4;
-- Якщо останній показаний рядок має id = 3,
-- його значення використовуються як курсор.
SELECT id, created_at, title
FROM articles
WHERE tenant_id = 7
AND (created_at, id) < ('2026-01-20 12:03:00+00', 3)
ORDER BY created_at DESC, id DESC
LIMIT 4;У реальному застосунку значення курсора передаються як параметри запиту, а не вставляються в SQL-рядок:
PREPARE next_articles(timestamptz, bigint, bigint, integer) AS
SELECT id, created_at, title
FROM articles
WHERE tenant_id = $3
AND (created_at, id) < ($1, $2)
ORDER BY created_at DESC, id DESC
LIMIT $4;
EXECUTE next_articles(
'2026-01-20 12:03:00+00',
3,
7,
4
);Параметризація запиту захищає від SQL-ін’єкцій і дозволяє драйверу коректно передавати типи значень.
Курсор повинен містити всі значення, які беруть участь у сортуванні:
(created_at, id)Якщо сортування має такий вигляд:
ORDER BY priority DESC, created_at DESC, id DESCкурсором повинні бути всі три значення:
(priority, created_at, id)Не можна використовувати лише id, якщо порядок визначається також priority і created_at. Інакше частина рядків може бути пропущена або продубльована.
У зовнішньому API курсор зазвичай подають як одне непрозоре значення. Наприклад, застосунок може серіалізувати значення сортування у JSON і кодувати їх у Base64. Але на рівні SQL курсор усе одно має відповідати всім колонкам сортування.
Для переходу до старіших записів при сортуванні за спаданням використовується:
WHERE (created_at, id) < ($1, $2)
ORDER BY created_at DESC, id DESCДля переходу до новіших записів потрібно змінити напрямок порівняння:
WHERE (created_at, id) > ($1, $2)
ORDER BY created_at ASC, id ASC
LIMIT 21;Результат такого запиту зазвичай розвертають у застосунку, щоб користувач знову бачив записи у порядку DESC.
Повний SQL-приклад:
-- Знаходимо записи, новіші за курсор.
SELECT id, created_at, title
FROM articles
WHERE tenant_id = 7
AND (created_at, id) > ('2026-01-20 12:03:00+00', 3)
ORDER BY created_at ASC, id ASC
LIMIT 4;Якщо потрібен той самий візуальний порядок, результати слід прочитати у зворотному порядку на рівні застосунку або використати зовнішній запит:
SELECT id, created_at, title
FROM (
SELECT id, created_at, title
FROM articles
WHERE tenant_id = 7
AND (created_at, id) > ('2026-01-20 12:03:00+00', 3)
ORDER BY created_at ASC, id ASC
LIMIT 4
) AS newer_articles
ORDER BY created_at DESC, id DESC;Порівняння кортежів зручно використовувати, коли всі ключі мають один напрямок:
ORDER BY created_at DESC, id DESC
WHERE (created_at, id) < ($1, $2)Якщо напрямки різні, наприклад:
ORDER BY priority DESC, created_at ASC, id ASCпросте порівняння кортежів уже не відповідає потрібному порядку. Умова має бути розписана явно:
WHERE priority < $1
OR (
priority = $1
AND (
created_at > $2
OR (created_at = $2 AND id > $3)
)
)Для priority DESC наступні рядки мають менший priority, а для created_at ASC та id ASC — більші значення.
У складних випадках важливо перевірити умову на граничних значеннях:
два рядки з однаковим першим ключем;
однакові перші два ключі;
рядок, що точно дорівнює курсору;
рядок безпосередньо перед курсором і безпосередньо після нього.
NULL у ключах сортуванняПорівняння з NULL дає результат UNKNOWN, а не TRUE або FALSE. Через це рядки з NULL можуть не потрапити до очікуваної сторінки.
Для курсорної пагінації найпростіше використовувати ключі з обмеженням NOT NULL:
created_at timestamptz NOT NULLЯкщо NULL є необхідним, потрібно явно визначити його позицію через NULLS FIRST або NULLS LAST і побудувати відповідну умову переходу. Таку логіку слід тестувати окремо, оскільки звичайне порівняння кортежів не замінює правил сортування з NULL.
Keyset pagination стабільніша за OFFSET, але вона не створює автоматичний знімок даних на весь час навігації.
Між двома запитами можуть відбутися такі зміни:
новий рядок може з’явитися перед поточним курсором;
рядок може бути видалений;
значення ключа сортування може бути змінене.
Якщо новий рядок має позицію перед курсором, він не з’явиться на вже переглянутій сторінці. Це зазвичай бажана поведінка для стрічок.
Щоб порядок залишався передбачуваним:
не змінюйте ключі сортування після створення запису, якщо це можливо;
використовуйте унікальний незмінний tie-breaker, наприклад id;
не покладайтеся на offset-подібну нумерацію сторінок для даних, що активно змінюються.
Перевірити використання індексу можна через:
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, created_at, title
FROM articles
WHERE tenant_id = 7
AND (created_at, id) < ('2026-01-20 12:03:00+00', 3)
ORDER BY created_at DESC, id DESC
LIMIT 21;У плані очікується використання індексу, який починається з tenant_id, а далі містить ключі сортування.
Однак фактичний план залежить від:
розміру таблиці;
статистики;
селективності фільтрів;
кількості рядків, які потрібно повернути;
актуальності індексу та статистики.
Тому для продуктивного запиту важливо перевіряти план на даних, близьких до реальних.
Для сторінки розміром N:
Якщо курсора немає, виконати запит без умови позиції.
Виконати запит із LIMIT N + 1.
Взяти перші N рядків як результат сторінки.
Якщо отримано N + 1 рядків, сформувати next_cursor з останнього показаного рядка.
Якщо отримано не більше N рядків, наступної сторінки немає.
Для наступного запиту передати значення next_cursor назад до SQL.
Курсор має бути пов’язаний із конкретним порядком сортування. Не можна використати курсор, сформований для:
ORDER BY created_at DESC, id DESCдля іншого запиту, наприклад:
ORDER BY title ASC, id ASCНеправильно:
ORDER BY created_at DESC
WHERE created_at < $1Рядки з однаковим created_at можуть бути пропущені або повторені.
Правильно:
ORDER BY created_at DESC, id DESC
WHERE (created_at, id) < ($1, $2)<= для наступної сторінкиНеправильно:
WHERE (created_at, id) <= ($1, $2)Курсорний рядок потрапить і на попередню, і на наступну сторінку.
Правильно:
WHERE (created_at, id) < ($1, $2)ORDER BY та умовоюДля:
ORDER BY created_at DESC, id DESCпотрібна умова переходу до наступної сторінки:
WHERE (created_at, id) < ($1, $2)Змішування DESC із > або зміна порядку колонок порушує межі сторінки.
Keyset pagination без індексу може не дати очікуваного прискорення. Фільтри та ключі сортування повинні бути враховані в індексі.
Курсор має містити значення з останнього показаного рядка, а не з додаткового рядка, отриманого для перевірки has_next.
Неправильно формувати SQL через конкатенацію рядків. Значення курсора потрібно передавати параметрами драйвера або підготовленого запиту.
Keyset pagination переходить між сторінками за значеннями ключів, а не за кількістю пропущених рядків.
Вона ефективніша за OFFSET на великих зміщеннях.
Сортування повинно бути стабільним і завершуватися унікальним ключем.
Для однакового напрямку сортування зручно використовувати порівняння кортежів.
Для ORDER BY ... DESC наступна сторінка зазвичай використовує строге порівняння <.
Курсор має містити всі ключі з ORDER BY.
Індекс повинен відповідати фільтру та порядку сортування.
Значення NULL у ключах потребують окремої обробки або обмеження NOT NULL.
Запит із LIMIT N + 1 дозволяє визначити наявність наступної сторінки без підрахунку всіх рядків.