SQLAlchemy (Python) + PostgreSQL: от основ до Pro

Подготовка окружения: PostgreSQL, драйверы, движок и сессии SQLAlchemy

В этой статье мы настроим рабочее окружение для Python-приложения, которое обращается к PostgreSQL через SQLAlchemy. Цель — получить воспроизводимую базовую конфигурацию: установленная база, выбранный драйвер, созданный движок (Engine), настроенная фабрика сессий и понятный жизненный цикл сессии.

Что именно мы настраиваем

Чтобы SQLAlchemy мог выполнять запросы в PostgreSQL, нужны четыре слоя:

PostgreSQL-сервер (сама база данных)

Драйвер (DBAPI) для подключения из Python

SQLAlchemy Engine (точка входа, пул соединений, диалект PostgreSQL)

SQLAlchemy Session (единица работы: транзакции, ORM-операции)

!Общая архитектура: как запрос проходит от приложения до PostgreSQL

Установка и запуск PostgreSQL

Нам нужен работающий PostgreSQL и доступ к нему по хосту/порту с логином и паролем.

Вариант A: установка локально

Установите PostgreSQL из официальных сборок: PostgreSQL: Downloads

Убедитесь, что сервер запущен и вы можете подключиться

Проверка наличия клиента и версии:

Вариант B: запуск через Docker

Docker удобен тем, что окружение легко повторить.

Пример запуска контейнера:

Официальный образ: Docker Hub: postgres

Создание базы и пользователя вручную (если нужно)

Если вы не создавали БД при старте Docker или при установке, можно сделать это через psql (под пользователем с правами администратора):

Python-окружение и зависимости

Рекомендуется изолировать зависимости проекта.

Создание виртуального окружения:

Активация:

Установка SQLAlchemy и драйвера.

Какой драйвер выбрать для PostgreSQL

На практике чаще всего используются:

psycopg (psycopg3) — современный драйвер, рекомендуемый для новых проектов

psycopg2 — старый, но широко распространённый драйвер

asyncpg — популярный драйвер для асинхронного стека

Ссылки на проекты:

SQLAlchemy: Installation

psycopg (psycopg3): Documentation

psycopg2: Documentation

asyncpg: Documentation

Установка (синхронный вариант)

Рекомендуемая связка для синхронного кода:

Если по каким-то причинам нужен psycopg2:

> Для продакшена часто предпочитают собирать psycopg2 из исходников вместо psycopg2-binary, но для обучения и локальной разработки -binary обычно удобнее.

URL подключения: как SQLAlchemy понимает, куда подключаться

Подключение задаётся строкой URL (DSN). Общий вид:

dialect+driver://username:password@host:port/database

Для PostgreSQL с psycopg3 чаще всего будет так:

postgresql+psycopg://app:app@localhost:5432/app_db

Где:

postgresql — диалект SQLAlchemy (набор правил генерации SQL для PostgreSQL)

psycopg — драйвер (DBAPI), через который реально открывается соединение

app:app — логин и пароль

localhost:5432 — адрес сервера и порт

app_db — имя базы данных

Движок SQLAlchemy (Engine): точка входа и пул соединений

Engine — основной объект SQLAlchemy для работы с БД на низком уровне. Он:

хранит настройки подключения

управляет пулом соединений

создаёт соединения по требованию

знает, как “разговаривать” с PostgreSQL через диалект

Создание Engine (синхронно)

Ключевые параметры:

echo=True полезен на старте, чтобы видеть, что реально уходит в БД

pool_pre_ping=True снижает шанс получить “битое” соединение из пула

pool_size и max_overflow помогают управлять нагрузкой и количеством одновременных соединений

Быстрая проверка подключения

Здесь важно:

engine.connect() берёт соединение из пула

text() — безопасный способ передать текстовый SQL в Core-слой

контекстный менеджер гарантирует возврат соединения в пул

Сессия SQLAlchemy (Session): единица работы и транзакции

Если Engine — это “инфраструктура”, то Session — это “рабочая область” для операций приложения.

Session в ORM-режиме отвечает за:

открытие/закрытие транзакций

постановку изменений в очередь (unit of work)

commit() (зафиксировать) и rollback() (отменить)

синхронизацию Python-объектов с данными в БД

Важная идея: Engine обычно один на приложение, а Session создаётся на короткое время (например, на один запрос в веб-приложении или на одну бизнес-операцию в скрипте).

!Жизненный цикл Session: где делать commit/rollback/close

Фабрика сессий: sessionmaker

SQLAlchemy рекомендует создавать фабрику сессий, привязанную к Engine.

Что значат параметры:

autocommit=False — сессия работает в транзакционном режиме; вы явно вызываете commit()

autoflush=False — сессия не отправляет изменения в БД “внезапно”; вы контролируете момент flush() (это упрощает понимание на старте)

expire_on_commit=False — после commit() объекты не “протухают” автоматически (часто удобно в прикладном коде)

Контекстный менеджер для сессии (рекомендуемый шаблон)

Сделаем функцию, которая гарантирует commit/rollback/close.

Использование:

Почему это важно:

если произошла ошибка, транзакция откатывается

соединение возвращается в пул

код, который работает с БД, становится предсказуемым

Где хранить строку подключения и секреты

Не храните пароль в коде. Минимальный практичный подход — переменные окружения.

Пример:

И в Python:

Если вы используете файл .env, обычно подключают библиотеку python-dotenv, но важно понимать принцип: приложение получает секреты извне, а не из репозитория.

Частые ошибки на старте и как их быстро диагностировать

Неверный драйвер в URL — проверьте postgresql+psycopg://... и установлен ли psycopg

PostgreSQL не запущен — проверьте, что 5432 доступен и контейнер/служба работает

Неверные креды или нет прав — попробуйте подключиться через psql тем же пользователем

Подключение “зависает” — часто проблема в сети/фаерволе или неверном хосте

Too many connections — приложение создаёт слишком много сессий/соединений и не закрывает их; используйте контекстные менеджеры и пул

Итог

Вы настроили минимально правильное окружение для работы SQLAlchemy с PostgreSQL:

PostgreSQL доступен локально или через Docker

выбран и установлен драйвер (обычно psycopg)

создан Engine как общий объект приложения

настроена фабрика SessionLocal и безопасный шаблон работы через session_scope()

В следующих материалах на этой базе мы перейдём к моделям ORM, таблицам, запросам и управлению схемой.

Основы ORM: модели, типы столбцов, связи и CRUD-операции

В предыдущей статье вы настроили PostgreSQL, драйвер, создали Engine и шаблон безопасной работы с Session через session_scope(). Теперь перейдём к ORM-части SQLAlchemy: научимся описывать таблицы Python-классами (моделями), задавать типы столбцов и ограничения, связывать таблицы отношениями и выполнять базовые CRUD-операции.

Материал ориентирован на стиль SQLAlchemy 2.0.

Что такое ORM в SQLAlchemy

ORM (Object-Relational Mapping) позволяет работать с таблицами как с Python-объектами:

класс = таблица

атрибут класса = столбец

экземпляр класса = строка

связи между таблицами = свойства с коллекциями/ссылками

Плюсы ORM:

меньше ручного SQL в прикладном коде

явная структура данных в виде моделей

удобные связи и каскадные операции

Минусы, о которых важно помнить:

SQL никуда не исчезает, его полезно понимать и иногда писать напрямую

неправильная настройка связей и загрузок может приводить к лишним запросам

