Какие проблемы возникают с offset пагинацией
Что проверяют
Знаете ли вы, как база на самом деле выполняет LIMIT и OFFSET, и умеете ли предложить keyset-пагинацию с правильным индексом. Следом почти всегда просят написать запрос для курсорной пагинации и объяснить, как отдать курсор в API.
Как отвечать
Три проблемы, в таком порядке:
- стоимость растёт с номером страницы:
OFFSET 100000не прыгает к нужной строке. PostgreSQL и MySQL читают и отбрасывают все сто тысяч строк, даже если есть индекс по полю сортировки. Первая страница отдаётся за миллисекунды, тысячная за секунды; - пропуски и дубли: пока пользователь листает, в начало списка вставляются новые записи, и строки сдвигаются. Одна запись появится на двух страницах подряд, а после удаления другая не покажется вовсе;
- нестабильный порядок:
ORDER BY created_atбез уникального поля в конце даёт произвольный порядок для одинаковых значений, и строки «прыгают» между страницами.
Решение: keyset (seek) пагинация. Вместо смещения передаётся значение последней строки: WHERE (created_at, id) < ($1, $2) ORDER BY created_at DESC, id DESC LIMIT 20. С составным индексом по (created_at, id) запрос стоит одинаково на любой глубине. В Go курсор обычно кодируют в base64 от JSON и отдают клиенту непрозрачной строкой next_cursor, а значения передают плейсхолдерами pgx или database/sql, без склейки строк.
Цена keyset: нельзя перейти сразу на 57-ю страницу, и общее число страниц считать дорого. Поэтому offset оставляют там, где страниц мало и нужны номера (админка), а для лент и API берут курсор. Вместо дорогого COUNT(*) отдают флаг has_more: запрашивают LIMIT 21 и смотрят, пришла ли лишняя строка.
Где спотыкаются
Говорят, что индекс решает проблему большого offset. Индекс помогает с сортировкой, но пропускаемые строки база всё равно проходит.
Строят курсор только по created_at. При одинаковых метках времени часть записей теряется на стыке страниц, нужен уникальный хвост вроде id.
Отдают клиенту сырое значение курсора и принимают его без проверки. Клиент начинает подставлять что угодно, а менять формат потом больно. Непрозрачный курсор с валидацией на сервере снимает обе проблемы.