Оглавление

• Почему пагинация по смещению подводит
• Как работает пагинация по ключу (keyset)
• Как выбрать надёжный ключ сортировки
• Индексы: один раз настроить — и забыть
• Примеры SQL для PostgreSQL
• Курсоры в API: безопасный формат и контроль целостности
  • Полностью рабочий пример API на Node.js (Express + PostgreSQL)
• Стабильные выгрузки: верхняя граница и «снимок» набора данных
• Движение назад: «предыдущая страница» без боли
• Фильтры, сортировки и совместимость с курсорами
• Миграция с offset на keyset без остановки сервиса
• Когда offset всё ещё уместен
• Короткий чек‑лист внедрения и выгода для бизнеса

Почему пагинация по смещению подводит

Пагинация по смещению (offset) — это привычные page=3&limit=50 и SQL вида ORDER BY ... LIMIT 50 OFFSET 100. На малых объёмах всё кажется нормальным, пока не всплывают две проблемы:

1. Медленно на больших смещениях. Чтобы отдать страницу, СУБД вынуждена «пролистать» первые N строк. На OFFSET=100 000 это уже заметная нагрузка и задержка для пользователей. CPU и диски работают зря, а стоимость растёт.

2. Нестабильность страниц. Пока пользователь листает, в таблицу добавляются и удаляются записи. Одни и те же элементы могут попасть на две разные страницы, а какие‑то — пропасть. В отчётах появляются дыры и дубли, в CRM — путаница.

Keyset‑пагинация решает обе проблемы и даёт стабильные, быстрые списки.

Как работает пагинация по ключу (keyset)

Идея простая: вместо «пролистать N строк» мы говорим «начни с элемента X и отдай следующие K». В SQL это выглядит как «смотреть дальше (>) или раньше (<) определённого ключа сортировки».

Пример логики:

• Первая страница: ORDER BY ключ LIMIT K.
• Следующая: WHERE ключ > последний_ключ_предыдущей_страницы ORDER BY ключ LIMIT K.

СУБД идёт по индексу сразу к нужному месту (seek), не тратя время на пропуск строк. Страницы не «прыгают» при вставках и удалениях: вы всегда движетесь от известного последнего элемента.

Как выбрать надёжный ключ сортировки

Ключ должен быть:

• Монотонным (не убывающим/не возрастающим).
• Уникальным или дополненным до уникальности.

Частые варианты:

• Автоинкрементный id — монотонен и уникален. Просто и быстро, но сортировка всегда по id.
• created_at — монотонен, но не уникален. Нужно добавить «развязку» id: пара (created_at, id).
• updated_at — плох как единственный ключ: запись может «перепрыгнуть» назад/вперёд. Для стабильной выгрузки лучше created_at (+ id) или отдельный монотонный столбец.

Правило: если ключ может совпадать у разных строк, добавляйте уникальный признак (обычно id) и работайте с кортежем, например (created_at, id).

Индексы: один раз настроить — и забыть

Чтобы запросы были быстрыми, индекс должен совпадать с порядком сортировки и условием.

• Для keyset по (created_at, id) достаточно одного B‑tree индекса: PostgreSQL умеет читать его в прямом и обратном направлении.
• Если сортируете только по id, достаточно обычного индекса по id (обычно это PK).

Индексы для примера:

-- Таблица с заказами
CREATE TABLE IF NOT EXISTS orders (
  id          BIGSERIAL PRIMARY KEY,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
  status      TEXT NOT NULL,
  amount      NUMERIC(12,2) NOT NULL
);

-- Индекс, покрывающий сортировку по (created_at, id)
CREATE INDEX IF NOT EXISTS idx_orders_created_id ON orders (created_at, id);

-- Для фильтрации по статусу полезен составной индекс, если часто фильтруем и сортируем одновременно
CREATE INDEX IF NOT EXISTS idx_orders_status_created_id ON orders (status, created_at, id);

Примеры SQL для PostgreSQL

Первые K заказов по времени создания:

SELECT id, created_at, status, amount
FROM orders
WHERE created_at <= $1 -- верхняя граница для снимка, об этом ниже
ORDER BY created_at ASC, id ASC
LIMIT $2; -- K

