Постраничная выдача: смещение против курсора

Почему смещение ломается на больших объёмах

Когда мы запрашиваем первую страницу результатов (OFFSET 0 LIMIT 100), база данных выполняет простую операцию: находит первые 100 строк и возвращает их клиенту. Всё работает быстро, потому что СУБД может сразу перейти к началу данных. Но уже на второй странице (OFFSET 100 LIMIT 100) начинаются проблемы: системе приходится прочитать все 100 пропущенных строк, чтобы добраться до нужных.

Чем дальше страница, тем хуже ситуация. Для десятой страницы база прочитает девятьсот ненужных записей, прежде чем вернуть сотню запрошенных. При этом объём возвращаемых данных остаётся прежним - мы получаем те же 100 строк, но с гораздо большими затратами ресурсов.

Ещё одна проблема проявляется при активном изменении данных. Если между двумя запросами кто-то добавит или удалит запись в начале диапазона, смещение сдвинется. Пользователь увидит дубликаты или пропущенные записи - одни и те же элементы появятся дважды или исчезнут из выдачи.

Тупик: первое, что пробуют все - и почему это не работает

Когда разработчики сталкиваются с проблемой медленной пагинации, первое, что приходит в голову - добавить индекс по полю сортировки. Кажется логичным, что индекс ускорит поиск и решит все проблемы. Однако на практике это решение оказывается тупиковым:

База данных действительно быстрее находит начало нужного диапазона благодаря индексу, но всё равно вынуждена физически проходить все пропущенные строки. Для запроса с большим смещением (OFFSET 10000 LIMIT 100) СУБД сначала быстро переходит к десятитысячной записи, но затем должна прочитать все 10000 записей, чтобы отбросить первые 9900.

Индекс не решает фундаментальную проблему смещения - необходимость пропускать ненужные данные. Он лишь ускоряет переход к началу диапазона, но не отменяет необходимость чтения всех пропущенных строк. В результате запрос всё равно будет выполняться медленно, особенно на больших объёмах данных.

Кроме того, индекс не спасает от проблемы дубликатов и пропусков. Если между запросами в начало диапазона добавится новая запись, смещение сдвинется, и пользователь получит не те данные, которые ожидал. Индекс не меняет логику работы OFFSET - он по-прежнему отсчитывает записи от начала, а не от конкретного значения.

Как работает курсорная пагинация

Курсорная пагинация принципиально отличается от смещения - вместо того чтобы пропускать N записей, она указывает конкретную точку, от которой нужно начинать выборку. Это условие вида WHERE id > last_seen_id LIMIT 100 позволяет базе сразу перейти к нужным записям, не читая пропущенные.

Однако курсорная пагинация требует выполнения нескольких важных условий:

1. Поле сортировки должно быть устойчивым - его значение не должно меняться между запросами. Для автоинкрементного идентификатора это условие выполняется, но для даты создания или приоритета - нет.

2. Поле должно быть уникальным. Если в таблице могут быть записи с одинаковым значением поля сортировки (например, одинаковое время создания), одного условия недостаточно.

Составной курсор для реальных данных

В реальных системах часто приходится сортировать по неуникальным полям - дате создания, рейтингу, приоритету. В таких случаях одного условия недостаточно, и приходится использовать составной курсор.

Например, при сортировке по дате создания условие будет выглядеть так: WHERE (created_at, id) > (last_created_at, last_id) LIMIT 100. Это гарантирует, что даже если несколько записей имеют одинаковое время создания, они будут возвращены в предсказуемом порядке - по идентификатору.

Для работы составного курсора необходим составной индекс: CREATE INDEX idx_created_at_id ON table (created_at, id). Без такого индекса база будет выполнять полное сканирование таблицы, сводя на нет все преимущества курсора.

Составной курсор решает проблему неуникальных значений, но требует дополнительной логики на клиентской стороне. Нужно хранить не только последнее значение поля сортировки, но и соответствующий идентификатор, чтобы правильно формировать следующий запрос.

Ограничения курсорной пагинации

Несмотря на все преимущества, курсорная пагинация имеет несколько существенных ограничений:

1. Невозможность произвольного доступа. Курсор не позволяет перейти сразу на N-ю страницу - чтобы добраться до середины таблицы, придется последовательно пройти все предыдущие записи. Это делает курсор непригодным для интерфейсов с номерами страниц.

2. Отсутствие общего числа страниц. Курсор не знает, сколько всего записей в таблице, и не может вычислить общее число страниц. Запрос COUNT(*) для большой таблицы может быть очень дорогим.

3. Сложность реализации. Для курсорной пагинации нужно хранить состояние (последнее увиденное значение) и правильно обрабатывать случаи, когда записи удаляются или изменяются между запросами.

4. Проблемы с обратной навигацией. Курсор хорошо работает для последовательного просмотра данных в одном направлении, но не поддерживает переход на предыдущую страницу без дополнительной логики.

