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

PostgreSQL & ORM: mô hình dữ liệu, index, transaction, hiệu năng

Database quyết định hiệu năng và tính đúng đắn của hệ thống: thiết kế schema, index đúng, đọc EXPLAIN ANALYZE, transaction và isolation level, N+1, migration không downtime.

PostgreSQLPrismaDrizzleIndexTransactionEXPLAIN
📌 Vì sao module này quan trọng

Phần lớn sự cố hiệu năng ở production là do database: thiếu index, N+1 query, transaction giữ lock quá lâu. Phần lớn sự cố sai dữ liệu (trừ tiền hai lần, bán quá số tồn kho) là do không hiểu transaction và concurrency. ORM giúp viết nhanh nhưng che mất SQL — developer $5000 phải đọc được SQL mà ORM sinh ra và biết khi nào bỏ ORM để viết tay.

🎯 Mục tiêu học tập

  • Thiết kế schema chuẩn hoá, chọn kiểu dữ liệu và ràng buộc đúng
  • Tạo index B-tree, composite, partial, GIN; đọc EXPLAIN ANALYZE
  • Dùng transaction, hiểu isolation level, chống race condition bằng lock hoặc ràng buộc
  • Dùng Prisma hoặc Drizzle hiệu quả; phát hiện và sửa N+1
  • Viết migration an toàn không downtime; hiểu connection pooling

Thiết kế schema

