Ràng buộc: CHECK, UNIQUE, FOREIGN KEY

Ràng buộc là cách rẻ nhất để dữ liệu không bị hỏng, nhưng "rẻ" là một từ định tính. Phần này gán số cho nó — mỗi loại ràng buộc lấy đi bao nhiêu thời gian, bao nhiêu WAL, bao nhiêu đĩa.

Chèn một triệu dòng

Cùng một bảng, cùng dữ liệu, mỗi lần thêm đúng một loại ràng buộc:

Ba nhóm rõ rệt.

NOT NULL và CHECK miễn phí. Chênh lệch 0,89–0,96 giây nằm trong dải nhiễu của lần đo không ràng buộc. Chúng chỉ là một phép kiểm trên giá trị đã nằm sẵn trong bộ nhớ, không đọc thêm gì, không ghi thêm gì.

Đây là điều đáng nhớ nhất trong bài: nếu bạn từng ngần ngại thêm CHECK vì sợ chậm, đừng. Không có lý do hiệu năng nào để bỏ qua CHECK và NOT NULL.

PRIMARY KEY và UNIQUE đắt vừa phải, và cái giá của chúng là cái giá của một chỉ mục — 21 MB và 50 MB. Con số này không có gì bất ngờ, và nó là chi phí bạn thường sẵn sàng trả vì chỉ mục đó cũng phục vụ truy vấn.

FOREIGN KEY đắt nhất mà không sinh ra chỉ mục nào. 5,67 giây so với 0,92 — chậm 6,2 lần. Với mỗi dòng chèn vào, PostgreSQL phải tra bảng cha xem giá trị đó có tồn tại không. Một triệu dòng là một triệu lần tra.

Chú ý WAL của khoá ngoại chỉ 94 MB — gần bằng bảng không ràng buộc. Chi phí của nó nằm ở đọc, không nằm ở ghi. Đó là lý do nó không hiện ra trong các chỉ số theo dõi ghi.

Cái bẫy lớn nhất: PostgreSQL không tự dựng chỉ mục cho khoá ngoại

Khi bạn khai references kh(id), PostgreSQL đòi bảng cha phải có chỉ mục duy nhất trên id — nhưng nó không dựng chỉ mục nào trên kh_id của bảng con.

Hậu quả lộ ra khi xoá dòng cha. Bảng con 2.000.000 dòng, on delete cascade:

Nhanh khoảng 120 lần.

Không có chỉ mục, mỗi lần xoá một dòng cha là một lần quét toàn bộ bảng con để tìm dòng liên quan. Với bảng con vài chục triệu dòng, một câu delete from kh where id = ? tưởng chừng tức thì sẽ mất hàng giây — và nếu bạn xoá 1.000 dòng cha thì đó là 1.000 lần quét toàn bảng.

Điều tương tự áp cho UPDATE trên khoá chính của bảng cha, và cho ON DELETE SET NULL.

Tìm những khoá ngoại đang thiếu chỉ mục:

select c.conrelid::regclass as bang_con,
       a.attname as cot,
       c.confrelid::regclass as bang_cha
from pg_constraint c
join pg_attribute a on a.attrelid = c.conrelid and a.attnum = c.conkey[1]
where c.contype = 'f'
  and not exists (
    select 1 from pg_index i
    where i.indrelid = c.conrelid and i.indkey[0] = c.conkey[1]
  );

Mỗi dòng trả về là một khoá ngoại chưa có chỉ mục. Không phải cái nào cũng cần — nếu bảng cha không bao giờ bị xoá dòng thì không sao — nhưng bạn nên biết chúng tồn tại.

CHECK không chặn NULL

create table n(id int, gia numeric, check (gia >= 0));

insert into n values (1, -5);      -- ERROR: violates check constraint
insert into n values (2, null);    -- thành công

Dòng thứ hai lọt qua.

null >= 0 cho ra UNKNOWN, không phải FALSE. Và CHECK chỉ từ chối khi biểu thức cho ra FALSE — UNKNOWN được coi là chấp nhận được. Đây là hệ quả trực tiếp của logic ba trị đã đo ở phần 9.

