Для API со списком из десятков миллионов записей, которые часто изменяются (вставки, удаления) в реальном времени, нужно спроектировать пагинацию, устойчивую к большому объёму данных и приемлемую по согласованности для пользователя. Как подойти к этому выбору? Обсудите компромиссы, риски и критерии выбора — единственного правильного решения может не быть.?

Короткий ответ

Cursor-based пагинация с устойчивым tie-breaker (например, (sort_field, id)) обычно нужна для производительности на больших объёмах, но нужно явно решить и задокументировать модель согласованности для пользователя при одновременных изменениях данных — показывать ли "снимок" на момент начала пролистывания (может уже не отражать последние изменения) или живые данные (может приводить к пропуску/дублированию записей при параллельных вставках/удалениях).

Как это работает подробнее

на десятках миллионов строк offset-пагинация деградирует по производительности пропорционально глубине смещения, так как БД должна пропустить все предыдущие строки даже с индексом; cursor-based подход с индексированным составным ключом сортировки избегает этой деградации, обращаясь напрямую к позиции по значению курсора. Но при живых данных курсор, зафиксированный на "последней увиденной записи", не защищает от того, что новые записи, вставленные перед текущей позицией сортировки, никогда не будут показаны в текущем пролистывании, а удалённые — вызовут видимый "пропуск" в ощущении пользователя.

Пример кода

example.sqlКопировать

-- Cursor-пагинация с корректным tie-breaker по неуникальному полю сортировки
SELECT id, name, created_at
FROM events
WHERE (created_at, id) < (:last_created_at, :last_id)
ORDER BY created_at DESC, id DESC
LIMIT 50;
-- Вариант со снимком для стабильной выгрузки при активно изменяющихся данных
SELECT id, name, created_at
FROM events
WHERE created_at <= :snapshot_time
  AND (created_at, id) < (:last_created_at, :last_id)
ORDER BY created_at DESC, id DESC
LIMIT 50;

Что использовать на практике

• Рекомендация. для сценариев, где пользователь ожидает стабильный список за время своей сессии пролистывания (например, экспорт отчёта), можно явно зафиксировать снимок на момент первого запроса (через выборку по created_at <= snapshot_time или через версионированную отдельную таблицу/snapshot), жертвуя "живостью" данных ради предсказуемости. Для сценариев вроде бесконечной ленты, где пользователь и ожидает, что новые события могут появляться сверху, живой cursor без снимка вполне приемлем. Выбор зависит от ожиданий пользователя и от того, критична ли полнота/отсутствие дублей больше, чем свежесть данных. Компромисс: фиксация снимка на момент начала пролистывания даёт предсказуемость и отсутствие дублей/пропусков, но увеличивает затраты на хранение (нужно либо версионирование строк, либо отдельная snapshot-таблица) и показывает пользователю данные, устаревающие по мере пролистывания; живой cursor без снимка проще и не требует дополнительного хранения, но даёт пользователю ощутимо "нестабильный" список при высокой частоте изменений исходных данных. Критерий выбора — бизнес-требования к точности списка (отчётность и финансовые выгрузки обычно требуют снимок), частота изменений данных и стоимость дополнительной инфраструктуры снимков.

Подводные камни

• Риски. использовать cursor по неуникальному полю (только created_at) без tie-breaker, что приводит к пропуску или дублированию записей с одинаковым значением сортировки на границе страницы; обещать пользователю одновременно и "актуальные данные в реальном времени", и "стабильный список без дублей/пропусков при пролистывании" без снимка — это взаимоисключающие гарантии при активно изменяющихся данных, и попытка дать оба без явного компромисса приведёт к скрытым дефектам.