Thiết kế schema & query: bảng đúng từ đầu, query không thừa

Bốn bài trước ta học cách PostgreSQL chạy bên trong: WAL, MVCC, index, lock. Bài này quay lại phía người viết: thiết kế bảng thế nào và viết query thế nào để mấy cơ chế đó làm việc tốt cho mình.

1. Bài toán: shop-app lúc mới có 1k user

Bản đầu tiên của shop-app được viết vội, gom hết vào một bảng:

orders
id | user_email | user_name | product_name | product_price | qty | total  | status
---+------------+-----------+--------------+---------------+-----+--------+--------
1  | an@x.com   | An        | Áo thun      | 199000.0      | 2   | 398000 | PAID
2  | an@x.com   | An Nguyen | Quần jean    | 450000.0      | 1   | 450000 | paid

Chạy được với vài trăm đơn. Nhưng nhìn kỹ đã thấy lỗi.

2. Vấn đề

• Dữ liệu trùng: tên user lặp ở mỗi đơn. An đổi tên thì phải update hàng nghìn dòng, sót dòng nào là lệch (dòng 1 "An", dòng 2 "An Nguyen").
• Kiểu dữ liệu sai: giá dùng double → 0.1 + 0.2 = 0.30000000000000004. Với tiền, sai một đồng cũng là bug.
• Không có ràng buộc: status lúc PAID lúc paid; qty có thể âm; email trùng.
• Query lười: SELECT * FROM orders ORDER BY id LIMIT 20 OFFSET 200000 càng về sau càng chậm.

3. Giải pháp

3.1 Normalization: mỗi sự thật chỉ lưu một chỗ. Tách thành users, products, orders, order_items. Đủ dùng là chuẩn 3NF: mỗi cột phụ thuộc vào khóa, cả khóa, và chỉ khóa.

3.2 Denormalization có chủ đích: chấp nhận lưu trùng khi có lý do rõ ràng. Ví dụ kinh điển: order_items.unit_price. Giá sản phẩm đổi ngày mai thì đơn hôm qua vẫn phải giữ giá lúc mua. Đây không phải "trùng dữ liệu", mà là snapshot lịch sử. Ví dụ khác: lưu sẵn orders.total_amount để khỏi SUM mỗi lần đọc.

3.3 Kiểu dữ liệu đúng:

3.4 Constraint là hàng phòng thủ cuối cùng. Validate ở Java (Bean Validation) là tốt, nhưng dữ liệu còn vào từ script, job, service khác, người chạy SQL tay. Chỉ DB mới thấy mọi đường ghi.

3.5 Query gọn: chỉ lấy cột cần, phân trang bằng keyset khi bảng lớn.

3.6 Soft delete: đánh dấu deleted_at thay vì DELETE khi cần khôi phục hoặc audit.

4. Code từng bước

Bước 1: Schema (đặt trong migration Flyway, xem bài Flyway/Liquibase):

-- V1__init_schema.sql
CREATE TABLE users (
    id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email      text        NOT NULL,
    full_name  text        NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now(),
    deleted_at timestamptz
);
-- email duy nhất trong các user chưa xóa
CREATE UNIQUE INDEX uq_users_email_active ON users (lower(email)) WHERE deleted_at IS NULL;

CREATE TABLE products (
    id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    sku   text          NOT NULL UNIQUE,
    name  text          NOT NULL,
    price numeric(12,2) NOT NULL CHECK (price >= 0),
    stock integer       NOT NULL CHECK (stock >= 0)
);

CREATE TABLE orders (
    id           bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    user_id      bigint        NOT NULL REFERENCES users(id),
    status       text          NOT NULL DEFAULT 'NEW'
                 CHECK (status IN ('NEW','PAID','SHIPPED','CANCELLED')),
    total_amount numeric(12,2) NOT NULL CHECK (total_amount >= 0),
    created_at   timestamptz   NOT NULL DEFAULT now()
);

CREATE TABLE order_items (
    order_id   bigint        NOT NULL REFERENCES orders(id),
    product_id bigint        NOT NULL REFERENCES products(id),
    quantity   integer       NOT NULL CHECK (quantity > 0),
    unit_price numeric(12,2) NOT NULL,   -- snapshot giá lúc mua
    PRIMARY KEY (order_id, product_id)
);

CHECK (stock >= 0) là chốt chặn chống bán âm kho: hai request trừ kho cùng lúc, request thứ hai sẽ lỗi thay vì kho thành -1.

