Index trên bảng phân vùng PostgreSQL: lan xuống, unique phải chứa khóa, và dựng không khóa

Index trên bảng phân vùng có vài quy tắc riêng mà nếu không nắm sẽ vấp lỗi khó hiểu hoặc khóa cả bảng lúc dựng index. Bài này đo thật ba điều quan trọng nhất: index trên bảng cha lan xuống thế nào, vì sao unique/primary key bắt buộc chứa khóa phân vùng, và cách dựng index trên bảng phân vùng lớn mà không khóa dài.

Index trên bảng cha lan xuống mọi partition

Tạo index trên bảng cha phân vùng sẽ tạo một "partitioned index" — tự động tạo index tương ứng ở mọi partition con, và các partition thêm sau cũng tự thừa kế:

CREATE INDEX idx_sales_kh ON sales(kh);

Đo thật cho thấy index này xuất hiện ở cả bảng cha (sales — chỉ là định nghĩa, không lưu trữ vật lý) lẫn từng partition (sales_q1_kh_idx, sales_q2_kh_idx, ...). Bạn quản lý index ở một chỗ (bảng cha), PostgreSQL lo phần còn lại — kể cả cho partition tương lai.

Hình 1: CREATE INDEX trên bảng cha lan xuống mọi partition (và partition mới tự thừa kế). Unique/PK phải chứa khóa phân vùng. Dựng index không khóa dài bằng ON ONLY + CREATE INDEX CONCURRENTLY từng vùng + ATTACH.

Đo thật: unique phải chứa khóa phân vùng

Đây là quy tắc hay khiến người mới vấp. Một ràng buộc UNIQUE (hoặc PRIMARY KEY) trên bảng phân vùng bắt buộc phải chứa mọi cột của khóa phân vùng:

Hình 2: Index cha lan xuống 4 partition. UNIQUE (id) thiếu cột ngay → lỗi "must include all partitioning columns". UNIQUE (id, ngay) OK; chèn trùng bị chặn ở mức partition (sales_q1_id_ngay_key). Partition mới sales_q4 tự có 2 index. ON ONLY index INVALID cho tới khi ATTACH đủ vùng.

• UNIQUE (id) (thiếu cột phân vùng ngay): ERROR: unique constraint on partitioned table must include all partitioning columns — DETAIL nói rõ thiếu cột ngay.
• UNIQUE (id, ngay) (có khóa phân vùng): OK. Chèn trùng (1, '2025-02-01') bị chặn: ERROR: duplicate key value violates unique constraint "sales_q1_id_ngay_key" — chú ý ràng buộc được thực thi ở mức partition (sales_q1).

Vì sao có quy tắc này? PostgreSQL thực thi unique trong từng partition riêng biệt (không có index unique toàn cục xuyên vùng). Nếu khóa unique không chứa khóa phân vùng, hai dòng trùng khóa unique có thể rơi vào hai partition khác nhau và PostgreSQL không phát hiện được. Bắt buộc chứa khóa phân vùng đảm bảo các dòng cùng khóa unique luôn ở cùng một vùng để so sánh.

Dựng index không khóa dài

CREATE INDEX ON sales(...) bình thường lấy khóa ACCESS EXCLUSIVE trên từng partition khi dựng — chặn truy vấn. Và CREATE INDEX CONCURRENTLY không dùng trực tiếp được trên bảng cha phân vùng. Cách chuẩn để dựng index trên bảng phân vùng lớn mà không khóa dài:

CREATE INDEX idx_tien ON ONLY sales(tien);          -- 1. chỉ cha, index INVALID
CREATE INDEX CONCURRENTLY idx_q1 ON sales_q1(tien); -- 2. từng vùng, KHÔNG khóa dài
ALTER INDEX idx_tien ATTACH PARTITION idx_q1;       -- 3. gắn vào cha
-- ...lặp cho mọi vùng => index cha thành VALID.

Đo thật: sau CREATE INDEX ON ONLY, index cha có indisvalid = f (chưa hợp lệ vì chưa gắn vùng nào); sau khi tạo CONCURRENTLY từng vùng và ATTACH đủ 4 vùng, index cha thành indisvalid = true. ON ONLY là chìa khóa — nó không đệ quy xuống các vùng nên không khóa chúng; phần dựng thật ở từng vùng dùng CONCURRENTLY (nhanh, không khóa dài).

Đánh đổi cần cân nhắc

Index toàn cục xuyên vùng không tồn tại. PostgreSQL không có unique index bao trùm mọi partition. Nếu ứng dụng cần đảm bảo unique trên một cột không phải khóa phân vùng (ví dụ email khi phân vùng theo ngày), bạn không thể ép ở tầng database phân vùng — phải xử lý ở tầng ứng dụng, hoặc chọn khóa phân vùng khác, hoặc không phân vùng.

Dựng index đồng loạt trên bảng phân vùng lớn vẫn tốn thời gian tổng. Cách ON ONLY + CONCURRENTLY tránh khóa dài, nhưng tổng thời gian dựng vẫn là tổng của mọi vùng — và CONCURRENTLY chậm hơn CREATE INDEX thường (quét bảng hai lần). Lên lịch vào giờ thấp điểm và làm từng vùng để kiểm soát tải.

Mỗi index nhân theo số partition. Một index trên bảng phân vùng thành N index vật lý (một cho mỗi vùng). Đừng đánh index thừa: chi phí ghi và bộ nhớ nhân lên theo số vùng. Chỉ giữ những index thật sự phục vụ truy vấn.

Ba ý mang về

1. Index trên bảng cha lan xuống mọi partition và partition mới tự thừa kế: đo thật, CREATE INDEX ON sales(kh) tạo index ở cả cha lẫn từng vùng con, và partition thêm sau (sales_q4) tự có đủ index — quản lý ở một chỗ.
2. UNIQUE/PRIMARY KEY bắt buộc chứa khóa phân vùng: đo thật, UNIQUE (id) thiếu cột ngay báo lỗi, UNIQUE (id, ngay) mới OK — vì unique thực thi ở mức từng partition, không có index unique toàn cục xuyên vùng.
3. Dựng index không khóa dài bằng ON ONLY + CONCURRENTLY + ATTACH: đo thật, index cha INVALID cho tới khi ATTACH đủ mọi vùng rồi thành VALID — tránh ACCESS EXCLUSIVE trên bảng lớn, nhưng tổng thời gian dựng vẫn là tổng mọi vùng.

Phần sau ta tổng hợp mọi chiến lược cho bảng cực lớn: Phần sau bàn chiến lược lưu trữ cho bảng rất lớn — kết hợp phân vùng, index, nén, TOAST và tablespace để bảng hàng tỉ dòng vẫn vận hành được.