PostgreSQL Indexing: Ngừng đánh Index mọi thứ và hãy bắt đầu tối ưu chính xác

Table of Contents

Vấn đề của việc lạm dụng Index trên các tập dữ liệu lớn

Đây là một sai lầm phổ biến: thêm một B-tree index vào mọi cột trong câu lệnh WHERE của bạn và hy vọng điều tốt nhất sẽ đến. Tôi cũng từng như vậy. Lúc đầu, nó hoạt động tốt. Nhưng một khi các bảng của bạn vượt quá mốc 50GB hoặc 100 triệu dòng, những index “tiêu chuẩn” này bắt đầu phản tác dụng. Chúng ngốn dung lượng đĩa, làm phình to các bản sao lưu và làm chậm mọi thao tác INSERT hoặc UPDATE khi database engine phải vất vả để giữ cho các cây chỉ mục (index trees) được cân bằng.

PostgreSQL cung cấp khả năng kiểm soát chi tiết hơn nhiều so với những gì hầu hết các lập trình viên nhận thấy. không phải lúc nào bạn cũng cần lập chỉ mục cho mọi hàng hoặc lưu trữ giá trị thô của một cột. Bằng cách sử dụng Partial và Functional index, bạn có thể tạo ra các cơ sở dữ liệu gọn nhẹ hơn, nhanh hơn và luôn nằm thường trực trong bộ nhớ. Hãy cùng xem cách để vượt qua những thiết lập mặc định.

Khái niệm cốt lõi: Suy nghĩ vượt ra ngoài B-Tree tiêu chuẩn

Lập chỉ mục tiêu chuẩn tuân theo một quy tắc đơn giản: một mục nhập trong index cho mỗi hàng trong bảng. Nếu bạn có 100 triệu hàng, index của bạn có 100 triệu mục nhập. Điều này thường gây lãng phí. Nếu 95% các truy vấn của bạn chỉ nhắm vào một tập hợp con dữ liệu cụ thể, tại sao lại phải lập chỉ mục cho 5% còn lại?

Partial Index là gì?

Hãy coi partial index (index một phần) như một bản đồ đã được lọc. Bạn xác định chính xác những hàng nào xứng đáng được đưa vào index bằng cách sử dụng mệnh đề WHERE. PostgreSQL sau đó sẽ bỏ qua bất kỳ hàng nào không đáp ứng tiêu chí của bạn. Kết quả là một tệp index cực nhỏ, quét nhanh hơn và tốn ít chi phí duy trì hơn trong quá trình ghi dữ liệu.

Functional Index là gì?

Đôi khi nút thắt cổ chai nằm ở cách bạn truy vấn dữ liệu, chứ không phải ở lượng dữ liệu. Nếu bạn chạy WHERE LOWER(email) = '[email protected]', một index tiêu chuẩn trên cột email sẽ trở nên vô dụng. Cơ sở dữ liệu phải thực hiện quét tuần tự (sequential scan) chậm chạp vì index lưu trữ định dạng chữ hoa/chữ thường nguyên bản. Một functional index (index hàm hoặc index biểu thức) sẽ lưu trữ kết quả đã được tính toán trước của một hàm, giúp việc tra cứu này gần như tức thì.

Thực hành: Các tình huống thực tế

Theo kinh nghiệm của tôi, hai kỹ thuật này giải quyết được phần lớn các nút thắt cổ chai về hiệu suất trong các ứng dụng đọc dữ liệu nhiều. Dưới đây là cách chúng vận hành trong môi trường production.

Tình huống 1: Mô hình “Xóa mềm” (Soft Delete)

Hầu hết các ứng dụng hiện đại không xóa hẳn các dòng; chúng đặt một mốc thời gian deleted_at hoặc một cờ is_active. Nếu 90% người dùng của bạn đang hoạt động, nhưng bạn vẫn giữ hàng triệu bản ghi cũ để tuân thủ quy định, thì một index tiêu chuẩn trên username sẽ bị phình to bởi những dữ liệu mà bạn chẳng bao giờ tìm kiếm.

-- Index tiêu chuẩn: Lập chỉ mục cho tất cả 10 triệu người dùng
CREATE INDEX idx_users_username ON users(username);

-- Partial index: Chỉ lập chỉ mục cho 1 triệu người dùng đang hoạt động
CREATE INDEX idx_users_username_active ON users(username) 
WHERE is_active IS TRUE;

Hiệu quả mang lại là tức thì. Trong một dự án gần đây, việc chuyển sang partial index cho các bản ghi đang hoạt động đã giảm kích thước index từ 4.2GB xuống còn chỉ 180MB. Vì index đủ nhỏ để nằm hoàn toàn trong RAM Buffer Cache, độ trễ truy vấn đã giảm từ 150ms xuống dưới 4ms.

Tình huống 2: Tra cứu không phân biệt chữ hoa chữ thường

Người dùng thường không nhất quán trong việc viết hoa. Để ngăn chặn các vấn đề đăng nhập, bạn có thể sử dụng LOWER() trong các truy vấn của mình. Nếu không có functional index, cơ sở dữ liệu của bạn đang phải làm rất nhiều việc không cần thiết.