Bước 2: Entity Java dùng BigDecimal cho tiền:

@Entity
@Table(name = "products")
public class Product {
    @Id @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(nullable = false, unique = true)
    private String sku;

    @Column(nullable = false, precision = 12, scale = 2)
    private BigDecimal price;   // không dùng double

    private int stock;
}

So sánh BigDecimal bằng compareTo, không dùng equals (2.0 và 2.00 khác scale nên equals trả false).

Bước 3: Query chỉ lấy cột cần bằng projection:

public record OrderSummary(Long id, String status, BigDecimal totalAmount, Instant createdAt) {}

public interface OrderRepository extends JpaRepository<Order, Long> {

    // Keyset pagination: lấy trang tiếp theo sau id cuối cùng đã thấy
    @Query("""
        select new com.shop.order.OrderSummary(o.id, o.status, o.totalAmount, o.createdAt)
        from Order o
        where o.user.id = :userId and o.id < :lastId
        order by o.id desc
        """)
    List<OrderSummary> nextPage(Long userId, Long lastId, Limit limit);
}

Kèm index CREATE INDEX idx_orders_user_id_id ON orders (user_id, id DESC); để query chỉ đi một đoạn index.

Bước 4: Test

-- constraint chặn dữ liệu bẩn
INSERT INTO orders (user_id, status, total_amount) VALUES (1, 'paid', 100);
-- ERROR: new row for relation "orders" violates check constraint "orders_status_check"

-- so sánh OFFSET và keyset
EXPLAIN ANALYZE SELECT id, status FROM orders ORDER BY id DESC LIMIT 20 OFFSET 500000;
-- đọc 500020 dòng rồi bỏ 500000 dòng
EXPLAIN ANALYZE SELECT id, status FROM orders WHERE id < 123456 ORDER BY id DESC LIMIT 20;
-- Index Scan, chỉ đọc 20 dòng

curl "http://localhost:8080/orders?userId=1&lastId=98765&size=20"

5. Cách hoạt động

OFFSET 500000 LIMIT 20
 index: [1][2][3] ... [500000][500001..500020]
         <------ đọc rồi vứt -------><-- trả về -->
 càng sâu càng chậm; có row mới chèn vào thì trang bị lệch

KEYSET  WHERE id < :lastId ORDER BY id DESC LIMIT 20
 index: ... [lastId-20 .. lastId-1][lastId] ...
             <-- nhảy thẳng tới đây -->
 tốc độ như nhau ở trang 1 hay trang 10000

Các lớp phòng thủ dữ liệu
  Frontend validation   (UX, bỏ qua được)
        ↓
  Bean Validation Java  (một service, bỏ qua được bởi script/job)
        ↓
  DB constraint         (NOT NULL, CHECK, UNIQUE, FK) ← mọi đường ghi đều qua

6. So sánh

7. Khi nào nên dùng / không nên dùng

• Normalize mặc định; denormalize khi đo được chỗ chậm hoặc cần snapshot.
• Tiền luôn numeric/BigDecimal, không có ngoại lệ.
• SELECT * chấp nhận được khi debug tay; trong code thì liệt kê cột hoặc dùng projection.
• Soft delete chỉ khi thật cần khôi phục/audit; nếu chỉ cần lịch sử thì bảng audit/archive thường gọn hơn.

Bạn hiểu đúng ý chính, chỉ cần bổ sung vài cạm bẫy hay gặp:

1. Quên filter: mọi query đều phải nhớ điều kiện. Hibernate 6.4+ có @SoftDelete; bản cũ hơn dùng @SQLDelete + @SQLRestriction. Native query vẫn phải tự thêm.
2. UNIQUE bị kẹt: user xóa tài khoản rồi đăng ký lại cùng email → trùng với dòng đã xóa. Giải pháp là partial unique index WHERE deleted_at IS NULL như ở trên.
3. FK vẫn trỏ vào dòng "đã xóa": đơn hàng của user đã xóa vẫn hợp lệ về mặt DB. Phải quyết định nghiệp vụ.
4. Bảng phình: dòng chết không bao giờ đi. Index nên là partial index chỉ trên dòng còn sống.
5. Luật bảo vệ dữ liệu cá nhân: user yêu cầu xóa thì soft delete chưa đủ, cần ẩn danh hóa hoặc xóa thật.

