• Содержание
  • Почему offset/limit тормозит и ломает UX
  • Что такое keyset (курсорная) пагинация и как она работает
  • Стабильная сортировка: выбор колонок и индексов
  • API с курсорами: формат, подпись и пример на TypeScript
  • Фильтры, prev‑страница, JOIN и удалённые строки
  • Счётчик страниц и «перейти на страницу N» — честно про ограничения
  • Миграция с offset/limit на курсоры без даунтайма
  • Наблюдаемость: EXPLAIN, планы, контроль деградаций
  • Частые ошибки и чек‑лист внедрения

Почему offset/limit тормозит и ломает UX

Offset/limit кажется простым решением:

SELECT id, created_at, total_amount
FROM orders
WHERE merchant_id = $1
ORDER BY created_at DESC
OFFSET $2 LIMIT $3;

На маленьких таблицах это работает. Но с ростом данных база вынуждена:

• Перебирать всё больше строк, чтобы «пролистать» OFFSET N.
• Снимать и сортировать большие объёмы, часто уходя в внешнюю сортировку на диск.
• Выдавать нестабильные результаты: пока пользователь переходит со страницы 3 на 4, часть строк уже вставили/удалили; появляются дубликаты или пропуски.

Цена для бизнеса: медленные страницы каталога/заказов, рост CPU и IO на базе, недовольство пользователей и затраты на горизонтальное масштабирование раньше, чем нужно.

Что такое keyset (курсорная) пагинация и как она работает

Идея: вместо «пролистывания» N строк мы запоминаем позицию последней записи и «ищем дальше» от неё по индексам.

Пример — сортируем заказы по убыванию времени создания и уникальному id как «страховочной» колонке, чтобы порядок был строго детерминирован:

-- Первая страница
SELECT id, created_at, status, total_amount
FROM orders
WHERE merchant_id = $1
ORDER BY created_at DESC, id DESC
LIMIT $2;

Сохраняем курсор из последней записи: (last_created_at, last_id). Следующая страница:

-- Следующая страница по курсору
SELECT id, created_at, status, total_amount
FROM orders
WHERE merchant_id = $1
  AND (created_at, id) < ($3, $4)
ORDER BY created_at DESC, id DESC
LIMIT $2;

Почему это быстро:

• WHERE с «строгим сравнением» по составному ключу использует индексный «seek» вместо перебора.
• Нет OFFSET, значит нет лишнего чтения.
• Порядок стабилен (created_at, id), значит не будет дублей или пропусков при вставках/удалениях между запросами.

Ограничение: нельзя быстро прыгнуть на произвольную страницу N — и это честная плата за скорость и стабильность.

Стабильная сортировка: выбор колонок и индексов

Правила стабильного порядка

• Минимум две колонки: основная для бизнес‑сортировки (например, created_at DESC) и детерминированный «разрешитель ничьих» (обычно id DESC/ASC в том же направлении).
• Значения основной колонки должны корректно сравниваться и сохранять инвариант при вставках: timestamp with time zone или монотонный ULID/BigSerial подойдут. updated_at — плохой выбор, он меняется и «колбасит» порядок.
• Типы и коллации должны совпадать с индексом: не делать сортировку по выражению без соответствующего функционального индекса.

Индексы под keyset

Для примера с сортировкой по created_at DESC, id DESC и выборкой по merchant_id:

-- Без простоя
CREATE INDEX CONCURRENTLY idx_orders_merchant_created_id
  ON orders (merchant_id, created_at DESC, id DESC)
  INCLUDE (status, total_amount);

Пояснения:

• merchant_id в начале — чтобы использовать индекс для фильтра по мерчанту.
• INCLUDE (PostgreSQL 11+) позволяет сделать «покрывающий» индекс: многие запросы станут Index Only Scan.
• Для сортировки ASC создайте индекс в ASC или используйте отдельный индекс, если нужно быстро ходить и в обратном направлении.

Проверка планом:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, created_at, status, total_amount
FROM orders
WHERE merchant_id = 42 AND (created_at, id) < ('2025-01-01T00:00:00Z', 123456)
ORDER BY created_at DESC, id DESC
LIMIT 50;

Ожидаем: Index Only Scan/Index Scan с минимальными буферами, без сортировки и без Seq Scan.

API с курсорами: формат, подпись и пример на TypeScript

Курсор — это просто «закодированная позиция». Его лучше сделать непрозрачным и подписывать, чтобы клиенты не подделывали значения и не ломали инварианты.

