Почему для пагинации предпочитают курсор, а не offset?

Пагинация по offset (?page=3&limit=20 или OFFSET 40) проста, но на больших и изменяющихся данных даёт дубли, пропуски и медленные запросы. Курсорная пагинация возвращает порядок от опорной записи и лишена этих проблем.

Offset

SELECT * FROM messages ORDER BY id DESC LIMIT 20 OFFSET 40;

Недостатки:

• Медленно на больших смещениях: база должна прочитать и отбросить OFFSET строк.
• Дубли и пропуски: если между запросами добавилась или удалилась запись, страницы «сдвигаются» — элемент приходит дважды или пропадает.
• Неудобна для бесконечной прокрутки и лент в реальном времени.

Курсор

Курсор — идентификатор последней полученной записи (id, время, составной ключ):

SELECT * FROM messages
WHERE id < :cursor
ORDER BY id DESC
LIMIT 20;

{ "items": [...], "nextCursor": "eyJpZCI6MTIwfQ" }

Запрос использует индекс и выполняется быстро независимо от глубины. Новые записи не сдвигают уже просмотренные.

Плюсы курсора

• Стабильные результаты при изменении данных.
• Постоянная скорость (по индексу).
• Подходит для чатов, лент и бесконечного скролла.

Минусы

• Нельзя перейти на произвольную страницу.
• Нужна стабильная уникальная сортировка (при совпадении значений добавляют id вторым ключом).
• Курсор обычно кодируют и считают непрозрачной строкой.

Когда выбрать offset

Небольшие, редко меняющиеся таблицы и интерфейсы с номерами страниц.

Коротко для собеседования

Offset медленно работает на больших смещениях и «плывёт» при изменении данных, курсор берёт записи после опорной по индексу. Курсор подходит для лент и чатов, offset — для небольших таблиц с номерами страниц.