@Entity
@SQLDelete(sql = "UPDATE users SET deleted_at = now() WHERE id = ?")
@SQLRestriction("deleted_at IS NULL")
public class User { ... }

Không thừa. Hai lớp này bảo vệ hai thứ khác nhau:

• Bean Validation bảo vệ một đường vào (API của service này) và trả lỗi đẹp cho người dùng.
• Constraint DB bảo vệ dữ liệu, bất kể ai ghi: batch job, service khác, migration, người chạy SQL tay, hoặc bug race condition.

Ví dụ race condition: hai request cùng kiểm tra "email chưa tồn tại" ở Java, cả hai thấy đúng, cả hai insert. Chỉ UNIQUE ở DB mới chặn được. Code Java bắt DataIntegrityViolationException rồi trả 409.

Chi phí của constraint rất nhỏ so với chi phí dọn dữ liệu bẩn trên production.

Q1. Normalization và denormalization khác nhau thế nào? Khi nào denormalize?

🇻🇳 Tiếng Việt:

Normalization tách dữ liệu để mỗi sự thật chỉ lưu một chỗ, tránh trùng và lệch khi update. Denormalization cố ý lưu trùng để đọc nhanh hơn, đổi lại ghi phức tạp hơn. Em normalize mặc định cho hệ thống OLTP, và chỉ denormalize khi có lý do rõ: snapshot lịch sử như giá lúc mua trong order_items, hoặc tổng tiền đọc rất nhiều.

🇬🇧 English:

Normalization splits data so each fact is stored in one place, which avoids duplication and update anomalies. Denormalization deliberately duplicates data to make reads faster, at the cost of more complex writes. I normalize by default for OLTP systems and denormalize only with a clear reason, such as a historical snapshot like the purchase price in order_items, or a heavily read total.

Q2. Lưu tiền bằng kiểu gì?

🇻🇳 Tiếng Việt:

Trong PostgreSQL dùng numeric với precision và scale cố định, trong Java dùng BigDecimal. Không dùng float hay double vì đó là số nhị phân xấp xỉ, cộng dồn sẽ sai. Một cách khác là lưu bigint theo đơn vị nhỏ nhất, ví dụ cent.

🇬🇧 English:

In PostgreSQL I use numeric with a fixed precision and scale, and BigDecimal in Java. I never use float or double because they are approximate binary numbers and rounding errors accumulate. Another option is a bigint in the smallest currency unit, such as cents.

Q3. Vì sao phân trang OFFSET chậm? Thay bằng gì?

🇻🇳 Tiếng Việt:

OFFSET N buộc database đọc rồi bỏ N dòng, nên trang càng sâu càng chậm, và khi có dòng mới chèn vào thì trang bị trùng hoặc sót. Em dùng keyset pagination: lọc theo giá trị cuối của trang trước, ví dụ WHERE id nhỏ hơn lastId ORDER BY id DESC LIMIT 20, có index phù hợp thì tốc độ ổn định ở mọi trang.

🇬🇧 English:

OFFSET N makes the database read and discard N rows, so deep pages get slower, and concurrent inserts cause duplicated or skipped rows. I use keyset pagination: filter by the last value of the previous page, for example WHERE id is less than lastId ORDER BY id DESC LIMIT 20. With a matching index the cost is the same on every page.

Q4. Validate ở Java rồi, sao vẫn cần constraint ở DB?

🇻🇳 Tiếng Việt:

Constraint là hàng phòng thủ cuối cùng. Validation ở Java chỉ bảo vệ một đường vào, còn dữ liệu có thể được ghi từ job, service khác hay SQL tay. Hơn nữa có những lỗi chỉ DB chặn được, như hai request đồng thời cùng insert một email: chỉ UNIQUE constraint mới đảm bảo.

🇬🇧 English:

Constraints are the last line of defense. Java validation only protects one entry point, while data can also be written by jobs, other services or manual SQL. Some problems can only be stopped by the database, such as two concurrent requests inserting the same email: only a UNIQUE constraint guarantees that.

Cách nhớ ngắn gọn

• Normalize mặc định, denormalize có lý do (snapshot, số đọc nhiều).
• Tiền = numeric + BigDecimal.
• Constraint DB = hàng phòng thủ cuối cùng.
• Lấy đúng cột; trang sâu dùng keyset, không OFFSET.
• Soft delete: nhớ partial unique index và filter mọi query.