Оглавление

• Зачем менять пагинацию OFFSET/LIMIT: симптомы и стоимость
• Как работает keyset‑пагинация
  • Базовая идея
  • Строгий и стабильный порядок
  • Навигация вперёд и назад
• Реализация на PostgreSQL
  • Индексы для ускорения и экономии I/O
  • SQL‑примеры с кортежным сравнением
• API с курсорами: безопасно и просто
  • Формат курсора и защита от подмены
  • Готовая ручка на Node.js
• Фильтры и сортировки без боли
  • Индексы под популярные сценарии
  • Быстрый приблизительный COUNT
• UX и продуктовые вопросы
  • «Показать ещё» против бесконечной прокрутки
  • Номера страниц и SEO
• Подводные камни и как их обойти
  • Вставки/удаления между страницами
  • Корректный переход «назад»
  • Смена фильтров и сброс курсора
• Экономика: сколько это экономит
• План внедрения по шагам
• Чек‑лист перед продом

Зачем менять пагинацию OFFSET/LIMIT: симптомы и стоимость

OFFSET/LIMIT кажется удобным: «дай мне 20 записей, пропустив 1000». Но база не «знает» как мгновенно перепрыгнуть тысячу строк — ей приходится сканировать, отсортировать и лишь затем отбросить первые N. На больших таблицах это превращается в лишние мегабайты чтения, рост p95/p99, а под нагрузкой — в время ожидания и тайм‑ауты.

Симптомы:

• чем дальше листаем, тем медленнее становится;
• от любой вставки/удаления рядом с текущей страницей «пляшут» результаты — пользователи видят дубликаты или пропуски;
• индекс есть, но EXPLAIN показывает дорогое чтение и сортировку;
• стоимость растёт почти линейно от номера страницы — деньги уходят в I/O и CPU.

Для бизнеса это значит: падает конверсия в каталоге и поиске, отчёты ждут дольше, инфраструктура дороже, а инцидентов — больше. Решение — не «железом прикрыть», а поменять подход к пагинации.

Как работает keyset‑пагинация

Базовая идея

Вместо «пропусти N строк» мы говорим «дай следующие 20 после вот этого ориентира». Ориентир — это курсор: значение ключевых полей последней (или первой) видимой записи. Типичный набор — пара из поля сортировки и уникального идентификатора, например (created_at, id) или (price_cents, id).

Запрос превращается из:

• «OFFSET 1000 LIMIT 20»

в

• «WHERE (created_at, id) < (
  ,
  ) ORDER BY created_at DESC, id DESC LIMIT 20»

База прыгает сразу к нужному месту по индексу — без линейного перебора.

Строгий и стабильный порядок

Чтобы не было «дрожания», порядок должен быть строгим и однозначным. Если сортируем по времени, добавляем вторым ключом уникальный id. Если по цене — аналогично: (price_cents, id). Тогда любая запись занимает фиксированное место в порядке, и курсор всегда «указывает» однозначно.

Навигация вперёд и назад

• Вперёд: используем оператор сравнения в сторону сортировки, например «<» для DESC.
• Назад: инвертируем оператор, забираем блок и переворачиваем массив на приложении, чтобы UX остался привычным.

Реализация на PostgreSQL

Индексы для ускорения и экономии I/O

Нам нужны составные индексы под каждый сценарий сортировки и частые фильтры. Часто достаточно пары:

-- Основная сортировка: новые сверху
CREATE INDEX IF NOT EXISTS idx_products_active_created
  ON products (created_at DESC, id DESC)
  WHERE is_active = TRUE;

-- Для сортировки по цене (дешёвые сверху)
CREATE INDEX IF NOT EXISTS idx_products_active_price
  ON products (price_cents ASC, id ASC)
  WHERE is_active = TRUE;

Если почти все запросы фильтруются по категории, продублируйте категорию в начале индекса:

CREATE INDEX IF NOT EXISTS idx_products_cat_created
  ON products (category_id, created_at DESC, id DESC)
  WHERE is_active = TRUE;

Чтобы сократить чтение таблицы, можно сделать «перекрывающий» индекс и выбирать только нужные поля прямо из индекса (Index‑Only Scan):

