К содержимому
Войти

Python Road Map

🐘 Postgres из Python: SQLAlchemy, asyncpg, Alembic

Между кодом на Python и Postgres обычно три слоя: драйвер (psycopg 3 или asyncpg), пул соединений и SQLAlchemy поверх них, а схему меняет Alembic. Почти все проблемы с базой в Python-сервисе живут на стыках этих слоёв: пул кончился под нагрузкой, ленивая загрузка упала в async, миграция заблокировала таблицу на проде. Отсюда и три вопроса техсекции: как работает сессия, почему падает MissingGreenlet, как выкатить миграцию без простоя

🧠 Как это устроено

Драйвер: psycopg 3 или asyncpg

Драйвер говорит с Postgres по его сетевому протоколу. SQLAlchemy сам SQL не отправляет, он отдаёт драйверу текст запроса и параметры

psycopg 3asyncpg
Режимыsync и async в одном пакететолько async
ИнтерфейсDB-API, как у psycopg2свой API, SQLAlchemy надевает на него прослойку DB-API
URL в SQLAlchemypostgresql+psycopg://postgresql+asyncpg://
Подготовленные запросыпосле prepare_threshold повторов на соединениикэш на каждом соединении, по умолчанию 100 запросов
С PgBouncer в режиме transactionprepare_threshold=None или PgBouncer 1.22+ с max_prepared_statementsотключить кэш (prepared_statement_cache_size=0), уникальные имена запросов, NullPool

В SQLAlchemy 2.1 psycopg 3 стал драйвером по умолчанию: URL postgresql:// без указания драйвера идёт в psycopg, а не в psycopg2. asyncpg написан под asyncio с нуля и работает с бинарным протоколом Postgres без прослойки DB-API, за это его и выбирают в чисто асинхронных сервисах. psycopg 3 удобнее, когда в проекте есть и синхронный код (скрипты, Celery, Django), и асинхронный: один драйвер, одни типы, один набор ошибок

Пул соединений

Открыть соединение с Postgres дорого: TCP, TLS, аутентификация, а на стороне сервера отдельный процесс на каждое соединение. Поэтому соединения держат открытыми и раздают из пула. В SQLAlchemy это QueuePool для синхронного движка и AsyncAdaptedQueuePool для create_async_engine, с одинаковыми настройками:

  • pool_size=5 — сколько соединений пул держит постоянно
  • max_overflow=10 — сколько ещё можно открыть сверх этого под пиком; после возврата они закрываются
  • pool_timeout=30 — сколько секунд ждать свободное соединение, прежде чем упасть с TimeoutError
  • pool_pre_ping=True — перед выдачей соединения из пула отправить лёгкий запрос вроде SELECT 1 и заменить соединение, если оно умерло

pool_pre_ping спасает после рестарта базы или когда балансировщик тихо рвёт простаивающие соединения. Платишь лишним round-trip на каждую выдачу, а от обрыва посреди транзакции он не защищает

Пул живёт в процессе. Один процесс по умолчанию откроет до 15 соединений. Четыре воркера uvicorn дадут 60, три пода по четыре воркера дадут 180. А max_connections в Postgres по умолчанию обычно 100. Поэтому размер пула считают от обратного: сколько соединений выдержит база, делённое на число всех процессов, которые к ней ходят

Core и ORM

Core — построитель SQL: таблицы, select(), insert(), результат в виде строк. ORM добавляет классы-модели, сессию и связи между объектами. В стиле 2.0 у них общий select(), старый session.query() остался как legacy

Модель в современном стиле описывается через Mapped[...] и mapped_column, и тип в аннотации сразу задаёт тип колонки и NOT NULL:

class User(Base):
    __tablename__ = "users"
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(50))
    bio: Mapped[str | None]                    # nullable
    orders: Mapped[list["Order"]] = relationship(back_populates="user")

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

Session: unit of work и identity map