Официальная документация:

SQLAlchemy ORM Quick Start

SQLAlchemy ORM Declarative Mapping

Базовая структура проекта

Ниже мы будем опираться на идею из предыдущей статьи: один Engine на приложение, а Session короткоживущая.

Пример минимального разбиения по файлам:

db.py — engine, SessionLocal, session_scope()

models.py — ORM-модели

main.py — примеры CRUD

Чтобы не распыляться, в примерах ниже код будет показан блоками, но логика остаётся той же.

Декларативная база и первая модель

В SQLAlchemy 2.0 рекомендуется объявлять базовый класс через DeclarativeBase, а поля типизировать через Mapped[...].

Здесь важно:

__tablename__ задаёт имя таблицы.

mapped_column(...) описывает столбец: тип, ограничения, поведение.

primary_key=True делает столбец первичным ключом.

ForeignKey("users.id") объявляет внешний ключ.

relationship(...) описывает связь на уровне ORM.

!Визуальная ER-схема связи один-ко-многим между пользователями и постами

Типы столбцов и ограничения

SQLAlchemy типы столбцов описывает через типы из sqlalchemy (они транслируются в типы PostgreSQL диалектом).

Часто используемые типы

| Тип SQLAlchemy | Когда использовать | Пример | |---|---|---| | Integer | целые числа | mapped_column(Integer) | | String(n) | строки ограниченной длины | mapped_column(String(255)) | | Text | длинный текст | mapped_column(Text) | | Boolean | флаг да/нет | mapped_column(Boolean, default=False) | | DateTime | дата и время | mapped_column(DateTime) | | Numeric(p, s) | деньги/точные числа | mapped_column(Numeric(10, 2)) |

Если вам нужны специфичные для PostgreSQL типы, их можно брать из sqlalchemy.dialects.postgresql.

Пример UUID (часто используют как публичный идентификатор):

Ограничения и индексы

Частые параметры mapped_column(...):

nullable=False — запрет NULL.

unique=True — уникальность.

index=True — создать индекс (ускоряет выборки по столбцу).

default=... — значение по умолчанию на стороне Python.

server_default=... — значение по умолчанию на стороне БД.

Пример с индексом:

Важное различие default и server_default:

default срабатывает, когда вы создаёте объект в Python и делаете flush/commit.

server_default задаётся на уровне PostgreSQL и срабатывает даже если вставка произошла не через ваше приложение.

Связи между моделями

Связи описываются через ForeignKey(...) и relationship(...). Важно понимать: ForeignKey создаёт ограничение на уровне таблиц, а relationship даёт удобную навигацию между объектами.

Один-ко-многим и многие-к-одному

Связь User и Post выше — классический пример:

Post.author_id хранит ссылку на users.id

Post.author даёт доступ к объекту User

User.posts даёт список постов пользователя

Ключевой параметр back_populates связывает два конца отношения и помогает ORM поддерживать согласованность.

Один-к-одному

Технически это почти как один-ко-многим, но вы гарантируете уникальность внешнего ключа.

Пример: профиль пользователя.

Многие-ко-многим

В PostgreSQL связь многие-ко-многим обычно реализуется через промежуточную таблицу.

Пример: Post и Tag.

Документация по связям:

SQLAlchemy ORM Relationship Configuration

Каскады и удаление

Когда вы удаляете родительский объект, часто нужно определить судьбу дочерних.

cascade="all, delete-orphan" означает: операции над родителем применяются к детям, а "осиротевшие" дети удаляются.

Пример:

Это решение должно быть осознанным: иногда посты нельзя удалять вместе с пользователем по бизнес-правилам.

Создание таблиц в базе

Для обучения можно создать таблицы напрямую из метаданных.

Важно:

это удобно для старта и экспериментов

в реальных проектах обычно используют миграции (например, Alembic), чтобы безопасно обновлять схему

CRUD-операции в ORM

Ниже примеры на синхронной сессии, используя шаблон session_scope() из предыдущей статьи.

Предположим, в db.py у вас есть session_scope().

Create: создание объектов

Практические замечания:

session.add(obj) ставит объект в очередь на вставку/обновление.

session.flush() полезен, когда нужно получить id до commit().

commit() фиксирует транзакцию и делает данные видимыми другим транзакциям.

Read: выборка данных

В SQLAlchemy 2.0 рекомендуется использовать select().

Полезные методы результата:

scalar_one() — ожидает ровно одну строку, иначе выбросит исключение.

scalar_one_or_none() — вернёт объект или None, но упадёт, если найдено больше одной строки.

scalars().all() — получить список объектов.

Получение по первичному ключу:

Read со связями: доступ к user.posts

Если вы загрузили пользователя, user.posts может быть загружен лениво (дополнительным запросом при обращении).

Когда важно заранее загрузить связанные объекты, применяют eager loading, например selectinload.

Update: обновление данных

ORM-обновление обычно выглядит так: загрузили объект, изменили поля, закоммитили.

Почему это работает:

Session отслеживает изменения (unit of work)

на commit() будут сгенерированы нужные UPDATE

Если нужно гарантированно обновить поля из БД после коммита, можно сделать session.refresh(user).

Delete: удаление данных

Если у вас настроены каскады, удаление может затронуть связанные объекты. Если каскады не настроены, PostgreSQL может запретить удаление из-за внешних ключей.

Частые ошибки при работе с ORM

Путать flush() и commit():

- flush() отправляет SQL в текущей транзакции, но не фиксирует - commit() фиксирует транзакцию

Держать Session слишком долго:

- сессия должна жить коротко (операция/запрос), иначе копятся соединения и состояние

Забывать rollback() при ошибках:

- используйте шаблон session_scope()

Ставить cascade="all, delete-orphan" без понимания:

- это может удалить данные, которые по бизнес-логике удалять нельзя

Итог

Теперь у вас есть базовый набор навыков ORM в SQLAlchemy:

вы умеете описывать таблицы моделями через декларативный стиль

понимаете назначение типов столбцов и ограничений (nullable, unique, index)

умеете строить связи один-ко-многим, один-к-одному и многие-ко-многим

выполняете CRUD через Session и select()

Дальше (в следующих темах курса) обычно переходят к более продвинутым запросам, транзакционным сценариям, миграциям схемы и оптимизации загрузки связей.

Запросы в SQLAlchemy: select/join, фильтрация, агрегации и подзапросы

В прошлых статьях вы настроили окружение (PostgreSQL, драйвер, Engine, жизненный цикл Session) и описали таблицы через ORM-модели, связи и базовый CRUD. Теперь цель — научиться писать читаемые и предсказуемые запросы в стиле SQLAlchemy 2.0: строить select(), делать join, фильтровать данные, считать агрегаты и применять подзапросы.

Основная идея: SQLAlchemy позволяет собирать запрос как выражение (expression) на Python, а затем выполнить его через Session.

Документация для углубления:

SQLAlchemy 2.0 Tutorial: Selecting Data

SQLAlchemy 2.0 Tutorial: ORM Querying Guide

Минимальная база примеров

Далее будем опираться на модели из урока про ORM (пользователи и посты). Упростим их до нужного минимума.

И также предполагаем, что у вас есть session_scope() из первого урока.

!Связь таблиц и то, как разные типы запросов опираются на неё

Как выполнить select() и как читать результат

В SQLAlchemy 2.0 рекомендуемый стиль выборок — через select().

Что здесь происходит:

