Tối ưu Index cho bảng 1TB trong PostgreSQL

Giới Thiệu

Trước Tết, mình may mắn được nhận lì xì sớm bằng 1 incident, tính năng golive làm cho latency của system từ 10s lên 30 phút. Cạn phước...🙏
Vấn đề mình gặp phải khi đó là một table size 1TB và câu query chạy chậm như sự nghiệp của mình (trung bình 1 câu query chạy mất 16s).
Và sau khi đi thỉnh giáo mấy thầy, mình đã cải thiện tốc độ truy vấn từ 16s giảm còn 0.54ms. Bài học đắt giá mình rút ra qua incident này chính là phải “đúng người, đúng thời điểm”.
À là “Không phải cứ có index là nhanh, mà phải đánh đúng cách”.

---

1. Bối Cảnh

Mình có 1 table transactions lưu thông tin giao dịch, với 1TB dữ liệu. Truy vấn phổ biến của mình là:

SELECT * FROM transactions 
WHERE user_id = $1 AND status = $2 AND created_at >= NOW() - INTERVAL '7 days';

Ban đầu database của mình đã có một compound index trước rồi, cộng với việc khi mình đi kiểm tra câu truy vấn thì thấy hit index, làm cho mình sinh ra tâm lý chủ quan khi golive tính năng performance sẽ ổn.

CREATE INDEX idx_transactions_user_id_created_at 
ON transactions (user_id, created_at);

Nhưng không query vẫn rất chậm (tốn gần 16s).

---

2. Phân Tích Nguyên Nhân

🔍 2.1. Kiểm Tra Kế Hoạch Thực Thi

Mình bắt đầu EXPLAIN ANALYZE để tìm lý do

EXPLAIN ANALYZE 
SELECT * FROM transactions 
WHERE user_id = 123 
AND status = 'active' 
AND created_at >= NOW() - INTERVAL '7 days';

Kết quả:

Index Scan using idx_transactions_user_id_created_at on leads  (cost=... rows=...)
  Filter: (status = 'active')
Heap Blocks: 1000000 read, 5000 hit

Vấn đề:

• PostgreSQL đã dùng index, nhưng vẫn phải đọc heap từ disk.

• Lý do: status không nằm trong index, nên phải quét nhiều dòng rồi lọc thủ công. Mọi đau thương xuất phát từ đây nè.

🔍 2.2. RAM vs Disk là sao ta?

Xưa giờ mình cứ nghĩ dữ liệu chỉ lưu trên disk thôi nhưng PostgreSQL không đọc dữ liệu trực tiếp từ disk mọi lúc, mà nó dùng một hệ thống quản lý bộ nhớ thông minh để tăng tốc truy vấn. Và đó là RAM.

---

⚡ Bộ nhớ RAM vs. Disk (Ổ cứng)

📌 PostgreSQL không load toàn bộ bảng lên RAM mà chỉ lấy phần cần thiết. Nếu dữ liệu đã có trong RAM (cache), nó sẽ đọc nhanh hơn nhiều so với phải đọc từ disk.

🔥 Tại sao Heap Scan làm chậm câu query của mình?

Heap Scan khi PostgreSQL đọc toàn bộ bảng từ disk để tìm dữ liệu phù hợp với truy vấn. PostgreSQL phải duyệt từng page (khối dữ liệu ~8KB) trong bảng, đọc toàn bộ các dòng và kiểm tra điều kiện WHERE.

• Bảng này của mình 1TB, mỗi lần cần lấy status, PostgreSQL phải truy cập heap, gây ra random I/O, rất tốn tài nguyên.

• Nếu dữ liệu quá lớn và không nằm trong RAM, hệ thống phải đọc từ disk, làm chậm query.

---

3. Giải Pháp: Tạo Index "Xịn" Hơn

✅ 3.1. Hóa phép Index

Lúc đầu, cùng sự thiếu hiểu biết, mình tính đi partition table này rồi. Nhưng nhờ sự cố vấn từ mấy thầy, mình tạo một index mới, thêm luôn status để PostgreSQL khỏi mất công đọc heap:

CREATE INDEX idx_transactions_user_id_status_created_at 
ON transactions (user_id, status, created_at);

Sau khi chạy lại query, tốc độ tăng "chóng mặt". PostgreSQL chuyển từ Index Scan sang Index Only Scan, nghĩa là chỉ cần đọc cây index, không cần truy cập heap.

---

4. Kiểm Tra Hiệu Quả

📊 4.1. So Sánh Trước Và Sau

📌 4.2. Index Scan vs. Index Only Scan

• Index Scan: PostgreSQL tìm dòng bằng index nhưng vẫn đọc heap từ disk.

• Index Only Scan: PostgreSQL chỉ cần đọc index, giảm I/O đáng kể.

Nhờ việc đánh index mới, PostgreSQL chuyển sang Index Only Scan, giảm truy cập heap và tăng tốc độ truy vấn. Query giảm từ 16s xuống 0.54ms

---

Kết Luận

Đến đây hy vọng phần kiến thức nhỏ của mình về Indexing có thể giúp ích được cho các bạn để các bạn sẽ tránh được những sai lầm mình đã trải qua.

Bài viết chắc chắn còn nhiều thiếu sót, mình hi vọng được mọi người đóng góp và chia sẻ để mình có thể học hỏi và cải thiện. Xin chân thành cảm ơn mọi người đã dành thời gian đọc bài viết của mình.🚀

✍️ PS: Ở bài viết sau mình sẽ bật mí thêm:

• Conditional index mà mình sử dụng trong Postgres

• Thời gian để đánh 1 index mới cho table 1TB mất bao lâu