-- Truy vấn này sẽ bỏ qua index tiêu chuẩn trên 'email'
SELECT * FROM users WHERE LOWER(email) = '[email protected]';

-- Sử dụng functional index để khắc phục nút thắt cổ chai
CREATE INDEX idx_users_email_lower ON users (LOWER(email));

Giờ đây, PostgreSQL lưu trữ trực tiếp các chuỗi chữ thường trong cây B-tree. Khi bộ lập kế hoạch truy vấn nhìn thấy hàm LOWER(), nó sẽ ánh xạ thẳng tới index này. Đó là sự khác biệt giữa việc quét 10 triệu dòng và việc tra cứu trực tiếp qua con trỏ.

Tình huống 3: Truy vấn JSONB hiệu quả

PostgreSQL xử lý lưu trữ tài liệu rất tốt, nhưng GIN index trên toàn bộ các cột JSONB có thể rất lớn — đôi khi lớn hơn cả chính cái bảng đó. Nếu bạn chỉ quan tâm đến một key cụ thể, hãy sử dụng functional index thay thế.

-- metadata: { "source": "mobile", "priority": "high" }
CREATE INDEX idx_metadata_source ON orders ((metadata->>'source'));

-- Truy vấn này hiện tại đã đạt tốc độ cực nhanh
SELECT * FROM orders WHERE metadata->>'source' = 'mobile';

Một functional index ở đây thường nhỏ hơn từ 10 đến 20 lần so với một GIN index đầy đủ, giúp bạn tiết kiệm đáng kể chỉ số IOPS, giúp bạn tiết kiệm đáng kể chỉ số IOPS.

Tình huống 4: Ràng buộc Unique với giá trị Null

Các unique index tiêu chuẩn coi NULL là một giá trị riêng biệt. Điều này cho phép nhiều hàng có số điện thoại là NULL, điều này thường là ổn. Nhưng chuyện gì sẽ xảy ra nếu bạn cần một ràng buộc duy nhất chỉ áp dụng cho các bản ghi đang hoạt động? Partial index giải quyết vấn đề này một cách dễ dàng.

-- Đảm bảo số điện thoại là duy nhất, nhưng chỉ dành cho người dùng đang hoạt động
CREATE UNIQUE INDEX idx_unique_phone_active 
ON users(phone_number) 
WHERE is_active IS TRUE;

Các lưu ý quan trọng trong môi trường Production

Những kỹ thuật này rất mạnh mẽ, nhưng chúng đòi hỏi sự chính xác. Đây là những gì tôi luôn lưu ý khi quản lý các cụm database production:

Khớp chính xác các truy vấn của bạn: Để một partial index hoạt động, mệnh đề WHERE trong truy vấn của bạn phải là một tập hợp con của mệnh đề trong index. Nếu bạn đánh index WHERE status = 'shipped', một truy vấn WHERE status = 'pending' sẽ quay lại việc quét tuần tự chậm chạp.
Kiểm tra tính biến động của hàm (function volatility): Bạn chỉ có thể đánh index cho các hàm IMMUTABLE. Đây là những hàm luôn trả về cùng một kết quả cho cùng một đầu vào, chẳng hạn như LOWER() hoặc UPPER(). Bạn không thể đánh index cho now() vì giá trị của nó thay đổi theo từng mili giây.
Theo dõi việc sử dụng: Sử dụng pg_stat_user_indexes để tìm các index “chết”. Không có lý do gì để phải chịu hình phạt về hiệu suất ghi cho một partial index phức tạp nếu bộ lập kế hoạch truy vấn không bao giờ chọn nó.
Kết hợp linh hoạt: Bạn có thể kết hợp các tính năng này. Một index LOWER(email) chỉ bao gồm WHERE is_verified IS TRUE sẽ mang lại hiệu quả cực kỳ cao.

Đo lường kết quả

Đừng đoán mò — hãy đo lường. Luôn chạy EXPLAIN ANALYZE trước và sau khi thực hiện các thay đổi. Bạn cần thấy dòng “Index Scan” trong kết quả đầu ra và chỉ số “execution time” giảm đáng kể.

EXPLAIN ANALYZE 
SELECT * FROM users 
WHERE is_active IS TRUE AND username = 'backend_pro';

Nếu bộ lập kế hoạch vẫn không sử dụng index mới của bạn, hãy chạy ANALYZE users; để cập nhật lại số liệu thống kê của bảng. Việc này thường sẽ điều hướng bộ tối ưu hóa đi đúng hướng.

Lời kết

PostgreSQL cho phép bạn thực hiện các tối ưu hóa một cách chính xác như phẫu thuật. Partial index xử lý quy mô dữ liệu, trong khi functional index xử lý sự phức tạp của các truy vấn. Bằng cách áp dụng chúng có chọn lọc, bạn có thể giữ cho cơ sở dữ liệu luôn nhanh nhạy và chi phí hạ tầng ở mức thấp khi dữ liệu tăng trưởng. Đừng chỉ đánh index mọi thứ — hãy đánh index có mục đích.