select(User) означает: верни строки таблицы users, но маппингом в объекты User.

session.execute(stmt) возвращает объект Result.

scalars() вытаскивает первую колонку из каждой строки результата (в данном случае это и есть объект User).

Часто используемые методы:

scalar_one() — ожидает ровно одну строку, иначе исключение.

scalar_one_or_none() — объект или None, но исключение, если строк больше одной.

scalars().first() — первый объект или None.

scalars().all() — список объектов.

Если вы выбираете несколько колонок, то обычно читают либо кортежами, либо словарями.

Фильтрация: where(), логика AND/OR, проверки NULL

Базовый where()

Выражения вроде User.email == "..." SQLAlchemy превращает в SQL-условия.

Несколько условий: AND

Если вы передаёте несколько аргументов в where(), это логическое AND.

OR: or_()

Для OR используйте or_().

IN, диапазоны, поиск по подстроке

like("A%") — чувствительно к регистру.

ilike("a%") — регистронезависимо (в PostgreSQL это ILIKE).

Проверки NULL

В SQL NULL — не значение, поэтому сравнение через = не работает так, как многие ожидают. В SQLAlchemy используйте:

col.is_(None) для IS NULL

col.is_not(None) для IS NOT NULL

Сортировка и пагинация: order_by(), limit(), offset()

Практические замечания:

limit/offset — простой способ пагинации.

на больших таблицах offset может быть дорогим; позже в курсе обычно переходят к пагинации по курсору (keyset pagination).

JOIN в ORM: как соединять таблицы

В ORM JOIN нужен, когда вы хотите:

фильтровать по полям связанной таблицы

выбирать данные сразу из нескольких таблиц

агрегировать по связям

Простой join() через relationship

Найти посты автора с определённым email.

Что важно:

join(Post.author) использует настройку relationship и сам понимает условие соединения.

в where() мы можем ссылаться на User, потому что он уже присутствует в FROM через JOIN.

Выборка нескольких сущностей

Иногда удобно получить и Post, и User в одном результате.

outerjoin()

LEFT OUTER JOIN нужен, когда вы хотите сохранить строки слева, даже если справа нет соответствий.

Пример: вывести всех пользователей и их посты, включая пользователей без постов.

Важная развилка: JOIN против подгрузки связей

Иногда задача звучит как: загрузить пользователей и потом пройтись по их постам. Здесь есть два разных подхода.

Ленивые связи и проблема N+1

Если связи ленивые (по умолчанию), то такой код может сделать много запросов:

Ситуация, когда выполняется 1 запрос на пользователей и затем N запросов на посты, называется N+1.

Решение: eager loading через selectinload()

Если вы хотите именно объекты + связи, обычно лучше использовать стратегии загрузки.

Идея selectinload:

сначала загружаем пользователей

затем одним дополнительным запросом загружаем все посты для этих пользователей

Документация:

SQLAlchemy ORM: Relationship Loading Techniques

Агрегации: count, sum, avg, group_by, having

Агрегации делают из набора строк сводные значения.

count() и число строк

Сколько всего постов в таблице:

Здесь:

func.count(Post.id) — SQL-функция COUNT(posts.id).

scalar_one() возвращает одно число.

group_by: количество постов по каждому пользователю

Почему в group_by несколько колонок:

в SQL нельзя выбрать обычную колонку вместе с агрегатом, если колонка не участвует в группировке (или не зависит от неё).

having: фильтрация групп

Найти пользователей, у которых хотя бы 3 поста:

Разница where и having:

where фильтрует строки до группировки

having фильтрует группы после группировки

Подзапросы: subquery(), scalar_subquery(), exists()

Подзапрос — это запрос, который используется внутри другого запроса. В SQLAlchemy это тоже выражение.

Подзапрос как таблица: subquery()

Сделаем подзапрос, который считает число постов по пользователю, и затем присоединим его к users.

Что здесь важно:

.subquery() превращает select(...) в объект, который можно использовать в FROM и JOIN.

posts_count_sq.c.posts_count — обращение к колонке подзапроса (через .c).

coalesce(a, 0) заменяет NULL на 0 для пользователей без постов.

Скалярный подзапрос: scalar_subquery()

Скалярный подзапрос возвращает одно значение и может стоять, например, в списке выбираемых колонок.

Пример: для каждого пользователя вывести количество его постов.

Идея:

Post.author_id == User.id делает подзапрос коррелированным: он зависит от текущей строки внешнего запроса.

результат — одно число на каждого пользователя.

EXISTS: проверить наличие связанных строк

Если нужно просто проверить, что у пользователя есть хотя бы один пост, EXISTS часто читается лучше и может быть эффективным.

Здесь exists(...) создаёт SQL-предикат вида EXISTS (SELECT ... ).

Документация:

SQLAlchemy Core: EXISTS

Практические рекомендации по читаемости и отладке

Держите запрос в переменной stmt и собирайте его цепочкой: так проще читать и логировать.

Если запрос получается длинным, выносите части в отдельные выражения (например, подзапросы или общие фильтры).

На этапе обучения включайте echo=True у Engine, чтобы видеть реальный SQL.

При работе со связями различайте:

- join() — способ изменить SQL (соединить таблицы) - selectinload() и другие стратегии — способ загрузить объектные графы без N+1

Итог

Теперь вы умеете строить запросы в SQLAlchemy 2.0 на уровне, достаточном для большинства прикладных задач:

выполнять select() и читать результаты через Result, scalars(), mappings()

фильтровать данные через where(), комбинировать условия AND/OR, корректно работать с NULL

сортировать и делать пагинацию order_by/limit/offset

использовать join/outerjoin и понимать, когда лучше применять eager loading

считать агрегаты через func.*, группировать group_by и фильтровать группы having

применять подзапросы: табличные subquery(), скалярные scalar_subquery() и логический exists()

Дальше по логике курса обычно углубляются в транзакционные сценарии, оптимизацию (планы запросов, индексы), миграции схемы и асинхронный стек.

Транзакции и конкурентный доступ: isolation, блокировки, ошибки и ретраи

В предыдущих статьях вы настроили Engine и жизненный цикл Session, научились описывать модели и писать запросы через select(). Следующий шаг к производственному уровню — понять, что происходит, когда:

несколько запросов объединяются в одну логическую операцию

несколько клиентов одновременно читают и изменяют одни и те же данные

возникают конфликты, блокировки, дедлоки и ошибки сериализации

Эта статья про транзакции в PostgreSQL и то, как правильно управлять ими из SQLAlchemy: выбирать уровень изоляции, использовать блокировки там, где нужно, и внедрять безопасные ретраи.

Транзакция: что это и зачем она нужна

Транзакция — это группа SQL-операций, которые выполняются как единое целое. У транзакции есть два финала:

COMMIT фиксирует изменения

ROLLBACK отменяет изменения

Главное практическое назначение транзакции:

сохранять целостность данных при ошибках

защититься от частичных обновлений

упорядочить конкурентные изменения

В SQLAlchemy ORM транзакцией управляет Session. В шаблоне session_scope() из первой статьи транзакция начинается автоматически при первом обращении к БД и завершается при commit() или rollback().

Ссылки:

SQLAlchemy ORM: Managing Transactions

SQLAlchemy Core: Working with Transactions

Конкурентный доступ в PostgreSQL: MVCC и почему блокировки не всегда очевидны

