Какие сложности при реализации пагинации в API для большого объема данных

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

Знаете ли вы, почему LIMIT/OFFSET перестаёт работать на больших таблицах, и умеете ли сделать keyset-пагинацию в Spring-приложении. Следом обычно спрашивают: «Как поведёт себя ваша пагинация, если во время листания добавились новые записи?»

Как отвечать

Разберите сложности по порядку:

• глубокий offset: OFFSET 1000000 заставляет базу прочитать и выбросить миллион строк, поэтому дальние страницы отвечают всё медленнее;
• подсчёт total: Page в Spring Data на каждую страницу делает отдельный count-запрос, и на большой таблице он бывает дороже самой выборки. Если общее число не нужно, возвращайте Slice: он берёт на одну запись больше и так узнаёт, есть ли следующая страница;
• сдвиг данных: пока клиент листает, новые записи сдвигают окно, одна запись попадает на две страницы, а другая пропадает;
• нестабильная сортировка: сортировка только по created_at с повторяющимися значениями даёт недетерминированный порядок. Нужен уникальный добивающий ключ, обычно id;
• fetch коллекций: запрос с JOIN FETCH по коллекции и лимитом Hibernate не может ограничить в SQL и режет результат уже в памяти. Решение: сначала выбрать страницу идентификаторов, потом догрузить связи.

Основное решение: keyset (seek) пагинация. Клиент получает не номер страницы, а курсор с ключом последней записи, и запрос выглядит как WHERE (created_at, id) < (:ts, :id) ORDER BY created_at DESC, id DESC LIMIT 20 при составном индексе по тем же колонкам. Время ответа не зависит от глубины, дубли от вставок исчезают. В Spring Data начиная с 3.1 для этого есть Window и ScrollPosition.keyset().

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

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

Предлагают кэшировать страницы как главное решение. Кэш не лечит медленный глубокий offset и добавляет устаревшие данные.

Не упоминают цену keyset: на страницу 57 сразу не перейти, а сортировка по любому полю на выбор пользователя требует своего индекса и своего курсора.

Строят курсор по неуникальной колонке и теряют записи на границе страниц.