Пагинация и фильтрация в FastAPI: чек-лист перед продакшеном

Почему offset/limit — это бомба замедленного действия

Стандартный подход к пагинации в FastAPI-проектах выглядит так: принимаем offset и limit как query-параметры, пробрасываем в SQL, отдаём результат. На тысяче записей всё летает. На миллионе — база выполняет OFFSET 999000, физически пропуская все предыдущие строки, и время ответа уходит в секунды. Если к этому добавить фильтрацию по нескольким полям без индексов — эндпоинт начнёт таймаутить задолго до того, как кто-то напишет нагрузочный тест.

Ниже — конкретные приёмы и чек-лист, которые закрывают самые частые проблемы с пагинацией и фильтрацией в FastAPI + SQLAlchemy + PostgreSQL. Подходы работают и с другими СУБД, но примеры даны для PostgreSQL 15+ и SQLAlchemy 2.x.

Два подхода к пагинации

Offset/limit: когда допустимо

Offset-пагинация подходит, если данных заведомо мало (справочники, внутренние админки с десятками тысяч записей) и пользователь реально хочет «перепрыгнуть на страницу 47». Во всех остальных случаях это плохой выбор по умолчанию.

Главная проблема — стоимость растёт линейно с номером страницы. СУБД вынуждена отсортировать и пропустить все строки до offset, даже если они не нужны. Вторая проблема: если между запросами появилась новая запись, пользователь увидит дубль или пропустит элемент.

Cursor-пагинация: как работает и что даёт

Cursor (keyset) пагинация использует значение последнего элемента предыдущей страницы как точку отсчёта. Вместо OFFSET 999000 запрос выглядит как WHERE id > :last_seen_id ORDER BY id LIMIT :size. База использует индекс, стоимость запроса постоянна независимо от «номера страницы».

Цена: нельзя прыгнуть на произвольную страницу, нужен стабильный порядок сортировки, а клиенту приходится хранить курсор.

Фильтрация: где обычно ломается

Динамические фильтры без валидации

Частая картина: эндпоинт принимает произвольные query-параметры и строит WHERE на лету. Это путь к SQL-инъекциям (если параметры попадают в сырой SQL) и к full table scan (если фильтруемое поле не покрыто индексом).

Правильный подход — явно объявить допустимые фильтры через Pydantic-модель с Query-параметрами. FastAPI провалидирует типы и границы за вас, а SQLAlchemy построит безопасный запрос.

# FastAPI 0.100+, SQLAlchemy 2.x, Pydantic v2
from fastapi import APIRouter, Depends, Query
from pydantic import BaseModel, Field
from sqlalchemy import select
from sqlalchemy.ext.asyncio import AsyncSession
from typing import Optional

router = APIRouter()


class OrderFilters(BaseModel):
    status: Optional[str] = Field(None, pattern="^(new|paid|shipped|cancelled)$")
    min_total: Optional[int] = Field(None, ge=0)
    max_total: Optional[int] = Field(None, ge=0)
    cursor: Optional[int] = Field(None, ge=1, description="ID последнего элемента предыдущей страницы")
    page_size: int = Field(20, ge=1, le=100)


async def get_filters(
    status: Optional[str] = Query(None, pattern="^(new|paid|shipped|cancelled)$"),
    min_total: Optional[int] = Query(None, ge=0),
    max_total: Optional[int] = Query(None, ge=0),
    cursor: Optional[int] = Query(None, ge=1),
    page_size: int = Query(20, ge=1, le=100),
) -> OrderFilters:
    return OrderFilters(
        status=status,
        min_total=min_total,
        max_total=max_total,
        cursor=cursor,
        page_size=page_size,
    )


@router.get("/orders")
async def list_orders(
    filters: OrderFilters = Depends(get_filters),
    db: AsyncSession = Depends(get_async_session),  # ваша фабрика сессий
):
    stmt = select(Order).order_by(Order.id)

    if filters.status:
        stmt = stmt.where(Order.status == filters.status)
    if filters.min_total is not None:
        stmt = stmt.where(Order.total >= filters.min_total)
    if filters.max_total is not None:
        stmt = stmt.where(Order.total <= filters.max_total)

    # Cursor-пагинация
    if filters.cursor:
        stmt = stmt.where(Order.id > filters.cursor)

    stmt = stmt.limit(filters.page_size + 1)  # +1, чтобы узнать, есть ли следующая страница

    result = await db.execute(stmt)
    rows = result.scalars().all()

    has_next = len(rows) > filters.page_size
    items = rows[: filters.page_size]
    next_cursor = items[-1].id if has_next and items else None

    return {
        "items": items,
        "next_cursor": next_cursor,
    }

Обратите внимание на limit(page_size + 1): это стандартный приём — запрашиваем на одну запись больше, чтобы понять, есть ли следующая страница, не выполняя отдельный COUNT(*).