Пример формата: base64(JSON) + HMAC‑подпись.

// cursor.ts
import crypto from 'crypto';

const secret = process.env.CURSOR_SECRET!; // храните в менеджере секретов

export type CursorPayload = {
  sort: 'created_desc';
  lastCreatedAt: string; // ISO
  lastId: number;
  v: 1; // версия
};

function hmac(data: string) {
  return crypto.createHmac('sha256', secret).update(data).digest('base64url');
}

export function encodeCursor(p: CursorPayload): string {
  const json = JSON.stringify(p);
  const b64 = Buffer.from(json).toString('base64url');
  const sig = hmac(b64);
  return `${b64}.${sig}`;
}

export function decodeCursor(cursor: string): CursorPayload {
  const [b64, sig] = cursor.split('.');
  if (!b64 || !sig || hmac(b64) !== sig) {
    throw new Error('invalid_cursor');
  }
  const json = Buffer.from(b64, 'base64url').toString('utf8');
  const payload = JSON.parse(json);
  if (payload.v !== 1 || payload.sort !== 'created_desc') {
    throw new Error('unsupported_cursor');
  }
  return payload as CursorPayload;
}

Маршрут списка заказов:

// orders.route.ts (Express)
import { Request, Response } from 'express';
import { Pool } from 'pg';
import { encodeCursor, decodeCursor } from './cursor';

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  statement_timeout: 5000,
});

export async function getOrders(req: Request, res: Response) {
  const merchantId = Number(req.query.merchant_id);
  const limit = Math.min(Number(req.query.limit) || 50, 200);
  const prev = req.query.prev === '1';
  const cursor = req.query.cursor ? String(req.query.cursor) : null;

  let params: any[] = [merchantId, limit];
  let where = 'merchant_id = $1';
  let order = 'created_at DESC, id DESC';

  if (cursor) {
    const p = decodeCursor(cursor);
    if (!prev) {
      params.push(p.lastCreatedAt, p.lastId);
      where += ' AND (created_at, id) < ($3, $4)';
      order = 'created_at DESC, id DESC';
    } else {
      // prev-страница: инвертируем сравнение и порядок, потом реверсируем в приложении
      params.push(p.lastCreatedAt, p.lastId);
      where += ' AND (created_at, id) > ($3, $4)';
      order = 'created_at ASC, id ASC';
    }
  }

  const sql = `
    WITH page AS (
      SELECT id, created_at, status, total_amount
      FROM orders
      WHERE ${where}
      ORDER BY ${order}
      LIMIT $2
    )
    SELECT * FROM page
  `;

  const client = await pool.connect();
  try {
    const { rows } = await client.query(sql, params);
    const data = prev ? rows.reverse() : rows; // чтобы вернуть в исходном порядке

    const nextCursor = data.length
      ? encodeCursor({
          sort: 'created_desc',
          lastCreatedAt: data[data.length - 1].created_at.toISOString(),
          lastId: Number(data[data.length - 1].id),
          v: 1,
        })
      : null;

    const prevCursor = data.length
      ? encodeCursor({
          sort: 'created_desc',
          lastCreatedAt: data[0].created_at.toISOString(),
          lastId: Number(data[0].id),
          v: 1,
        })
      : cursor;

    res.json({ items: data, next_cursor: nextCursor, prev_cursor: prevCursor, limit });
  } finally {
    client.release();
  }
}

Примечания:

• Используйте timestamp with time zone, храните в UTC и сериализуйте в ISO.
• Контролируйте limit и таймауты на уровне пула и запросов.
• prev реализован «зеркалом» запроса и реверсом массива на приложении.

Фильтры, prev‑страница, JOIN и удалённые строки

Фильтры

Любые детерминированные фильтры можно добавлять до условия курсора. Важно, чтобы фильтры были совместимы с индексом. Если добавляете status, подумайте о частичном индексе:

CREATE INDEX CONCURRENTLY idx_orders_open_merchant_created_id
  ON orders (merchant_id, created_at DESC, id DESC)
  WHERE status IN ('pending', 'paid');

prev‑страница

Мы уже показали приём: инвертируем сравнение и сортировку, затем переворачиваем результат на приложении. Это позволяет не создавать отдельный эндпоинт.

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

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

