text, varchar và varchar(20) giống nhau tới từng byte — còn char(20) đổi cả kết quả truy vấn

Câu hỏi này xuất hiện trong mọi lần review schema: dùng text hay varchar(255)? Tôi nạp một triệu chuỗi 10 ký tự vào bốn kiểu rồi đo.

Ba kiểu đầu giống nhau tới từng byte

text, varchar và varchar(n) không khác nhau ở bất cứ con số nào. Trong PostgreSQL chúng dùng chung một biểu diễn lưu trữ; varchar(n) chỉ thêm một ràng buộc kiểm tra độ dài.

Và ràng buộc đó gần như miễn phí. Chèn 500.000 dòng:

Không đo được chênh lệch. Nên lập luận "dùng text cho nhanh" không có cơ sở, mà lập luận "varchar(n) tốn thêm để kiểm tra" cũng không.

char(20) thì tốn thêm 19% dung lượng vì nó đệm khoảng trắng cho đủ 20 ký tự — 24 byte cho một chuỗi 10 ký tự.

Cái làm char(n) nguy hiểm không phải kích thước

SELECT 'ab   '::char(5) = 'ab'::char(5);   -- true
SELECT 'ab   '::text    = 'ab'::text;      -- false

char(n) bỏ qua khoảng trắng cuối khi so sánh. Đó là quy định của chuẩn SQL, không phải lỗi PostgreSQL. Nhưng nó nghĩa là:

SELECT length('ab'::char(5));        -- 2
SELECT octet_length('ab'::char(5));  -- 5

Chuỗi dài 2 theo length nhưng chiếm 5 byte theo octet_length. Hai hàm nói hai chuyện khác nhau về cùng một giá trị.

Hệ quả thực dụng: đổi một cột từ char(n) sang text làm đổi kết quả truy vấn, không chỉ đổi kích thước. Những dòng trước đây khớp nhau vì hệ thống bỏ qua khoảng trắng cuối, sau khi đổi sẽ không khớp nữa. Nếu bạn có cột char(n) chứa mã sản phẩm hay mã vùng, kiểm tra kỹ trước khi đổi.

Và ngược lại, dữ liệu nhập từ hệ thống cũ có khoảng trắng thừa sẽ trông như sạch trong char(n) rồi lộ ra khi bạn chuyển sang text.

Đổi kiểu về sau: cái nào khoá bảng

filenode đổi nghĩa là PostgreSQL đã viết lại toàn bộ bảng — và trong suốt thời gian đó bảng bị khoá ở mức ACCESS EXCLUSIVE, không ai đọc được.

Nới rộng giới hạn thì miễn phí. Từ PostgreSQL 9.2, tăng n của varchar(n), hoặc bỏ giới hạn để thành text, chỉ sửa siêu dữ liệu — dữ liệu trên đĩa không đụng tới.

Thu hẹp thì phải viết lại, vì PostgreSQL cần kiểm mọi dòng. Trên bảng một triệu dòng của tôi là 0,39 giây; trên bảng trăm triệu dòng đó là hàng phút khoá bảng trong giờ làm việc.

Đây là lập luận thực tế nhất cho text: nó là hướng rộng nhất, nên bạn không bao giờ phải đi ngược.

Vậy chọn cái nào

Dùng text theo mặc định. Không tốn hơn, không chậm hơn, và không bao giờ bắt bạn viết lại bảng để nới giới hạn.

Dùng varchar(n) khi n là một quy tắc nghiệp vụ thật, ví dụ mã bưu chính đúng 5 ký tự, mã tiền tệ ISO đúng 3. Khi đó ràng buộc đó có ý nghĩa và bạn muốn CSDL bắt lỗi giúp.

Đừng dùng char(n). Nó tốn thêm chỗ, và ngữ nghĩa so sánh khác biệt của nó là loại bất ngờ xuất hiện sau nhiều tháng. Ngay cả với dữ liệu thật sự cố định độ dài, text với một CHECK rõ ràng hơn:

ma_tien_te text CHECK (length(ma_tien_te) = 3)

Một lưu ý về con số varchar(255) mà mọi người hay gõ theo phản xạ: nó đến từ MySQL, nơi 255 là ngưỡng để độ dài vừa trong một byte. PostgreSQL không có ngưỡng đó — 255 không có ý nghĩa gì đặc biệt ở đây.

Thử ba mươi giây

Tìm mọi cột char(n) trong CSDL của bạn:

SELECT table_name, column_name, character_maximum_length
FROM information_schema.columns
WHERE data_type = 'character'
ORDER BY table_name;

Mỗi dòng in ra là một cột đang đệm khoảng trắng và đang so sánh theo quy tắc khác với phần còn lại của schema.

Và kiểm xem đổi kiểu có viết lại bảng không, trước khi chạy trên máy thật:

SELECT pg_relation_filenode('ten_bang');
ALTER TABLE ten_bang ALTER COLUMN cot TYPE text;
SELECT pg_relation_filenode('ten_bang');

Hai con số giống nhau là an toàn chạy bất cứ lúc nào. Khác nhau nghĩa là bảng vừa bị viết lại — hãy hẹn giờ bảo trì.

Phần sau đo numeric và float: sai số của từng kiểu, và cái giá phải trả cho độ chính xác.