Bảng phân mảnh

Phân mảnh bảng hay được giới thiệu như cách tăng tốc truy vấn trên bảng lớn. Phần này đo xem nó tăng tốc bao nhiêu, và tìm ra rằng lợi ích thật của nó nằm ở chỗ khác hẳn.

Bố trí

10.000.000 đơn hàng trải hai năm, 867 MB. Hai bảng chứa cùng dữ liệu:

create table dh_pm(id bigserial, kh int, tien numeric(12,2),
                   luc timestamptz not null, tt text)
  partition by range (luc);
-- 24 phân vùng, mỗi tháng một cái

create table dh_thuong(...);   -- một bảng, cùng dữ liệu, cùng chỉ mục

Cắt bỏ phân vùng chỉ thắng ở truy vấn hẹp

Đọc bảng này kỹ hơn một chút thì thấy chuyện thú vị.

Ở mọi mức, phân mảnh đọc ít trang hơn — 3.600 so với 5.876 ở một tháng, 43.920 so với 83.334 ở một năm. Cắt bỏ phân vùng hoạt động đúng như quảng cáo.

Nhưng thời gian thì không theo. Ở ba tháng, phân mảnh đọc ít hơn 39% số trang mà lại chậm hơn 24%. Và khi quét toàn bộ, cả hai đọc đúng 83.334 trang nhưng phân mảnh mất 607 ms so với 385 ms.

Lý do là chi phí ghép: Append phải mở 24 quan hệ, khởi tạo 24 lần quét, rồi nối kết quả — thay vì một lần quét tuần tự liên tục trên một tệp. Với mỗi phân vùng chỉ chiếm 37 MB, chi phí cố định đó chiếm tỷ lệ đáng kể.

Kết luận đầu tiên, và nó ngược với lý do người ta hay phân mảnh: nếu bạn phân mảnh để tăng tốc truy vấn, hãy đo trước. Nó chỉ thắng khi truy vấn thật sự chạm đúng một hoặc hai phân vùng.

Chỗ phân mảnh thắng áp đảo

Xoá sáu tháng dữ liệu cũ — thao tác mà mọi hệ thống có bảng lịch sử đều phải làm:

WAL ít hơn 1.800 lần.

Chênh lệch thời gian (0,83 so với 0,43 s) không lớn, nhưng nó không phải điểm chính. Ba hệ quả của cột WAL và cột "dòng chết" mới là điểm chính:

Máy dự phòng. 312 MB WAL phải truyền sang mọi replica và phát lại ở đó. Trên đường truyền chậm, đó là khác biệt giữa "không ai nhận ra" và "replica tụt lại nửa tiếng".

Sao lưu. Nếu bạn dùng sao lưu liên tục, 312 MB đó nằm trong kho lưu trữ vĩnh viễn — cho một thao tác xoá dữ liệu.

2,6 triệu dòng chết. Bảng vẫn chiếm 651 MB sau khi xoá. Muốn lấy lại dung lượng phải VACUUM FULL và khoá cả bảng (phần 22), hoặc chấp nhận nó phình cho tới khi được ghi đè dần.

DROP phân vùng thì chỉ xoá một tệp. Không có dòng chết, không có gì để dọn, không có WAL đáng kể.

Đây là lý do thật để phân mảnh bảng lịch sử: không phải để đọc nhanh hơn, mà để xoá được.

Bảo trì chia nhỏ được

Cột "khoá" quan trọng hơn cột thời gian. Trên bảng 500 GB, dựng một chỉ mục là thao tác hàng giờ; chia thành 24 phân vùng nghĩa là bạn làm 24 lần, mỗi lần một phân vùng, và mỗi lần chỉ chặn truy vấn chạm vào phân vùng đó.

Phân vùng cũ thì không ai đụng tới, nên bạn có thể làm chúng vào giữa trưa. Chỉ phân vùng của tháng hiện tại mới cần cửa sổ bảo trì.

Cái giá: thời gian lập kế hoạch

Tuyến tính, không có ngoại lệ.

Với 1.000 phân vùng, chỉ riêng việc lập kế hoạch đã mất 6,85 ms — nhiều hơn thời gian chạy của phần lớn truy vấn tra cứu. Bộ lập lịch phải xét từng phân vùng để quyết định cắt bỏ cái nào, và việc đó tốn thời gian ngay cả khi kết quả là "bỏ 999 cái".

Con số này định ra một ngưỡng thực tế: giữ số phân vùng dưới vài trăm. Phân theo tháng cho 10 năm là 120 phân vùng — vừa đủ. Phân theo ngày cho 3 năm là 1.095 — quá nhiều, và bạn nên gộp các năm cũ lại.

Câu lệnh chuẩn bị sẵn vẫn cắt bỏ được

Một lo ngại hợp lý: nếu điều kiện lọc là tham số, bộ lập lịch không biết giá trị lúc lập kế hoạch thì làm sao cắt bỏ?

prepare p1(timestamptz) as
  select count(*) from dh_pm where luc >= $1 and luc < $1 + interval '1 month';
execute p1('2024-08-01');

Sau năm lần chạy, PostgreSQL chuyển sang kế hoạch chung. EXPLAIN ANALYZE cho thấy:

Append  (actual rows=446400)
  Subplans Removed: 17
Planning Time: 0.365 ms

Subplans Removed: 17 — nó cắt bỏ 17 trên 18 phân vùng lúc chạy, sau khi biết giá trị tham số. Cơ chế này gọi là cắt bỏ lúc thực thi, có từ PostgreSQL 11.

Chú ý Planning Time chỉ 0,365 ms vì kế hoạch đã được chuẩn bị sẵn. Đây là cách giảm chi phí lập kế hoạch ở bảng trên: dùng câu lệnh chuẩn bị sẵn thì trả chi phí đó một lần thay vì mỗi lần gọi.

Khi nào nên phân mảnh

Dòng áp chót đáng nhấn mạnh. Nếu ứng dụng của bạn tra theo don_hang.id là chính, phân mảnh theo luc sẽ khiến mọi truy vấn tra cứu phải quét đủ 24 phân vùng. Cột phân mảnh phải là cột xuất hiện trong WHERE của các truy vấn quan trọng.

Và một ràng buộc kỹ thuật hay bị vấp: khoá chính bắt buộc phải chứa cột phân mảnh. primary key (id) không hợp lệ trên bảng phân mảnh theo luc; phải là primary key (id, luc). Điều đó cũng có nghĩa là không đặt được ràng buộc duy nhất trên riêng id ở phạm vi toàn bảng.

Thử ba mươi giây

Nếu bạn đang có bảng lớn và đang cân nhắc phân mảnh, kiểm câu hỏi quyết định trước:

select
  count(*) filter (where <cột định phân mảnh> >= now() - interval '1 month') as thang_nay,
  count(*) as tong,
  round(100.0 * count(*) filter (where <cột> >= now() - interval '1 month') / count(*), 1) as phan_tram
from <bảng>;

Rồi hỏi: bao nhiêu phần trăm truy vấn của bạn chỉ chạm vào phần dữ liệu gần đây? Nếu câu trả lời là "hầu hết", phân mảnh giúp. Nếu là "truy vấn nào cũng quét cả bảng", nó chỉ thêm chi phí.

Và nếu lý do của bạn là "cần xoá dữ liệu cũ hàng tháng" thì không cần hỏi gì thêm — 181 kB WAL so với 312 MB đã trả lời rồi.

Phần sau đo kế thừa bảng và khoá ngoại: chi phí thật của việc kiểm tra ràng buộc, và chuyện gì xảy ra khi xoá một dòng cha có hàng triệu dòng con.