Index trong PostgreSQL là một trong những công cụ quan trọng nhất để tối ưu query performance cho backend production. Khi bảng nhỏ, mọi truy vấn đều có vẻ “ổn”. Nhưng khi dữ liệu tăng lên vài triệu dòng, một endpoint tưởng đơn giản có thể kéo latency từ 50ms lên vài giây, làm nghẽn connection pool và kéo sập cả API.

Bài này dành cho backend developer đã dùng PostgreSQL trong dự án thực tế và muốn hiểu cách chọn index đúng, đọc EXPLAIN, tránh index thừa, và review query trước khi đưa lên production.

Index trong PostgreSQL là gì?

Index là cấu trúc dữ liệu phụ giúp PostgreSQL tìm dòng dữ liệu nhanh hơn thay vì phải quét toàn bộ bảng. Với kiểu index phổ biến nhất là B-tree, database có thể tìm theo điều kiện =, range, sort và một số pattern query thường gặp hiệu quả hơn nhiều so với sequential scan.

Sơ đồ query plan PostgreSQL dùng index để giảm số dòng phải quét
Index tốt giúp PostgreSQL giảm số dòng phải đọc trước khi trả kết quả cho API.

Nhưng index không miễn phí. Mỗi index làm tăng dung lượng lưu trữ và làm chậm thao tác ghi như INSERT, UPDATE, DELETE. Vì vậy mục tiêu không phải là “thêm càng nhiều index càng tốt”, mà là thêm đúng index cho workload thật.

Khi nào cần thêm index?

Hãy nghĩ đến index khi một query quan trọng có các dấu hiệu sau:

  • lọc theo cột có độ chọn lọc cao như user_id, order_id, status + created_at;
  • endpoint có latency tăng rõ khi dữ liệu lớn;
  • query xuất hiện nhiều trong slow query log;
  • truy vấn có ORDER BY, LIMIT nhưng vẫn phải sort nhiều dòng;
  • join giữa bảng lớn qua foreign key chưa có index phù hợp;
  • job nền quét dữ liệu theo mốc thời gian hoặc trạng thái.

Không nên thêm index chỉ vì “cột này hay được dùng”. Cần kiểm bằng query thực tế, tham số thực tế và số lượng row gần production.

Đọc EXPLAIN ANALYZE trước khi đoán

Lệnh quan trọng nhất khi tối ưu PostgreSQL là:

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders
WHERE user_id = 42 AND status = 'paid'
ORDER BY created_at DESC
LIMIT 20;

Khi đọc plan, hãy chú ý:

  • Seq Scan: PostgreSQL đang quét toàn bảng. Không phải lúc nào cũng xấu, nhưng đáng kiểm nếu bảng lớn.
  • Index Scan / Index Only Scan: query đang dùng index.
  • Rows Removed by Filter: số dòng bị đọc rồi loại bỏ; cao bất thường thường là dấu hiệu index chưa đúng.
  • Sort Method: nếu sort nhiều dữ liệu, có thể cần index hỗ trợ thứ tự.
  • Buffers: cho biết query đọc nhiều page từ cache/disk đến mức nào.

Đừng chỉ nhìn “cost”. Với backend production, thời gian thực tế, số row thực đọc và buffer mới là tín hiệu quan trọng.

B-tree và composite index

B-tree là lựa chọn mặc định cho phần lớn query backend. Ví dụ:

CREATE INDEX CONCURRENTLY idx_orders_user_status_created
ON orders (user_id, status, created_at DESC);

Index này phù hợp với query lọc theo user_id, status và sắp xếp theo created_at DESC. Thứ tự cột trong composite index rất quan trọng. Quy tắc thực dụng:

  • đặt cột dùng điều kiện equality quan trọng trước;
  • đặt cột range/sort sau;
  • thiết kế theo query cụ thể, không theo cảm tính;
  • kiểm lại bằng EXPLAIN ANALYZE.

Partial index và covering index

Với bảng lớn nhưng query chỉ quan tâm một phần dữ liệu, partial index rất hữu ích:

CREATE INDEX CONCURRENTLY idx_orders_pending_created
ON orders (created_at DESC)
WHERE status = 'pending';

Index này nhỏ hơn index toàn bảng và tốt cho dashboard/job chỉ xử lý order pending. Với query đọc một số cột cố định, có thể cân nhắc covering index:

CREATE INDEX CONCURRENTLY idx_users_email_include
ON users (email) INCLUDE (id, name, created_at);

Covering index có thể giúp Index Only Scan, nhưng không nên lạm dụng vì index lớn sẽ tốn memory và chậm write.

Lỗi thường gặp khi dùng index

1. Thêm index nhưng query không dùng

Nguyên nhân thường là thứ tự cột sai, điều kiện query không khớp, statistics cũ, hoặc PostgreSQL đánh giá sequential scan rẻ hơn.

2. Dùng function lên cột được index

WHERE DATE(created_at) = '2026-05-22'

Pattern này có thể làm index thường không hiệu quả. Nên viết range:

WHERE created_at >= '2026-05-22'
  AND created_at < '2026-05-23'

3. Quên index cho foreign key

PostgreSQL không tự tạo index cho foreign key. Nếu join/delete/update theo khóa ngoại nhiều, hãy kiểm index rõ ràng.

4. Tạo index blocking production

Với bảng lớn, dùng CREATE INDEX CONCURRENTLY để giảm nguy cơ lock write. Migration production cần plan rollback và theo dõi.

Checklist review index trước khi release

  • Query đã được đo bằng EXPLAIN (ANALYZE, BUFFERS).
  • Index khớp với điều kiện lọc, sort và join thật.
  • Không tạo index trùng hoặc gần trùng với index cũ.
  • Đã cân nhắc tác động lên write path.
  • Migration dùng CONCURRENTLY nếu bảng lớn.
  • Có metric/slow log để kiểm sau deploy.

FAQ

Có nên index mọi foreign key?

Không bắt buộc tuyệt đối, nhưng trong backend production, foreign key dùng để join, filter hoặc cascade delete/update thường nên có index.

Index có làm query luôn nhanh hơn không?

Không. Với bảng nhỏ hoặc query đọc phần lớn bảng, sequential scan có thể nhanh hơn. Hãy đo thay vì đoán.

Composite index có thay thế được index đơn không?

Có thể, nếu query dùng prefix của composite index. Nhưng không phải mọi index đơn đều thừa; cần kiểm workload.

Kết luận

Index PostgreSQL là kỹ năng nền tảng của backend developer. Muốn tối ưu đúng, hãy bắt đầu từ slow query thật, đọc plan, thiết kế index theo workload, deploy an toàn và đo lại sau release.

Nên đọc tiếp: Backend Engineering là gì, REST API Design Checklist, System Design cho Backend Developer.