pagination

Пагинация

Предпосылки

сортировка и ограничение (ORDER BY, LIMIT/OFFSET), SELECT и фильтрация (WHERE), подзапросы и CTE (подзапрос в FROM), индексы (CREATE INDEX, INCLUDE), типы данных и NULL (NULL и сортировка).

← Индексы | DML — изменение данных →

E-commerce площадка отображает заказы на dashboard. Таблица orders:

 id  | customer_id | amount  | created_at
-----+-------------+---------+---------------------
   1 |          42 |  150.00 | 2024-01-15 09:30:00
   2 |          17 |   89.50 | 2024-01-15 10:00:00
   3 |          42 |  320.00 | 2024-01-15 10:15:00
 ... |         ... |     ... | ...

Пока заказов двести, «покажи следующие 50» работает мгновенно любым способом. Когда их станет 12 миллионов, выбор подхода определит — ответ за 2 мс или за 4 секунды.

Существуют две фундаментальные стратегии. Позиционная (OFFSET) — «пропусти N строк, верни следующие M». Запрос прост, но база обязана пройти и отбросить все N строк, поэтому стоимость растёт линейно с глубиной. По значению (keyset) — «начни после этой границы и верни M строк». База делает seek в индексе, стоимость постоянна независимо от глубины. Но прыгнуть на произвольную страницу keyset не умеет — он знает только «вперёд от текущей позиции».

Стабильный порядок: без него пагинация не работает

Без ORDER BY база возвращает строки в произвольном порядке, который может меняться между запросами — из-за параллельных worker’ов, обновлённой статистики планировщика или физического расположения строк на диске. Если первая страница вернула строки в одном порядке, а вторая — в другом, пользователь увидит дубликаты или пропуски.

-- Порядок не задан: страницы будут «гулять»
SELECT * FROM orders LIMIT 50;
 
-- Порядок зафиксирован
SELECT * FROM orders ORDER BY created_at DESC LIMIT 50;

Но ORDER BY по неуникальному столбцу тоже недостаточен. Если у нескольких заказов одинаковый created_at, их взаимный порядок не определён, и СУБД может расположить их по-разному на разных страницах. Решение — tie-breaker: добавить уникальный столбец в конец сортировки.

SELECT *
FROM orders
ORDER BY created_at DESC, id DESC
LIMIT 50;

Теперь порядок детерминирован: пара (created_at, id) уникальна для каждой строки, и «следующая страница» имеет точный смысл — строки, которые «меньше» последней строки текущей страницы по этой паре.

OFFSET: простой, но дорожает с глубиной

Dashboard показывает 50 заказов на странице. Классический подход:

-- Страница 1
SELECT * FROM orders ORDER BY created_at DESC, id DESC LIMIT 50;
 
-- Страница 2
SELECT * FROM orders ORDER BY created_at DESC, id DESC LIMIT 50 OFFSET 50;
 
-- Страница N
SELECT * FROM orders ORDER BY created_at DESC, id DESC LIMIT 50 OFFSET (N-1)*50;

При 200 заказах и 4 страницах OFFSET работает идеально. Но таблица растёт до 12 миллионов строк, и менеджер жалуется: страница 50 000 загружается 4 секунды.

Причина в том, как СУБД выполняет LIMIT/OFFSET. Чтобы вернуть 50 строк с OFFSET 2 500 000, база проходит через 2 500 050 строк в заданном порядке, отбрасывает первые 2 500 000 и отдаёт оставшиеся 50. Даже если индекс поддерживает порядок и не нужна сортировка в памяти, пройти 2.5 миллиона записей индекса — ощутимая работа. Стоимость линейно растёт с номером страницы: страница 1 читает 50 строк, страница 1000 — 50 000 строк, страница 50 000 — 2 500 050 строк.

OFFSET — правильный выбор, когда таблица небольшая (тысячи–десятки тысяч строк), пользователь редко уходит дальше первых десяти страниц, или когда UI принципиально требует «перейти на страницу N по номеру».

Keyset (seek): стоимость не зависит от глубины

Keyset-пагинация не считает позицию — она запоминает значения ключа последней строки и запрашивает «всё, что идёт после».

