Пагинация в SQL: OFFSET или keyset (курсорная) пагинация

Короткий ответ

• LIMIT/OFFSET — простой вариант для небольших таблиц и случаев, когда нужны номера страниц.
• Keyset (курсорная) пагинация — вариант для больших таблиц, бесконечной ленты и API: скорость не зависит от того, насколько далеко пользователь пролистал, и записи не дублируются.

Почему OFFSET становится медленным

SELECT id, title, created_at
FROM posts
ORDER BY created_at DESC
LIMIT 20 OFFSET 100000;

Чтобы отдать 20 строк, база должна найти и пропустить 100 000 строк. Индекс помогает отсортировать, но не позволяет «перепрыгнуть» сразу на нужную позицию. Чем глубже страница, тем больше работы: первые страницы быстрые, далёкие — медленные.

Почему OFFSET даёт дубли и пропуски

Пока пользователь смотрит первую страницу, в таблицу добавляется новая запись. Все строки сдвигаются на одну позицию, и последняя запись первой страницы снова появляется в начале второй. При удалении — наоборот, одна запись пропадает между страницами.

В бесконечной ленте это особенно заметно: пользователь видит один и тот же пост дважды.

Как работает keyset-пагинация

Вместо «пропусти N строк» запрос говорит «дай записи после последней показанной»:

SELECT id, title, created_at
FROM posts
WHERE (created_at, id) < ($1, $2)
ORDER BY created_at DESC, id DESC
LIMIT 20;

Здесь $1 и $2 — created_at и id последней записи предыдущей страницы.

Три обязательных условия:

1. Стабильная сортировка. Сортировка должна однозначно упорядочивать строки. Одного created_at мало: у двух записей может совпасть время. Поэтому добавляем уникальный id как tie-breaker.
2. Условие WHERE совпадает с ORDER BY. Те же поля, в том же порядке и в том же направлении.
3. Составной индекс по полям сортировки:

CREATE INDEX posts_created_at_id_idx ON posts (created_at DESC, id DESC);

С таким индексом база сразу находит место по курсору и читает только 20 строк — на первой странице и на тысячной одинаково.

Сравнение кортежей (a, b) < (x, y) поддерживается в PostgreSQL и MySQL. Если ваша СУБД или планировщик плохо его использует, напишите условие развёрнуто:

WHERE created_at < $1 OR (created_at = $1 AND id < $2)

Как сделать это в API

Курсор отдают клиенту как непрозрачную строку, например base64 от JSON с полями сортировки:

{
  "items": [ ... ],
  "nextCursor": "eyJjcmVhdGVkQXQiOiIyMDI2LTAxLTE1VDEwOjAwOjAwWiIsImlkIjo0Mn0="
}

Практичные правила:

• Запрашивайте LIMIT + 1. Если пришла 21 строка, следующая страница есть: отдаёте 20 и курсор от двадцатой. Если нет — nextCursor: null.
• Не давайте клиенту собирать курсор самому. Тогда можно менять формат без поломки клиентов.
• Проверяйте курсор на сервере. Это пользовательский ввод: его можно подделать или прислать битым.
• Курсор привязан к сортировке. Если пользователь сменил сортировку или фильтр, пагинация начинается заново.

Ленты, админки и номера страниц

Keyset не умеет «перейти на страницу 37» — для этого нужно знать позицию, а значит, считать строки. Учтите и то, что COUNT(*) для «всего страниц» на большой таблице тоже дорогой. Часто достаточно показать «больше 10 000» или приблизительное значение.

Назад реализуется зеркально: условие > вместо <, сортировка в обратную сторону, а полученную пачку разворачиваете перед отдачей клиенту.

Частые ошибки

• Сортировка только по неуникальному полю: записи с одинаковым значением теряются или дублируются между страницами.
• Индекс есть, но порядок полей или направление не совпадает с ORDER BY.
• Сортировка по полю, которое может быть NULL, без явной обработки NULL в условии и индексе.
• Курсор из номера страницы, то есть тот же OFFSET под другим именем.