PostgreSQL использует MVCC (Multi-Version Concurrency Control): читатели обычно не блокируют писателей, а писатели не блокируют читателей. Вместо этого PostgreSQL хранит версии строк и показывает каждой транзакции подходящую версию.

Но это не означает, что блокировок нет:

изменения одной и той же строки двумя транзакциями одновременно приводят к ожиданиям

некоторые операции требуют более сильных блокировок (например, ALTER TABLE)

при определённых шаблонах обновления возможны дедлоки

!Временная шкала показывает, как UPDATE держит блокировку строки до завершения транзакции

Уровни изоляции: что означает isolation level

Уровень изоляции определяет, какие эффекты конкурентной работы вы допускаете. Он отвечает на вопрос: что именно может “увидеть” транзакция, пока другие транзакции тоже работают?

PostgreSQL поддерживает уровни изоляции (важные для практики):

READ COMMITTED (по умолчанию) — каждый SQL-запрос внутри транзакции видит данные, зафиксированные на момент начала этого запроса

REPEATABLE READ — все запросы внутри транзакции видят снимок данных на момент начала транзакции

SERIALIZABLE — самое строгое поведение, как если бы транзакции выполнялись по очереди; возможны ошибки сериализации, которые нужно ретраить

Официально:

PostgreSQL: Transaction Isolation

Как выбирать уровень изоляции

Практическая эвристика:

READ COMMITTED подходит большинству веб-приложений

REPEATABLE READ полезен для длинных отчётов и сложных чтений, где важна согласованность снимка

SERIALIZABLE применяют для критичных финансовых и учётных сценариев, где проще ретраить конфликт, чем вручную выстраивать блокировки

Важно: более строгий уровень изоляции не означает “быстрее и надёжнее”. Обычно это означает больше конфликтов и необходимость ретраев.

Как транзакции выглядят в SQLAlchemy: Session.begin() и вложенные транзакции

В ORM-режиме удобно явно описывать границы транзакции.

Явная транзакция через Session.begin()

Практический смысл:

код внутри блока либо фиксируется целиком, либо откатывается целиком

проще правильно закрывать транзакцию в сложных сценариях

Savepoint: begin_nested()

Savepoint (в SQLAlchemy: begin_nested()) — это “точка отката” внутри транзакции. Полезно, когда часть операции может упасть, но вы хотите продолжить транзакцию, откатив только проблемный фрагмент.

Блокировки в PostgreSQL: когда они появляются и зачем ими управлять

Блокировка — это механизм, который не даёт двум транзакциям сделать несовместимые действия одновременно.

Есть два важных уровня для прикладного кода:

блокировки строк (row-level) при UPDATE и DELETE

явные блокировки строк через SELECT ... FOR UPDATE

Официально:

PostgreSQL: Explicit Locking

Классическая проблема: потерянное обновление

Сценарий “потерянного обновления”:

транзакция T1 читает баланс 100

транзакция T2 читает баланс 100

T1 записывает 90

T2 записывает 80

Если бизнес-логика подразумевала последовательное списание (100→90→70), то итог неверный.

Решения обычно такие:

обновлять через атомарный SQL: balance = balance - 10

использовать блокировку строки: SELECT ... FOR UPDATE

применять оптимистическую конкуренцию (версионирование)

SELECT ... FOR UPDATE в SQLAlchemy

SELECT ... FOR UPDATE берёт блокировку выбранных строк до конца транзакции.

Важные замечания:

блокировка удерживается до COMMIT или ROLLBACK

если другая транзакция пытается обновить ту же строку, она будет ждать

NOWAIT и SKIP LOCKED

Иногда ждать нельзя:

вы обслуживаете очередь задач

вы хотите быстро “забрать” свободную работу

Тогда применяют:

NOWAIT — сразу упасть с ошибкой, если строка заблокирована

SKIP LOCKED — пропустить заблокированные строки и взять следующие

В SQLAlchemy:

Это типичный паттерн для воркеров, которые конкурентно забирают задачи.

Дедлоки: что это и как их предотвращать

Дедлок — это ситуация, когда:

транзакция T1 держит блокировку A и ждёт B

транзакция T2 держит блокировку B и ждёт A

PostgreSQL обнаруживает дедлок и принудительно завершает одну из транзакций ошибкой.

!Иллюстрация показывает цикл ожиданий, из-за которого одна транзакция будет прервана

Практические способы снизить вероятность дедлоков:

брать блокировки всегда в одном порядке (например, по id)

не держать транзакции открытыми дольше необходимого

избегать интерактивного ввода и внешних сетевых вызовов внутри транзакции

Ошибки конкурентности: что ловить и как понимать

В конкурентных сценариях вы чаще всего встречаете:

deadlock detected — PostgreSQL прервал одну транзакцию из-за дедлока

serialization failure — конфликт на уровне сериализации (чаще на SERIALIZABLE, иногда на REPEATABLE READ)

lock not available — вы запросили NOWAIT, но строка занята

PostgreSQL коды ошибок можно сверять по справочнику:

PostgreSQL: Error Codes

В SQLAlchemy исключения обычно приходят как:

sqlalchemy.exc.OperationalError

sqlalchemy.exc.DBAPIError

Внутри есть оригинальная DBAPI-ошибка (e.orig), у которой для PostgreSQL часто доступен SQLSTATE-код.

Ретраи: когда это правильно и как сделать безопасно

Ретрай — повтор выполнения транзакции после ошибки конкурентности.

Когда ретраи уместны:

при serialization failure на SERIALIZABLE

при дедлоках (вторая попытка часто проходит)

при временных сбоях соединения

Когда ретраи опасны:

если операция имеет внешние побочные эффекты (например, списали деньги через внешний API)

если код не идемпотентен

Идемпотентность означает: повтор выполнения не приводит к повторному эффекту. Пример идемпотентного действия: “установить статус в done”, пример неидемпотентного: “прибавить 1 к счётчику” без защиты.

Базовый шаблон ретрая транзакции в SQLAlchemy

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

Ключевые идеи этого шаблона:

каждая попытка создаёт новую Session и новую транзакцию

ретраится только ограниченный набор ошибок

между попытками есть задержка, чтобы снизить повторный конфликт

Как выставить уровень изоляции в SQLAlchemy

На уровне Engine

Если вы уверены, что приложению в целом нужен определённый уровень изоляции:

На уровне конкретного соединения

Иногда уровень изоляции нужен только для одной операции:

Практическая рекомендация: чаще безопаснее менять изоляцию локально для конкретного сценария, чем для всего приложения.

Практический чеклист: как писать код, устойчивый к конкуренции

Держите транзакции короткими.

Не делайте сетевые вызовы и долгие вычисления внутри транзакции.

Для изменения счётчиков и балансов предпочитайте атомарные UPDATE.

Если нужно “прочитал → проверил → записал”, используйте SELECT ... FOR UPDATE.

На SERIALIZABLE закладывайте ретраи как нормальный рабочий режим.

Убедитесь, что повтор транзакции не создаст дубликаты и не повторит внешние эффекты.

Итог

Вы связали SQLAlchemy ORM с реальным поведением PostgreSQL под нагрузкой:

понимаете, что транзакция — основа целостности и конкурентной корректности

различаете уровни изоляции и знаете, когда они нужны

понимаете, где появляются блокировки, и как применять SELECT ... FOR UPDATE, NOWAIT, SKIP LOCKED

знаете, что дедлоки и ошибки сериализации нормальны в конкурентной среде

умеете строить безопасные ретраи транзакций в SQLAlchemy