CREATE TABLE organizations (
  id          uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  name        text NOT NULL,
  created_at  timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE tasks (
  id          uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  org_id      uuid NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  project_id  uuid NOT NULL REFERENCES projects(id),
  title       text NOT NULL CHECK (length(title) BETWEEN 1 AND 200),
  status      text NOT NULL DEFAULT 'todo' CHECK (status IN ('todo','doing','done')),
  assignee_id uuid REFERENCES users(id),
  due_at      timestamptz,
  metadata    jsonb NOT NULL DEFAULT '{}',
  created_at  timestamptz NOT NULL DEFAULT now(),
  updated_at  timestamptz NOT NULL DEFAULT now()
);
  • Luôn timestamptz, không timestamp. Tiền: numeric hoặc số nguyên theo đơn vị nhỏ nhất (cent), không bao giờ float.
  • Ràng buộc trong DB (NOT NULL, UNIQUE, CHECK, FK) là tuyến phòng thủ cuối — validate ở app không thay thế được.
  • jsonb cho dữ liệu linh hoạt, nhưng field nào hay lọc/join thì tách thành cột.
  • UUID v7 (có thứ tự thời gian) tốt cho index hơn UUID v4 ngẫu nhiên.

Index và EXPLAIN ANALYZE

-- Truy vấn hay dùng: task của một project, lọc trạng thái, mới nhất trước
CREATE INDEX tasks_project_status_created_idx ON tasks (project_id, status, created_at DESC);

-- Partial index: chỉ index task chưa xong (nhỏ hơn nhiều)
CREATE INDEX tasks_open_due_idx ON tasks (due_at) WHERE status <> 'done';

-- GIN cho jsonb và full-text search
CREATE INDEX tasks_metadata_gin ON tasks USING gin (metadata);

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM tasks WHERE project_id = $1 AND status = 'todo' ORDER BY created_at DESC LIMIT 20;
Thấy trong planNghĩa là
Seq Scan trên bảng lớnThiếu index hoặc điều kiện không dùng được index
Index Scan / Index Only ScanTốt
Ước lượng rows=10 vs thực tế rows=100000Thống kê lỗi thời → ANALYZE
Sort Method: external merge DiskSort tràn ra đĩa; cần index theo thứ tự sort

Quy tắc composite index: cột lọc bằng (=) trước, cột khoảng/sort sau. Mỗi index làm chậm ghi — đừng index mọi cột. Bật pg_stat_statements để tìm truy vấn tốn nhất.

Transaction, isolation và race condition

Bài toán: hai request cùng mua sản phẩm cuối cùng trong kho.

-- ❌ Đọc rồi ghi: cả hai cùng đọc stock = 1, cùng trừ → stock = -1
SELECT stock FROM products WHERE id = $1;
UPDATE products SET stock = stock - 1 WHERE id = $1;

-- ✅ Cách 1: cập nhật có điều kiện, nguyên tử
UPDATE products SET stock = stock - 1 WHERE id = $1 AND stock > 0 RETURNING stock;
-- 0 dòng bị ảnh hưởng → hết hàng

-- ✅ Cách 2: khoá dòng khi cần logic phức tạp
BEGIN;
SELECT * FROM products WHERE id = $1 FOR UPDATE;
-- ... kiểm tra, tính toán ...
UPDATE products SET stock = ... WHERE id = $1;
COMMIT;

-- ✅ Cách 3: optimistic locking bằng cột version
UPDATE docs SET body = $2, version = version + 1 WHERE id = $1 AND version = $3;
IsolationPostgres hành xử
Read Committed (mặc định)Mỗi câu lệnh thấy dữ liệu đã commit tại thời điểm câu lệnh chạy
Repeatable ReadCả transaction thấy một snapshot; ghi xung đột → lỗi, phải retry
SerializableNhư chạy tuần tự; an toàn nhất, bắt buộc có retry khi lỗi serialization
⚠️ Không gọi API ngoài trong transaction

Gọi Stripe/gửi email bên trong transaction giữ lock hàng giây và không rollback được việc đã gửi. Dùng outbox pattern (Module 7).

ORM: Prisma vs Drizzle, và N+1

PrismaDrizzle
Phong cáchSchema riêng (schema.prisma), client sinh ra, API cấp caoSchema bằng TypeScript, API gần SQL
Phổ biếnPhổ biến nhất, nhiều JDTăng rất nhanh, nhẹ, hợp serverless/edge
SQL phức tạp$queryRaw / TypedSQLViết gần như SQL, vẫn type-safe
// ❌ N+1: 1 query lấy project + N query lấy task
const projects = await prisma.project.findMany();
for (const p of projects) p.tasks = await prisma.task.findMany({ where: { projectId: p.id } });

// ✅ 1-2 query
const projects2 = await prisma.project.findMany({
  include: { tasks: { where: { status: { not: 'done' } }, take: 5 } },
});

// ✅ Chỉ lấy cột cần cho danh sách
await prisma.task.findMany({ select: { id: true, title: true, status: true } });

Bật log query ở môi trường dev và đếm số query mỗi request. Module 9 (GraphQL) gặp lại N+1 ở dạng khó hơn và giải bằng DataLoader.

Migration không downtime và connection pooling

  • Expand → migrate → contract: thêm cột mới (nullable) → code ghi cả hai → backfill theo lô → code đọc cột mới → xoá cột cũ ở lần deploy sau. Không bao giờ đổi tên cột trong một bước.
  • CREATE INDEX CONCURRENTLY trên bảng lớn để không khoá ghi.
  • Thêm NOT NULL cho bảng lớn: thêm CHECK ... NOT VALID trước, VALIDATE CONSTRAINT sau.
  • Mỗi kết nối Postgres tốn vài MB RAM. 20 pod × pool 20 = 400 kết nối → dùng PgBouncer (transaction mode) hoặc pooler của nhà cung cấp; với serverless thì bắt buộc.
  • Đọc nhiều: read replica — chú ý replication lag (vừa ghi xong đọc từ replica có thể chưa thấy).

🏋️ 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) Thiết kế schema multi-tenant (organization → project → task, user, membership) đủ ràng buộc; viết migration bằng Prisma hoặc Drizzle. (2) Seed 1 triệu task, đo truy vấn danh sách trước và sau khi thêm index, lưu EXPLAIN ANALYZE vào README. (3) Viết endpoint "nhận task" (claim) — chỉ một người nhận được — và script bắn 50 request đồng thời chứng minh không bao giờ có 2 người cùng nhận. (4) Tìm và sửa ít nhất một N+1. (5) Chạy Postgres bằng Docker Compose; integration test dùng Testcontainers.

📚 Tài nguyên học tập

✅ Tự đánh giá hoàn thành

  • Schema có đủ ràng buộc, kiểu dữ liệu đúng (timestamptz, tiền)
  • Đọc được EXPLAIN ANALYZE và chứng minh index có hiệu quả
  • Giải được bài toán race condition bằng ít nhất 2 cách
  • Không còn N+1 trong các endpoint danh sách
  • Nắm quy trình migration expand/contract