Следующая страница после (last_created_at, last_id):

SELECT id, created_at, status, amount
FROM orders
WHERE (created_at, id) > ($1, $2)
  AND created_at <= $3
ORDER BY created_at ASC, id ASC
LIMIT $4;

Предыдущая страница (движение назад):

SELECT id, created_at, status, amount
FROM orders
WHERE (created_at, id) < ($1, $2)
  AND created_at <= $3
ORDER BY created_at DESC, id DESC
LIMIT $4; -- получим в обратном порядке

Потом разворачиваем результат на приложении, чтобы показать по возрастанию.

С фильтром по статусу:

SELECT id, created_at, status, amount
FROM orders
WHERE status = $1
  AND (created_at, id) > ($2, $3)
  AND created_at <= $4
ORDER BY created_at ASC, id ASC
LIMIT $5;

Курсоры в API: безопасный формат и контроль целостности

Вместо page/offset API принимает и возвращает курсор — компактный токен, где зашиты «последний ключ», фильтры и параметры сортировки. Курсор можно хранить на клиенте и передавать обратно для следующей страницы.

Требования к курсурам:

• Самодостаточны: содержат все параметры, влияющие на результат (фильтры, сорт, «верхнюю границу» снимка).
• Защищены от подмены: используйте подпись (например, HMAC).
• С ограничением жизни (TTL) — на случай, если схема изменилась.

Полностью рабочий пример API на Node.js (Express + PostgreSQL)

// package.json (для справки):
// {
//   "type": "module",
//   "dependencies": { "express": "^4.19.2", "pg": "^8.11.5" }
// }

import express from 'express';
import crypto from 'crypto';
import { Pool } from 'pg';

const app = express();
app.use(express.json());

const DB_URL = process.env.DATABASE_URL || 'postgres://postgres:postgres@localhost:5432/postgres';
const SECRET = process.env.CURSOR_SECRET || 'dev-secret-change-me';
const pool = new Pool({ connectionString: DB_URL });

function hmacSign(payload) {
  return crypto.createHmac('sha256', SECRET).update(payload).digest('base64url');
}

function encodeCursor(obj) {
  const json = JSON.stringify(obj);
  const sig = hmacSign(json);
  return Buffer.from(json + '.' + sig).toString('base64url');
}

function decodeCursor(token) {
  const raw = Buffer.from(token, 'base64url').toString('utf8');
  const i = raw.lastIndexOf('.');
  if (i < 0) throw new Error('Некорректный курсор');
  const json = raw.slice(0, i);
  const sig = raw.slice(i + 1);
  const expSig = hmacSign(json);
  if (!crypto.timingSafeEqual(Buffer.from(sig), Buffer.from(expSig))) {
    throw new Error('Подпись курсора не совпала');
  }
  return JSON.parse(json);
}

// Хелпер: ограничиваем размер страницы
function normalizeLimit(n) {
  const num = Number(n) || 50;
  return Math.max(1, Math.min(num, 500));
}

