← назад к разделу

Когда сервис на SQLAlchemy тормозит, виновата почти никогда не сама библиотека. Виноваты лишние запросы, объекты там, где нужны строки, и пул, который кончился. Разберём, где уходит время, в порядке убывания частоты, и как каждое место измерить, прежде чем чинить.

Обязательно

Сначала измерить

Три числа, которые отвечают на большинство вопросов: сколько запросов делает один HTTP-запрос, сколько длится каждый, сколько соединений занято в пуле.

Число запросов и их длительность снимают событиями движка:

import time
from sqlalchemy import event

@event.listens_for(engine, "before_cursor_execute")
def start_timer(conn, cursor, statement, parameters, context, executemany):
    conn.info.setdefault("query_start", []).append(time.perf_counter())

@event.listens_for(engine, "after_cursor_execute")
def record(conn, cursor, statement, parameters, context, executemany):
    elapsed = time.perf_counter() - conn.info["query_start"].pop()
    DB_QUERY_SECONDS.observe(elapsed)
    if elapsed > 0.2:
        log.warning("slow query", duration=elapsed, statement=statement[:200])

Состояние пула даёт engine.pool.status() строкой и engine.pool.checkedout() числом занятых соединений; второе выводят в метрику. Как собирать метрики и куда их отдавать, рассказывает статья про метрики на Python. Локально самый быстрый инструмент это echo=True и подсчёт строк SELECT в консоли на одном обработчике.

Со стороны базы тот же вопрос задают pg_stat_statements: какие запросы суммарно едят время. Если там на первом месте простой запрос с миллионом вызовов, это N+1 из приложения; если тяжёлый запрос с сотней вызовов, это план, и его смотрят через EXPLAIN, как в статье про планы запросов.

Лишние запросы

Первая причина медленного сервиса на ORM это N+1, и ей посвящена отдельная статья. Вторая, менее заметная: лишние запросы после commit при expire_on_commit=True, когда обработчик читает поля сохранённого объекта для ответа. Третья: session.refresh там, где достаточно returning. Все три видны по счётчику запросов на обработчик, и норма для команды это два-четыре запроса, для списка два-три независимо от размера.

Объекты там, где нужны строки

Загрузить тысячу объектов Order, чтобы посчитать сумму, дорого не из-за SQL, а из-за Python: на каждый объект ORM создаёт экземпляр, заполняет атрибуты, регистрирует в identity map и следит за изменениями. Для чтения без изменений это работа впустую.

Отчёты, экспорт и списки для API, где из объекта берут три поля, читают колонками: select(Order.id, Order.total, Order.status) возвращает лёгкие строки, которые сразу превращаются в DTO. Разница на тысячах строк кратная. Правило: объекты модели нужны там, где их будут менять; где только читают, достаточно строк.

Если объекты всё же нужны, но изменений не будет, select(...).execution_options(populate_existing=False) ничего не ускорит, а вот отказ от лишних связей и отложенные колонки ускорят.

Отложенные колонки

Тяжёлая колонка (текст описания, JSONB с историей, бинарный документ) в каждой строке списка это трафик и память впустую. deferred=True в mapped_column исключает колонку из загрузки по умолчанию, и она читается отдельным запросом при первом обращении; undefer() в запросе возвращает её туда, где она нужна:

class Product(Base):
    description: Mapped[str] = mapped_column(Text, deferred=True)

products = session.scalars(select(Product).limit(50)).all()                       # без description
product = session.scalar(select(Product).options(undefer(Product.description)).where(Product.id == pid))

Обратная операция defer() в запросе откладывает обычную колонку разово. Отложенная колонка в цикле это тот же N+1, поэтому в списке к ней не обращаются либо делают undefer сразу.

Пакетные операции

Вставить десять тысяч строк через session.add в цикле медленнее пакетной вставки не на порядки, как было в старых версиях, но в разы, и главное, создаёт десять тысяч объектов в сессии. Для загрузок, импорта и генерации данных берут пакетные формы:

session.execute(insert(Order), rows)                                   # один INSERT со многими VALUES
session.execute(update(Order), [{"id": 1, "status": "PAID"}, {"id": 2, "status": "PAID"}])   # UPDATE по ключу
session.execute(update(Order).where(Order.status == "NEW").values(status="EXPIRED"))        # один UPDATE для всех

