Как делать пагинацию при выводе большого количества данных
Что проверяют
Знаете ли вы, почему OFFSET деградирует на больших таблицах и чем его заменить. Следом почти всегда спрашивают, как сделать курсорную пагинацию при сортировке по неуникальному полю и как быть с общим числом страниц.
Как отвечать
Разберите два подхода и их цену:
- LIMIT/OFFSET:
ORDER BY id LIMIT 20 OFFSET 100000. Просто и позволяет прыгнуть на любую страницу, но база читает и выбрасывает сто тысяч строк, так что каждая следующая страница дороже. При вставках между запросами строки съезжают, и пользователь видит дубли или пропуски; - keyset (курсорная): клиент передаёт ключ последней строки, запрос вида
WHERE (created_at, id) < (:ts, :id) ORDER BY created_at DESC, id DESC LIMIT 20. С составным индексом по(created_at, id)страница стоит одинаково на любой глубине. Цена: на страницу 500 не прыгнуть, только вперёд и назад.
Сортировка обязана быть однозначной: сортируя по дате, добавьте id вторым ключом, иначе строки с одинаковой датой теряются на стыке страниц. Курсор отдают клиенту непрозрачной строкой, например base64 от пары значений.
В Python-стеке:
- Django REST Framework:
PageNumberPaginationиLimitOffsetPaginationработают через смещение,CursorPaginationчерез keyset и требует порядка по уникальному неизменяемому полю; - SQLAlchemy 2.0: keyset пишется обычным
select(...).where(...).order_by(...).limit(...); - FastAPI: встроенной пагинации нет, её пишут сами или берут
fastapi-pagination.
Общее число страниц требует COUNT(*), а в PostgreSQL на большой таблице это полный проход. Часто хватает запросить limit + 1 строк и вернуть флаг «есть ещё». Размер страницы ограничьте сверху, чтобы per_page=100000 не положил сервис.
Где спотыкаются
Говорят, что курсорная пагинация ломается при удалении строк. Наоборот: keyset устойчив к изменениям, а дубли и пропуски даёт как раз смещение.
Не ставят индекс под сортировку. Без него keyset тоже сортирует всю таблицу, и выигрыша нет.
Путают пагинацию для интерфейса с выгрузкой. Для экспорта миллиона строк нужны QuerySet.iterator(chunk_size=...) в Django или yield_per в SQLAlchemy, а не тысяча запросов со смещением.