Fullstack Python
Trang chủ / Module 6
🐘 Module 6 / 19 · 6 ngày 📅 Tuần 4-5 ●●●○ Khó

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.

PostgreSQLSQLAlchemy 2.0asyncpgAlembicN+1Locking
📌 Vì sao module này quan trọng

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))
  • joinedload cho quan hệ nhiều-một; selectinload cho 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=False khi 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_per theo lô; insert hàng loạt bằng insert().values([...]) hoặc COPY (asyncpg copy_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
⚠️ Không gọi API ngoài trong transaction

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 --autogenerate rồ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 CONCURRENTLY trong khối autocommit_block(); đặt lock_timeout.
  • Pool: pool_size × số worker process × số pod phải nhỏ hơn max_connections củ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

Chuyển API sang PostgreSQL và chứng minh đúng dưới tải đồng thời

(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

✅ 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