-- PostgreSQL 11+: INCLUDE добавляет неключевые столбцы
CREATE INDEX IF NOT EXISTS idx_products_active_created_cover
  ON products (created_at DESC, id DESC)
  INCLUDE (title, price_cents)
  WHERE is_active = TRUE;

SQL‑примеры с кортежным сравнением

Пусть у нас таблица:

CREATE TABLE IF NOT EXISTS products (
  id           BIGSERIAL PRIMARY KEY,
  title        TEXT NOT NULL,
  price_cents  INTEGER NOT NULL,
  category_id  INTEGER,
  is_active    BOOLEAN NOT NULL DEFAULT TRUE,
  created_at   TIMESTAMPTZ NOT NULL DEFAULT now()
);

Первая страница (нет курсора):

SELECT id, title, price_cents, created_at
FROM products
WHERE is_active = TRUE
ORDER BY created_at DESC, id DESC
LIMIT 20;

Следующая страница после курсора (created_at = $1, id = $2):

SELECT id, title, price_cents, created_at
FROM products
WHERE is_active = TRUE
  AND (created_at, id) < ($1, $2)
ORDER BY created_at DESC, id DESC
LIMIT 20;

Назад (получить предыдущие):

SELECT id, title, price_cents, created_at
FROM products
WHERE is_active = TRUE
  AND (created_at, id) > ($1, $2)
ORDER BY created_at ASC, id ASC
LIMIT 20;  -- потом развернуть результаты в приложении

Для сортировки по цене — просто меняем пары полей и операторы на согласованные с направлением.

API с курсорами: безопасно и просто

Формат курсора и защита от подмены

Курсор — это компактный сериализованный объект с ключами сортировки и метаданными (вариант сортировки, направление). Чтобы пользователь не мог его подделать и получить неожиданно дорогой запрос, подпишем курсор HMAC‑подписью и закодируем в base64url.

Структура курсора:

• key1, key2 — значения полей сортировки (например, метка времени/цена и id);
• sort — выбранный сортировщик;
• dir — «next» или «prev».

Готовая ручка на Node.js

Ниже полноценный пример на Express + pg. Он поддерживает фильтры, сортировку, курсоры вперёд/назад и защищённую подпись курсора.

const express = require('express');
const { Pool } = require('pg');
const crypto = require('crypto');

const app = express();
const pool = new Pool({
  connectionString: process.env.DATABASE_URL || 'postgres://postgres:postgres@localhost:5432/app'
});

const CURSOR_SECRET = process.env.CURSOR_SECRET || 'dev-secret-change-me';

function encodeCursor(obj) {
  const payload = Buffer.from(JSON.stringify(obj)).toString('base64url');
  const sig = crypto.createHmac('sha256', CURSOR_SECRET).update(payload).digest('base64url');
  return `${payload}.${sig}`;
}

function decodeCursor(cursor) {
  const [payload, sig] = String(cursor).split('.');
  if (!payload || !sig) throw new Error('Некорректный курсор');
  const expected = crypto.createHmac('sha256', CURSOR_SECRET).update(payload).digest('base64url');
  if (sig !== expected) throw new Error('Подпись курсора невалидна');
  return JSON.parse(Buffer.from(payload, 'base64url').toString('utf8'));
}