С SQLAlchemy 2.0 вставка списка словарей компилируется в один INSERT ... VALUES (...), (...), ... пакетами (режим insertmanyvalues), и returning() работает даже для такого пакета, возвращая сгенерированные ключи в порядке вставки. На пяти тысячах строк на локальной базе это около трёхкратной разницы с session.add; на удалённой базе разница больше, потому что меньше обходов по сети.

Для сотен тысяч строк и дальше даже это медленно: берут COPY драйвера (cursor.copy у psycopg, copy_records_to_table у asyncpg) мимо SQLAlchemy. Это честный случай «обойти ORM».

Потоковое чтение

Выгрузить миллион строк через .all() значит собрать миллион объектов в памяти. yield_per заставляет драйвер отдавать строки пачками, а сессию не копить объекты:

for order in session.scalars(select(Order).execution_options(yield_per=1000)):
    export(order)

Ограничения: с yield_per не работает joinedload коллекций (строки одной коллекции могут разъехаться по пачкам), а selectinload работает по пачкам. На стороне PostgreSQL для этого нужен серверный курсор, и SQLAlchemy включает его сама через stream_results. В async то же самое делает await session.stream(stmt) с асинхронной итерацией.

Пул

Пул по умолчанию держит пять соединений и открывает до десяти сверх под пиком; сверх пятнадцати запрос ждёт pool_timeout 30 секунд и падает с TimeoutError. Под нагрузкой это выглядит как внезапное зависание всего сервиса: обработчики ждут соединений, а соединения держат долгие транзакции.

Три настройки, которые стоит задать осознанно. pool_size и max_overflow считают от max_connections базы, делённого на число подов, а не от желаемого. pool_pre_ping=True проверяет соединение перед выдачей и спасает от «соединение закрыто сервером» после перезапуска базы или обрыва через балансировщик. pool_recycle=1800 переоткрывает соединения старше получаса, что нужно за балансировщиками, которые режут простаивающие TCP-сессии.

Главный способ не исчерпать пул не в настройках, а в коротких транзакциях: соединение занято от первого запроса до commit, и внешний HTTP внутри транзакции держит его на всё время ожидания. Об этом статьи про транзакции и про пул соединений PostgreSQL.

Дополнительно: при первом чтении можно пропустить

Глубже: кеш запросов и стоимость сборки selectрасширенное

У SQLAlchemy есть кеш скомпилированных выражений: один и тот же select() второй раз не превращается в строку SQL заново, а берётся из кеша по структуре выражения. Он включён по умолчанию (query_cache_size=500 у движка), и в логах с echo это видно по пометкам [cached since ...] и [generated in ...] рядом с запросом. Если почти все запросы помечены как generated, кеш не работает: обычно потому, что в выражение попадают разные литералы вместо параметров (например, in_ со списком разной длины до версии 2.0 или text() с подставленными значениями). Тогда стоимость сборки запроса на Python становится заметной на горячих обработчиках.

Для хранимых фильтров с переменным числом условий кеш всё равно работает, потому что кешируется форма, а значения идут параметрами. А вот literal_binds при компиляции, нужный для отладки, в прод-код не попадает по той же причине.

И последнее про Python-накладные расходы: session.scalars(...).all() на тысячах объектов тратит время на создание экземпляров, и это не оптимизируется настройками. Если профилировщик показывает ORM-загрузку как горячее место, ответ один: читать строки, а не объекты, либо уменьшать выборку. Как профилировать Python-сервис, рассказывает статья про профилирование.

Коротко

  • Сначала измерить: запросов на HTTP-запрос, длительность каждого (события before_cursor_execute и after_cursor_execute), занятых соединений (engine.pool.checkedout()), pg_stat_statements со стороны базы.
  • Лишние запросы: N+1, перечитывание после commit, refresh вместо returning; норма для команды два-четыре запроса, для списка два-три.
  • Где только читают, выбирают колонки, а не объекты: экземпляры ORM стоят Python-времени на каждую строку.
  • Тяжёлые колонки объявляют deferred=True и возвращают undefer() там, где нужны; отложенная колонка в цикле это N+1.
  • Пакетные insert(Model), rows и update(Model), rows вместо циклов; для сотен тысяч строк COPY драйвера мимо ORM.
  • Большие выборки через yield_per или session.stream, без joinedload коллекций.
  • Пул: размер от max_connections базы на все поды, pool_pre_ping=True, pool_recycle за балансировщиком; главное лекарство короткие транзакции.
  • Кеш скомпилированных запросов работает, пока значения идут параметрами; пометки cached и generated в echo показывают, попадает ли запрос в кеш.

Что почитать дальше