Дальше логично углубляться в миграции схемы (Alembic), стратегии загрузки и производительность (планы запросов, индексы), а также в асинхронный стек SQLAlchemy.

Alembic и эволюция схемы: миграции, сиды, ревизии и стратегии изменений

В предыдущих материалах курса вы настроили подключение SQLAlchemy к PostgreSQL (через Engine и Session) и научились описывать таблицы ORM-моделями, писать запросы и управлять транзакциями. Но в реальном проекте схема базы данных почти никогда не бывает статичной: добавляются поля, индексы, ограничения, таблицы, меняются связи.

Alembic — официальный инструмент миграций для SQLAlchemy. Он позволяет:

фиксировать изменения схемы в виде версионированных файлов

накатывать изменения на разные окружения (dev/stage/prod) предсказуемо

откатывать изменения (там, где это возможно и уместно)

сопровождать изменения схемы изменениями данных (data migrations)

!Поток работы: от моделей SQLAlchemy к миграциям Alembic и применению изменений в PostgreSQL

Что такое миграция и что такое ревизия

Термины, которые важно различать:

Миграция — изменение состояния схемы БД (например, добавить столбец, создать индекс).

Ревизия (revision) в Alembic — файл в папке versions/, который содержит:

- уникальный идентификатор revision - ссылку на предка down_revision - функции upgrade() (применить) и downgrade() (откатить)

Одна ревизия обычно соответствует одному логическому изменению.

Установка и инициализация Alembic

Установка:

Инициализация в проекте:

Обычно появляется структура:

alembic.ini — конфигурация

alembic/

- env.py — как Alembic подключается к вашему приложению/метаданным - versions/ — папка с ревизиями

Документация:

Alembic Documentation

SQLAlchemy: Alembic (раздел про миграции)

Подключаем Alembic к вашим моделям SQLAlchemy

Чтобы --autogenerate работал корректно, Alembic должен знать, какие таблицы и объекты считаются “целевой” схемой приложения. Это делается через target_metadata в alembic/env.py.

Предположим, у вас есть models.py, где определён Base через DeclarativeBase, как в прошлых уроках.

Минимальная привязка target_metadata

Пример фрагмента alembic/env.py:

Что здесь важно:

target_metadata = Base.metadata — Alembic сравнивает схему БД с тем, что описано в Base.metadata.

DATABASE_URL берётся из окружения (как и в уроке про настройку окружения).

compare_type=True и compare_server_default=True повышают качество автогенерации, но всё равно не отменяют необходимость проверять ревизию вручную.

Первая миграция: создаём “базовую” схему

Есть два распространённых подхода:

Создать “первичную” миграцию вручную (явно описав таблицы через op.create_table).

Создать модели и сделать автогенерацию.

Для учебного проекта обычно удобнее автогенерация.

Создаём ревизию

После этого в alembic/versions/ появится файл ревизии. Внутри будут upgrade() и downgrade().

Применяем миграции

Команда head означает “последняя ревизия в текущей ветке”.

Что Alembic хранит в базе

Alembic создаёт служебную таблицу alembic_version, где хранится текущая ревизия, применённая к базе.

Полезные команды:

alembic current — какая ревизия применена

alembic history — история ревизий

alembic heads — текущие “головы” (важно при ветвлениях)

Автогенерация: как она работает и где ошибается

--autogenerate сравнивает:

фактическое состояние схемы в PostgreSQL

“целевую” схему из target_metadata

И генерирует набор операций Alembic (op.*).

Типичные ограничения автогенерации

Автогенерация может:

не распознать переименование столбца как “rename” и предложить “drop + add”

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

не угадать ваш замысел при изменении ограничений и индексов

Практическое правило:

Автогенерация — это черновик. Ревизию нужно читать и править вручную.

Ревизии, ветвления и merge

В командной разработке часто возникает ситуация:

два разработчика создали ревизии параллельно

образовались две “головы” (две несовместимые конечные точки)

Тогда:

alembic heads покажет несколько head-ревизий

нужно создать merge-ревизию:

Merge-ревизия обычно не меняет схему сама по себе, а лишь “склеивает” ветки истории.

Сиды и миграции данных

Важно различать два сценария.

Сиды (seeds)

Сиды — начальные данные, которые удобно заливать в dev/test окружение:

тестовые пользователи

демо-справочники

данные для локальной разработки

Сиды обычно делают отдельной командой/скриптом (не обязательно через Alembic).

Пример идеи сид-скрипта (использует ваш SessionLocal из ранних уроков):

Миграции данных (data migrations)

Миграции данных — изменения данных, которые должны произойти гарантированно на всех окружениях вместе со схемой. Например:

заполнить новый столбец значениями (backfill)

перенести данные в новую таблицу

создать обязательные записи справочника, без которых приложение не работает

Такие изменения обычно делают прямо в Alembic-ревизиях.

Практическое правило для миграций данных:

в ревизиях лучше использовать SQLAlchemy Core и op.*, а не импортировать ORM-модели приложения (они со временем меняются, и старая миграция может сломаться при запуске на “чистой” базе).

Пример вставки “справочника” через op.bulk_insert:

Стратегии изменений схемы в PostgreSQL: как делать безопасно

В “Pro” сценариях миграция — это не просто “поменять схему”, а сделать это так, чтобы:

приложение продолжало работать

миграция не держала блокировки слишком долго

можно было развернуть изменения поэтапно

Стратегия expand/contract

Один из самых практичных подходов для изменений “без простоя”:

Expand (расширение)

- добавить новые объекты (столбцы/таблицы), не ломая старый код - новые столбцы сначала часто делают NULL-able

Обновить приложение

- начать писать данные и в новые структуры (или читать из новых)

Backfill

- заполнить исторические данные

Contract (сжатие)

- добавить ограничения NOT NULL, убрать старые столбцы/таблицы, удалить старые индексы

#### Пример: добавляем обязательный столбец

Задача: добавить users.created_at и сделать его обязательным.

Практически безопаснее сделать это в несколько шагов:

Миграция 1: добавить столбец created_at nullable и с server_default (чтобы новые записи имели значение)

Backfill: проставить значения для старых строк (если server_default не заполнил автоматически)

Миграция 2: убрать server_default (если он больше не нужен) и сделать nullable=False

Почему так:

попытка сразу сделать NOT NULL без backfill упадёт

попытка “тяжёлого” backfill в одной транзакции может держать блокировки слишком долго

Переименование: почти всегда “создать новое и перенести”

Автогенерация часто интерпретирует переименование как “удалить старое + создать новое”. Если данных много, это опасно.

Безопасная тактика:

добавить новый столбец

написать в приложение поддержку чтения из обоих (на время)

перенести данные (backfill)

переключить приложение на новый столбец

удалить старый

Для реального rename (когда вы уверены) можно использовать op.alter_column(..., new_column_name=...), но это требует дисциплины и понимания совместимости приложений.

Индексы в PostgreSQL и CONCURRENTLY

Создание индекса на большой таблице может блокировать операции записи. В PostgreSQL есть режим CREATE INDEX CONCURRENTLY, который снижает блокировки, но имеет важное ограничение:

такая операция не может выполняться внутри транзакции

В Alembic это делается через autocommit_block().

Пример:

Документация:

Alembic: Autocommit Blocks

PostgreSQL: CREATE INDEX

Ограничения и внешние ключи

С ограничениями (FK, UNIQUE, CHECK) важно помнить:

добавление/валидация ограничения может потребовать проверки существующих данных

на больших таблицах это может быть дорогой операцией

Практика:

планируйте миграции так, чтобы сначала данные стали корректными, а уже потом добавляйте “жёсткие” ограничения

Как писать миграции: хорошие привычки

Держите миграции маленькими и логически цельными: проще ревьюить и откатывать.

Не редактируйте уже применённые миграции в общей истории: создавайте новые ревизии.

Не импортируйте ORM-модели приложения в старые миграции: используйте sa.table, sa.column, op.execute.

Проверяйте автогенерацию вручную.

Помните про транзакции и блокировки: миграция — это тоже нагрузка на PostgreSQL.

Частые проблемы и диагностика

Alembic ничего не видит при автогенерации

- проверьте target_metadata - убедитесь, что models.py импортируется и модели “зарегистрированы” в Base.metadata

Автогенерация пытается удалить/создать вместо rename

- чаще всего нужно править ревизию вручную

Миграции долго выполняются

- ищите: создание индекса без CONCURRENTLY, массовый backfill в одной транзакции, длительные блокировки

Конфликт ревизий (две головы)

- используйте alembic merge

Итог

Вы добавили в курс ключевой “производственный” слой — управление схемой:

понимаете, что такое ревизии Alembic и как они связаны в цепочку

умеете подключать Alembic к Base.metadata и генерировать миграции

различаете сиды (dev/test данные) и миграции данных (обязательные изменения для всех окружений)

знаете практические стратегии изменения схемы, включая expand/contract и особенности PostgreSQL (CONCURRENTLY, блокировки)

На базе этого материала проще строить дальнейшие “pro” практики: миграции без простоя, безопасные backfill, управление индексами и ограничениями под нагрузкой.

PostgreSQL-специфика: JSONB, ARRAY, ENUM, UPSERT, CTE и полнотекстовый поиск

В предыдущих статьях курса вы научились настраивать Engine и Session, описывать модели ORM, строить запросы и управлять транзакциями, а также сопровождать эволюцию схемы через Alembic. Теперь добавим слой PostgreSQL-специфики: типы и возможности, которые делают PostgreSQL особенно удобным для прикладных систем, и посмотрим, как использовать их через SQLAlchemy 2.0.

В этой статье разберём:

JSONB: хранение и поиск по документам

ARRAY: массивы значений и операции над ними

ENUM: ограниченные множества значений и миграции

UPSERT: INSERT ... ON CONFLICT ...

CTE: WITH и рекурсивные запросы

полнотекстовый поиск: tsvector, tsquery, ранжирование и индексы

Ссылки на первоисточники:

PostgreSQL: JSON Types

PostgreSQL: Arrays

PostgreSQL: Enumerated Types

PostgreSQL: INSERT

PostgreSQL: WITH Queries (CTE)

PostgreSQL: Full Text Search

SQLAlchemy: PostgreSQL Dialect

SQLAlchemy: INSERT ... ON CONFLICT

Базовый контекст примеров

Далее примеры будут в стиле SQLAlchemy 2.0 и подразумевают, что у вас уже есть engine, SessionLocal и шаблон управления транзакцией, как в первых уроках.

Условные модели:

User с профилем в JSONB

Post с тегами в ARRAY

статусы в ENUM

В реальном проекте эти конструкции часто смешиваются.

JSONB

JSONB в PostgreSQL хранит JSON-документ в бинарном виде и даёт эффективные операции поиска и индексации. Это удобно для:

динамических атрибутов (поля могут отличаться для разных записей)

интеграций (сырые payload-ы внешних систем)

поисков по вложенным ключам, если схема не полностью фиксирована

!Как JSONB хранится в таблице и какие типовые операции к нему применяют

Маппинг JSONB в SQLAlchemy

Используйте тип JSONB из диалекта PostgreSQL:

Замечания:

default=dict создаёт пустой словарь на стороне Python при создании объекта.

если вы хотите дефолт на стороне БД, используйте server_default и аккуратно задавайте выражение через SQL (часто делают отдельно в миграции).

Фильтрация по ключам и значениям

Типовые задачи:

достать значение по ключу

сравнить значение

проверить, что документ содержит фрагмент

Пример: найти пользователей из города Berlin (достаём текст по ключу city).

Пример: проверить, что JSON содержит фрагмент (оператор PostgreSQL @>).

Практическая рекомендация:

contains(...) хорошо сочетается с GIN-индексом по JSONB.

Индексация JSONB

Чтобы запросы по JSONB не стали узким местом, обычно применяют GIN-индекс.

GIN хорошо подходит для @>, ?, ?|, ?& и ряда других операций.

В SQLAlchemy индекс можно описать так:

Замечания про миграции:

на больших таблицах индекс часто создают в режиме CONCURRENTLY (это обсуждалось в уроке про Alembic).

Когда JSONB не подходит

JSONB удобен, но не всегда лучший выбор:

если по полю часто фильтруются и сортируют, лучше сделать обычную колонку

если нужны строгие ограничения и внешние ключи, лучше нормализовать в таблицы

если схема стабилизировалась, часто выгодно вынести важные поля из JSONB в отдельные столбцы

ARRAY

ARRAY в PostgreSQL — тип массива значений одного типа: text[], int[] и т.д. Это удобно для:

небольших наборов значений, которые логически принадлежат строке

тегов, ролей, флагов, списков идентификаторов (если нет строгих требований к нормализации)

Важное практическое ограничение:

если нужен полноценный many-to-many с дополнительными атрибутами и ограничениями, чаще лучше использовать таблицу-связку.

Маппинг ARRAY

Пример: массив тегов у поста.

Запросы к массивам

Примеры типовых операций.

Проверка, что массив содержит элемент (оператор = ANY(array) или эквиваленты):

Проверка, что массив содержит все элементы из набора (оператор @>):

Проверка пересечения (оператор &&):

Индексация ARRAY

Для ARRAY часто тоже применяют GIN.

ENUM

ENUM в PostgreSQL — тип с ограниченным набором значений, например draft, published, archived. Это даёт:

защиту от некорректных значений на уровне БД

компактное хранение

Сложность в том, что изменение ENUM в PostgreSQL требует аккуратных миграций.

Маппинг ENUM в SQLAlchemy

Вариант с типом диалекта PostgreSQL обычно самый явный для продакшена.

Пояснения:

name="post_status" задаёт имя типа в PostgreSQL.

create_type=True означает, что SQLAlchemy может создать тип при создании схемы, но в реальном проекте вы обычно управляете этим через Alembic.

Миграции ENUM: важные нюансы

Самая частая операция — добавить новое значение.

В PostgreSQL это делается через ALTER TYPE ... ADD VALUE, и Alembic-автогенерация может не всегда корректно отразить это как отдельную операцию.

Практические рекомендации:

изменения ENUM делайте отдельной ревизией

сначала добавляйте новое значение в тип, потом обновляйте код приложения

для сложных изменений иногда проще перейти на CHECK-ограничение или справочник (таблица) вместо ENUM

UPSERT: INSERT ... ON CONFLICT

UPSERT решает задачу: вставь строку, а если конфликт по уникальному ключу — обнови (или ничего не делай). Это ключевой инструмент для идемпотентных операций, очередей, синхронизаций и импорта.

С точки зрения тем курса:

в уроке про транзакции мы обсуждали конкуренцию и ретраи

UPSERT часто снижает количество гонок при конкурентной записи