Как считать общее количество записей, когда это необходимо

Когда интерфейс требует показывать общее число страниц, приходится искать компромиссы. Вот несколько подходов:

1. Приблизительная оценка. Можно запускать фоновый запрос с COUNT(*) раз в час и кешировать результат. Это даст приблизительное число, но не будет тормозить основную выдачу. Для многих интерфейсов такой точности достаточно.

2. Ограничение глубины. Можно запретить переход дальше определенного количества страниц (например, 10 или 20). Это снизит нагрузку на базу и уменьшит вероятность ошибок.

3. Замена пагинации. Вместо номеров страниц можно использовать бесконечную прокрутку или кнопку “Загрузить еще”. Это избавит от необходимости знать общее число страниц.

4. Асинхронный подсчет. Можно запускать подсчет общего количества в фоне и показывать его, когда он будет готов. Пользователь увидит сначала данные, а затем - общее число записей.

Точный подсчет на каждой странице - самый дорогой вариант. Даже с индексом запрос COUNT(*) для большой таблицы может выполняться несколько секунд, что неприемлемо для интерактивных интерфейсов. В большинстве случаев приблизительной оценки достаточно для пользовательского опыта.

Когда смещение все еще лучше курсора

Несмотря на все недостатки, смещение остается лучшим выбором в нескольких случаях:

1. Интерфейс требует произвольный доступ. Если пользователи должны иметь возможность переходить на любую страницу по номеру, курсор не подойдет. Это характерно для многих административных интерфейсов и отчетов.

2. Нужно общее число страниц. Если интерфейс не может обойтись без этой информации (например, для отображения номеров страниц), смещение остается единственным вариантом.

3. Таблица небольшая и стабильная. Если таблица содержит несколько тысяч записей и редко меняется, смещение не создаст заметных проблем. В таких случаях простота реализации может перевесить недостатки.

4. Сложная сортировка. Если сортировка идет по нескольким полям или включает вычисляемые значения, реализация курсора может быть слишком сложной. В таких случаях смещение может быть проще в реализации.

5. Обратная совместимость. Если система уже использует смещение и изменение подхода потребует значительных изменений в интерфейсе и логике приложения.

В таких случаях стоит ограничить глубину пагинации - например, показывать только первые несколько тысяч записей или запрещать переход дальше определенного количества страниц. Это снизит нагрузку на базу и уменьшит вероятность ошибок.

Проблемы реализации курсорной пагинации

При переходе на курсорную пагинацию приходится решать несколько технических проблем:

1. Хранение состояния. Нужно хранить последнее увиденное значение курсора между запросами. Это может быть реализовано на клиентской стороне (в URL или локальном хранилище) или на серверной (в сессии).

2. Обработка изменений данных. Если между запросами записи удаляются или изменяются, курсор может “проскочить” некоторые записи или вернуть дубликаты. Нужно предусмотреть механизмы обработки таких ситуаций.

3. Сложные условия фильтрации. Если запрос включает условия фильтрации, их нужно учитывать при формировании курсора. Например, если мы фильтруем по статусу, курсор должен включать не только поле сортировки, но и статус.

4. Производительность составных курсоров. Для составных курсоров нужно правильно строить индексы и учитывать порядок полей в условии.

5. Обработка ошибок. Нужно предусмотреть случаи, когда курсор указывает на несуществующую запись (например, если все записи с таким значением были удалены).

Позиция редакции

Смещение - простой и понятный инструмент, который хорошо работает для небольших наборов данных и редких запросов. Однако по мере роста таблицы и увеличения нагрузки его недостатки становятся очевидными:

• Лишняя работа базы данных при больших смещениях
• Проблемы с дубликатами и пропусками при изменении данных
• Низкая производительность на больших объёмах

Курсорная пагинация решает эти проблемы, но требует более сложной реализации и не поддерживает все возможности традиционной пагинации. Выбор между смещением и курсором зависит от конкретных требований системы:

1. Если важна скорость и стабильность выдачи, а интерфейс может обойтись без произвольного доступа к страницам - выбирайте курсор. Это особенно актуально для интерфейсов с бесконечной прокруткой или кнопкой “Загрузить еще”.

2. Если интерфейс требует показывать общее число страниц или поддерживать переход по номерам - используйте смещение с ограничением глубины. В этом случае стоит ограничить максимальное количество страниц или записей, чтобы избежать проблем с производительностью.

Условие, при котором наша позиция неверна: если таблица никогда не меняется, а запросы всегда идут по уникальному полю, смещение не создаст проблем. Однако в реальных системах данные обычно живые, а сортировка часто идет по неуникальным полям. Для небольших и стабильных таблиц смещение может быть проще и удобнее курсора.

В любом случае, при выборе подхода стоит учитывать не только технические аспекты, но и требования пользовательского интерфейса. Часто лучшее решение - это комбинация подходов: курсор для последовательного просмотра данных и смещение для произвольного доступа к страницам.