Mảng và kiểu tổng hợp

PostgreSQL cho phép một cột chứa cả một mảng, hoặc cả một bản ghi con. Cả hai đều tránh được một bảng phụ và một phép nối. Phần này đo cái giá của việc tránh đó — bằng bài toán quen thuộc nhất: gắn thẻ cho bài viết.

Bố trí

1.000.000 bài viết, 500 thẻ, trung bình 2,99 thẻ mỗi bài. Hai cách lưu:

-- Cách 1: mảng
create table bv_mang(id bigserial primary key, tieu_de text, the_ids int[]);
create index bv_mang_gin on bv_mang using gin(the_ids);

-- Cách 2: bảng phụ
create table bv_bang(id bigserial primary key, tieu_de text);
create table bv_the(bv_id bigint, the_id int, primary key(bv_id, the_id));
create index bv_the_the on bv_the(the_id, bv_id);

Kết quả

Mảng nhỏ hơn 4 lần. Bảng phụ chứa 3 triệu dòng, mỗi dòng có phần đầu 24 byte của MVCC (phần 21) cộng khoá chính cộng chỉ mục — trong khi mảng nhét ba số nguyên vào chính dòng bài viết.

Nhưng tra một thẻ thì bảng phụ nhanh hơn 57 lần, và lý do nằm ở cột "trang phải đọc": 19 so với 4.008. Chỉ mục (the_id, bv_id) chứa đủ thông tin để trả lời mà không chạm vào bảng — Index Only Scan lần nữa (phần 15). Chỉ mục GIN trên mảng chỉ cho biết dòng nào có thẻ đó, rồi vẫn phải đọc từng dòng ấy từ heap.

Đây là quy luật đã lặp lại ba lần trong sê-ri này — với JSONB ở phần 34, với kiểu tổng hợp ở cuối bài này, và bây giờ với mảng: gói dữ liệu vào một cột thì mất Index Only Scan.

Chiều ngược lại thì mảng thắng rõ: tìm bài có cả hai thẻ mất 3,98 ms so với 17,25 ms. Toán tử @> hỏi chỉ mục GIN đúng một lần cho cả tập; bảng phụ phải làm hai phép tìm rồi giao kết quả.

Chỗ mảng làm truy vấn khó

Đây mới là phần quan trọng, và nó không phải chuyện hiệu năng.

Không có khoá ngoại. Bảng phụ chặn được thẻ không tồn tại:

alter table bv_the add constraint fk foreign key (the_id) references the(id);
insert into bv_the values (1, 99999);
--  ERROR: insert or update on table "bv_the" violates foreign key constraint "fk"

Mảng thì không:

insert into bv_mang(tieu_de, the_ids) values ('Bai loi', array[99999, -5]);
--  chấp nhận, không lỗi
select the_ids from bv_mang where tieu_de = 'Bai loi';
--  {99999,-5}

PostgreSQL không có cú pháp khoá ngoại trỏ từ phần tử mảng. Hệ quả kéo dài: xoá một thẻ khỏi bảng the cũng không dọn được các mảng đang trỏ tới nó, nên dữ liệu mồ côi tích lại theo thời gian mà không có cơ chế nào phát hiện.

Cách chữa duy nhất là kiểm ở tầng ứng dụng, hoặc viết một CHECK gọi hàm — và cả hai đều có thể bị vượt qua bởi một câu UPDATE chạy tay.

Không có ràng buộc ở mức phần tử. Không đặt được not null cho từng phần tử, không đặt được unique để chặn thẻ trùng trong cùng một mảng, không đặt được check riêng cho mỗi phần tử. array[7, 7, 7] là hợp lệ.

Đếm theo nhóm phải unnest. Câu hỏi "mỗi thẻ có bao nhiêu bài" là câu hỏi báo cáo cơ bản nhất:

-- bảng phụ
select the_id, count(*) from bv_the group by the_id;

-- mảng: phải bung mảng ra thành dòng trước
select unnest(the_ids) t, count(*) from bv_mang group by 1;