ON CONFLICT DO NOTHING

Пример: создать пользователя, но не падать, если email уже существует.

Замечания:

user_id будет None, если строка не вставилась из-за конфликта.

index_elements обычно ссылается на уникальные колонки (или используйте constraint=...).

ON CONFLICT DO UPDATE

Пример: при конфликте обновить профиль.

Практические замечания:

в set_ можно ссылаться на значения вставки через stmt.excluded, когда нужно частично мерджить данные

если вы обновляете JSONB, чаще нужна логика мерджа на стороне SQL (например, оператор ||), а не полная замена

CTE: WITH (включая рекурсивные)

CTE (Common Table Expression) — это именованный подзапрос, который улучшает читаемость и помогает строить многошаговые запросы.

CTE полезны, когда:

вы хотите разбить сложный запрос на шаги

нужно переиспользовать подзапрос несколько раз

нужны рекурсивные обходы (деревья, графы)

Обычный CTE

Пример: вынести подсчёт постов по автору в CTE и затем выбрать топ авторов.

Рекурсивный CTE

Рекурсивные CTE часто используют для иерархий, например таблица categories с parent_id.

Идея (структурно):

базовый запрос выбирает стартовую категорию

рекурсивная часть присоединяет дочерние

В SQLAlchemy рекурсивный CTE собирается через cte(recursive=True) и union_all(...). Это мощная тема, но для первого знакомства важно запомнить, что:

рекурсивный CTE — это инструмент SQL, не ORM-магия

удобнее сначала написать SQL-версию, а потом перенести в SQLAlchemy

Документация SQLAlchemy по CTE находится в разделе Core-выражений: SQLAlchemy: CTE.

Полнотекстовый поиск (FTS)

Полнотекстовый поиск PostgreSQL строится на двух сущностях:

tsvector — нормализованный “словарный” документ

tsquery — запрос поиска

Идея такая:

текст приводится к вектору (to_tsvector)

запрос приводится к tsquery (plainto_tsquery, websearch_to_tsquery)

выполняется матч (@@)

при необходимости считается релевантность (ts_rank)

!| ' ' || body) -> tsvector; сверху блок plainto_tsquery('russian', 'поисковая фраза') -> tsquery; затем оператор @@, затем сортировка по ts_rank; рядом значок индекса GIN на tsvector | Поток полнотекстового поиска: как текст превращается в tsvector, запрос в tsquery, и как применяется индекс

Быстрый поиск без отдельного столбца

Так можно стартовать, но на больших объёмах это будет медленнее и хуже индексироваться.

Пояснения:

.op("@@") создаёт оператор PostgreSQL @@ (матч tsvector и tsquery).

ts_rank позволяет ранжировать результаты.

Производственный подход: хранить tsvector и индексировать

Типичный паттерн:

добавить колонку search_vector типа TSVECTOR

поддерживать её через триггер или вычисление

создать GIN-индекс

SQLAlchemy-тип TSVECTOR есть в диалекте PostgreSQL.

Далее вы ищете по search_vector, а не пересчитываете to_tsvector(...) на лету.

Поддержка search_vector обычно делается миграцией (через op.execute(...)) и триггером. Важно помнить правило из урока про Alembic:

в миграциях лучше использовать SQL/Core, а не импортировать “живые” ORM-модели приложения

Полезный раздел документации PostgreSQL по деталям индексации и конфигурациям: PostgreSQL: Text Search.

Практические связки с темами курса

С запросами: JSONB, ARRAY, UPSERT и FTS часто приводят к использованию диалектных типов и операторов (contains, overlap, op("@@")), а также к CTE для читаемости.

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

С Alembic: ENUM и индексы (особенно CONCURRENTLY) требуют аккуратных миграций и понимания, какие изменения безопасно раскатывать по стратегии expand/contract.

Итог

Вы освоили ключевые PostgreSQL-возможности, которые часто отличают “базовое использование SQLAlchemy” от производственного:

JSONB: хранение документов, запросы по ключам, contains и GIN-индексы

ARRAY: операции any/contains/overlap и индексация

ENUM: строгие статусы и нюансы миграций

UPSERT: on_conflict_do_nothing/do_update для идемпотентности и конкуренции

CTE: читаемые многошаговые запросы через cte()

полнотекстовый поиск: tsvector/tsquery, матч @@, ts_rank, индексирование

Pro-практики: производительность, профилирование, асинхронность, тестирование и архитектура

Вы уже умеете настраивать Engine и Session, описывать модели ORM, писать запросы (select/join/агрегации/CTE), управлять транзакциями и конкурентным доступом, сопровождать схему через Alembic и применять PostgreSQL-специфику (JSONB, ARRAY, UPSERT, FTS). Эта статья закрывает типичные вопросы производственного уровня: почему приложение медленное, как это измерить, как безопасно перейти на async, как тестировать работу с базой и как устроить архитектуру так, чтобы код не распался.

!Карта того, где обычно появляется проблема производительности и где её измерять

Производительность: что оптимизировать в первую очередь

Оптимизация в связке SQLAlchemy + PostgreSQL почти всегда упирается в три вещи:

Количество запросов (часто проблема N+1).

Стоимость каждого запроса (плохие планы, отсутствие индексов, лишние JOIN).

Время удержания транзакций и соединений (пул забит, конкуренция растёт).

Правильный порядок работы:

Зафиксировать симптом и метрику (время ответа, RPS, CPU, количество запросов).

Понять, какие запросы выполняются и сколько их.

Для самых дорогих запросов изучить план выполнения в PostgreSQL.

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

Повторить измерение.

N+1 и стратегии загрузки связей

Проблема N+1 возникает, когда вы выбираете список сущностей, а затем лениво ходите в связи, провоцируя дополнительный запрос на каждую сущность.

Рекомендуемые стратегии в SQLAlchemy ORM:

selectinload обычно лучший первый выбор для коллекций (один дополнительный запрос на все связи пачкой).

joinedload полезен, когда вы уверены, что умножение строк приемлемо (часто для many-to-one).

Явный join нужен, когда вы меняете семантику SQL (фильтрация/сортировка/агрегации по связанным таблицам), а не просто хотите загрузить граф объектов.

Таблица для выбора стратегии:

| Задача | Что использовать | Почему | |---|---|---| | "Загрузить пользователей и их посты" | selectinload(User.posts) | Не создаёт умножения строк, снижает риск N+1 | | "Отфильтровать посты по email автора" | select(Post).join(Post.author).where(User.email == ...) | Это именно SQL-условие, связь должна попасть в FROM | | "Загрузить пост и автора" | joinedload(Post.author) или selectinload(Post.author) | Обычно one-to-many/ many-to-one, можно выгодно сделать одним запросом |

Пример устранения N+1:

Документация:

SQLAlchemy: Relationship Loading Techniques

Индексы, EXPLAIN и типовой цикл оптимизации запроса

SQLAlchemy генерирует SQL, но выбирает план выполнения PostgreSQL. Поэтому оптимизация дорогих запросов почти всегда требует EXPLAIN.

Минимально полезные команды в PostgreSQL:

EXPLAIN показывает план.

EXPLAIN (ANALYZE, BUFFERS) показывает план плюс фактические времена и работу с памятью/диском.

Документация:

PostgreSQL: EXPLAIN

Как прогнать EXPLAIN для запроса из SQLAlchemy