Session не соединение, а рабочая область. Ты добавляешь и меняешь объекты, сессия запоминает, что изменилось, и в нужный момент отправляет в базу пачку INSERT, UPDATE, DELETE в правильном порядке. Это unit of work

  • flush() отправляет накопленные изменения в базу внутри текущей транзакции. После него у новых объектов появляются id, но другие соединения изменений ещё не видят
  • commit() делает flush() и затем COMMIT
  • autoflush срабатывает сам перед запросом через session.execute(), чтобы запрос увидел твои несохранённые изменения

Identity map — словарь внутри сессии: одна строка таблицы по первичному ключу соответствует ровно одному объекту. Дважды загрузил пользователя с id=1 — получил тот же объект. session.get(User, 1) сначала смотрит в эту карту и, если объект там есть, в базу не ходит

После commit() сессия по умолчанию помечает все объекты устаревшими (expire_on_commit=True), и следующее обращение к атрибуту перечитает его из базы. В синхронном коде это незаметный лишний SELECT. В async это ошибка, и вот почему

Почему lazy loading ломается в async

В синхронном ORM обращение user.orders молча делает запрос в базу. Это lazy loading. В asyncio любой ввод-вывод должен идти через await, а обращение к атрибуту не корутина. SQLAlchemy запускает асинхронный драйвер через greenlet, и вне await session.execute(...) переключиться ему не на чем. Так и появляется ошибка MissingGreenlet с текстом «greenlet_spawn has not been called»

Отсюда два правила для async:

  1. expire_on_commit=False, иначе после commit() упадёт даже user.name
  2. Связи загружать заранее, в том же запросе, через selectinload или joinedload
selectinloadjoinedload
SQLвторой запрос WHERE user_id IN (...)LEFT OUTER JOIN в основном запросе
Запросов2 на связь1
Хорош дляколлекций (один ко многим)одиночных связей (многие к одному)
Подвохна тысячах родителей IN режется пачками по 500, запросов становится большестроки родителя дублируются на каждого ребёнка, для коллекций нужен .unique()

🛠️ На практике

MissingGreenlet и eager loading на aiosqlite

Запуск на SQLAlchemy 2.1.8 и aiosqlite, SQL собран через событие before_cursor_execute. На asyncpg поведение то же

Python · Модели, flush и identity map

engine = create_async_engine("sqlite+aiosqlite:///:memory:")
Session = async_sessionmaker(engine, expire_on_commit=False)

async with Session() as s:
    async with s.begin():                 # COMMIT в конце блока
        u = User(name="ann", orders=[Order(total=100), Order(total=250)])
        s.add(u)
        print("id до flush:", u.id)
        await s.flush()
        print("id после flush:", u.id)
    same = await s.get(User, u.id)        # без запроса в базу
    print("identity map:", same is u)
id до flush: None
id после flush: 1
identity map: True

Теперь новая сессия и обращение к связи без eager loading:

async with Session() as s:
    u = (await s.execute(select(User))).scalar_one()
    u.orders          # ленивая загрузка вне await
SQL: SELECT users.id, users.name FROM users
sqlalchemy.exc.StatementError: (sqlalchemy.exc.MissingGreenlet) greenlet_spawn has not been called; can't call await_() here. Was IO attempted in an unexpected place?

С selectinload(User.orders) и с joinedload(User.orders) тот же код отрабатывает, а SQL разный:

-- selectinload
SELECT users.id, users.name FROM users
SELECT orders.user_id, orders.id, orders.total FROM orders WHERE orders.user_id IN (?)

-- joinedload
SELECT users.id, users.name, orders_1.id AS id_1, orders_1.user_id, orders_1.total
FROM users LEFT OUTER JOIN orders AS orders_1 ON users.id = orders_1.user_id

Без .unique() после joinedload по коллекции SQLAlchemy 2.x падает с ошибкой InvalidRequestError: The unique() method must be invoked on this Result

Тот же код с expire_on_commit=True упал на u.name сразу после commit(), с False вернул ann