Nghĩa là check (gia >= 0) không bảo đảm "mọi giá đều không âm". Nó bảo đảm "không có giá nào âm, nhưng có thể có giá không xác định".

Muốn chặn cả hai thì phải viết cả hai:

create table n(id int, gia numeric not null, check (gia >= 0));

Hoặc gộp vào chính CHECK:

check (gia is not null and gia >= 0)

Cách thứ nhất tốt hơn vì NOT NULL được bộ lập lịch dùng để tối ưu, còn CHECK thì không.

Thêm ràng buộc vào bảng đang chạy

ALTER TABLE ADD CONSTRAINT phải quét toàn bảng để kiểm dữ liệu cũ, và nó giữ khoá ACCESS EXCLUSIVE suốt thời gian đó — mọi truy vấn chạm vào bảng đều phải đợi.

Trên bảng 3 triệu dòng thì chỉ 0,17 giây, không sao. Trên bảng 300 triệu dòng thì đó là nhiều phút ngừng dịch vụ.

Cách tách làm hai bước:

-- Bước 1: tức thì, chỉ áp cho dòng mới
alter table big add constraint c1 check (gia >= 0) not valid;   -- 0,072 s

-- Bước 2: quét bảng, nhưng khoá nhẹ hơn nhiều
alter table big validate constraint c1;                          -- 0,16 s

Bước 1 chỉ ghi định nghĩa ràng buộc vào catalog và không kiểm dữ liệu cũ. Từ giây đó, mọi dòng mới và mọi lần cập nhật đều phải thoả ràng buộc — nên dữ liệu không xấu thêm.

Bước 2 quét bảng để xác nhận dữ liệu cũ cũng thoả, nhưng nó chỉ giữ SHARE UPDATE EXCLUSIVE — đọc và ghi vẫn chạy bình thường.

Cách này áp được cho cả CHECK lẫn FOREIGN KEY, và nó là cách duy nhất thêm ràng buộc vào bảng lớn mà không ngừng dịch vụ.

Nếu bước 2 báo lỗi vì dữ liệu cũ vi phạm, ràng buộc vẫn ở trạng thái NOT VALID — vẫn chặn dòng mới. Bạn có thời gian dọn dữ liệu cũ rồi chạy lại VALIDATE.

Bảng chọn nhanh

Dòng cuối là dòng duy nhất cần cân nhắc. 516% nghe rất nhiều, nhưng con số tuyệt đối là 5,67 giây cho một triệu dòng — tức 5,7 micro giây mỗi dòng. Với ứng dụng chèn vài trăm dòng mỗi giây, chi phí đó vô hình.

Nó chỉ thành vấn đề ở hai chỗ: nạp dữ liệu hàng loạt (và khi ấy hãy bỏ khoá ngoại, nạp, rồi thêm lại bằng NOT VALID + VALIDATE), và bảng có tần suất ghi rất cao.

Phần 35 đã đo mặt còn lại của lựa chọn này: dùng mảng thay bảng phụ thì tiết kiệm được 4 lần dung lượng, nhưng mất hẳn khả năng đặt khoá ngoại — và dữ liệu mồ côi tích lại mà không có gì phát hiện.

Thử ba mươi giây

Chạy câu tìm khoá ngoại thiếu chỉ mục ở trên. Rồi với mỗi kết quả, hỏi: bảng cha này có bao giờ bị xoá dòng không?

Nếu có, dựng chỉ mục. 15 MB đổi lấy 120 lần tốc độ là đánh đổi dễ.

Và kiểm xem có ràng buộc nào đang ở trạng thái chưa xác nhận:

select conrelid::regclass, conname, contype
from pg_constraint where not convalidated;

Ràng buộc NOT VALID chặn được dòng mới nhưng bộ lập lịch không dùng nó để tối ưu truy vấn. Nếu bạn thêm nó rồi quên chạy VALIDATE, bạn mất một nửa lợi ích mà không biết.

Phần sau đo trigger: chi phí thật của mỗi lần kích hoạt, và ba chỗ nó gây bất ngờ.