Практичный способ для продакшена: сначала получить реальный SQL и параметры из логов (или собрать отдельно), затем выполнить EXPLAIN вручную.

Для локальной разработки удобен быстрый подход: взять скомпилированный запрос.

Далее вы вставляете получившийся SQL в psql и запускаете:

Индекс как лекарство, но не от всех болезней

Индекс помогает, когда:

есть селективный фильтр в WHERE или JOIN.

есть сортировка ORDER BY, которую можно поддержать индексом.

Индекс часто не помогает, когда:

условие низкоселективное (например, почти все строки подходят).

запрос всё равно должен прочитать большую часть таблицы.

вы делаете функции над колонкой, и индекс не применим без функционального индекса.

Подсказка из предыдущих уроков:

Для JSONB и ARRAY часто нужен GIN индекс (и это нормально).

Для FTS нужен GIN индекс по tsvector.

См. в документации:

PostgreSQL: Indexes

Пул соединений и тайминг транзакций

Даже быстрые запросы могут “тормозить”, если соединения заняты.

Причины:

Сессии/соединения не закрываются (нет контекстных менеджеров).

Транзакции слишком длинные (делаете сетевые вызовы внутри транзакции).

Пул слишком маленький или не соответствует нагрузке.

Практики:

Используйте короткоживущую Session и шаблоны вроде session_scope() из первого урока.

Держите транзакции минимальными по времени.

Настройте pool_pre_ping=True, чтобы снижать ошибки “битых” соединений.

Документация:

SQLAlchemy: Connection Pooling

Профилирование: как увидеть реальные запросы и их стоимость

Профилирование в работе с БД отвечает на вопросы:

Сколько запросов на один запрос API?

Какие запросы самые медленные?

Где появился N+1?

Сколько времени уходит на SQL, а сколько на Python?

Базовое логирование SQL

Самый простой старт:

Для более контролируемого логирования используйте стандартный logging.

Документация:

SQLAlchemy: Logging

Замер времени каждого запроса через события (events)

SQLAlchemy позволяет подписаться на события выполнения.

Так вы быстро находите “самые дорогие” запросы и связываете их с кодом.

Документация:

SQLAlchemy: Events

Граница ответственности: профилируем SQL отдельно от Python

Рекомендация:

SQL измеряйте логами, событиями и EXPLAIN (ANALYZE).

Python измеряйте профайлерами вроде cProfile.

Асинхронность: AsyncEngine, AsyncSession и практические ограничения

Асинхронный SQLAlchemy полезен, когда:

приложение высоконагруженное и I/O-bound.

вы уже используете async веб-стек (например, FastAPI/Starlette).

Асинхронность не решает:

плохие планы запросов.

отсутствие индексов.

N+1.

Она помогает эффективнее использовать один процесс при большом числе одновременных ожиданий.

Документация:

SQLAlchemy: AsyncIO Support

Минимальная настройка async (PostgreSQL + asyncpg)

Установка:

Создание async engine и фабрики сессий:

Транзакция и запрос в async стиле

Правила, которые нельзя нарушать:

Нельзя использовать sync Session в async приложении (будет блокировка event loop).

Нельзя вызывать async операции без await.

Нужно внимательно относиться к ленивой загрузке связей, потому что в async она может приводить к неожиданным местам await.

Практическая рекомендация для async:

Старайтесь всегда использовать selectinload/joinedload и меньше полагаться на ленивые отношения.

Sync vs Async: что меняется в архитектуре

| Область | Sync | Async | |---|---|---| | Сессия | Session | AsyncSession | | Выполнение | session.execute(stmt) | await session.execute(stmt) | | Транзакция | with session.begin(): | async with session.begin(): | | Драйвер | psycopg | asyncpg |

Тестирование: как проверять код, который ходит в PostgreSQL

В тестах важны три свойства:

Изоляция: тесты не должны влиять друг на друга.

Повторяемость: одинаковый результат на любом окружении.

Скорость: тесты должны быть достаточно быстрыми для CI.

Инструменты:

pytest

Testcontainers for Python

Рекомендуемая стратегия интеграционных тестов

На практике обычно делают так:

Поднимают временный PostgreSQL (Docker/Testcontainers).

Накатывают миграции Alembic до head.

На каждый тест дают чистую транзакцию и откатывают её.

Это даёт хорошую скорость и сильную изоляцию.

Пример pytest-фикстур: Engine и транзакционный тест (sync)

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

Что это даёт:

Каждый тест видит чистое состояние (если тест не зависит от сидов).

Любые изменения откатываются.

Тесты работают быстро, потому что не нужно пересоздавать схему каждый раз.

Важно:

Если тестируемый код сам делает commit(), он “пробивает” подход. Тогда либо запрещают commit на уровне приложного кода (через unit of work), либо тестируют иначе.

Миграции в тестах

Практика для CI:

В пайплайне разворачивать пустую БД.

Запускать alembic upgrade head.

Прогонять интеграционные тесты.

Это одновременно тестирует и миграции, и совместимость кода с реальной схемой.

Документация:

Alembic: Tutorial

Архитектура: как организовать код вокруг SQLAlchemy

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

Единый контроль транзакций.

Предсказуемый жизненный цикл сессии.

Тонкие границы между доменной логикой и хранением.

Тестируемость без реальной базы там, где это возможно.

Рекомендуемая схема слоёв

Упрощённый вариант:

API слой (веб, CLI): парсит ввод, формирует DTO.

Service слой: бизнес-операции, транзакционные границы.

Repository слой: запросы к БД.

Модели ORM: отображение таблиц.

!Слои приложения и место транзакции

Unit of Work: один вход для транзакции

Идея: сервис получает объект, который управляет сессией и транзакцией, и через него берёт репозитории.

Sync пример:

Что это улучшает:

Транзакции управляются в одном месте.

Репозитории не коммитят сами.

Тесты проще: можно подменить session_factory.

Граница: где писать Core-SQL, а где ORM

Правило, которое часто работает:

ORM для CRUD и навигации по связям.

Core (или диалектные конструкции PostgreSQL) для тяжёлых операций:

- массовые UPDATE/DELETE. - INSERT ... ON CONFLICT. - сложные CTE. - выражения JSONB/FTS.

Именно так вы сохраняете читаемость кода и получаете контроль над SQL там, где он реально важен.

Чеклист “Pro”: что обычно внедряют в реальном проекте

Логи SQL с временем выполнения (хотя бы в staging).

Защита от N+1 через selectinload/joinedload и ревью запросов.

Регулярный EXPLAIN (ANALYZE) для дорогих запросов.

Индексы под реальные фильтры и сортировки.

Короткие транзакции, ретраи только на конкурентные ошибки (как в уроке про транзакции).

Интеграционные тесты с реальным PostgreSQL, миграции проверяются в CI.

Архитектура, где транзакции централизованы (unit of work) и запросы сосредоточены в репозиториях.

Итог

Теперь у вас есть связная “производственная” картина поверх предыдущих тем курса:

Вы знаете, как искать и устранять N+1 и выбирать стратегию загрузки связей.

Умеете выстраивать цикл оптимизации: лог запросов → EXPLAIN (ANALYZE) → индекс/переписывание запроса → повторное измерение.

Понимаете, что даёт async SQLAlchemy и какие ограничения он накладывает на код.

Умеете строить изолированные интеграционные тесты и проверять миграции в CI.

Знаете базовые архитектурные практики, которые удерживают SQLAlchemy-код в порядке по мере роста проекта.