Bài toán trông vô hại như thế nào?#

Màn hình cần một read model tương đối quen thuộc:

• danh sách chứng từ công nợ;
• các dòng hàng của chứng từ;
• tổng số dòng để phân trang;
• tổng tiền theo currency;
• gợi ý chứng từ trùng;
• thông tin purchase order liên quan đến từng receipt line.

Logic nghiệp vụ nằm trong một query builder dùng chung. Mỗi repository method lấy builder đó rồi gắn thêm filter, sort, pagination hoặc aggregate.

Nếu viết thành pseudo-code, luồng cũ trông gần như thế này:

summary := summarize(payableDocumentQuery(filters))
total := count(payableDocumentQuery(filters))
rows := paginate(payableDocumentQuery(filters).Join(detailLines()))
currencies := groupByCurrency(payableDocumentQuery(filters))

Không có câu nào sai. Kết quả trả về cũng đúng. Vấn đề là mỗi dòng đang yêu cầu database dựng lại cùng một read model phức tạp.

Tệ hơn nữa, bên trong detailLines() có một LEFT JOIN LATERAL để tổng hợp purchase order cho từng receipt line. Nói đơn giản, với mỗi dòng hàng bên trái, database lại chạy một câu hỏi nhỏ ở bên phải:

Dòng này liên quan đến những purchase order nào?

Nếu trang kết quả có nhiều chứng từ và mỗi chứng từ có nhiều dòng, “câu hỏi nhỏ” đó bị lặp lại rất nhanh.

flowchart TB
    accTitle: So sánh luồng đọc dữ liệu trước và sau khi materialize
    accDescr: Trước đây mỗi nhánh summary, count và page tự dựng lại read model, đồng thời purchase order được tổng hợp theo từng dòng. Sau khi tối ưu, read model và detail lines được dựng một lần rồi tái sử dụng.

    R1["Request trước khi tối ưu"] --> Q1["4 SQL statements"]
    Q1 --> D1["Read model bị dựng lại"]
    D1 --> L1["LATERAL aggregate theo từng line"]
    L1 --> X1["52.668 vòng lặp"]

    X1 -. "Refactor" .-> R2["Request sau khi tối ưu"]
    R2 --> A2["payable_documents_all MATERIALIZED"]
    A2 --> F2["payable_documents MATERIALIZED"]
    F2 --> O2["Summary, count và page dùng chung kết quả"]
    O2 --> L2["detail_lines MATERIALIZED"]
    L2 --> G2["PO aggregate theo nhóm một lần"]

Đừng đoán, hãy xem database đã làm gì#

Thời gian tổng chỉ nói query chậm. EXPLAIN (ANALYZE, BUFFERS) mới cho biết thời gian biến mất ở đâu.

EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
WITH ...
SELECT ...;

Trong plan, mình tập trung vào ba tín hiệu:

1. Actual Loops: node đó thực sự được chạy bao nhiêu lần.
2. shared hit: query chạm bao nhiêu page đã có trong shared buffer. Dữ liệu không phải đọc từ disk vẫn có chi phí CPU và memory access.
3. Quan hệ cha-con giữa các node: một aggregate rẻ khi chạy một lần có thể cực đắt nếu nó nằm dưới nested loop và chạy vài chục nghìn lần.

Ở bộ lọc ngày gây chậm, page query mất khoảng 580–930 ms và tạo ra 387.870 shared buffer hits. Hai con số trong plan giải thích gần như toàn bộ câu chuyện:

• detail-line subtree chạy lại 22 lần;
• lateral purchase-order aggregate chạy 52.668 lần.

Đây là điểm dễ đánh lừa mình nhất: từng phép aggregate riêng lẻ không chậm. Công việc bị nhân bản mới chậm.

Tại sao tách thành CTE vẫn chưa chắc chạy một lần?#

CTE thường làm SQL dễ đọc hơn, nhưng nó không phải mặc định là một biến đã được tính sẵn.

Với CTE không có side effect, PostgreSQL có thể gộp CTE vào query cha để tối ưu chung. Cách này rất tốt khi giúp đẩy filter xuống sâu hơn. Nhưng nếu một nhánh đắt tiền bị tham chiếu hoặc lồng vào một plan khiến nó được chạy lặp lại, planner có thể tạo ra đúng kiểu nhân bản mà mình đang muốn tránh.

Tài liệu chính thức của PostgreSQL về CTE materialization mô tả hai mặt này khá rõ: materialization có thể tránh lặp lại phép tính đắt tiền, nhưng cũng có thể ngăn parent query đẩy điều kiện xuống CTE.

Vậy nên giải pháp không phải là thêm MATERIALIZED vào mọi CTE. Câu hỏi đúng hơn là:

Khối dữ liệu nào đủ đắt, được tái sử dụng nhiều lần, và có phạm vi đủ nhỏ để đáng dựng đúng một lần?

Lần sửa đầu tiên vẫn chưa đủ#

Mình tách query thành hai tầng:

WITH payable_documents_all AS MATERIALIZED (
    -- Dựng read model cho toàn bộ organization
),
payable_documents AS MATERIALIZED (
    SELECT *
    FROM payable_documents_all
    WHERE ... -- filter và search một lần
)
SELECT ...;

Thử nghiệm ban đầu rất hứa hẹn: page query tương đương giảm xuống khoảng 57,6 ms và 18.795 shared buffer hits trên dataset đang điều tra.