WITH keys AS (
  SELECT id, created_at
  FROM orders
  WHERE merchant_id = $1 AND (created_at, id) < ($3, $4)
  ORDER BY created_at DESC, id DESC
  LIMIT $2
)
SELECT o.id, o.created_at, o.status, o.total_amount, c.name AS customer_name
FROM keys k
JOIN orders o USING (id)
LEFT JOIN customers c ON c.id = o.customer_id
ORDER BY o.created_at DESC, o.id DESC;

Так мы сохраняем быстрый «seek» и не провоцируем Постгрес на сортировку большого JOIN‑результата.

Удалённые строки и вставки

• Вставки между страницами не приводят к дублям: детерминированный порядок (created_at, id) обеспечивает стабильный «срез». Новые записи окажутся на более «ранних» страницах.
• Удаления не ломают навигацию: мы не опираемся на «позицию» страницы, а только на курсор‑значение последней записи.

Счётчик страниц и «перейти на страницу N» — честно про ограничения

Keyset нельзя эффективно «перемотать» на страницу 287 — для этого нужно пройти 286 шагов. Варианты, которые работают на практике:

• Отказаться от понятия «номер страницы», использовать «Показаны 50 из более чем 10 000». Пользователь всё равно листает вперёд.
• Делать точный COUNT только для коротких списков или с малым количеством фильтров. С порогом: если COUNT > 10 000, отдаём «10 000+». Пример:

-- безопасная защита от дорогих подсчётов
WITH limited AS (
  SELECT 1 FROM orders WHERE merchant_id = $1 LIMIT 10001
)
SELECT CASE WHEN COUNT(*) = 10001 THEN '10000+' ELSE CAST(COUNT(*) AS TEXT) END AS total
FROM limited;

• Хранить агрегированные счётчики асинхронно (например, по merchant_id и статусу) и обновлять их из стрима событий. Это дешёво и достаточно точно для «шапки».
• Для поиска с тяжёлыми фильтрами — отдельный поисковый индекс (например, внешняя поисковая система), где «страницы» считаются быстро.

Миграция с offset/limit на курсоры без даунтайма

Плавный план:

1. Добавить индексы под будущий порядок (CONCURRENTLY).
2. На API научиться принимать оба параметра: cursor и offset. Приоритет — cursor.
3. Для старых клиентов продолжать работать по offset.
4. В веб‑клиенте переключить листинг на курсоры.
5. «Глубокий переход»: если пользователь пришёл на страницу /orders?page=5, один раз вычислить курсор «границы» через OFFSET (да, это медленный запрос), затем дальше листать курсорами.
6. Когда доля курсоров > 95%, удалить поддержку offset/limit.

Наблюдаемость: EXPLAIN, планы, контроль деградаций

• Следите, чтобы запросы с курсорами использовали индексный скан, а не Seq Scan. Признак беды — сортировка в плане, большие буферы, рост времени.
• Включите логирование медленных запросов и планов в отдельные сэмплы.
• Мониторьте hit‑ratio кэша, количество логических/физических чтений (BUFFERS в EXPLAIN), рост размера индексов.
• Готовьте «план Б»: фича‑флаг для возврата к offset в случае регресса (как временная мера).

Частые ошибки и как их избежать

• Нет «страховочной» колонки в ORDER BY. Итог — нестабильный порядок и дубликаты. Всегда добавляйте уникальный id вторым ключом.
• ORDER BY не соответствует индексу. Итог — сортировка в памяти/на диске. Сверьте направление и список колонок, при необходимости создайте отдельный индекс.
• Курсор несёт только id. Если основной порядок по дате — нужен и timestamp в курсоре.
• Прозрачный курсор без подписи. Пользователи смогут подделывать и ломать логику. Подписывайте.
• Попытка делать «страницу 1000» курсорами в цикле. Пользователь всё равно не долистает. Меняйте UX.

Чек‑лист внедрения

• Выбраны колонки сортировки: created_at DESC, id DESC (или свой порядок)
• Создан индекс под фильтры и порядок (CONCURRENTLY), при необходимости INCLUDE
• SQL‑шаблоны: первая страница, next, prev; CTE keys для JOIN
• Курсор: формат, версия, подпись HMAC, лимит и валидация
• Обработаны фильтры и пустые результаты, стабильная сериализация времени (UTC)
• Решение по counters: пороги, асинхронные агрегаты или «10 000+»
• Наблюдаемость: EXPLAIN‑снапшоты, slow‑query‑лог, алерты на Seq Scan
• План миграции и фича‑флаг для отката

Итог для бизнеса

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