Предзагрузил пачкой — получил N² запросов. Как expire_on_commit превращает оптимизацию в квадрат

Бывают ошибки, которые не видит ни ревью, ни тесты. Код отрабатывает правильно, прогон зелёный, а запросов к базе он делает в сто раз больше прежнего. Я убрал из фонового планировщика классический N+1, причём самым учебным способом, и чуть не выкатил в прод версию, где нагрузка росла как квадрат числа пользователей.

Самое интересное вскрылось потом. Виновата была не та строка, на которую я думал, и объяснение, которое я себе придумал, оказалось неверным.

Контекст

Я делаю GymDesk — приложение для персональных фитнес-тренеров: расписание, журнал тренировок, напоминания перед занятием. Внутри FastAPI, SQLAlchemy 2.0, PostgreSQL, бот на aiogram. Крутится на скромной виртуалке в пол-ядра и гигабайт памяти.

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

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

Как было и что я сделал

Упрощённо:

users = db.query(User).filter(...).all()for u in users:    slots = db.query(Slot).filter(Slot.user_id == u.id, ...).all()   # запрос на каждого    for s in slots:        if pora(s, u):            send(u, s)            s.notified = True            db.commit()

Лечится по учебнику: поднять всё одной пачкой и разложить в словарь.

by_user = {}for chunk in _chunks([u.id for u in users]):    for s in db.query(Slot).filter(Slot.user_id.in_(chunk), ...).all():        by_user.setdefault(s.user_id, []).append(s)

_chunks режет IN (...) кусками по 500, потому что у PostgreSQL есть предел числа параметров, и упереться в него на большой базе проще, чем кажется.

Написал, прогнал тесты — зелено. Поведение не изменилось: уходят те же уведомления, в базе встают те же флаги.

Замер

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

from sqlalchemy import event@event.listens_for(engine, "before_cursor_execute")def _count(conn, cursor, statement, params, context, executemany):    global _sql    _sql += 1

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

Тесты при этом были зелёные, и они не врали. Уведомления уходили ровно те же самые.

Почему: expire_on_commit

Первая зацепка нашлась в строке, которую я до этого читал десяток раз:

SessionLocal = sessionmaker(bind=engine, autoflush=False, autocommit=False)

Здесь нет expire_on_commit=False, а значение по умолчанию — True. Это значит: после каждого db.commit() все объекты сессии помечаются протухшими. Не портятся и не исчезают, просто SQLAlchemy считает, что данные могли устареть, и при следующем обращении к любому полю честно идёт за свежими. Отдельным SELECT на объект.

А в моём цикле commit есть: я отмечаю отправленное.

Казалось бы, объяснение найдено: предзагрузил пачку, первый же коммит её обнулил, дальше всё читается заново. Логично. И — неверно.

Что показал эксперимент

Я собрал отдельный маленький бенчмарк, чтобы проверить объяснение, а не поверить ему. Он ничего не знает про мой проект, работает на SQLite в памяти и запускается одним файлом:

from sqlalchemy import Integer, String, Boolean, create_engine, event, select, updatefrom sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, sessionmakerclass Base(DeclarativeBase):    passclass User(Base):    __tablename__ = "users"    id: Mapped[int] = mapped_column(Integer, primary_key=True)    name: Mapped[str] = mapped_column(String(50))    tz: Mapped[str] = mapped_column(String(50))    lead: Mapped[int] = mapped_column(Integer)    chat_id: Mapped[int] = mapped_column(Integer)    notified: Mapped[bool] = mapped_column(Boolean, default=False)engine = create_engine("sqlite://")Session = sessionmaker(bind=engine)      # expire_on_commit не указан -> True_sql = 0@event.listens_for(engine, "before_cursor_execute")def _count(conn, cursor, statement, params, context, executemany):    global _sql    _sql += 1

И четыре варианта одного и того же цикла «прочитать поля → отправить → отметить → закоммитить»:

def naive_per_item(db, ids):    """Классический N+1."""    for uid in ids:        u = db.get(User, uid)        send(u.chat_id, u.name, u.tz, u.lead)        u.notified = True        db.commit()def batch_orm(db, ids):    """Пачкой, держим ORM-объекты."""    users = list(db.scalars(select(User).where(User.id.in_(ids))))    for u in users:        send(u.chat_id, u.name, u.tz, u.lead)        u.notified = True        db.commit()def batch_orm_rescan(db, ids):    """То же самое плюс ОДНА строка: обращение ко всей пачке внутри цикла."""    users = list(db.scalars(select(User).where(User.id.in_(ids))))    for u in users:        send(u.chat_id, u.name, u.tz, u.lead)        u.notified = True        db.commit()        _ = [x.id for x in users]        # выглядит как перебор спискаdef batch_tuples(db, ids):    """Пачкой + значения сняты в кортежи ДО цикла."""    users = list(db.scalars(select(User).where(User.id.in_(ids))))    plan = [(u, u.chat_id, u.name, u.tz, u.lead) for u in users]    for u, chat_id, name, tz, lead in plan:        send(chat_id, name, tz, lead)        u.notified = True        db.commit()def batch_bulk_mark(db, ids):    """Кортежи + отметка одним UPDATE вместо коммита на каждого."""    rows = db.execute(select(User.id, User.chat_id, User.name, User.tz, User.lead)                      .where(User.id.in_(ids))).all()    done = []    for uid, chat_id, name, tz, lead in rows:        send(chat_id, name, tz, lead)        done.append(uid)    db.execute(update(User).where(User.id.in_(done)).values(notified=True))    db.commit()

Результат:

Тут меня ждали два сюрприза, и оба оказались важнее моего красивого объяснения.

Первый: сама по себе предзагрузка пачкой ничего не ломает. batch_orm даёт ровно столько же запросов, сколько наивный N+1, то есть 2N. Протухший объект перечитывается одним SELECT целиком, а не по запросу на каждое поле. За db.get() в наивной версии и за неявный рефреш в пачечной вы платите одинаково. Оптимизация просто не работает, но и хуже от неё не становится.

Значит версия «коммит обнулил пачку, поэтому всё подорожало» с замером не сходится. Красиво звучало, а объясняло не то.

Второй сюрприз: всю катастрофу устраивает одна строка, где происходит обращение ко ВСЕЙ пачке после коммита. В бенчмарке это [x.id for x in users]. В боевом коде это был _chunks([u.id for u in users]), спрятанный внутри вспомогательной функции. Глазами читается как перебор списка, операция вообще без базы. А на деле после каждого коммита протухли все N объектов, поэтому перебор стоит N запросов, и цикл из N итераций делает N × N обращений.

Формула ровно такая: N² + N + 1. Отсюда и мои сорок тысяч: двести пользователей дают 200² = 40 000.

Поэтому на маленьких данных проблему и не видно. Десять записей — сотня запросов, ничего подозрительного. Тысяча записей — миллион.

Лечение

Правило, к которому я пришёл: если в цикле есть commit, не держи в нём ORM-объекты ради чтения.

Всё нужное для решения снимается в обычные значения (кортежи, словари чисел) ещё до цикла. Кортежу протухать нечем, коммитов он не замечает:

plan = [(u, u.id, u.tz, u.lead, u.chat_id) for u in users]uids = [u.id for u in users]     # тоже заранее: перебор ПОСЛЕ коммита стоил бы N запросов

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

Второе касается отметок. Вместо коммита на каждой итерации:

db.execute(update(Slot).where(Slot.id.in_(done)).values(notified=True))db.commit()

Один UPDATE ... WHERE id IN (...) вместо сотен мелких коммитов. В бенчмарке это даёт две строки вместо двух тысяч. И дело не только в запросах: сотни коммитов в минуту это ещё и сотни дисковых синхронизаций.

В боевом коде после переделки тихий тик планировщика (а это 99% минут в сутках) укладывается в единицы запросов вместо тысяч.

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

Что я из этого вынес

Юнит-тесты такое не ловят в принципе. Поведение-то верное: отправилось нужное, флаги встали, тест абсолютно прав. Деградирует только число запросов, а его никто не утверждает. Если хотите такое ловить, нужен счётчик на before_cursor_execute и замер на данных, сравнимых с боевыми. У меня после этого случая замер лежит рядом с обычными тестами.

Но главный вывод не про тесты, а про то, как легко удовлетвориться собственной версией. У меня было объяснение, которое звучало убедительно, ложилось на документацию и сходилось с симптомом. Оно было неверным. Причём если бы я не полез собирать бенчмарк, я бы всё равно починил код правильно, потому что кортежи спасают в любом случае, и унёс бы с собой сломанную модель происходящего. Она бы дождалась своего часа где-нибудь, где чинить дороже.

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