🐘 Postgres из Python: SQLAlchemy, asyncpg, Alembic
Между кодом на Python и Postgres обычно три слоя: драйвер (psycopg 3 или asyncpg), пул соединений и SQLAlchemy поверх них, а схему меняет Alembic. Почти все проблемы с базой в Python-сервисе живут на стыках этих слоёв: пул кончился под нагрузкой, ленивая загрузка упала в async, миграция заблокировала таблицу на проде. Отсюда и три вопроса техсекции: как работает сессия, почему падает MissingGreenlet, как выкатить миграцию без простоя
🧠 Как это устроено
Драйвер: psycopg 3 или asyncpg
Драйвер говорит с Postgres по его сетевому протоколу. SQLAlchemy сам SQL не отправляет, он отдаёт драйверу текст запроса и параметры
| psycopg 3 | asyncpg | |
|---|---|---|
| Режимы | sync и async в одном пакете | только async |
| Интерфейс | DB-API, как у psycopg2 | свой API, SQLAlchemy надевает на него прослойку DB-API |
| URL в SQLAlchemy | postgresql+psycopg:// | postgresql+asyncpg:// |
| Подготовленные запросы | после prepare_threshold повторов на соединении | кэш на каждом соединении, по умолчанию 100 запросов |
| С PgBouncer в режиме transaction | prepare_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— сколько секунд ждать свободное соединение, прежде чем упасть сTimeoutErrorpool_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:
expire_on_commit=False, иначе послеcommit()упадёт дажеuser.name- Связи загружать заранее, в том же запросе, через
selectinloadилиjoinedload
selectinload | joinedload | |
|---|---|---|
| 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 # ленивая загрузка вне awaitSQL: 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 вычитывают руками: переименование он сгенерирует как удаление колонки с данными
📚 Что почитать
- Connection Pooling — настройки пула,
pool_pre_pingи что делать после форка процесса - Asynchronous I/O (asyncio) —
AsyncSession,expire_on_commitи способы избежать ленивой загрузки - Alembic: Auto Generating Migrations — что autogenerate видит, а что нет, и настройки сравнения
- Psycopg 3: Prepared statements — как драйвер готовит запросы и что с этим делать за PgBouncer