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

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

Понимаете ли вы, почему OFFSET деградирует на больших таблицах, и знаете ли keyset-пагинацию. Следом обычно спрашивают, как быть при сортировке по неуникальному полю и что делать с номерами страниц и общим количеством.

Как отвечать

Сначала причина. При OFFSET 1000000 LIMIT 20 база читает и выбрасывает миллион строк, даже если есть индекс. Вдобавок новые строки между запросами сдвигают страницы: появляются дубли и пропуски.

Решение: запоминать ключ последней строки и просить «следующие после неё».

SELECT id, created_at, title FROM items
WHERE (created_at, id) < (%s, %s)
ORDER BY created_at DESC, id DESC
LIMIT 21;

• уникальный ключ сортировки: created_at может совпадать у нескольких строк, поэтому добавляется id как второй ключ и сравнивается кортеж. Сравнение кортежей понимают PostgreSQL и MySQL, в остальных случаях условие раскрывают как a < x OR (a = x AND b < y);
• индекс: составной (created_at, id) в том же порядке, иначе выигрыша не будет;
• курсор для клиента: значения последней строки кодируются в непрозрачный токен (например, base64 от JSON), API отдаёт его как next_cursor;
• есть ли следующая страница: запросите на одну строку больше лимита вместо COUNT(*), который на большой таблице тоже читает всё.

В Django REST Framework есть готовая CursorPagination, а обычный Paginator Django работает через OFFSET и COUNT. В SQLAlchemy условие пишется через tuple_(Item.created_at, Item.id) < (ts, last_id).

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

Называют это «курсорами» и путают с серверными курсорами базы (DECLARE CURSOR, именованный курсор в psycopg). Такой курсор держит соединение и транзакцию: годится для выгрузки, но не для HTTP API.

Сортируют по неуникальному полю без второго ключа. Строки с одинаковым временем на границе страниц теряются или повторяются. Мелочь того же рода: if last_id: считает нулевой id отсутствующим.

Не обсуждают ограничение: keyset не умеет прыгнуть на пятидесятую страницу. Для ленты и бесконечной прокрутки это нормально, для админской таблицы с номерами страниц нужен компромисс, например ограничение глубины.