Nhưng khi thêm fixture hiệu năng lớn hơn vào integration test, EXPLAIN cho thấy một chi tiết khó chịu: receipt-line aggregate nằm sâu bên trong vẫn chạy 24 vòng, tạo ra 319.677 shared hits.

Tức là read model bên ngoài đã được materialize, nhưng một số aggregate bên trong nó vẫn bị planner đưa vào plan lặp. Nếu chỉ nhìn thời gian của một lần chạy, mình rất dễ kết luận sớm rằng đã xong.

Đây cũng là lý do performance test nên kiểm tra hình dạng execution plan, không chỉ kiểm tra “dưới 150 ms”. Máy CI nhanh hay chậm có thể làm duration dao động; một subtree lẽ ra chạy một lần nhưng có Actual Loops = 24 thì vẫn là lỗi thiết kế.

Chốt phạm vi trước, rồi aggregate một lần#

Bản sửa cuối cùng có ba ý chính.

WITH scoped_lines AS MATERIALIZED (
    SELECT receipt_line_id
    FROM receipt_lines
    JOIN payable_documents USING (receipt_id)
),
po_line_agg AS MATERIALIZED (
    SELECT
        project_id,
        receipt_line_id,
        jsonb_agg(...) AS purchase_orders
    FROM purchase_order_links
    JOIN scoped_lines USING (receipt_line_id)
    GROUP BY project_id, receipt_line_id
),
detail_lines AS MATERIALIZED (
    SELECT ...
    FROM scoped_lines
    LEFT JOIN po_line_agg USING (project_id, receipt_line_id)
)
SELECT ...;

Một statement không có nghĩa là một cục JSON khổng lồ#

Để summary, total và rows về trong cùng một DB statement, API danh sách dùng một JSON envelope nội bộ:

{
  "rows": [],
  "total": 0,
  "summary": {},
  "currencySummaries": []
}

Cách này còn giải quyết một edge case hay bị bỏ quên: page vượt quá phạm vi vẫn phải trả total và summary. Nếu chỉ dùng window function gắn metadata lên từng row, page rỗng sẽ không còn row nào để mang metadata về.

Với export, repository trả flat rows từ một statement rồi group thành documents trong Go. Nhờ vậy export không còn:

• vòng lặp qua từng page;
• một query lấy danh sách chứng từ;
• N query lấy detail của từng chứng từ.

Đổi lại, export một statement có thể dùng nhiều memory hơn nếu dữ liệu cực lớn. Đây là trade-off cần theo dõi, không phải chiến thắng miễn phí.

Kết quả và cách đọc cho đúng#

Regression fixture gồm 24 receipts × 100 lines, tổng cộng 2.400 lines. Plan cuối cùng cho kết quả:

Hai cột trên đến từ hai dataset khác nhau, nên không nên lấy chúng để tuyên bố một phần trăm tăng tốc tuyệt đối. Điều đáng tin hơn là invariant của execution plan: lateral aggregate biến mất và detail subtree có Actual Loops = 1.

Trên dataset local dùng để kiểm tra đồng thời tám request, request nhanh nhất khoảng 120,3 ms, median 165,5 ms, chậm nhất 184,5 ms. Đây cũng chỉ là số đo của một môi trường cụ thể, không phải SLA cho mọi hệ thống.

Ngoài benchmark, mình giữ regression test cho những trường hợp dễ vỡ khi refactor query:

• chứng từ không có line;
• một receipt liên kết nhiều purchase order;
• page rỗng vẫn có total và summary;
• nhiều currency và amount bị thiếu;
• supplier inactive nhưng vẫn đang được chứng từ tham chiếu;
• filter, search, sort và thứ tự export;
• mỗi read API chỉ tạo đúng một DB statement.

Những điều mình rút ra#

Đếm HTTP request chưa đủ. Ba API song song có thể hoàn toàn hợp lý; vấn đề là mỗi API đang tạo bao nhiêu DB statement và mỗi statement lặp lại bao nhiêu công việc bên trong.

Đếm SQL statement cũng chưa đủ. Một statement vẫn có thể chứa nested loop khiến aggregate chạy hàng chục nghìn lần.

MATERIALIZED là một ranh giới thực thi, không phải bùa tăng tốc. Nó hữu ích khi cần tái sử dụng kết quả đắt tiền hoặc ngăn phép tính bị lặp. Nó có thể phản tác dụng nếu làm mất filter pushdown hoặc materialize một tập dữ liệu quá lớn.

Pre-aggregation thường tốt hơn correlated work. Nếu có thể group theo key một lần rồi join lại, hãy so sánh plan đó với LATERAL hoặc correlated subquery chạy theo từng row.

Test plan shape, không chỉ test stopwatch. Duration dao động theo máy và cache. Actual Loops, sự tồn tại của lateral aggregate và buffer hits thường cho tín hiệu regression ổn định hơn.

Cuối cùng, phần khó nhất của tối ưu query không phải viết thêm CTE. Phần khó là xác định grain của từng tập dữ liệu, phần nào phải tính trên toàn scope để giữ đúng nghiệp vụ, phần nào được phép filter sớm, và công việc nào đang vô tình bị nhân bản.

Khi một API chậm mà từng phép tính trông đều “không đáng bao nhiêu”, hãy mở execution plan và tìm phép nhân. Rất có thể vấn đề không nằm ở một công việc lớn, mà ở một công việc nhỏ bị bắt làm lại 52.668 lần.