char(5) bảo 'ab ' bằng 'ab', text bảo khác nhau — và đổi text sang varchar(15) khoá viết lại cả bảng một triệu dòng

Nhớ những tờ khai có dãy ô vuông, mỗi ô điền đúng một chữ cái? Ô kiểu đó cố định số lượng: tên bạn ngắn thì mấy ô cuối bỏ trống, nhưng chúng vẫn chiếm chỗ trên tờ giấy. Và nếu hai người cùng điền "AB" rồi để trống ba ô sau, người ta coi hai tờ là giống nhau — mấy ô trống ở cuối không được tính. Ngược lại, một dòng kẻ để bạn viết tự do thì co đúng theo chữ bạn viết, và "AB" khác hẳn "AB␣␣␣". Trong PostgreSQL, char(n) chính là dãy ô vuông cố định ấy, còn text/varchar là dòng kẻ tự do — và sự khác biệt đó không nằm ở kích thước mà ở cách so sánh. Tôi nạp một triệu chuỗi 10 ký tự vào bốn kiểu rồi đo cho rõ.

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ự. Đúng như dãy ô vuông: mấy ô cuối bỏ trống vẫn chiếm giấy.

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 — đúng cái luật "mấy ô trống cuối tờ khai không tính". 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.

Tự soi schema của bạn

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ì.

Mẫu số chung

Bài học đầu tiên, chính là dãy ô vuông trên tờ khai: chọn một kiểu dữ liệu không chỉ là chọn chỗ chứa, mà là chọn luôn hành vi — nhất là cách hai giá trị được coi là bằng nhau hay không. char(n) và text chứa cùng chữ "ab" nhưng 'ab ' = 'ab' cho ra true ở kiểu này và false ở kiểu kia; đổi kiểu là lặng lẽ đổi kết quả truy vấn. Cùng cái bẫy "bằng nhau tuỳ kiểu/luật" ở khắp nơi: trong Java, "ab" == "ab" so danh tính ô nhớ còn .equals mới so nội dung — hai chữ giống hệt vẫn có thể == ra false; so hai double bằng == sau vài phép cộng cũng phản bội bạn vì sai số; trong Go, một string và một []byte cùng byte không so thẳng được bằng ==; ngay trong CSDL, cùng cột text nhưng đổi collation là đổi cả thứ tự sắp xếp lẫn việc 'e' = 'é'. Nguyên tắc: trước khi chốt một kiểu (hay một collation, một locale), hỏi thẳng "hai giá trị bằng nhau và dài bao nhiêu được định nghĩa thế nào ở đây", vì đó mới là thứ quyết định hành vi lâu dài — kích thước byte chỉ là bề nổi.

Điều thứ hai, đọc từ bảng "nới miễn phí, thu hẹp khoá bảng": nới lỏng một ràng buộc thường rẻ và chỉ đụng siêu dữ liệu, còn siết chặt lại luôn bắt kiểm toàn bộ dữ liệu đang có — nên hãy mặc định chọn cái rộng nhất hợp lý để không bao giờ phải chạy cuộc di trú siết chặt tốn kém dưới ổ khoá. text → varchar(15) viết lại cả triệu dòng và khoá ACCESS EXCLUSIVE; chiều ngược lại chỉ 0,07 giây. Cùng sự bất đối xứng "lỏng rẻ, chặt đắt" ở khắp nơi: thêm một cột NULL được thì tức thì, nhưng thêm NOT NULL vào bảng đang có dữ liệu buộc PostgreSQL quét từng dòng (và từ chối nếu có dòng rỗng); thêm một giá trị vào enum thì dễ, bỏ bớt thì phải soát mọi bản ghi đang dùng; nới một hợp đồng API (thêm trường tuỳ chọn) không phá ai, siết nó (bắt buộc thêm điều kiện) làm gãy mọi client cũ. Nguyên tắc: khi thiết kế, nghiêng về phía rộng và lỏng ở những chỗ bạn chưa chắc — mở luôn dễ hơn thu; và mỗi lần buộc phải siết, hãy lường trước một lượt quét toàn bảng cùng một cửa sổ bảo trì, đừng chạy nó vô tư giữa giờ cao điểm.

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.