COUNT(*) на каждом запросе

Клиентам часто нужен total_count для отображения «страница 3 из 128». На больших таблицах SELECT COUNT(*) FROM orders WHERE … — тяжёлый запрос, особенно в PostgreSQL, где нет кеша точного количества строк. Варианты:

• Отказаться от точного числа: отдавать только has_next — достаточно для бесконечного скролла и мобильных приложений.
• Кешировать count с коротким TTL (5–30 секунд) и пересчитывать в фоне.
• Использовать приблизительный подсчёт через pg_class.reltuples — точность до сотен на таблицах в миллионы строк, зато мгновенно.

Если ваш эндпоинт выполняет два запроса к базе — один за данными, другой за COUNT — и оба ходят по одним и тем же фильтрам, вы платите за одну страницу дважды. На глубокой пагинации это умножает проблему, а не решает.

Чек-лист перед выкатыванием в прод

1. Ограничен максимальный page_size. Без верхней границы один запрос с page_size=1000000 убьёт и базу, и сериализацию. Разумный потолок — 100, для тяжёлых сущностей — 50.
2. Есть валидация всех фильтров. Каждый query-параметр объявлен явно, с типом и ограничениями. Никаких **kwargs в построителе запросов.
3. Для каждого фильтруемого поля есть индекс или составной индекс, покрывающий типичную комбинацию фильтров. Проверяйте через EXPLAIN ANALYZE, а не на глаз.
4. Cursor-пагинация используется для списков, которые растут (логи, заказы, ленты). Offset оставлен только для статичных справочников и админок.
5. Порядок сортировки стабилен. Если сортируете по created_at, добавьте id вторым ключом — иначе записи с одинаковым timestamp будут прыгать между страницами.
6. COUNT(*) не выполняется на каждый запрос или кешируется. Проверьте, нужен ли total_count клиенту вообще.
7. Ответ содержит метаданные пагинации. Минимум: next_cursor (или next_page) и has_next. Клиент не должен гадать, есть ли ещё данные.
8. Пустая выдача — 200, не 404. Пустой список фильтрации — корректный результат. 404 — это «ресурс не существует», а не «ничего не нашлось».
9. Таймаут на уровне запроса к базе. SQLAlchemy позволяет задать execution_options({"timeout": 5000}) — точное имя параметра зависит от драйвера, для asyncpg это command_timeout в параметрах подключения. Без таймаута один тяжёлый запрос займёт коннект пула навсегда.
10. Нагрузочный тест на глубокую страницу. Проверьте не только первую страницу, но и запрос с offset=500000 или курсором в середине таблицы. Если используете offset — знайте его реальную стоимость.

Составной индекс под фильтры: на что смотреть

Допустим, 80% запросов к /orders приходят с фильтром status и сортировкой по id. Один составной индекс (status, id) покроет и фильтрацию, и курсорную пагинацию. Добавлять отдельный индекс на каждое поле — не выход: оптимизатор PostgreSQL использует Bitmap Index Scan для объединения, но это медленнее одного точного индекса.

Правило: индекс строится под запрос, а не под таблицу. Посмотрите в логах, какие комбинации фильтров реально прилетают, и стройте индексы под топ-3 паттерна. Остальные запросы переживут Seq Scan, если они редкие.

Keyset-пагинация (курсорная пагинация) — способ постраничной навигации, при котором следующая порция данных запрашивается не смещением от начала, а условием «больше, чем последний элемент предыдущей страницы». Работает за счёт индекса по полю сортировки и не деградирует на глубоких страницах.

Где подход ломается

• Сортировка по вычисляемому полю. Если клиент хочет сортировать по relevance, а relevance считается на лету — курсорная пагинация не ложится: нет стабильного ключа. Выход — материализовать score и индексировать его, либо перенести ранжирование на сторону поискового движка.
• Мульти-колоночный курсор. Сортировка по (created_at, id) требует курсора из двух значений и условия (created_at, id) > (:last_ts, :last_id). SQLAlchemy поддерживает такой синтаксис через tuple_, но клиенту нужно передавать оба значения, что усложняет API.
• Фильтры с OR. Запрос WHERE status = 'new' OR total > 10000 не ляжет на один составной индекс. PostgreSQL разложит на два Bitmap Scan, но если таблица большая и оба условия дают широкую выборку — будет медленно. Пересмотрите, действительно ли OR нужен, или это два разных списка.

Что делать дальше

После закрытия чек-листа следующий шаг — мониторинг. Добавьте метрику времени ответа эндпоинта с разбивкой по глубине пагинации (значение курсора или offset). Это покажет деградацию до того, как её заметят пользователи. Если пагинация cursor-based, а у вас нет механизма инвалидации — это отдельная тема, но начните хотя бы с TTL на стороне клиента и заголовка Cache-Control в ответе.