Как реализовать endpoint с пагинацией по большой таблице

Что проверяют

Знаете ли вы, почему LIMIT/OFFSET деградирует на больших таблицах и чем его заменить. Следом обычно спрашивают, как отдавать общее количество записей и что будет, если данные меняются между запросами.

Как отвечать

Начните с проблемы. При OFFSET 100000 база всё равно читает и отбрасывает сто тысяч строк, поэтому дальние страницы становятся всё медленнее. Вдобавок при вставке новых записей страницы съезжают, и пользователь видит дубли или теряет строки.

Основное решение это keyset (курсорная) пагинация:

• стабильная сортировка: поле плюс уникальный хвост, например ORDER BY created_at DESC, id DESC, и составной индекс ровно по этим колонкам;
• условие вместо смещения: WHERE (created_at, id) < (:last_created_at, :last_id) LIMIT :n. Сравнение кортежей есть в PostgreSQL и MySQL, запрос идёт по индексу и стоит одинаково на любой глубине;
• непрозрачный курсор: значения последней строки кодируются в base64 и возвращаются как next_cursor, клиент не собирает их сам;
• признак конца: запрашиваете n + 1 строку, лишняя означает has_more = true, отдельный COUNT не нужен;
• валидация: потолок размера страницы, в FastAPI через Query(default=50, le=100).

Назовите готовые реализации: CursorPagination в DRF, пакет fastapi-pagination. Offset оставьте для небольших таблиц и админок, где нужен переход на произвольный номер страницы.

Про общее количество: точный COUNT(*) на большой таблице в PostgreSQL сканирует её из-за MVCC. Если число нужно только для интерфейса, берут оценку из pg_class.reltuples или EXPLAIN либо пишут «больше 10 000».

Где спотыкаются

Берут paginate() из Flask-SQLAlchemy для огромной таблицы. Он делает OFFSET и по умолчанию ещё отдельный COUNT на каждый запрос, а с версии 3.0 принимает аргументы только по имени: paginate(page=..., per_page=...).

Строят курсор по неуникальной колонке, например только по created_at. Строки с одинаковым временем на границе страницы теряются или повторяются.

Меняют фильтры или сортировку, оставляя старый курсор. Курсор имеет смысл только для того же запроса, поэтому параметры фильтра либо кладут в курсор, либо сбрасывают его при изменении.