Ещё есть await user.awaitable_attrs.orders (миксин AsyncAttrs) и lazy="raise" на связи: тогда случайная ленивая загрузка падает сразу, в том числе в синхронном коде

Сессия на запрос

Движок и фабрику сессий создают один раз на приложение, а сессию открывают на каждый запрос. Одна AsyncSession не годится для нескольких задач одновременно: в asyncio.gather каждой задаче нужна своя

engine = create_async_engine(
    "postgresql+asyncpg://app:secret@db/app",
    pool_size=10, max_overflow=5, pool_pre_ping=True,
)
SessionLocal = async_sessionmaker(engine, expire_on_commit=False)

async def transfer(from_id: int, to_id: int, amount: int) -> None:
    async with SessionLocal() as session, session.begin():
        src = await session.get(Account, from_id, with_for_update=True)
        dst = await session.get(Account, to_id, with_for_update=True)
        src.balance -= amount
        dst.balance += amount
    # тут COMMIT уже прошёл, при исключении был бы ROLLBACK

В FastAPI ту же сессию отдают через зависимость с yield: открыли до обработчика, закрыли после. Как устроены такие зависимости и когда выполняется код после yield, разобрано на странице про FastAPI. Про блокировку строк и уровни изоляции — в модуле про базы данных

Alembic и миграции без простоя

alembic revision --autogenerate -m "..." сравнивает модели с живой схемой и пишет черновик миграции. Это именно черновик:

  • переименование колонки он видит как удаление и добавление. На модели, где name стала full_name, autogenerate выдал add_column full_name и remove_column name. Если так и выкатить, данные пропадут. Правят руками на op.alter_column(..., new_column_name=...)
  • изменение типа колонки видит: compare_type включён по умолчанию с Alembic 1.12
  • изменение серверного дефолта не видит, пока не включить compare_server_default=True в env.py

Миграция без простоя держится на двух вещах. Первая — expand/contract: схема меняется так, чтобы со старой и новой версией кода работали обе версии приложения, пока идёт выкатка. Переименование колонки в три релиза: добавить новую колонку и писать в обе, перенести данные и перевести чтение, удалить старую. Удалять колонку в том же релизе, где код перестал её читать, нельзя: старые поды ещё работают и упадут

Вторая — не держать долгих блокировок. Обычный CREATE INDEX блокирует запись в таблицу на всё время построения. CREATE INDEX CONCURRENTLY не блокирует, но не может выполняться внутри транзакции, а Alembic оборачивает миграцию в транзакцию. Для этого есть autocommit_block():

def upgrade() -> None:
    with op.get_context().autocommit_block():
        op.create_index(
            "ix_orders_user_id", "orders", ["user_id"],
            postgresql_concurrently=True,
        )

В офлайн-режиме этот фрагмент вместе с add_column перед ним даёт такой SQL:

BEGIN;
ALTER TABLE orders ADD COLUMN status VARCHAR(20);
COMMIT;
CREATE INDEX CONCURRENTLY ix_orders_user_id ON orders (user_id);
BEGIN;
COMMIT;

Видно, что транзакция перед блоком коммитится принудительно. Поэтому индекс выносят в отдельную миграцию, а в env.py включают transaction_per_migration=True. Если CONCURRENTLY упал на середине, в базе остаётся невалидный индекс: его удаляют и создают заново

⚠️ Где ошибаются

Пул без учёта числа процессов. Подняли pool_size=50, чтобы не ждать соединений, и раскатили на 8 подов по 4 воркера. Под пиком сервис открывает больше соединений, чем позволяет max_connections, и база отказывает новым клиентам, включая соседние сервисы. Маленький пул бьёт с другой стороны: запросы ждут соединение 30 секунд и падают с TimeoutError, хотя база простаивает

Ленивая загрузка в цикле. В синхронном коде for u in users: u.orders молча делает N+1 запросов, в async падает. Лечится одинаково: selectinload в запросе

