
Offset/limit кажется простым решением:
SELECT id, created_at, total_amount
FROM orders
WHERE merchant_id = $1
ORDER BY created_at DESC
OFFSET $2 LIMIT $3;
На маленьких таблицах это работает. Но с ростом данных база вынуждена:
Цена для бизнеса: медленные страницы каталога/заказов, рост CPU и IO на базе, недовольство пользователей и затраты на горизонтальное масштабирование раньше, чем нужно.
Идея: вместо «пролистывания» 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;
Почему это быстро:
Ограничение: нельзя быстро прыгнуть на произвольную страницу N — и это честная плата за скорость и стабильность.
Для примера с сортировкой по 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);
Пояснения:
Проверка планом:
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.
Курсор — это просто «закодированная позиция». Его лучше сделать непрозрачным и подписывать, чтобы клиенты не подделывали значения и не ломали инварианты.
Пример формата: 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();
}
}
Примечания:
Любые детерминированные фильтры можно добавлять до условия курсора. Важно, чтобы фильтры были совместимы с индексом. Если добавляете status, подумайте о частичном индексе:
CREATE INDEX CONCURRENTLY idx_orders_open_merchant_created_id
ON orders (merchant_id, created_at DESC, id DESC)
WHERE status IN ('pending', 'paid');
Мы уже показали приём: инвертируем сравнение и сортировку, затем переворачиваем результат на приложении. Это позволяет не создавать отдельный эндпоинт.
Часто нужно возвращать связанные данные (имя клиента). Чтобы не сломать производительность, сперва выберите «ключи» из базовой таблицы, затем 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‑результата.
Keyset нельзя эффективно «перемотать» на страницу 287 — для этого нужно пройти 286 шагов. Варианты, которые работают на практике:
-- безопасная защита от дорогих подсчётов
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;
Плавный план:
Курсорная пагинация даёт предсказуемую скорость листингов даже на десятках миллионов строк, резко снижает нагрузку на БД и убирает «пляску» записей между страницами. Пользователь получает быстрый и стабильный список, а вы — меньше инцидентов и затрат на инфраструктуру.