Пагинация и лимиты API: как отдавать данные постранично и не дать выгрести базу

Ваш API отдаёт страницы по двадцать записей и работает прекрасно ровно до того дня, когда у первого крупного клиента накопится полмиллиона строк. Тогда выяснится сразу две вещи: страницы стали открываться секундами, а синхронизация на стороне клиента дважды затянула одни и те же заказы.

Оба симптома дают один и тот же виновник — постраничная выдача через OFFSET . И оба чинятся одним переходом, причём переписывать приходится только слой выдачи.

Пагинация — это способ отдать большую коллекцию по частям. Вариантов ровно два. Offset -пагинация просит базу пропустить N строк и вернуть следующие двадцать. Курсорная (её же называют keyset) запоминает последнюю отданную запись и просит всё, что идёт после неё.

-- offset: пропустить 4000 строк
SELECT * FROM orders ORDER BY id LIMIT 20 OFFSET 4000;

-- курсор: всё, что после известной записи
SELECT * FROM orders WHERE id > 4000 ORDER BY id LIMIT 20;

Результат на спокойной базе одинаковый. Поведение под ростом данных и параллельными записями — принципиально разное.

Offset ломается двумя разными способами

Про первый знают почти все, про второй вспоминают уже после инцидента.

Он линейно замедляется

PostgreSQL, MySQL и SQLite не умеют «перепрыгнуть» через строки. При OFFSET 100000 база честно читает сто тысяч строк и выбрасывает их, чтобы отдать следующие двадцать. Стоимость запроса растёт вместе со смещением.

Автор разбора замерил это на таблице в 1,2 млн строк в PostgreSQL 15 с холодным кешем и обычным btree-индексом по id :

LIMIT 20 OFFSET 0 -> 3 мс
LIMIT 20 OFFSET 10 000 -> 45 мс
LIMIT 20 OFFSET 100 000 -> 310 мс
LIMIT 20 OFFSET 1 000 000 -> 2,4 с

Курсорный вариант на той же таблице держал 3 мс на любой глубине. Разница объясняется сложностью: поиск по индексу это O(log n), а большое смещение — O(offset). На пятидесятитысячной странице пользователь ждёт больше двух секунд вместо миллисекунд.

Он врёт, когда в таблицу пишут

Этот сценарий коварнее. Пользователь загрузил первую страницу с двадцатью записями, отсортированными от новых к старым. Пока он читал, в таблицу добавились три новые записи. Запрос второй страницы с OFFSET 20 вернёт часть того, что уже было на первой: всё сдвинулось на три позиции.

Клиент получает дубликаты. При удалении записей картина обратная — часть строк он не увидит никогда, потому что они проскочили мимо границы страницы. У автора статьи на этом сломалась синхронизация у клиента: задание дважды загрузило одни и те же двадцать заказов, и три письма в поддержку ушло на то, чтобы понять, что виновата пагинация, а не код клиента.

Курсор от этого защищён по построению. Курсор — это последняя фактически полученная запись, поэтому «всё, что после неё» остаётся верным независимо от того, сколько строк вставили или удалили между запросами.

Как выглядит курсор в коде

Простейшая версия работает по первичному ключу и умещается в один обработчик:

@app.get("/orders")
def list_orders(
db: Annotated[Session, Depends(get_db)], # без Depends схема не соберётся
cursor: int | None = Query(default=None),
per_page: int = Query(default=20, ge=1, le=100),
):
stmt = select(Order).order_by(Order.id.desc()).limit(per_page + 1)
if cursor is not None:
stmt = stmt.where(Order.id < cursor)

rows = db.scalars(stmt).all()
has_more = len(rows) > per_page
rows = rows[:per_page]
next_cursor = rows[-1].id if has_more and rows else None

return {"data": rows, "next_cursor": next_cursor, "has_more": has_more}

Обратите внимание на per_page + 1 . Запрашивая на одну запись больше, чем собираетесь отдать, вы узнаёте о существовании следующей страницы без отдельного запроса COUNT . Лишняя запись отбрасывается, а её наличие становится флагом has_more .

Составной курсор: место, где ломается наивная версия

Курсор по одному id корректен, только пока вы сортируете по id . Как только сортировка идёт по created_at , простое условие created_at > X начинает терять записи: два заказа, созданные в одну миллисекунду, дают ничью, и одна из строк молча исчезает из выдачи.

Лечится это составным курсором — ключ сортировки плюс первичный ключ, сравниваемые как кортеж:

WHERE (created_at, id) < (:cursor_created_at, :cursor_id)
ORDER BY created_at DESC, id DESC
LIMIT 21

Сравнение кортежей разрешает ничьи детерминированно, поэтому ни одна строка не дублируется и не пропадает. Под это нужен составной индекс (created_at DESC, id DESC) , иначе выигрыш в скорости пропадёт вместе с планом запроса.