Одна сессия на всё приложение или на несколько задач. Глобальная сессия копит объекты в identity map, отдаёт устаревшие данные и делит транзакцию между запросами, а параллельные задачи на одной AsyncSession ломают её состояние. Одна сессия — один запрос или одна задача

Долгая транзакция вокруг внешних вызовов. session.begin(), потом HTTP-запрос в платёжный сервис на 5 секунд, потом commit. Всё это время соединение занято, а строки заблокированы. Внешний вызов делают до или после транзакции

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

🎯 Вопросы с собеседований

Как работает сессия SQLAlchemy: unit of work и identity map

Сессия отслеживает новые, изменённые и удалённые объекты и при flush() отправляет их в базу пачкой в правильном порядке, это unit of work. commit() делает flush() и COMMIT, а autoflush срабатывает сам перед запросами. После flush() у новых строк уже есть id, но другие соединения их не видят до коммита. Identity map гарантирует, что одной строке по первичному ключу соответствует один объект в сессии, поэтому session.get() для уже загруженного объекта в базу не ходит. После коммита объекты по умолчанию устаревают и перечитываются при следующем обращении

Почему lazy loading ломается в async и что такое MissingGreenlet

Ленивая загрузка делает запрос при обращении к атрибуту, а обращение к атрибуту нельзя await. SQLAlchemy гоняет асинхронный драйвер через greenlet, и вне await session.execute() ему не на что переключиться, отсюда MissingGreenlet. Связи загружают заранее через selectinload или joinedload и ставят expire_on_commit=False, иначе после commit() упадёт даже чтение простого поля

selectinload или joinedload

selectinload делает второй запрос WHERE parent_id IN (...) и хорош для коллекций: строки родителя не дублируются. joinedload добавляет LEFT OUTER JOIN в основной запрос и хорош для связей многие к одному, где на строку один связанный объект. С коллекциями joinedload раздувает результат в число детей и требует .unique(), а с LIMIT на родителях даёт неочевидные подзапросы

Зачем пул соединений и что будет, если он мал или велик

Соединение с Postgres дорого открывать, а на сервере за каждым стоит отдельный процесс. Пул держит готовые соединения, в SQLAlchemy по умолчанию 5 плюс 10 сверх под пиком. Маленький пул — запросы стоят в очереди за соединением и падают по pool_timeout, хотя база свободна. Большой — база упирается в max_connections (по умолчанию обычно 100) и тратит память на простаивающие процессы. Пул у каждого процесса свой, поэтому итог считают как размер пула на число воркеров и подов, а при сотнях процессов ставят PgBouncer

Core или ORM: когда что

ORM — для бизнес-логики: загрузил агрегат, поменял, сохранил, связи и транзакцию ведёт сессия. Core — для массовых операций и отчётов, где объекты не нужны и важен точный SQL: insert().values([...]) на 100 тысяч строк, UPDATE ... WHERE без загрузки, агрегаты. В 2.x граница мягкая: Core-запрос выполняется и через session.execute()

asyncpg или psycopg 3

asyncpg только асинхронный, со своим API и бинарным протоколом, без прослойки DB-API. psycopg 3 — наследник psycopg2 с DB-API, умеет sync и async, и в SQLAlchemy 2.1 стал драйвером по умолчанию для postgresql://. Для чистого async-сервиса подходят оба, а если рядом синхронные скрипты или воркеры, psycopg 3 закрывает всё одним драйвером. За PgBouncer в режиме transaction у обоих надо настроить подготовленные запросы

Как выкатить миграцию без простоя

Через expand/contract: схема меняется так, чтобы старая и новая версия кода работали с ней одновременно. Новую колонку добавляют nullable или с дефолтом, данные переносят отдельно пачками, старую колонку удаляют только в следующем релизе, когда её никто не читает. Индексы строят через CREATE INDEX CONCURRENTLY, в Alembic это postgresql_concurrently=True внутри autocommit_block(). Autogenerate вычитывают руками: переименование он сгенерирует как удаление колонки с данными

📚 Что почитать