Как делать пагинацию в 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; -- страница 3const 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.
Сравнение
| Offset | Keyset | |
|---|---|---|
| Переход на страницу 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.