re:re:actable
Урок 024 минуты чтения

Как делать пагинацию в SQL

На этой странице
Кратко

Два основных подхода. Offset-пагинация (ORDER BY … LIMIT 20 OFFSET 40) проста и позволяет перейти на любую страницу по номеру, но база всё равно читает и отбрасывает все пропущенные строки — чем дальше страница, тем медленнее, а при вставке новых записей строки «съезжают» между страницами (дубли и пропуски). Keyset (cursor) пагинация запоминает ключ последней показанной строки и продолжает с него: WHERE (created_at, id) < ($1, $2) ORDER BY created_at DESC, id DESC LIMIT 20 — каждая страница стоит одинаково дёшево с подходящим индексом и устойчива к вставкам, но нельзя прыгнуть на «страницу 37». Для обоих подходов порядок сортировки должен быть уникальным (tie-breaker id). Keyset — стандарт для бесконечных лент и API, offset — для небольших списков с номерами страниц.

Вопрос

Как реализовать пагинацию в SQL и чем offset отличается от keyset?

Offset

SELECT id, title, created_at
FROM posts
WHERE is_published
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 40;          -- страница 3
const page = Math.max(1, Number(query.page) || 1);
const limit = Math.min(100, Number(query.limit) || 20);   // ограничить размер страницы
const offset = (page - 1) * limit;

Проблемы offset

ПроблемаПричина
Медленные дальние страницыOFFSET 100000 — прочитать и выбросить 100 000 строк
Дубли при прокруткеНовый пост сверху сдвигает всё на одну позицию
ПропускиУдалённая запись сдвигает в другую сторону

Keyset

-- первая страница
SELECT id, title, created_at
FROM posts
WHERE is_published
ORDER BY created_at DESC, id DESC
LIMIT 20;
 
-- следующая: курсор = (created_at, id) последней строки
SELECT id, title, created_at
FROM posts
WHERE is_published
  AND (created_at, id) < ($1, $2)
ORDER BY created_at DESC, id DESC
LIMIT 20;
 
CREATE INDEX posts_feed_idx ON posts (created_at DESC, id DESC) WHERE is_published;

Сравнение кортежей (a, b) < (x, y) в PostgreSQL использует составной индекс. В СУБД без поддержки кортежей пишут a < x OR (a = x AND b < y).

Курсор в API

const encode = (row: { created_at: Date; id: number }) =>
  Buffer.from(JSON.stringify([row.created_at.toISOString(), row.id])).toString("base64url");
 
const decode = (cursor: string) => JSON.parse(Buffer.from(cursor, "base64url").toString()) as [string, number];
 
// ответ
{ "items": [...], "nextCursor": "WyIyMDI2LTEwLTI0VDEwOjAwOjAwWiIsMTIzXQ" }

Курсор — непрозрачная строка для клиента: формат можно менять без изменения API. Чтобы узнать, есть ли следующая страница, запрашивают LIMIT 21 и показывают 20.

Сравнение

OffsetKeyset
Переход на страницу NДаНет
Скорость глубоких страницПадает линейноПостоянная
Стабильность при вставкахДубли и пропускиСтабильна
СложностьМинимальнаяКурсор и составной индекс
Подходит дляАдминки, таблицы с номерами страницЛенты, бесконечный скролл, API, экспорт

Общее количество

SELECT id, title, count(*) OVER () AS total
FROM posts WHERE is_published
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 0;

Точный count(*) по большой таблице дорог — для «найдено ~12 000» используют оценку или кешированный счётчик, а для бесконечной ленты общее число часто не нужно.

Частые ошибки

  • ORDER BY created_at без tie-breaker.
  • Нет ограничения максимального limit — запрос на 1 000 000 строк.
  • Keyset без составного индекса под порядок сортировки.
  • Курсор только по created_at — строки с одинаковым временем теряются.

Что важно помнить

  • Offset прост, но медленный на глубине и нестабилен.
  • Keyset — постоянная скорость и стабильность.
  • Порядок сортировки должен быть уникальным.

Мини-шпаргалка

  • LIMIT n OFFSET (page - 1) * n.
  • WHERE (t, id) < ($t, $id) ORDER BY t DESC, id DESC LIMIT n.
  • Индекс (t DESC, id DESC).
  • LIMIT n + 1 — есть ли следующая страница.

Документация

PostgreSQL: LIMIT and OFFSET, Use The Index, Luke: Paging Through Results.

Дочитали до конца?
Отметьте урок — прогресс сохранится в браузере.

Поиск

Поиск по урокам