PostgreSQL & SQLAlchemy 2.0: model, async, N+1, transaction, Alembic
Database quyết định hiệu năng và tính đúng đắn: model SQLAlchemy 2.0 có kiểu, session async, loading strategy chống N+1, index và EXPLAIN, transaction và khoá dòng chống race condition, migration bằng Alembic không downtime, connection pool.
Hầu hết sự cố hiệu năng của backend Python nằm ở database: lazy loading sinh hàng trăm query, thiếu index, session mở quá lâu giữ connection. Lỗi sai dữ liệu (bán âm kho, trừ tiền hai lần) đến từ không hiểu transaction. SQLAlchemy mạnh nhưng phức tạp — đặc biệt ở chế độ async, nơi lazy loading ngầm sẽ ném lỗi. Developer Python $5000 phải đọc được SQL mà ORM sinh ra.
🎯 Mục tiêu học tập
- Khai báo model SQLAlchemy 2.0 với Mapped/mapped_column và ràng buộc đầy đủ
- Dùng AsyncSession đúng vòng đời; chọn loading strategy (selectinload, joinedload) chống N+1
- Đọc EXPLAIN ANALYZE, tạo index phù hợp
- Giải race condition bằng UPDATE nguyên tử, with_for_update, optimistic version
- Viết migration Alembic an toàn và cấu hình connection pool
Model SQLAlchemy 2.0
class Base(DeclarativeBase): ...
class Task(Base):
__tablename__ = "tasks"
__table_args__ = (Index("ix_tasks_project_status_created", "project_id", "status", "created_at"),)
id: Mapped[int] = mapped_column(primary_key=True)
public_id: Mapped[UUID] = mapped_column(default=uuid7, unique=True)
org_id: Mapped[int] = mapped_column(ForeignKey("organizations.id", ondelete="CASCADE"), index=True)
project_id: Mapped[int] = mapped_column(ForeignKey("projects.id"))
title: Mapped[str] = mapped_column(String(200))
status: Mapped[TaskStatus] = mapped_column(default=TaskStatus.TODO)
assignee_id: Mapped[int | None] = mapped_column(ForeignKey("users.id", ondelete="SET NULL"))
version: Mapped[int] = mapped_column(default=1)
created_at: Mapped[datetime] = mapped_column(server_default=func.now()) # timestamptz
project: Mapped["Project"] = relationship(back_populates="tasks", lazy="raise") # cấm lazy load ngầm
__mapper_args__ = {"version_id_col": version} # optimistic locking tự động
Ràng buộc trong DB (NOT NULL, UNIQUE, CHECK, FK) là tuyến phòng thủ cuối. Thời gian lưu timestamptz UTC; tiền
Numeric hoặc số nguyên.
AsyncSession và N+1
# ❌ N+1 (và trong async sẽ lỗi MissingGreenlet khi lazy load)
projects = (await session.scalars(select(Project))).all()
for p in projects: print(p.owner.name)
# ✅ Chọn loading strategy rõ ràng
stmt = (
select(Project)
.where(Project.org_id == org_id)
.options(joinedload(Project.owner), selectinload(Project.tasks.and_(Task.status != TaskStatus.DONE)))
.order_by(Project.created_at.desc(), Project.id.desc())
.limit(20)
)
projects = (await session.scalars(stmt)).unique().all()
# Tổng hợp không cần load object
counts = await session.execute(select(Task.status, func.count()).where(Task.project_id == pid).group_by(Task.status))
joinedloadcho quan hệ nhiều-một;selectinloadcho một-nhiều (tránh nhân dòng).lazy="raise"trên relationship biến N+1 thành lỗi ngay ở dev.- Session theo request (dependency),
expire_on_commit=Falsekhi trả object sau commit; không chia sẻ session giữa task song song. - Bật
echo=Trueở dev hoặc đếm query trong test.
Index, EXPLAIN và truy vấn lớn
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM tasks WHERE project_id = 42 AND status = 'todo' ORDER BY created_at DESC LIMIT 20;
- Composite index: cột lọc bằng trước, cột khoảng/sort sau. Partial index cho điều kiện cố định (
WHERE status <> 'done'). - Seq Scan trên bảng lớn, ước lượng số dòng lệch xa thực tế, sort tràn đĩa là ba dấu hiệu cần xử lý.
- Xử lý hàng triệu dòng:
session.stream_scalars()/yield_pertheo lô; insert hàng loạt bằnginsert().values([...])hoặc COPY (asyncpgcopy_records_to_table). - Bật
pg_stat_statementsđể tìm truy vấn tốn nhất.
Transaction, khoá và race condition
# ✅ UPDATE nguyên tử có điều kiện — chống bán âm kho
result = await session.execute(
update(Product).where(Product.id == pid, Product.stock > 0).values(stock=Product.stock - 1).returning(Product.stock)
)
if result.scalar_one_or_none() is None:
raise OutOfStock(pid)
# ✅ Khoá dòng khi logic phức tạp
order = await session.scalar(select(Order).where(Order.id == oid).with_for_update())
# ✅ Hàng đợi job trong Postgres: nhiều worker không lấy trùng
jobs = await session.scalars(select(Job).where(Job.status == "queued").limit(10).with_for_update(skip_locked=True))
# Optimistic locking: version_id_col → StaleDataError khi người khác đã sửa → trả 409
Gọi Stripe/gửi email trong transaction giữ khoá và connection lâu, không rollback được việc đã gửi. Ghi trạng thái + outbox trong transaction, việc ngoài chạy sau commit (Module 8).
Alembic và connection pool
alembic revision --autogeneraterồi đọc lại migration (autogenerate không hiểu đổi tên cột — nó sẽ xoá và tạo mới, mất dữ liệu).- Expand → migrate → contract cho thay đổi phá vỡ;
CREATE INDEX CONCURRENTLYtrong khốiautocommit_block(); đặtlock_timeout. - Pool:
pool_size× số worker process × số pod phải nhỏ hơnmax_connectionscủa Postgres; dùng PgBouncer khi nhiều instance/serverless;pool_pre_ping=True. - Sau khi thêm migration, chạy test trên DB thật (Testcontainers, Module 14).
🏋️ Bài tập thực hành
(1) Schema multi-tenant (organization, membership, project, task, comment) bằng SQLAlchemy 2.0 + Alembic. (2) Seed 1 triệu task,
đo truy vấn danh sách trước/sau index, lưu EXPLAIN vào README. (3) Đặt lazy="raise" cho mọi relationship, sửa hết
N+1. (4) Endpoint "nhận task" chỉ một người nhận được — 50 request song song chứng minh đúng. (5) Optimistic locking cho mô tả task
trả 409. (6) Một migration đổi tên cột theo expand/contract.
📚 Tài nguyên học tập
-
SQLAlchemy 2.0 DocumentationORM Quickstart, Unified Tutorial, asyncioDocs
-
AlembicMigration cho SQLAlchemyDocs
-
PostgreSQL DocumentationIndex, Concurrency Control, Performance TipsDocs
-
Use The Index, LukeIndex SQL cho developerSách
-
SQLModelLớp kết hợp SQLAlchemy + Pydantic (lựa chọn khác)Docs
✅ Tự đánh giá hoàn thành
- Model SQLAlchemy 2.0 có kiểu, ràng buộc đủ, lazy='raise'
- Không còn N+1; dùng đúng joinedload/selectinload
- Giải race condition bằng UPDATE nguyên tử và with_for_update
- Migration Alembic được đọc lại và an toàn với bảng lớn