app.get('/products', async (req, res) => {
  try {
    const limit = Math.min(Math.max(parseInt(req.query.limit || '20', 10), 1), 100);
    const sort = (req.query.sort || 'created_desc'); // created_desc | price_asc

    // Выбор порядка и операторов для seek
    let orderBy = 'created_at DESC, id DESC';
    let seekOp = '<'; // для направления next при DESC
    let sortFields = ['created_at', 'id'];

    if (sort === 'price_asc') {
      orderBy = 'price_cents ASC, id ASC';
      seekOp = '>';
      sortFields = ['price_cents', 'id'];
    }

    const categoryId = req.query.category_id ? parseInt(req.query.category_id, 10) : null;
    const minPrice = req.query.min_price ? parseInt(req.query.min_price, 10) : null;
    const maxPrice = req.query.max_price ? parseInt(req.query.max_price, 10) : null;

    const filters = ['is_active = TRUE'];
    const params = [];

    if (Number.isFinite(categoryId)) {
      params.push(categoryId);
      filters.push(`category_id = $${params.length}`);
    }
    if (Number.isFinite(minPrice)) {
      params.push(minPrice);
      filters.push(`price_cents >= $${params.length}`);
    }
    if (Number.isFinite(maxPrice)) {
      params.push(maxPrice);
      filters.push(`price_cents <= $${params.length}`);
    }

    let cursor = null;
    let direction = 'next';

    if (req.query.cursor) {
      cursor = decodeCursor(req.query.cursor);
      if (cursor && cursor.dir === 'prev') direction = 'prev';
      if (cursor && cursor.sort !== sort) {
        // Сортировку сменили — курсор невалиден для нового порядка
        cursor = null;
      }
    }

    let whereSeek = '';
    if (cursor) {
      // Для created_at храним миллисекунды в курсоре, конвертируем в timestamptz
      let key1 = cursor.key1;
      let key2 = cursor.key2;
      const tuple = `(${sortFields[0]}, ${sortFields[1]})`;

      // Подготовим параметры под типы
      if (sort === 'created_desc') {
        // key1 — миллисекунды, приводим к timestamptz
        params.push(new Date(key1));
      } else {
        params.push(key1);
      }
      params.push(key2);

      const a = `$${params.length - 1}`;
      const b = `$${params.length}`;
      const tupleVal = `(${a}, ${b})`;

      const op = (direction === 'next') ? seekOp : (seekOp === '<' ? '>' : '<');
      whereSeek = `${tuple} ${op} ${tupleVal}`;
    }

    const whereClause = [whereSeek, ...filters].filter(Boolean).join(' AND ');

    const sql = `
      SELECT id, title, price_cents, created_at
      FROM products
      WHERE ${whereClause}
      ORDER BY ${orderBy}
      LIMIT $${params.push(limit)}
    `;

    const { rows } = await pool.query(sql, params);

    // Курсы следующей/предыдущей страниц
    let nextCursor = null;
    let prevCursor = null;

    if (rows.length > 0) {
      const first = rows[0];
      const last = rows[rows.length - 1];
      const toKey = (r) => {
        if (sort === 'price_asc') {
          return { key1: r.price_cents, key2: r.id, sort, dir: 'next' };
        }
        // created_desc: храним время в миллисекундах UTC
        return { key1: Date.parse(r.created_at), key2: r.id, sort, dir: 'next' };
      };

      nextCursor = encodeCursor(toKey(last));
      const prevObj = toKey(first);
      prevObj.dir = 'prev';
      prevCursor = encodeCursor(prevObj);
    }

    // Если шли назад — разворачиваем, чтобы вернуть в привычном порядке
    let items = rows;
    if (direction === 'prev') {
      items = [...rows].reverse();
      // Перекидываем курсоры местами, чтобы UI было проще
      const tmp = nextCursor;
      nextCursor = prevCursor;
      prevCursor = tmp;
    }

    res.json({ items, next_cursor: nextCursor, prev_cursor: prevCursor, limit, sort });
  } catch (e) {
    res.status(400).json({ error: e.message });
  }
});

app.listen(process.env.PORT || 3000, () => {
  console.log('Server listening');
});

Совет: курсор лучше привязывать к текущим фильтрам/сортировке (включать их хэш в подписываемый payload) — так вы гарантируете, что курсор не «переедет» в другой контекст и не вызовет дорогой план.

Фильтры и сортировки без боли

Индексы под популярные сценарии

Не пытайтесь угодить всем сортировкам одним индексом. В реальном продукте 1–2 варианта покрывают 95% трафика: «новые сверху» и «дешёвые сверху». Для них делают отдельные индексы (см. выше) и при необходимости — варианты с категорией или статусом. Остальные редкие сортировки можно оставить на OFFSET/LIMIT или ограничить.

Правила подбора:

• поле сортировки + уникальный id — в ORDER BY и в индексе в том же порядке и направлении;
• частые фильтры (category_id, is_active) — ведущие столбцы индекса или PARTIAL INDEX с WHERE is_active = TRUE;
• для лёгких ответов и экономии I/O — INCLUDE выбранные столбцы (title, price_cents), чтобы работал Index‑Only Scan.

Быстрый приблизительный COUNT

Полный COUNT(*) с тяжёлыми фильтрами дорог. В каталоге это редко нужно. Подходы:

