Chọn kiểu dữ liệu là quyết định bạn ra trong năm phút đầu của dự án và sống với nó nhiều năm. Tôi đo xem quyết định đó tốn bao nhiêu.

Thứ tự cột, chi phí mỗi dòng, và chọn kiểu cẩu thả so với cẩn thận

Kích thước một giá trị

pg_column_size cho biết một giá trị chiếm bao nhiêu byte:

Kiểu Byte Kiểu Byte
boolean 1 uuid 16
smallint 2 date 4
integer 4 timestamp 8
bigint 8 timestamptz 8
real 4 text 1 ký tự 5
double precision 8 text 10 ký tự 14
numeric(10,2) 8 text 100 ký tự 104
numeric lớn 16

Hai dòng đáng để ý:

timestamptz bằng đúng timestamp — cả hai 8 byte. Múi giờ không được lưu; PostgreSQL quy về UTC rồi đổi lại khi đọc. Nghĩa là dùng timestamptz không tốn thêm gì, và bạn nên dùng nó theo mặc định.

text cộng thêm 4 byte cho phần khai độ dài. Chuỗi một ký tự tốn 5 byte. Với cột chứa mã ngắn như 'VN', 'US', bốn byte đó chiếm phần lớn.

Chi phí cố định mỗi dòng, và nó lớn hơn bạn nghĩ

Ba bảng, mỗi bảng một triệu dòng:

Bảng Dữ liệu thật Kích thước Byte/dòng
Một cột boolean 1 byte 35 MB 36
Một cột integer 4 byte 35 MB 36
Hai cột integer 8 byte 35 MB 36

Cả ba giống hệt nhau. Một byte dữ liệu và tám byte dữ liệu cho cùng một kích thước bảng.

Mỗi dòng PostgreSQL có 23 byte header (thông tin MVCC — phần 21 sẽ đo kỹ), cộng phần căn lề lên bội số của 8, cộng 4 byte con trỏ trong trang. Tổng khoảng 28 byte trước khi bạn lưu được gì.

Hai hệ quả thực dụng:

  • Tám byte dữ liệu đầu tiên gần như miễn phí. Đừng dùng smallint thay integer để "tiết kiệm" nếu nó là cột duy nhất — bạn không tiết kiệm được gì.
  • Chia nhỏ thành nhiều bảng hẹp thì đắt. Tách một cột ra bảng riêng để "chuẩn hoá" nghĩa là bạn trả thêm 28 byte cho mỗi dòng, cộng chi phí JOIN.

Thứ tự cột thay đổi kích thước bảng 21%

Đây là phép đo bất ngờ nhất trong bài. Hai bảng, cùng sáu cột, chỉ khác thứ tự khai:

CREATE TABLE xen_ke   (a smallint, b bigint, c smallint, d bigint, e smallint, f bigint);
CREATE TABLE xep_giam (b bigint, d bigint, f bigint, a smallint, c smallint, e smallint);

Nạp một triệu dòng giống hệt nhau vào cả hai:

Bảng Kích thước Byte/dòng
xen_ke 73 MB 77
xep_giam 57 MB 60

Chênh 16 MB, tức 21,3%, chỉ vì thứ tự khai cột.

Lý do là căn lề: CPU đọc bigint nhanh nhất khi nó nằm ở địa chỉ chia hết cho 8. Mỗi smallint đứng trước một bigint đẩy vị trí lệch đi, và PostgreSQL chèn 6 byte đệm để căn lại. Ba lần như vậy là 18 byte bỏ đi mỗi dòng — khớp gần đúng với 17 byte chênh lệch đo được.

Xếp cột giảm dần theo kích thước thì các khoảng đệm biến mất. Quy tắc gọn: bigint, timestamptz, double trước; rồi integer, date, real; rồi smallint; cuối cùng là boolean và các cột độ dài thay đổi.

Đây là thứ không sửa được về sau mà không viết lại bảng, nên đáng làm đúng từ đầu.

Chọn kiểu cẩu thả so với cẩn thận

Cùng một triệu dòng dữ liệu y hệt, hai cách khai:

-- cau tha: cai gi cung text
CREATE TABLE cau_tha (id text, tao_luc text, gia numeric,
                      dang_ban text, so_luot bigint, ma_vung text);

