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.
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ôngtimestamp. Tiền:numerichoặ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. jsonbcho 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 plan | Nghĩa là |
|---|---|
Seq Scan trên bảng lớn | Thiếu index hoặc điều kiện không dùng được index |
Index Scan / Index Only Scan | Tốt |
Ước lượng rows=10 vs thực tế rows=100000 | Thống kê lỗi thời → ANALYZE |
Sort Method: external merge Disk | Sort 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;
| Isolation | Postgres 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 Read | Cả transaction thấy một snapshot; ghi xung đột → lỗi, phải retry |
| Serializable | Như chạy tuần tự; an toàn nhất, bắt buộc có retry khi lỗi serialization |
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
| Prisma | Drizzle | |
|---|---|---|
| Phong cách | Schema riêng (schema.prisma), client sinh ra, API cấp cao | Schema bằng TypeScript, API gần SQL |
| Phổ biến | Phổ biến nhất, nhiều JD | Tăng rất nhanh, nhẹ, hợp serverless/edge |
| SQL phức tạp | $queryRaw / TypedSQL | Viế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 CONCURRENTLYtrên bảng lớn để không khoá ghi.- Thêm
NOT NULLcho bảng lớn: thêmCHECK ... NOT VALIDtrước,VALIDATE CONSTRAINTsau. - 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
(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
-
PostgreSQL DocumentationCác chương Indexes, Concurrency Control, Performance TipsDocs
-
Use The Index, LukeIndex SQL giải thích cho developerSách
-
Prisma DocsORM phổ biến nhất cho NodeDocs
-
Drizzle ORMORM TypeScript gần SQLDocs
-
Designing Data-Intensive ApplicationsChương Transactions là bắt buộc đọcSách
✅ 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