Первая страница — обычный запрос:

SELECT id, customer_id, amount, created_at
FROM orders
ORDER BY created_at DESC, id DESC
LIMIT 50;

Приложение запоминает значения ключа последней строки результата — курсор:

last_created_at = '2024-01-10 14:30:00'
last_id = 98432

Следующая страница:

SELECT id, customer_id, amount, created_at
FROM orders
WHERE (created_at, id) < ('2024-01-10 14:30:00', 98432)
ORDER BY created_at DESC, id DESC
LIMIT 50;

Условие (created_at, id) < ($1, $2) использует row-value comparison — сравнение кортежей. SQL разворачивает его в лексикографический порядок:

(created_at, id) < ($1, $2)
-- эквивалентно:
created_at < $1
OR (created_at = $1 AND id < $2)

Для трёх столбцов паттерн расширяется аналогично: (a, b, c) < (x, y, z) → a < x OR (a = x AND b < y) OR (a = x AND b = y AND c < z). Но SQL-движки оптимизируют row-value comparison напрямую, без развёртывания в OR-цепочки.

Почему keyset быстрее на глубоких страницах: B-tree хранит ключи в порядке. Условие (created_at, id) < (...) позволяет начать сканирование индекса с конкретной позиции (seek) и прочитать ровно 50 следующих записей. Стоимость — O(log n) для поиска позиции плюс чтение LIMIT записей. Ни одна строка не читается и не отбрасывается. Страница 50 000 стоит столько же, сколько страница 1.

Индекс под keyset

Чтобы keyset работал предсказуемо, нужен составной индекс, колонки и направления которого совпадают с ORDER BY:

CREATE INDEX idx_orders_pagination
ON orders (created_at DESC, id DESC);

Если запрос возвращает только несколько столбцов, covering index (INCLUDE) избавит от обращения к таблице за данными — подробнее в индексах.

Составной курсор и несколько столбцов сортировки

Dashboard сортирует заказы по сумме убывания, а при одинаковой сумме — по id:

SELECT id, amount, created_at
FROM orders
ORDER BY amount DESC, id DESC
LIMIT 50;

Курсор — пара значений (last_amount, last_id). Следующая страница:

SELECT id, amount, created_at
FROM orders
WHERE (amount, id) < (320.00, 97)
ORDER BY amount DESC, id DESC
LIMIT 50;

Паттерн одинаков для любого количества столбцов — курсор содержит столько значений, сколько столбцов в ORDER BY. При смешанных направлениях (например, amount ASC, id DESC) row-value comparison усложняется: (a, b) < (x, y) предполагает одинаковое направление для обоих элементов. Для смешанных направлений придётся развернуть условие вручную:

-- ORDER BY amount ASC, id DESC
WHERE amount > $1
   OR (amount = $1 AND id < $2)

Навигация назад

Keyset естественно идёт вперёд. Для «предыдущей страницы» нужно обратное сравнение и обратный ORDER BY:

-- «Предыдущая страница»: строки, которые «больше» первой строки текущей страницы
SELECT id, amount, created_at
FROM orders
WHERE (created_at, id) > ('2024-01-10 14:30:00', 98432)
ORDER BY created_at ASC, id ASC
LIMIT 50;

Результат придёт в обратном порядке — приложение разворачивает его перед отображением. Для полноценной двунаправленной навигации приложение хранит курсор первой и последней строки каждой страницы.

Ограничение keyset: переход на произвольную страницу по номеру невозможен. Нет курсора — не от чего отталкиваться. Если UI требует «перейти на страницу 500», остаётся OFFSET или гибридный подход: keyset для обычного пролистывания, OFFSET для редких прыжков.

NULL в ключах пагинации

NULL ломает row-value comparison. Если amount допускает NULL:

-- (amount, id) < (NULL, 42) → NULL, не TRUE и не FALSE
-- строка не пройдёт WHERE, даже если должна быть на следующей странице

Любое сравнение с NULL возвращает NULL, а WHERE пропускает всё, что не TRUE. NULLS FIRST / NULLS LAST влияет на позицию NULL в сортировке, но не спасает row-value comparison. Для keyset с nullable-столбцами нужны явные IS NULL / IS NOT NULL условия, и логика быстро усложняется.