// Эндпоинт: GET /orders?limit=50&cursor=...&status=paid
app.get('/orders', async (req, res) => {
  try {
    const limit = normalizeLimit(req.query.limit);
    const status = req.query.status ? String(req.query.status) : null;

    let cursor = null;
    if (req.query.cursor) cursor = decodeCursor(String(req.query.cursor));

    // Если курсора нет — создаём «снимок» набора: верхняя граница по времени
    const snapshotUpperBound = cursor?.snapshotUpperBound || new Date().toISOString();

    // Параметры движения
    const dir = cursor?.dir === 'backward' ? 'backward' : 'forward';
    const lastCreatedAt = cursor?.lastCreatedAt || null;
    const lastId = cursor?.lastId || null;

    // Функция сборки SQL и параметров под текущий запрос
    function buildQuery(wantExtra) {
      const params = [];
      let where = 'created_at <= $1';
      params.push(snapshotUpperBound);

      if (status) {
        where = `status = $${params.length + 1} AND ` + where;
        params.push(status);
      }

      let order, seek = '';
      if (dir === 'forward') {
        order = 'ORDER BY created_at ASC, id ASC';
        if (lastCreatedAt && lastId) {
          seek = ` AND (created_at, id) > ($${params.length + 1}, $${params.length + 2})`;
          params.push(lastCreatedAt, lastId);
        }
      } else {
        // backward
        order = 'ORDER BY created_at DESC, id DESC';
        if (lastCreatedAt && lastId) {
          seek = ` AND (created_at, id) < ($${params.length + 1}, $${params.length + 2})`;
          params.push(lastCreatedAt, lastId);
        }
      }

      const lim = wantExtra ? limit + 1 : limit; // возьмём на один больше, чтобы понять has_more

      const sql = `
        SELECT id, created_at, status, amount
        FROM orders
        WHERE ${where}${seek}
        ${order}
        LIMIT ${lim}
      `;
      return { sql, params };
    }

    // Получаем текущую страницу (+1 для has_more)
    const { sql, params } = buildQuery(true);
    const { rows } = await pool.query(sql, params);

    let items = rows;
    let hasMore = false;
    if (rows.length > limit) {
      hasMore = true;
      items = rows.slice(0, limit);
    }

    // Если шли назад, разворачиваем элементы для показа по возрастанию
    if (dir === 'backward') items = items.slice().reverse();

    // Курсор «вперёд»
    let nextCursor = null;
    if (items.length > 0 && (dir === 'forward' ? hasMore : true)) {
      const last = items[items.length - 1];
      nextCursor = encodeCursor({
        v: 1,
        dir: 'forward',
        lastCreatedAt: last.created_at,
        lastId: String(last.id),
        snapshotUpperBound,
        status,
        ttl: Date.now() + 1000 * 60 * 60 * 24 * 3 // 3 дня
      });
    }

    // Курсор «назад» — проверим, есть ли элементы перед первым текущей страницы
    let prevCursor = null;
    if (items.length > 0) {
      const first = items[0];
      // Есть ли что‑то раньше first?
      const prevCheckParams = [snapshotUpperBound];
      let prevWhere = 'created_at <= $1 AND (created_at, id) < ($2, $3)';
      prevCheckParams.push(first.created_at, String(first.id));
      if (status) {
        prevWhere = `status = $${prevCheckParams.length + 1} AND ` + prevWhere;
        prevCheckParams.push(status);
      }
      const prevSql = `SELECT 1 FROM orders WHERE ${prevWhere} LIMIT 1`;
      const prev = await pool.query(prevSql, prevCheckParams);
      if (prev.rows.length > 0) {
        prevCursor = encodeCursor({
          v: 1,
          dir: 'backward',
          lastCreatedAt: first.created_at,
          lastId: String(first.id),
          snapshotUpperBound,
          status,
          ttl: Date.now() + 1000 * 60 * 60 * 24 * 3
        });
      }
    }

    res.json({
      items,
      page_info: {
        has_more: hasMore && dir === 'forward',
        next_cursor: nextCursor,
        prev_cursor: prevCursor,
        snapshot_upper_bound: snapshotUpperBound,
        limit
      }
    });
  } catch (e) {
    res.status(400).json({ error: String(e.message || e) });
  }
});

const port = Number(process.env.PORT || 3000);
app.listen(port, () => console.log(`Listening on :${port}`));

Что важно в примере:

• Курсор подписан HMAC, его нельзя подделать без секрета.
• В курсоре есть snapshotUpperBound — ISO‑время, взятое на первой странице; оно «замораживает» выдачу: новые записи позже этой границы не попадут, что исключает дубли и пропуски между страницами.
• Лимит ограничен сверху, чтобы никто не попросил «миллион за раз».

Стабильные выгрузки: верхняя граница и «снимок» набора данных

В идеале мы бы держали длительную транзакцию уровня REPEATABLE READ на всё листание — тогда «картинка мира» была бы фиксированной. На практике это невозможно: пользователь листает минуты, а то и часы. Решение — фиксировать верхнюю границу по времени создания на первой странице и включать её в каждый последующий запрос: created_at <= snapshot_upper_bound.