-- can than
CREATE TABLE can_than (id bigint, tao_luc timestamptz, gia numeric(12,2),
                       so_luot integer, ma_vung smallint, dang_ban boolean);
Kích thước Byte/dòng
cau_tha 96 MB 101
can_than 57 MB 60

Nhỏ hơn 40,1%.

Còn tốc độ thì khiêm tốn hơn nhiều so với những gì người ta hay hứa. Ba lần chạy mỗi câu:

Truy vấn cau_tha can_than
count(*) 13 ms 11–12 ms
sum(so_luot) 18 ms 17–18 ms
Lọc ma_vung = 42 21 ms 12 ms

Hai dòng đầu gần như bằng nhau. Chỉ dòng cuối chênh rõ — 1,6 lần — và nó chênh vì so sánh: so hai smallint là một phép so số nguyên, so hai text phải theo quy tắc đối chiếu chuỗi.

Một lưu ý về phạm vi: hai bảng này nằm gọn trong bộ nhớ đệm của máy tôi, nên phép đo không chạm tới đĩa. Với bảng lớn hơn RAM, chênh lệch 40% kích thước chuyển thẳng thành 40% số trang phải đọc. Ở quy mô nhỏ, 40% đó hiện ra ở chỗ khác: dung lượng sao lưu, băng thông sao chép, và số dòng vừa trong bộ nhớ đệm.

jsonb không phải lúc nào cũng nhỏ hơn json

Tài liệu json jsonb
{"a":1} 11 byte 28 byte
~500 byte, 20 khoá 697 byte 679 byte

Với tài liệu nhỏ, jsonb lớn hơn 2,5 lần vì nó lưu cấu trúc đã phân tích kèm bảng khoá. Chỉ khi tài liệu đủ lớn, việc bỏ khoảng trắng và khử trùng lặp khoá mới bù lại.

Điều đó không có nghĩa là dùng json — jsonb vẫn đúng gần như luôn, vì nó truy vấn được và đánh chỉ mục được (phần 16 sẽ đo GIN). Chỉ là đừng chọn jsonb vì nghĩ nó tiết kiệm chỗ.

Tôi đo sai lần đầu, và cách phát hiện

Lần chạy đầu của phép đo thứ tự cột cho kết quả:

xen_ke     2480 kB    2 byte/dong
xep_giam   1952 kB    1 byte/dong

Hai byte một dòng cho một triệu dòng — bất khả, vì chỉ riêng header đã 23 byte.

Nguyên nhân: tôi nạp generate_series(1,1000000) vào cột smallint, mà smallint tràn ở 32767. Câu INSERT báo ERROR: smallint out of range và không chèn dòng nào, nhưng tôi đã viết >/dev/null sau nó cho gọn.

Cái làm lộ ra là đối chiếu với giới hạn lý thuyết: kích thước một dòng không thể nhỏ hơn header của nó. Đây là lần thứ n trong hai sê-ri liền việc giấu đầu ra làm hỏng một phép đo — và lần nào cũng phát hiện bằng cách hỏi "con số này có khả dĩ không" chứ không phải bằng cách đọc lại lệnh.

Thử ba mươi giây

Xem bảng của bạn mất bao nhiêu vì căn lề:

SELECT a.attname, t.typname, t.typalign, t.typlen
FROM pg_attribute a
JOIN pg_type t ON t.oid = a.atttypid
WHERE a.attrelid = 'ten_bang_cua_ban'::regclass AND a.attnum > 0
ORDER BY a.attnum;

Cột typalign là yêu cầu căn lề (d = 8 byte, i = 4, s = 2, c = 1). Nếu bạn thấy một cột c hay s đứng ngay trước một cột d, đó là chỗ đang có khoảng đệm bỏ đi.

Và so kích thước thật với dữ liệu thật:

SELECT pg_size_pretty(pg_relation_size('ten_bang')) AS tren_dia,
       pg_relation_size('ten_bang') / count(*) AS byte_moi_dong
FROM ten_bang;

Phần sau đo ba kiểu chuỗi — text, varchar và char — và xem chúng có thật sự khác nhau không.