Рекомендация: столбцы, входящие в ключ пагинации, должны быть NOT NULL. Суррогатный id идеален — он уникален, NOT NULL и монотонно возрастает. Если бизнес-требования вынуждают сортировать по nullable-столбцу, tie-breaker id гарантирует, что хотя бы последний элемент ключа всегда определён.

Дрейф при конкурентных изменениях

OFFSET drift

Пользователь открывает страницу 1 (строки 1–50 по порядку id DESC). Пока он читает, другой процесс вставляет 3 новых заказа. Пользователь нажимает «следующая страница» — OFFSET 50. Но теперь в таблице 3 новые строки в начале, и строки 51–100 сместились: строки 48–50 первой страницы стали строками 51–53, и пользователь увидит их повторно. При удалении — наоборот, часть строк будет пропущена.

Страница 1: видит строки [new+1..new+50] — ожидалось [1..50]
                                              |
                              3 новые строки сдвинули позиции
                                              v
Страница 2: OFFSET 50 → строки [48..97] — повтор строк 48-50

Keyset стабильность

Keyset запоминает не позицию, а значение: «всё после id = 50». Вставка новых строк с id > 50 не влияет — они окажутся до курсора, а страница 2 начнётся с id < 50, как и раньше. Удаление строки-курсора тоже безопасно: условие id < 50 не требует существования строки с id = 50.

Проблемный edge case: UPDATE, меняющий столбец сортировки. Если заказ переоценивается (amount меняется) между запросами страниц, он может «перепрыгнуть» через курсор и появиться повторно или исчезнуть. Ни OFFSET, ни keyset не дают snapshot consistency между отдельными запросами страниц — каждый запрос видит текущее состояние таблицы.

Проблема общего количества: COUNT(*) и альтернативы

UI «Страница 3 из 127» требует общее количество строк. Наивный подход:

SELECT COUNT(*) FROM orders WHERE customer_id = 42;

Для таблицы в миллионы строк с WHERE-фильтром COUNT(*) вынужден просканировать все подходящие строки — отдельного счётчика нет. При сложных условиях это может занимать секунды.

Альтернативы:

Кэшированный счётчик — приложение хранит approximate total и обновляет его периодически или инкрементально. Точность снижается, но latency предсказуемо.

Флаг has_next_page — вместо точного количества запрашивается LIMIT + 1 строка. Если вернулось 51 — следующая страница есть, показываем кнопку «далее». Если 50 или меньше — это последняя страница. Общее количество не нужно.

API-уровень пагинации (cursor-based, relay-style) избегает total count по дизайну — подробнее в API design.

Deferred join: пагинация по лёгкому индексу

Таблица orders содержит 30 столбцов, включая текстовые поля с описаниями. Keyset-запрос возвращает полные строки, а каждое обращение к таблице за широкой строкой — дорогое (чтение heap-страницы). Если индекс покрывает только ключ сортировки, база делает 50 random I/O для каждой страницы.

Deferred join разделяет задачу на два шага: сначала пагинация по узкому индексу, затем загрузка полных данных только для нужных строк:

SELECT o.*
FROM orders o
JOIN (
    SELECT id
    FROM orders
    WHERE customer_id = 42
    ORDER BY created_at DESC, id DESC
    LIMIT 50 OFFSET 5000
) page ON page.id = o.id
ORDER BY o.created_at DESC, o.id DESC;

Подзапрос page работает по узкому индексу (customer_id, created_at DESC, id DESC) — сканирует только id, не трогая широкие строки. Внешний JOIN загружает полные данные ровно для 50 строк. Этот паттерн — стандартный SQL, работает в любой СУБД.

Covering index решает ту же задачу на уровне индекса: если все запрашиваемые столбцы входят в индекс (через INCLUDE), heap access не нужен совсем.

Серверные курсоры: DECLARE CURSOR / FETCH

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

BEGIN;
 
DECLARE order_cursor CURSOR FOR
    SELECT * FROM orders
    WHERE customer_id = 42
    ORDER BY created_at DESC;
 