• «Ещё N товаров» — считать N = лимит до конца по текущему курсору (быстро, информативно);
• приблизительный размер — брать оценку из pg_class.reltuples (грубо, но мгновенно) и кэшировать по ключу фильтров;
• если важно точно — считать асинхронно в фоне и кэшировать, обновлять при изменениях.

Пример грубой оценки:

SELECT reltuples::bigint AS approx
FROM pg_class
WHERE oid = 'products'::regclass;

Для фильтров лучше вести отдельные счётчики по популярным сегментам (например, активные по категории) и обновлять триггером или периодической задачей.

UX и продуктовые вопросы

«Показать ещё» против бесконечной прокрутки

Обе модели хорошо работают с курсорами. «Показать ещё» — проще, понятнее и дружелюбнее к аналитике. Бесконечная прокрутка — внимательнее к состоянию и кнопке «Назад», но с keyset это решаемо: храните курсоры в истории навигации.

Номера страниц и SEO

Курсоры удобны пользователям и серверам, но поисковики любят стабильные адреса с параметрами страниц. Компромисс:

• для SEO‑листингов держать «плоскую» пагинацию по нескольким первым страницам с кэшированными снапшотами;
• для живых пользователей (после первого взаимодействия/фильтрации) переключаться на курсорную модель.

Подводные камни и как их обойти

Вставки/удаления между страницами

Если порядок строгий (поле сортировки + уникальный id), дубликатов и пропусков не будет: «seek» позиционируется строго после/до полученной ранее записи. Но при очень высокой скорости вставок на «первой» странице пользователь может видеть обновление витрины — это нормально.

Корректный переход «назад»

Для «prev» инвертируем оператор, сортируем в обратном порядке и разворачиваем результат в приложении. Обязательно возвращайте оба курсора (next и prev) на каждой странице — это экономит логику на фронте и делает поведение предсказуемым.

Смена фильтров и сброс курсора

Любое изменение фильтров или сортировки обнуляет курсор. Добавьте в курсор хэш текущих параметров и проверяйте его при декодировании — так вы избежите невалидных запросов.

Экономика: сколько это экономит

Из практики внедрений в интернет‑витринах и лентах:

• p95 листинга с ~600 мс до ~90–120 мс на страницах «глубже второй» при той же инфраструктуре;
• минус 30–60% чтения с диска за счёт Index‑Only Scan и отказа от OFFSET;
• в 2–4 раза меньше CPU на горячей таблице, реже вылетают тайм‑ауты;
• меньше инцидентов «медленный каталог» в пиковые часы.

На больших коллекциях (10+ млн строк) выгода растёт ещё сильнее: OFFSET становится просто неприемлемым.

План внедрения по шагам

1. Замерьте текущие метрики: p50/p95, нагрузку на диск/CPU, объём трафика по сортировкам и фильтрам.
2. Выберите 1–2 основных сценария сортировки и подготовьте индексы (в т.ч. PARTIAL/INCLUDE).
3. Добавьте в API поддержку курсоров параллельно с действующей пагинацией — фича‑флаг.
4. Переключите фронт: первая страница без курсора, далее — next/prev через курсор.
5. Покройте редкие сортировки ограничениями или отложенным внедрением.
6. Добавьте защиту курсора подписью и валидацией параметров.
7. Перемерьте метрики, снимите профили, скорректируйте индексы по факту.
8. Уберите OFFSET/LIMIT из горячих маршрутов.

Чек‑лист перед продом

• Есть строгий порядок сортировки: поле + уникальный id.
• Под каждую горячую сортировку — свой индекс, под фильтры — частичные/составные.
• Курсор подписан и валидируется, меняется при смене фильтров/сортировки.
• Обе навигации (вперёд/назад) возвращают корректные курсоры.
• Первая страница — без курсора, дальше — только курсорная пагинация.
• Тесты на краевые случаи: одинаковые created_at/price, удаления/вставки, большие id.
• Мониторинг на p95/p99, планы запросов, использование индексов.

Переход на keyset‑пагинацию — не «магия разработчиков», а понятная бизнес‑инициатива: быстрее списки — выше конверсия, меньше инцидентов — спокойнее релизы, меньше I/O — ниже счёт за инфраструктуру.