vi
new
0
Avatar
HUy Hoàng @hathile
10 0 25
Đã đăng vào khoảng 7 giờ trước 8 phút đọc
7

PostgreSQL Bài 24: Kỹ thuật Partial Index và Expression-based Index: Tối ưu dung lượng và tốc độ

Thông thường, khi bạn chạy lệnh CREATE INDEX idx_name ON table_name (column_name);, PostgreSQL sẽ quét qua 100% các dòng trong bảng để đưa toàn bộ giá trị của cột đó vào cấu trúc cây chỉ mục.

Tuy nhiên, trong các hệ thống sản xuất thực tế:

  • Có những bảng lưu hàng chục triệu bản ghi, nhưng ứng dụng chỉ thường xuyên truy vấn một phần nhỏ dữ liệu (ví dụ: chỉ tìm các đơn hàng chưa xử lý status = 'UNPROCESSED', chiếm 1% bảng).

  • Có những câu truy vấn bắt buộc phải dùng các hàm biến đổi dữ liệu (ví dụ: LOWER(email), DATE(created_at)), khiến các B-Tree Index thông thường bị vô hiệu hóa hoàn toàn.

PostgreSQL giải quyết hai bài toán này bằng hai kỹ thuật: Partial Index (Chỉ mục một phần) và Expression-based Index (Chỉ mục dựa trên biểu thức).

1. Partial Index (Chỉ mục một phần): Nguyên lý và Cơ chế

Một Partial Index là một chỉ mục được xây dựng trên một tập con (subset) của bảng, được lọc thông qua một mệnh đề điều kiện WHERE ngay trong câu lệnh tạo index:

SQL

CREATE INDEX idx_name ON table_name (column_name) 
WHERE condition_expression;


BẢNG DỮ LIỆU ĐƠN HÀNG (10.000.000 dòng):
┌────────────────────────────────────────────────────────┐
│ 9.900.000 dòng mang trạng thái 'COMPLETED' (99%)       │ ──► KHÔNG ĐƯA VÀO INDEX!
├────────────────────────────────────────────────────────┤
│ 100.000 dòng mang trạng thái 'PENDING' / 'FAILED' (1%) │ ──► ĐƯA VÀO PARTIAL INDEX
└────────────────────────────────────────────────────────┘
                                                                │
                                                                ▼
                                                   [ PARTIAL INDEX SIÊU NHẸ ]
                                                   (Chỉ lưu 100.000 mục trên RAM)

1.1. Lợi ích của Partial Index:

  1. Tiết kiệm dung lượng bộ nhớ (RAM & Disk): File index chỉ chứa các dòng thỏa mãn điều kiện WHERE, có thể nhỏ hơn từ 10 đến 100 lần so với Full Index. Càng nhỏ thì càng dễ nằm trọn trong shared_buffers.

  2. Giảm chi phí ghi (INSERT/UPDATE): Khi chèn hoặc cập nhật một dòng có status = 'COMPLETED', PostgreSQL hoàn toàn không cần cập nhật Partial Index. Thao tác ghi diễn ra với tốc độ tối đa và bảo toàn khả năng HOT Update.

  3. Tốc độ đọc vượt trội: Cây B-Tree thấp hơn (ít level hơn), giảm số lượng trang cần duyệt.

2. Kịch bản thực chiến với Partial Index

2.1. Kịch bản 1: Hệ thống hàng đợi (Task Queue / Outbox Pattern)

Mô hình Transactional Outbox rất phổ biến trong kiến trúc Microservices:

  • Các event được ghi vào bảng outbox_events với status = 'PENDING'.

  • Worker quét liên tục để gửi đi, sau đó đổi trạng thái thành 'PROCESSED'.

  • Sau 1 năm, bảng có 50 triệu dòng 'PROCESSED' và chỉ có khoảng 50 dòng 'PENDING' đang chờ xử lý.

SQL

CREATE SCHEMA IF NOT EXISTS partial_lab;

