Пагинация при работе с БД

Хочу рассказать не только базовую теорию, но и про подводные камни и практические подходы к оптимизации.

➡️ OFFSET пагинация

Это самый распространенный вариант.
С фронтенда приходит запрос вида:

GET /orders?page=3&size=20

Если считаем, что страницы нумеруются с 1 (не с 0), то для page = 3 бэк превратит это примерно в такой запрос:

SELECT ...
FROM orders
ORDER BY created_at DESC, id DESC
LIMIT 20
OFFSET 40;

Без ORDER BY порядок строк не гарантирован, а дополнительный уникальный id пригодится на случай одинакового created_at.


Что возвращает бэк:
{
"items": [...],
"page": 3,
"size": 20,
"totalElements": 247,
"totalPages": 13
}


У OFFSET пагинации много плюсов:

➕ просто реализовать, есть встроенная реализация в некоторых фреймворках

➕ легко реализовать переход к любой странице

Например, когда книгу то читаю, то слушаю, мне бывает надо сразу перейти на страницу 134.
Или помнила, что в адмике нужная запись на 78 странице 🤪

Но у такого подхода есть и минусы.

Ключевое слово OFFSET 40 говорит базе, что нужно пропустить первые 40 записей и начать отдавать, начиная с 41-й (для 3-й страницы). Однако база не может сразу прыгнуть на 41-ю строку. Она работает так:

➡️ найти начало упорядоченной выборки
➡️ пройти первые 40 записей
➡️ отбросить их
➡️ вернуть следующие 20 записей

Тут наблюдается лишняя работа с первыми 40 записями. Чем больше номер страницы, тем больше OFFSET и тем больше времени понадобится базе на ответ — это и есть основной минус.

Из-за большего OFFSET записи с последних страниц будут отдаваться дольше, чем с первых, но иногда возможен лайфхак с разворотом сортировки.

Например, если мы показываем на UI сначала самые свежие заказы, то последняя страница заказов будет содержать самые ранние:

SELECT ...
FROM orders
ORDER BY created_at ASC, id ASC
LIMIT 20;

Оговорюсь, что такой запрос подходит для задачи "дай 20 самых ранних записей", но не является способом получить последнюю страницу классической пагинации, потому что возможно следующее несоответствие:

Всего 53 записи, размер страницы 20.
1 стр: 1-20 - 20 строк
2 стр: 21-40 - 20 строк
3 стр: 41-53 - 13 строк

ORDER BY created_at ASC LIMIT 20 вернет 20 самых старых строк вместо 13.

Проблема с OFFSET пагинацией может стать заметной, когда запрашиваются глубокие страницы: чем больше OFFSET, тем больше результатов базе приходится обработать и отбросить. Это может не проявляться на пользовательском UI с 10 страницами по 50 элементов, а вот если мы пишем публичный технический API, которым будут пользоваться соседние команды для получения каких-то больших списков, то там это может стать заметно:

SELECT ...
FROM payments
ORDER BY created_at DESC, id DESC
LIMIT 500
OFFSET 70000;
😱

Еще OFFSET пагинация может быть не очень удобна, когда список часто меняется.

Вообще это довольно редкий кейс, но давайте рассмотрим на всякий случай.

➡️ В БД лежат новости с id = 1 ... 100

➡️ Пользователь запросил 10 самых свежих новостей

➡️ Бэк отдал последние новости с id = 100, 99 ... 92, 91

➡️ Пока пользователь читал новости, модератор добавил две новых записи, они получили id = 101 и id = 102

➡️ Пользователь запросил следующую страницу более старых новостей

➡️ При выполнении OFFSET 10 БД отбросила записи с id = 102 ... 93 и отдала новости с id = 92, 91, 90 ...

➡️ Результат: пользователь дважды увидел новости с id = 92 и 91

Итого минусы:

➖ замедление ответа по мере увеличения номера запрашиваемой страницы
➖ нестабильность ответа, когда список меняется

Когда использовать OFFSET-пагинацию:

✅ пользователи обычно работают с первыми страницами списка, глубокие переходы редки (или количество строк в таблице небольшое, если очень грубо —несколько тысяч строк)

✅ список записей обычно не меняются, пока пользователь их смотрит

✅ на UI нужен блок с привычной пагинацией и переходом на конкретную страницу