FETCH FORWARD 50 FROM order_cursor;  -- первые 50
FETCH FORWARD 50 FROM order_cursor;  -- следующие 50
-- ...
 
CLOSE order_cursor;
COMMIT;

Преимущество: внутри транзакции курсор видит один snapshot — нет дрейфа, нет дубликатов, нет пропусков. Это единственный способ получить настоящую консистентность между страницами.

Цена: курсор держит ресурсы сервера (память или temp-файлы для результата), требует открытую транзакцию на всё время навигации. Долгая транзакция блокирует VACUUM и увеличивает bloat — это неприемлемо для web-приложений, где пользователь может открыть страницу и уйти на обед. Серверные курсоры уместны для batch-обработки, экспорта данных и генерации отчётов.

PostgreSQL расширяет стандарт: SCROLL позволяет двигаться в обоих направлениях (FETCH BACKWARD), WITH HOLD сохраняет курсор после COMMIT (ценой материализации результата).

FETCH FIRST: стандартный синтаксис вместо LIMIT

LIMIT — не часть стандарта SQL. SQL:2008 ввёл стандартный синтаксис:

-- Стандарт SQL:2008
SELECT * FROM orders
ORDER BY created_at DESC
FETCH FIRST 50 ROWS ONLY;
 
-- С пропуском строк (аналог OFFSET)
SELECT * FROM orders
ORDER BY created_at DESC
OFFSET 100 ROWS
FETCH NEXT 50 ROWS ONLY;

Функционально FETCH FIRST N ROWS ONLY эквивалентен LIMIT N. PostgreSQL поддерживает оба варианта. Вопрос переносимости: LIMIT понимают PostgreSQL, MySQL, SQLite. FETCH FIRST — PostgreSQL, Oracle, DB2, SQL Server (с небольшими вариациями синтаксиса).

Когда какой подход

OFFSET подходит для небольших таблиц (тысячи строк), мелкой навигации (первые 10–20 страниц), UI с номерами страниц, и там, где простота реализации важнее производительности.

Keyset — выбор по умолчанию для больших таблиц, глубокой навигации (бесконечный скролл, ленты), API с cursor-based пагинацией, и сценариев с частыми вставками, где OFFSET-дрейф неприемлем.

Deferred join — оптимизация поверх OFFSET или keyset, когда таблица широкая, а индекс узкий. Снижает I/O за счёт двухфазного запроса.

Серверные курсоры — batch-обработка, экспорт, отчёты. Гарантируют snapshot consistency, но неприменимы для web-пагинации из-за ресурсов и длинных транзакций.

Гибридный подход — keyset для обычной навигации, OFFSET для «прыжка на страницу N» (редкая операция, допустимо заплатить за неё).

Проверка на практике

Сравнение OFFSET и keyset на одной таблице:

-- OFFSET: страница 50 000 (пропустить 2.5M строк)
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders
ORDER BY created_at DESC, id DESC
LIMIT 50 OFFSET 2500000;
 
-- Keyset: та же «глубина»
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders
WHERE (created_at, id) < ('2024-06-15 12:00:00', 9500000)
ORDER BY created_at DESC, id DESC
LIMIT 50;

На что смотреть в EXPLAIN: количество прочитанных строк (rows), shared read и shared hit (дисковый vs кэшированный I/O), наличие узла Sort (индекс не поддерживает порядок — нужен CREATE INDEX с правильными направлениями). У OFFSET-варианта rows будет ~2 500 050, у keyset — ~50. Подробнее о чтении планов — в EXPLAIN.

Sources

• PostgreSQL Documentation (v16): SELECT — LIMIT, OFFSET, FETCH. https://www.postgresql.org/docs/16/sql-select.html
• PostgreSQL Documentation (v16): Row and Array Comparisons. https://www.postgresql.org/docs/16/functions-comparisons.html
• PostgreSQL Documentation (v16): DECLARE CURSOR. https://www.postgresql.org/docs/16/sql-declare.html
• SQL:2008 Standard (ISO/IEC 9075-2:2008): FETCH FIRST clause, OFFSET clause.

---

← Индексы | DML — изменение данных →