Про формат курсора: Не отдавайте наружу голый идентификатор, если планируете менять схему. Заверните значения в маленький JSON с полем версии и закодируйте в base64: тогда через год вы поменяете состав курсора, не сломав клиентов, которые прямо сейчас держат страницу открытой.

Когда офсетную пагинацию нужно оставить

Курсор не бесплатный, и подменять им всё подряд не стоит. Есть четыре ситуации, где offset честно лучше:

• Интерфейсу нужен переход на конкретную страницу. У курсора нет номеров страниц, поэтому админки, таблицы и всё с кнопкой «перейти к странице 47» остаются на offset.
• Данных мало и они не меняются. Справочник на пять тысяч строк не выиграет ничего, зато offset проще объяснить и отладить.
• Пагинация идёт по памяти или по кешу, где смещение и так стоит O(1).
• Сортировка задаётся пользователем по произвольной колонке. Курсор тут превращается в кодирование кортежей в непрозрачные строки, и это уже заметная сложность.

Разумное правило: курсор по умолчанию для всего, что может перерасти десять тысяч строк или читается во время записи. Offset — для небольших, статичных и внутренних данных.

И отдельно стоит помнить, что пагинация обычно не единственная нагрузка на базу. Кто ещё и насколько сильно её грузит, разбирали в переводе про паттерны управления трафиком к PostgreSQL .

Вторая половина задачи: кто и сколько может забрать

Правильная пагинация решает, как отдавать данные. Она не решает, сколько их заберут за минуту. Ограничение частоты запросов выглядит тривиально («считай запросы, блокируй сверх N») и превращается в задачу распределённых систем ровно в тот момент, когда серверов становится два.

Сначала ответьте, от чего защищаетесь

От выбранной цели зависит вся конструкция, и целей обычно выделяют четыре :

• Защита от перегрузки — не дать всплеску положить сервис.
• Честное распределение — не дать одному шумному клиенту вытеснить остальных.
• Защита от злоупотреблений — притупить перебор паролей, скрейпинг и подстановку учётных данных.
• Контроль расходов — ограничить дорогие ручки вроде вызова модели или построения отчёта. Тот же принцип со стороны поставщика разбирали на примере лимитов трат в OpenAI API .

Три алгоритма и один, который выбирают почти всегда

Фиксированное окно считает запросы в календарном интервале: сто в минуту со сбросом на начале минуты. Просто и дыряво: клиент отправляет сотню в 12:00:59 и ещё сотню в 12:01:00, то есть двести за одну секунду. Граница окна и есть дыра.

Скользящее окно считает в непрерывно сдвигающемся интервале, часто взвешивая счётчик предыдущего окна по мере хода времени. Точнее, но требует больше состояния.

Ведро токенов обычно и оказывается верным выбором по умолчанию. В ведре лежит до N токенов, оно пополняется с постоянной скоростью, каждый запрос тратит один токен. Такая схема разрешает контролируемые всплески (молчавший клиент накопил токены и может потратить их разом), но держит среднюю нагрузку в рамках. Родственное «дырявое ведро» выдаёт идеально ровный поток и нужно там, где принимающая сторона всплесков не переносит.

Фиксированное окно пишут первым и потом жалеют. На ведро токенов мигрируют. Weston Carnes автор руководства по ограничению частоты запросов в API

Ловушка, из-за которой лимит не работает вообще

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

Лечится это переносом счётчика в общее хранилище, обычно в Redis, причём проверка и инкремент обязаны быть атомарными. Последовательность «прочитать, сравнить, увеличить» из кода приложения гоняется между инстансами и недосчитывает запросы ровно под той нагрузкой, ради которой лимит и ставили.

Скользящее окно на Redis: пятнадцать строк Lua

Команда сервиса jo4.io столкнулась с частным случаем этой задачи: ручка подсказки категорий ходит в модель через Groq, каждый вызов стоит денег, а пользователь может дёрнуть её полсотни раз за минуту. Нужны были лимиты на конкретную ручку и на конкретного пользователя, причём в тот же день.

Скользящее окно они собрали на сортированном множестве Redis . Скор записи — временная метка в миллисекундах, а весь алгоритм умещается в один Lua-скрипт, который Redis выполняет атомарно:

Первая команда чистит записи старше окна относительно текущего момента — именно поэтому окно скользящее, а не фиксированное: границ, к которым можно приурочить всплеск, здесь просто нет. PEXPIRE с запасом в секунду доубивает ключ, если новых запросов не приходит, так что чистить хранилище отдельно не нужно.

Две детали, на которых легко ошибиться

Уникальность члена множества. ZADD не добавляет запись, а перезаписывает существующую, если член совпал. Два запроса в одну миллисекунду с одинаковым значением дадут счётчик, который занижен. Авторы склеивают метку времени с восемью символами UUID: метка обеспечивает уникальность почти всегда, а восемь шестнадцатеричных символов дают около четырёх миллиардов вариантов внутри одной миллисекунды.

