PostgreSQL index và EXPLAIN: tối ưu query bằng dữ liệu thật

Index không phải cứ thêm là nhanh#

Index giúp database tìm dữ liệu nhanh hơn, nhưng index cũng có chi phí: tốn storage, làm insert/update/delete chậm hơn và cần maintenance. Một hệ thống tốt không thêm index theo cảm giác. Nó thêm index dựa trên query thật, dữ liệu thật và kết quả đo bằng EXPLAIN hoặc EXPLAIN ANALYZE.

Bắt đầu từ query pattern#

Hãy liệt kê các query quan trọng: trang listing lọc theo gì, sort theo gì, pagination ra sao, endpoint detail lookup bằng khóa nào, dashboard aggregate theo khoảng thời gian nào. Index nên phục vụ pattern này.

Ví dụ bảng posts public thường có query: lấy bài đã publish, publish time nhỏ hơn hiện tại, sort mới nhất và giới hạn số lượng. Index trên published_at giúp sort/filter tốt hơn. Lookup bài detail theo slug nên có unique index.

CREATE UNIQUE INDEX idx_posts_slug ON posts(slug);
CREATE INDEX idx_posts_published_at ON posts(published_at DESC);
CREATE INDEX idx_posts_category ON posts(category);

Composite index cần đúng thứ tự#

Nếu query thường lọc theo category rồi sort theo published_at, composite index có thể hữu ích. Nhưng thứ tự cột trong index quan trọng. Database có thể tận dụng tốt phần đầu của index; nếu bạn đảo thứ tự không phù hợp với filter, index kém hiệu quả hơn.

CREATE INDEX idx_posts_category_published
ON posts(category, published_at DESC);

Đừng tạo mọi tổ hợp index. Hãy đo query đang chậm, xem plan, thêm index phục vụ query đó, rồi đo lại.

EXPLAIN giúp nhìn query plan#

EXPLAIN cho biết PostgreSQL dự định đọc dữ liệu bằng cách nào: sequential scan, index scan, bitmap scan, join strategy và chi phí ước tính. EXPLAIN ANALYZE chạy query thật và trả thời gian thực tế, nên hãy cẩn thận với query ghi dữ liệu hoặc query nặng ở production.

EXPLAIN ANALYZE
SELECT id, title, slug, published_at
FROM posts
WHERE category = 'DevOps'
  AND published_at IS NOT NULL
ORDER BY published_at DESC
LIMIT 10;

Khi đọc plan, hãy chú ý số row ước tính so với thực tế. Nếu lệch lớn, thống kê có thể cũ hoặc data distribution đặc biệt. Chạy analyze định kỳ và hiểu dữ liệu giúp planner chọn plan tốt hơn.

Constraint bảo vệ dữ liệu, không chỉ performance#

Index thường đi cùng constraint như unique slug. Nhưng database còn cần foreign key, check constraint và not null để bảo vệ bất biến dữ liệu. Validation ở app tốt cho UX; constraint ở database tốt cho tính đúng đắn khi có bug, job hoặc script ghi dữ liệu.

• UNIQUE: bảo đảm slug, email hoặc external id không trùng.
• FOREIGN KEY: bảo đảm quan hệ tồn tại.
• CHECK: bảo đảm giá trị hợp lệ như view_count không âm.
• NOT NULL: bảo đảm field bắt buộc luôn có dữ liệu.

Checklist tối ưu query#

• Đo query chậm bằng log hoặc APM trước khi tối ưu.
• Xem query pattern: filter, sort, join, limit, pagination.
• Dùng EXPLAIN để hiểu plan, dùng EXPLAIN ANALYZE khi an toàn.
• Thêm index phục vụ query cụ thể, tránh index theo cảm giác.
• Kiểm tra chi phí write sau khi thêm index.
• Dùng constraint để bảo vệ dữ liệu quan trọng.

Nguồn tham khảo chính#

• PostgreSQL Docs: Indexes
• PostgreSQL Docs: Using EXPLAIN
• PostgreSQL Docs: Constraints