Как оптимизировать выборку данных с использованием id вместо offset для пагинации
Что проверяют
Понимаете ли вы, почему OFFSET деградирует на дальних страницах, и умеете ли сделать keyset-пагинацию без дублей и пропусков. Следом почти всегда спрашивают, что делать при сортировке по неуникальному полю вроде даты и как отдавать курсор в API.
Как отвечать
- цена OFFSET:
LIMIT 20 OFFSET 100000заставляет базу прочитать и выбросить сто тысяч строк, время растёт линейно с номером страницы. Вдобавок при вставке новых строк записи сдвигаются, и пользователь видит дубли или теряет элементы; - keyset (seek): запоминаем ключ последней строки и продолжаем от него:
WHERE id > %s ORDER BY id LIMIT %s. База спускается по индексу сразу к нужному месту, цена любой страницы одинакова; - неуникальная сортировка: при сортировке только по
created_atстроки с одинаковым временем теряются. Добавьте уникальный хвост и сравнивайте кортежем:WHERE (created_at, id) < (%s, %s) ORDER BY created_at DESC, id DESC, плюс составной индекс(created_at, id). PostgreSQL использует такой индекс для сравнения кортежей; - конец данных: запрашивайте
LIMIT n + 1. Лишняя строка значит, что следующая страница есть, саму строку клиенту не отдают; - API: курсор отдают непрозрачным токеном, например base64 от значений последней строки, в поле
next_cursor. Клиент не собирает его сам, и формат можно менять.
В Python это Django filter(id__gt=last_id).order_by('id')[:n] или CursorPagination в DRF, в SQLAlchemy select(Item).where(Item.id > last_id).order_by(Item.id).limit(n). Значения всегда передаются параметрами драйвера.
Цену назовите сами: перейти сразу на 50-ю страницу и показать общее число страниц keyset не умеет. Для ленты и API это не проблема, а в админке с номерами страниц иногда остаются на OFFSET.
Где спотыкаются
Подставляют last_id в SQL через f-строку. Это SQL-инъекция, и на собеседовании её заметят сразу.
Сортируют по created_at без уникального поля. При пачке строк с одинаковым временем часть записей не попадёт ни на одну страницу.
Берут ключом случайный UUIDv4. Пагинация будет работать, но порядок получится бессмысленным. Здесь подходят автоинкремент или UUIDv7, упорядоченный по времени.