Куда падать при отказе Redis. Решение здесь принимается осознанно и по-разному для двух случаев. Если вызов не получил внятного ответа вовремя (таймаут клиента, сетевая икота), запрос пропускают: лимит здесь страхует бюджет, а не безопасность, и блокировать живого пользователя из-за сбоя инфраструктуры хуже, чем оплатить лишний вызов модели. А вот жёсткое исключение (Redis недоступен, соединение отвергнуто) пробрасывают наверх: если хранилище легло целиком, лучше шумно упасть, чем молча жечь бюджет вообще без ограничений.

Обошлось это в два часа работы вместе с тестами. Одна запись в сортированном множестве занимает около 50 байт, на пользователя их пять, и даже при десяти тысячах активных пользователей всё хранилище лимитов весит порядка 2,5 МБ. Настроенный лимит составил пять запросов за шестьдесят секунд на пользователя. Первым же уловом стал автоматизированный скрипт, бивший в ручку больше двухсот раз в час.

Честный контракт с клиентом

Ограничение без внятного ответа превращает вежливого клиента в невежливого: не понимая, что происходит, он начинает долбить чаще. Минимальный набор обязательств выглядит так:

• Отдавать 429 Too Many Requests, а не общий 400 или 503. По коду клиент понимает, что делать.
• Присылать заголовок Retry-After, чтобы клиент знал момент следующей попытки, а не подбирал его перебором.
• Показывать остаток бюджета в заголовках вида X-RateLimit-Remaining и X-RateLimit-Reset.

HTTP/1.1 429 Too Many Requests
Retry-After: 42
X-RateLimit-Limit: 5
X-RateLimit-Remaining: 0
X-RateLimit-Reset: 1788712345
Content-Type: application/json

{"error": "rate_limit_exceeded", "retry_after": 42}

Последний пункт стоит дешевле всего и работает лучше всего: клиент, который видит свой остаток, обычно распределяет запросы сам и до стены не доходит.

1. 01 Замерьте глубокую страницу Выполните запрос со смещением в сто тысяч строк и сравните время с первой страницей. Разница в разы означает, что курсор нужен уже сейчас.
2. 02 Проверьте выдачу под записью Загрузите первую страницу, вставьте несколько строк, загрузите вторую. Повторы в ответе подтверждают, что offset поедет у любого клиента с синхронизацией.
3. 03 Добавьте составной ключ Если сортируете не по первичному ключу, переведите курсор на пару «ключ сортировки плюс id» и заведите под неё составной индекс.
4. 04 Найдите счётчики в памяти процесса Любой лимит, живущий в переменной инстанса, умножается на число инстансов. Перенесите счётчик в общее хранилище с атомарной проверкой.
5. 05 Опишите поведение при отказе хранилища Решите заранее, пропускать запросы или отклонять, если Redis недоступен, и запишите это решение в код явно, а не оставляйте на волю обработчика исключений.
6. 06 Проверьте ответ на превышение Убедитесь, что отдаётся 429 с заголовком Retry-After, а не общая пятисотка и не тихий обрыв соединения.

Offset просит базу пропустить N строк и вернуть следующие. Курсорная запоминает последнюю выданную запись и запрашивает всё, что идёт после неё. Первая замедляется линейно с глубиной и сбивается при параллельных записях, вторая держит постоянное время и не теряет строки.

На таблице в 1,2 млн строк в PostgreSQL 15 выборка двадцати записей занимала 3 мс без смещения, 310 мс при смещении в сто тысяч и 2,4 с при смещении в миллион. Курсорный запрос на той же таблице отрабатывал за 3 мс на любой глубине.

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

По умолчанию ведро токенов: оно разрешает короткие всплески и при этом держит среднюю нагрузку. Скользящее окно точнее, когда нужен жёсткий потолок за интервал без права на всплеск. Фиксированное окно проще всех, но на границе интервала пропускает двойной объём.

Потому что за балансировщиком таких счётчиков столько же, сколько инстансов: настроенные сто запросов в минуту при десяти инстансах законно превращаются в тысячу. Счётчик нужно держать в общем хранилище вроде Redis, а проверку и увеличение выполнять атомарно, иначе инстансы гоняются между собой и недосчитывают запросы ровно под той нагрузкой, ради которой лимит и ставили.

Что забрать с собой

Обе половины задачи решаются до того, как появится нагрузка, и обе стоят один вечер. Курсорная пагинация убирает и деградацию на глубоких страницах, и молчаливую порчу выдачи при параллельных записях. Атомарный счётчик в общем хранилище превращает лимит из декоративного в настоящий.

Отложить их дешевле всего прямо сейчас и дороже всего в момент, когда в поддержку придёт клиент с задвоенными заказами: тогда чинить придётся не только API, но и данные на его стороне.

Разборы, на которых основан материал: переход с offset на курсор с замерами , паттерны и алгоритмы ограничения частоты и скользящее окно на Redis и Lua .

Откройте свой самый нагруженный список и выполните запрос с большим смещением. Если ответ идёт дольше сотни миллисекунд, вы уже нашли, с чего начать.