CREATE TABLE partial_lab.outbox_events (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    aggregate_type VARCHAR(50) NOT NULL,
    payload JSONB NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'PENDING',
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

-- SAI LẦM: Tạo full index trên status
-- CREATE INDEX idx_outbox_status ON partial_lab.outbox_events(status); 
-- => Index này sẽ nuốt hàng Gigabytes RAM chỉ để lưu 50 triệu chữ 'PROCESSED'!

-- CHUẨN MỰC: Tạo Partial Index chỉ cho các tác vụ chưa xử lý
CREATE INDEX idx_outbox_pending_events 
ON partial_lab.outbox_events (created_at) 
WHERE status = 'PENDING';

Khi Worker truy vấn:

SQL

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM partial_lab.outbox_events 
WHERE status = 'PENDING' 
ORDER BY created_at ASC 
LIMIT 10;

PostgreSQL sẽ sử dụng ngay idx_outbox_pending_events. Kích thước của index này luôn được duy trì ở mức vài Kilobytes bất kể bảng chính phình to bao nhiêu Gigabytes.

Điều kiện bắt buộc để kích hoạt: Mệnh đề WHERE trong câu lệnh SQL của bạn phải tương thích hoặc hẹp hơn mệnh đề WHERE của Partial Index. Nếu bạn query WHERE status = 'PROCESSED', engine sẽ bỏ qua index này.

2.2. Kịch bản 2: Đảm bảo tính duy nhất có điều kiện (Unique Partial Index)

Thông thường, ràng buộc UNIQUE sẽ ép toàn bộ các dòng không được trùng nhau. Nhưng nghiệp vụ thực tế thường yêu cầu:

  • Một khách hàng có thể có nhiều địa chỉ giao hàng, nhưng chỉ được phép có duy nhất 1 địa chỉ mặc định (is_default = TRUE).

  • Một người dùng có thể xóa tài khoản nhiều lần (Soft Delete: deleted_at IS NOT NULL), nhưng chỉ được có 1 tài khoản đang hoạt động (deleted_at IS NULL) với cùng một email.

B-Tree thông thường không làm được điều này. Giải pháp là Unique Partial Index:

SQL

CREATE TABLE partial_lab.users (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email VARCHAR(255) NOT NULL,
    deleted_at TIMESTAMPTZ DEFAULT NULL
);

-- Ràng buộc: Email chỉ được duy nhất đối với các user ĐANG HOẠT ĐỘNG (chưa bị xóa)
CREATE UNIQUE INDEX uq_active_user_email 
ON partial_lab.users (email) 
WHERE deleted_at IS NULL;

Thử nghiệm:

SQL

-- User đăng ký tài khoản (Thành công)
INSERT INTO partial_lab.users (email) VALUES ('hieu@example.com');

-- User xóa tài khoản (Gán deleted_at)
UPDATE partial_lab.users SET deleted_at = CURRENT_TIMESTAMP WHERE email = 'hieu@example.com';

-- User đăng ký lại bằng chính email đó (Thành công!)
INSERT INTO partial_lab.users (email) VALUES ('hieu@example.com');

-- Cố tình tạo thêm 1 user nữa đang hoạt động cùng email (BỊ CHẶN NGAY LẬP TỨC)
INSERT INTO partial_lab.users (email) VALUES ('hieu@example.com');
-- ERROR: duplicate key value violates unique constraint "uq_active_user_email"

3. Expression-based Index (Chỉ mục trên biểu thức)

3.1. Bẫy vô hiệu hóa Index khi dùng hàm trong mệnh đề WHERE

Xét câu lệnh tìm kiếm người dùng không phân biệt hoa thường:

SQL

SELECT * FROM partial_lab.users WHERE LOWER(email) = 'hieu@example.com';

Nếu bạn chỉ tạo index thông thường trên cột email:

SQL

CREATE INDEX idx_users_email ON partial_lab.users(email);

Hậu quả: PostgreSQL bắt buộc phải chạy Sequential Scan toàn bảng!

Lý do: Cây B-Tree chỉ lưu trữ giá trị gốc của chuỗi email (ví dụ: 'Hieu@Example.COM'). Nó không biết kết quả sau khi chạy qua hàm LOWER() sẽ là gì. Với mỗi dòng, engine buộc phải gọi hàm LOWER() để tính toán rồi mới so sánh.

3.2. Cú pháp và Bản chất của Expression-based Index

PostgreSQL cho phép bạn lập chỉ mục trên kết quả tính toán của một hàm hoặc một biểu thức logic:

SQL

CREATE INDEX idx_name ON table_name ( (expression) );

(Lưu ý: Biểu thức bắt buộc phải được bọc trong cặp ngoặc đơn phụ ((...))).

SQL

-- Tạo Index trên kết quả của hàm LOWER(email):
CREATE INDEX idx_users_lower_email ON partial_lab.users ( (LOWER(email)) );


CƠ CHẾ HOẠT ĐỘNG:
Khi INSERT 'Hieu@Example.com':
  ├── Heap Table lưu nguyên bản: 'Hieu@Example.com'
  └── B-Tree Index tính toán và lưu: 'hieu@example.com' ──► ctid (0, 1)

Khi SELECT ... WHERE LOWER(email) = 'hieu@example.com':
  └── Engine tìm kiếm trực tiếp giá trị 'hieu@example.com' trên B-Tree!

4. Các ứng dụng thực tế phổ biến của Expression Index

4.1. Lọc theo ngày từ cột Timestamp (DATE(timestamptz))

Nhiều lập trình viên viết:

SQL

SELECT * FROM orders WHERE created_at::DATE = '2026-10-09';

Câu lệnh này sẽ bỏ qua index trên created_at. Để tối ưu:

SQL

-- Đánh index trên biểu thức ép kiểu ngày (chuyển timezone về mốc mong muốn)
CREATE INDEX idx_orders_created_date 
ON orders ( (DATE(created_at AT TIME ZONE 'Asia/Ho_Chi_Minh')) );

(Mẹo tối ưu hơn nếu không muốn tạo index biểu thức: Hãy viết lại câu query theo dạng khoảng: WHERE created_at >= '2026-10-09 00:00:00+07' AND created_at < '2026-10-10 00:00:00+07' để tận dụng B-Tree Index thường).

4.2. Bóc tách trường JSONB thường xuyên truy vấn

Nếu bạn có một cột attributes JSONB và thường xuyên lọc theo mã khách hàng VIP attributes ->> 'vip_tier', thay vì đánh GIN Index nặng nề, hãy dùng Expression Index:

SQL

CREATE INDEX idx_customers_vip_tier 
ON customers ( ((attributes ->> 'vip_tier')) );

-- Kích hoạt Index Scan ngay lập tức:
SELECT * FROM customers WHERE (attributes ->> 'vip_tier') = 'PLATINUM';

4.3. Tìm kiếm không dấu với Extension unaccent

Để tìm kiếm tiếng Việt không phân biệt dấu ("Nguyễn" khớp với "Nguyen"):

SQL

CREATE EXTENSION IF NOT EXISTS unaccent;

-- Tạo index trên chuỗi đã loại bỏ dấu tiếng Việt và chữ hoa:
CREATE INDEX idx_products_search_name 
ON products ( (LOWER(unaccent(name))) );

-- Query kích hoạt Index:
SELECT * FROM products 
WHERE LOWER(unaccent(name)) = LOWER(unaccent('Bàn phím cơ'));

5. Quy tắc sống còn: Tính chất Bất biến (IMMUTABLE) của Hàm

Khi tạo một Expression-based Index, PostgreSQL đặt ra một yêu cầu kỹ thuật bắt buộc:

Hàm hoặc biểu thức được dùng trong Index BẮT BUỘC PHẢI LÀ IMMUTABLE (Bất biến).

Trong PostgreSQL, các hàm được phân loại theo 3 mức độ ổn định (Volatility):

  1. VOLATILE: Kết quả thay đổi liên tục ngay cả trong cùng 1 câu lệnh (ví dụ: random(), clock_timestamp()).

  2. STABLE: Kết quả cố định trong suốt 1 transaction, nhưng thay đổi giữa các transaction (ví dụ: CURRENT_TIMESTAMP, now()).

  3. IMMUTABLE: Với cùng một tham số đầu vào, hàm luôn luôn trả về duy nhất một kết quả như nhau trong mọi thời điểm và mọi môi trường (ví dụ: LOWER(), MD5(), SQRT()).

Tại sao?

Nếu một hàm không phải là IMMUTABLE, kết quả của nó có thể thay đổi sau khi index đã được ghi xuống đĩa. Cây B-Tree sẽ bị sai lệch vị trí định tuyến và phá vỡ tính đúng đắn của dữ liệu.

SQL

-- LỆNH NÀY SẼ NÉM LỖI NGAY LẬP TỨC:
CREATE INDEX idx_bad ON orders ( (created_at + now()) );
-- ERROR: functions in index expression must be marked IMMUTABLE

6. Bảng tổng hợp so sánh

Tiêu chí Full Index thông thường Partial Index Expression-based Index
Phạm vi lập chỉ mục 100% các dòng trong bảng Chỉ các dòng thỏa điều kiện WHERE 100% các dòng (hoặc kết hợp với Partial)
Giá trị lưu trong Index Giá trị cột thô Giá trị cột thô Kết quả sau khi tính toán biểu thức/hàm
Dung lượng Index Lớn Nhỏ nhất Phụ thuộc vào kích thước kết quả trả về của hàm
Chi phí ghi (INSERT/UPDATE) Cập nhật trên mọi thao tác Không tốn chi phí nếu dòng nằm ngoài điều kiện WHERE Tốn CPU để tính toán biểu thức trước khi ghi vào cây
Kịch bản lý tưởng Cột được query đồng đều mọi giá trị Dữ liệu bị lệch (Skewed), Task Queue, Unique Soft Delete Hàm biến đổi chuỗi (LOWER, unaccent), trích xuất JSONB

7. Tóm tắt & Bài tiếp theo

  • Partial Index giúp thu nhỏ dung lượng RAM và tăng tốc độ ghi bằng cách loại bỏ các tập dữ liệu không quan trọng ra khỏi cây chỉ mục qua mệnh đề WHERE.

  • Sử dụng Partial Index kết hợp UNIQUE để tạo các ràng buộc duy nhất có điều kiện (như Soft Delete, Default Flag).

  • Expression-based Index giải quyết bài toán tìm kiếm khi mệnh đề WHERE chứa các hàm xử lý tính toán.

  • Hàm dùng trong Expression Index bắt buộc phải mang tính chất IMMUTABLE.

Bài 25 xem tiếp: Đọc và phân tích Execution Plan bằng EXPLAIN (ANALYZE, BUFFERS, VERBOSE) — chúng ta sẽ học cách giải mã các báo cáo chi phí của Query Planner: phân biệt Startup Cost vs Total Cost, theo dõi số lượng block đọc từ RAM vs Disk (Buffers: shared hit, read), và nhận diện chính xác các điểm nghẽn làm chậm truy vấn.

PostgreSQL

All rights reserved

Mục lục


Viblo
Hãy đăng ký một tài khoản Viblo để nhận được nhiều bài viết thú vị hơn.
Đăng kí