Bản mảng mất 133,09 ms so với 103,25 ms — không quá tệ, nhưng nó đọc toàn bộ bảng bài viết chứ không chỉ đọc phần thẻ. Với bảng có cột text dài, khoảng cách này giãn ra nhanh.

Nối sang bảng thẻ để lấy tên cũng cần unnest hoặc = any. Câu truy vấn dài hơn, khó đọc hơn, và người viết SQL sau bạn sẽ mất thời gian với nó.

Nhưng thống kê thì tốt hơn tôi tưởng

Tôi vào phép đo này với giả định là bộ lập lịch không hiểu gì về nội dung mảng. Sai:

select n_distinct, most_common_vals is not null
from pg_stats where tablename = 'bv_mang' and attname = 'the_ids';
--  n_distinct = 499,  có bảng giá trị phổ biến

PostgreSQL có một hàm phân tích riêng cho kiểu mảng: nó thu thập thống kê ở mức phần tử, không phải mức mảng. Kết quả:

Mảng ước lượng chính xác hơn bảng phụ ở cả hai trường hợp. Với thẻ phổ biến, bảng phụ đoán 5.643 cho một giá trị thật là 8.000 — vì the_id có 500 giá trị và thống kê một cột chỉ giữ được một số giá trị phổ biến nhất. Mảng thì có bảng giá trị phổ biến riêng cho phần tử và bắt đúng.

Đây là điểm cộng thật của mảng mà tôi không thấy ai nhắc tới.

Kiểu tổng hợp: cùng một vấn đề, nặng hơn

create type dia_chi as (so text, duong text, quan text, tinh text);
create table dc_th(id bigserial primary key, ten text, d dia_chi);

So với bốn cột riêng, trên một triệu dòng:

Chỉ mục trên ((d).tinh) dựng được — cú pháp cần hai lớp ngoặc — nhưng nó không cho Index Only Scan, nên vẫn phải quay lại heap và mất 47 lần thời gian.

Cập nhật một trường cũng phải viết lại cả bản ghi con:

update dc_th set d = row((d).so, (d).duong, (d).quan, 'Tinh moi')::dia_chi where ...;

So với update dc_cot set tinh = 'Tinh moi'. Chênh lệch thời gian nhỏ (0,36 so với 0,30 s) nhưng cú pháp thì rõ ràng tệ hơn, và nếu kiểu có mười trường thì bạn phải liệt kê đủ chín trường không đổi.

Và giống mảng: không đặt được not null hay check cho một trường bên trong bằng cú pháp bình thường.

Tôi không tìm được trường hợp nào kiểu tổng hợp thắng bốn cột riêng trong một bảng. Chỗ nó thật sự hữu ích là làm kiểu trả về của hàm và làm tham số — nơi nó không bị đánh chỉ mục hay ràng buộc.

Bảng chọn nhanh

Với thẻ bài viết cụ thể, tôi sẽ chọn bảng phụ: mất thêm 268 MB nhưng được khoá ngoại, được Index Only Scan cho câu truy vấn phổ biến nhất, và được báo cáo bằng SQL bình thường. 268 MB rẻ hơn nhiều so với việc phát hiện ra dữ liệu mồ côi sau sáu tháng.

Mảng đáng dùng khi tập giá trị không phải là thực thể — danh sách quyền dạng chuỗi cố định, các cờ, các nhãn không có bảng riêng. Lúc đó không có gì để đặt khoá ngoại vào, và lập luận chính chống lại mảng biến mất.

Thử ba mươi giây

Nếu bạn đang có cột mảng, kiểm xem có phần tử mồ côi không:

select distinct e
from <bảng>, unnest(<cột mảng>) e
where e not in (select id from <bảng gốc>);

Câu này trả về những giá trị đang nằm trong mảng mà không tồn tại ở bảng gốc. Trên hệ thống dùng bảng phụ có khoá ngoại, kết quả luôn rỗng và bạn không cần chạy. Trên hệ thống dùng mảng, đây là cách duy nhất biết.

Phần sau đo bảng phân mảnh: cắt bỏ phân vùng khi truy vấn, chi phí quản lý, và ngưỡng số dòng đáng để chia.