Так мы исключаем «поздние» вставки из набора. Если записи удаляют — мы их уже прошли, курсор не «поедет». В результате выгрузки и отчёты становятся детерминированными: два пользователя при одинаковых фильтрах и одинаковой дате снимка увидят одну и ту же последовательность.

Если сортируете не по created_at, выберите другой монотонный столбец для верхней границы (например, id) и зафиксируйте условие id <= max_id_at_start.

Движение назад: «предыдущая страница» без боли

Чтобы работать «назад», используйте обратное сравнение и обратный порядок, а затем разворачивайте результат на стороне приложения. Проверку «есть ли раньше что‑то» можно делать отдельным лёгким запросом с LIMIT 1 — как в примере. В курсоре для движения назад ставьте dir=backward и передавайте ключ первого элемента текущей страницы.

Фильтры, сортировки и совместимость с курсорами

• Любой параметр, влияющий на набор данных, обязан попасть в курсор: фильтры (status), порядок сортировки, верхняя граница снимка.
• Менять сортировку «между страницами» нельзя: это другой набор.
• Для сложных фильтров создавайте подходящие составные индексы: порядок столбцов — сначала фильтры (=, IN), потом сортировка.

Пример индекса под фильтр по статусу и сортировку по времени: (status, created_at, id).

Миграция с offset на keyset без остановки сервиса

• Шаг 1. Добавьте индексы для keyset.
• Шаг 2. Поддерживайте обе схемы в API: старые клиенты продолжают ходить с page/offset, новые используют курсоры.
• Шаг 3. На бэкенде для offset используйте keyset «под капотом»: чтобы «продолжить с N‑й страницы», сделайте один запрос с OFFSET/LIMIT, получите последний элемент страницы и сгенерируйте курсор уже по ключу. Следующие страницы — только keyset.
• Шаг 4. Мягко переключайте клиентов, собирайте метрики, отключайте offset после падения трафика на него до минимума.

Промежуточный запрос для «подхвата» с page=N (разово, не в горячем пути):

WITH boundary AS (
  SELECT created_at, id
  FROM orders
  WHERE status = $1
  ORDER BY created_at ASC, id ASC
  LIMIT 1 OFFSET $2 -- N*limit - 1
)
SELECT id, created_at, status, amount
FROM orders
WHERE status = $1 AND (created_at, id) >= (SELECT created_at, id FROM boundary)
ORDER BY created_at ASC, id ASC
LIMIT $3; -- обычная страница по keyset

Когда offset всё ещё уместен

• Небольшие справочники с десятками или сотнями строк, где пользователю важно «перейти на страницу 7». Админки с явной нумерацией.
• Сложные аналитические сортировки без подходящего монотонного ключа. Иногда проще сделать материализованное представление или временную таблицу с нужным ключом — и уже потом применять keyset.

Как только объёмы и нагрузка растут, keyset окупается сразу: стабильность, скорость, предсказуемость.

Короткий чек‑лист внедрения и выгода для бизнеса

• Выберите ключ сортировки: id или (created_at, id).
• Добавьте индексы под ключ и частые фильтры.
• Спроектируйте курсор: кодируйте JSON, подписывайте HMAC, добавьте TTL.
• Зафиксируйте верхнюю границу снимка на первой странице и передавайте её дальше.
• Поддержите движение назад (обратный ORDER BY и разворот результата).
• Ограничьте максимум limit и валидируйте входные параметры.
• Проведите миграцию с offset на keyset без обрывов для клиентов.

Выгода:

• Время ответа страниц падает кратно на больших наборах, нагрузка на БД — тоже. Это прямое сокращение счетов за инфраструктуру.
• Списки и выгрузки становятся стабильными: нет дублей и «пропавших» записей. Меньше обращений в поддержку и холостых расследований.
• API проще масштабировать: seek по индексу работает быстро независимо от номера страницы.

---

Итог: keyset‑пагинация — простой архитектурный приём, который убирает класс проблем с производительностью и стабильностью, заметных для бизнеса. Достаточно один раз выбрать правильный ключ и аккуратно завернуть его в курсор — и списки в вашем продукте становятся предсказуемыми и быстрыми.