Sắp thứ tự cột để giảm padding trong PostgreSQL: cùng bảng, khác dung lượng

Bài về kiểu số đã chạm tới nó, giờ đào sâu: trong PostgreSQL, thứ tự bạn khai báo các cột ảnh hưởng trực tiếp đến kích thước bảng — cùng một bộ cột, cùng dữ liệu y hệt, chỉ đổi thứ tự có thể chênh gần 30% dung lượng. Nguyên nhân là căn lề bộ nhớ (alignment): CPU truy cập dữ liệu nhanh nhất khi mỗi giá trị nằm ở địa chỉ chia hết cho kích thước của nó, nên PostgreSQL chèn byte đệm (padding) để đảm bảo điều đó. Xếp cột sai làm byte đệm phình lên. Bài này đo thật và cho công thức sắp xếp tối ưu.

Quy tắc căn lề: mỗi kiểu một mốc

Mỗi kiểu dữ liệu có một yêu cầu căn lề, đọc được từ pg_type.typalign:

• int8, timestamptz, float8 → căn 8 byte (một giá trị phải bắt đầu ở địa chỉ chia hết cho 8).
• int4 → căn 4 byte; int2 → 2 byte; bool, "char" → 1 byte.
• uuid → 16 byte; text, numeric, jsonb → biến thiên, căn theo header 4 byte.

Khi một cột nhỏ (như bool, 1 byte) đứng ngay trước một int8, giá trị int8 không thể bắt đầu ngay sau — nó phải chờ tới mốc 8 byte tiếp theo, và PostgreSQL chèn tới 7 byte đệm vào giữa. Byte đệm đó không chứa gì, chỉ tốn chỗ.

Hình 1: Mỗi kiểu căn lề theo mốc riêng. Xen kẽ cột nhỏ giữa cột lớn phí byte đệm; sắp theo độ rộng giảm dần dồn đệm về không. Có truy vấn tự tìm thứ tự tối ưu.

Đo thật: 403 MB so với 287 MB

Cùng 7 cột (hai int8, một timestamptz, một int2, ba bool), 5 triệu dòng, chỉ khác thứ tự khai báo:

Hình 2: Thứ tự xấu (bool xen int8) chiếm 403 MB, 73 byte/dòng; thứ tự tối ưu (int8 trước, bool dồn cuối) chỉ 287 MB, 53 byte/dòng — chênh 116 MB (~29%) và 20 byte mỗi dòng, cùng dữ liệu y hệt.

• Thứ tự xấu (bool, int8, bool, int8, int2, tstz, bool): 403 MB, mỗi dòng 73 byte. Mỗi bool đứng trước một int8 gây tới 7 byte đệm.
• Thứ tự tối ưu (int8, int8, tstz, int2, bool, bool, bool): 287 MB, mỗi dòng 53 byte. Các cột 8 byte liền nhau không cần đệm, ba bool dồn cuối chỉ tốn 3 byte.

Chênh 116 MB (~29%) và 20 byte mỗi dòng — chỉ vì thứ tự khai báo. Với bảng hàng trăm triệu dòng, đây là hàng gigabyte dung lượng và băng thông I/O tiết kiệm được mà không thay đổi một byte dữ liệu thật.

Quy tắc sắp xếp và cách tự tìm

Công thức đơn giản: sắp các cột theo độ rộng căn lề giảm dần. Cụ thể theo thứ tự:

1. Kiểu 8 byte: int8, bigint, timestamptz, timestamp, float8, uuid (16 byte, xếp trước cùng nhóm).
2. Kiểu 4 byte: int4, float4, date.
3. Kiểu 2 byte: int2.
4. Kiểu 1 byte: bool, "char".
5. Kiểu biến thiên: text, varchar, numeric, jsonb — xếp cuối.

Bạn không cần tính tay. Truy vấn này liệt kê cột của một bảng theo đúng thứ tự tối ưu:

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'::regclass
  AND a.attnum > 0 AND NOT a.attisdropped
ORDER BY t.typlen DESC;

Chạy nó trên bảng hiện có để thấy thứ tự lý tưởng, rồi so với thứ tự thật của bạn để biết có đáng sắp lại không.

Đánh đổi cần cân nhắc

Padding đáng quan tâm ở bảng lớn, không phải bảng nhỏ. Với bảng vài nghìn dòng, tiết kiệm vài chục MB không đáng đánh đổi tính dễ đọc của schema. Ưu tiên nhóm cột theo logic nghiệp vụ cho bảng nhỏ; chỉ sắp theo alignment cho những bảng hàng chục triệu dòng trở lên, nơi 29% là thật.

Đổi thứ tự cột trên bảng đang có dữ liệu là thao tác nặng. PostgreSQL không cho ALTER TABLE đổi thứ tự cột trực tiếp; muốn sắp lại phải tạo bảng mới với thứ tự đúng rồi INSERT INTO ... SELECT, hoặc pg_repack. Vì vậy tối ưu này giá trị nhất khi thiết kế bảng mới — sắp cột đúng từ đầu gần như miễn phí, sửa sau thì tốn.

Đừng hy sinh tính đúng đắn cho vài byte. Nếu một thứ tự cột "xấu" về padding nhưng phản ánh rõ cấu trúc dữ liệu và dễ bảo trì, cân nhắc. Tối ưu padding là điều nên làm khi ngang nhau về mọi mặt khác — nó không bao giờ nên là lý do làm schema khó hiểu.

Ba ý mang về

1. Thứ tự cột ảnh hưởng kích thước bảng vì căn lề: PostgreSQL căn mỗi cột theo mốc kiểu của nó (8 byte cho int8/timestamptz, 1 byte cho bool) và chèn byte đệm khi lệch — đo thật cùng 7 cột, thứ tự xấu chiếm 403 MB còn tối ưu chỉ 287 MB (chênh ~29%).
2. Sắp cột theo độ rộng giảm dần: kiểu 8 byte trước, rồi 4, 2, 1 byte, và kiểu biến thiên (text, numeric, jsonb) cuối — dồn các cột lớn liền nhau và các cột nhỏ về cuối để byte đệm về gần không.
3. Tối ưu này quý nhất khi thiết kế bảng mới: sắp đúng từ đầu gần như miễn phí, nhưng đổi thứ tự bảng đã có dữ liệu phải tạo lại bảng; dùng truy vấn pg_attribute/pg_type ORDER BY typlen DESC để tự tìm thứ tự tối ưu.

Phần sau ta xét một quyết định thiết kế lớn hơn: Phần sau đo jsonb so với cột riêng — khi nào gom dữ liệu vào một cột JSONB linh hoạt, khi nào tách thành cột riêng có kiểu và index, và cái giá hiệu năng của mỗi lựa chọn.