PostgreSQL thực chiến: Tìm truy vấn tốn tài nguyên bằng pg_stat_statements và auto_explain

Một truy vấn mất 2 giây có thể gây khó chịu, nhưng một truy vấn mất 8 ms và chạy hai triệu lần mới là thứ tiêu thụ phần lớn database. Muốn tối ưu PostgreSQL đúng chỗ, đội vận hành cần nhìn cả chi phí tích lũy, độ trễ mỗi lần gọi, I/O, temporary spill và execution plan thực tế.

Bài viết kết hợp pg_stat_statements để xếp hạng workload đã chuẩn hóa, auto_explain để bắt plan của câu lệnh chậm và EXPLAIN để kiểm chứng giả thuyết. Ví dụ hướng tới production nhưng phải đối chiếu đúng phiên bản PostgreSQL đang chạy vì cột và option thay đổi giữa các phiên bản.

1. Ba câu hỏi khác nhau cần ba lớp dữ liệu

Đừng bắt đầu bằng cách lấy một SQL ngẫu nhiên từ log rồi thêm index. Trước tiên xác định query family đóng góp nhiều nhất vào thời gian, I/O hoặc WAL trong một cửa sổ đại diện; sau đó mới thu plan và thay đổi có thể kiểm chứng.

2. Bật pg_stat_statements an toàn

# postgresql.conf
# Giữ lại mọi thư viện đã có, chỉ thêm pg_stat_statements vào danh sách
shared_preload_libraries = 'pg_stat_statements'
compute_query_id = auto
pg_stat_statements.max = 10000
pg_stat_statements.track = top

Module cần shared memory nên thay đổi shared_preload_libraries đòi hỏi restart server. Không ghi đè mù danh sách hiện có. Sau restart, tạo extension trong từng database cần truy vấn view:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

SELECT extversion
FROM pg_extension
WHERE extname = 'pg_stat_statements';

SELECT * FROM pg_stat_statements_info;

track = top là baseline dễ kiểm soát. all còn thu statement lồng trong function/procedure và có thể tăng cardinality/overhead. track_planning mặc định tắt vì có thể gây contention đáng kể ở workload nhiều connection cùng cập nhật ít entry.

3. Hiểu query normalization và queryid

Module gom câu lệnh cùng cấu trúc sau parse analysis và thay literal bằng placeholder như $1. Vì vậy hàng triệu truy vấn theo ID khác nhau thường nằm trong một entry. Khóa logic của entry còn gồm database, user và top-level status; chỉ nhìn text có thể gộp sai ngữ cảnh.

queryid hữu ích để nối snapshot, log và dashboard, nhưng không phải ID toàn cầu vĩnh viễn. Tài liệu PostgreSQL không đảm bảo nó ổn định qua major version, kiến trúc khác nhau hoặc thay đổi catalog nhất định. Dashboard dài hạn nên lưu thêm database, user, query text đã chuẩn hóa, version server và mốc thời gian.

4. Xếp hạng theo tổng thời gian trước

SELECT
    queryid,
    calls,
    round(total_exec_time::numeric, 1) AS total_ms,
    round(mean_exec_time::numeric, 2) AS mean_ms,
    round(max_exec_time::numeric, 2) AS max_ms,
    rows,
    shared_blks_read,
    temp_blks_written,
    wal_bytes,
    left(query, 180) AS sample
FROM pg_stat_statements
WHERE dbid = (SELECT oid FROM pg_database WHERE datname = current_database())
ORDER BY total_exec_time DESC
LIMIT 20;

total_exec_time tìm “heavy hitter” của cả hệ thống. mean_exec_time tìm request chậm điển hình; max_exec_time gợi ý tail spike nhưng một giá trị cực đại không cho biết phân phối. calls phân biệt query chậm với query quá chatty. Một query trung bình rất nhanh nhưng tổng thời gian cao thường cần batching, cache hoặc giảm round trip hơn là chỉ thêm index.

Các cột có thể khác theo major version. Hãy xem \d+ pg_stat_statements trên hệ thống thật trước khi đưa SQL dashboard vào automation.

5. Đọc buffer, temp và WAL như tín hiệu

• shared_blks_hit là lượt truy cập buffer cache, không phải số block distinct và không đồng nghĩa “không có chi phí”.
• shared_blks_read cho thấy block phải đọc vào shared buffers; thời gian I/O chỉ có ý nghĩa khi track_io_timing bật.
• temp_blks_written/read cao gợi ý sort/hash spill, nhưng không tự động kết luận chỉ cần tăng work_mem.
• wal_bytes cao giúp tìm write path tạo nhiều WAL, bulk update hoặc index amplification.

SELECT
    queryid,
    calls,
    pg_size_pretty((shared_blks_read * current_setting('block_size')::bigint)) AS read_bytes,
    pg_size_pretty((temp_blks_written * current_setting('block_size')::bigint)) AS temp_written,
    pg_size_pretty(wal_bytes) AS wal_generated,
    left(query, 140) AS sample
FROM pg_stat_statements
ORDER BY temp_blks_written DESC
LIMIT 15;

Bật track_io_timing sau khi đo overhead trên nền tảng thật. Counter block vẫn hữu ích khi timing tắt, nhưng không phân biệt storage cache, filesystem cache và thời gian chờ thiết bị một cách hoàn chỉnh.

6. Cửa sổ đo quan trọng hơn một ảnh chụp tích lũy

Statistics cộng dồn từ lúc reset/start. So sánh một entry 30 ngày với deployment vừa chạy 20 phút dễ dẫn tới kết luận sai. Thu snapshot định kỳ rồi tính delta của calls, time, rows, blocks, temp và WAL trên cùng identity.

Không reset toàn bộ chỉ để có dashboard đẹp; reset phá baseline của mọi người dùng chung module. Ghi pg_stat_statements_info.stats_reset vào snapshot để phát hiện discontinuity. Nếu cần thí nghiệm riêng, dùng database/role/workload cô lập hoặc reset có phạm vi được hỗ trợ bởi đúng version và có owner rõ ràng.

7. Từ query family đến một execution plan

Lấy query đã chuẩn hóa, tìm code path và dựng lại tham số đại diện trên staging với volume/statistics gần production. Chạy plain EXPLAIN trước:

EXPLAIN (COSTS, VERBOSE, SETTINGS)
SELECT o.id, o.created_at
FROM orders AS o
WHERE o.tenant_id = '00000000-0000-0000-0000-000000000001'
  AND o.status = 'pending'
ORDER BY o.created_at
LIMIT 100;

Ước lượng row sai ở node sớm thường kéo theo join order hoặc access path sai. Kiểm tra statistics freshness, phân phối lệch, correlation và điều kiện đa cột trước khi ép planner bằng setting toàn cục.

8. EXPLAIN ANALYZE thực thi câu lệnh thật

EXPLAIN (ANALYZE, BUFFERS, WAL, SETTINGS, TIMING OFF)
SELECT ...;

ANALYZE chạy câu lệnh, chờ lock và tiêu thụ tài nguyên thật. Với INSERT, UPDATE, DELETE hoặc function có side effect, nó có thể thay đổi dữ liệu, gọi trigger và hệ thống bên ngoài. Bọc transaction rồi rollback không hoàn tác mọi external side effect và vẫn có thể khóa/ghi WAL. Ưu tiên staging, replica phù hợp hoặc plain EXPLAIN; chỉ chạy production khi đã đánh giá blast radius.

BUFFERS cho số lượt block ở từng node; WAL cho lượng WAL; SETTINGS ghi các planner setting khác mặc định. TIMING OFF giảm overhead đo thời gian từng node nhưng vẫn giữ row counts và tổng thời gian khi ANALYZE bật.

9. Dùng auto_explain theo canary và sampling

# Chỉ là baseline canary; hiệu chỉnh theo SLO và tải thật
session_preload_libraries = 'auto_explain'
auto_explain.log_min_duration = '1000ms'
auto_explain.sample_rate = 0.05
auto_explain.log_analyze = on
auto_explain.log_timing = off
auto_explain.log_buffers = on
auto_explain.log_wal = on
auto_explain.log_parameter_max_length = 0
auto_explain.log_nested_statements = off

auto_explain mặc định không log plan cho tới khi đặt threshold. Đừng bắt đầu bằng duration 0 hay sample rate 1 trên toàn cluster. log_analyze yêu cầu instrumentation trong lúc câu lệnh chạy, và per-node timing có thể rất đắt; canary một role/database/instance, đo CPU, latency và log volume rồi mới mở rộng.

Tùy chọn và khả năng reload phụ thuộc version/context. Kiểm tra bằng SHOW, pg_settings và tài liệu đúng major version; chuẩn bị rollback cấu hình trước rollout.

10. Log là biên dữ liệu nhạy cảm

Trong PostgreSQL hiện hành, auto_explain.log_parameter_max_length mặc định có thể ghi đầy đủ parameter. Đặt 0 để tắt trước khi rollout nếu bind value có email, token, nội dung khách hàng hoặc secret. Query normalization của pg_stat_statements giảm literal nhưng không phải hệ thống DLP; utility command, query text đại diện và comment vẫn cần policy.

Hạn chế quyền đọc view/log. Chỉ superuser và role có quyền thống kê phù hợp được xem query text/queryid của user khác; không cấp pg_read_all_stats rộng chỉ để dựng dashboard. Log sink cần encryption, retention, redaction và audit access như một data store production.

11. Đừng tối ưu chỉ bằng một plan

Cùng query family có thể có tham số selectivity rất khác, generic/custom plan khác, cache nóng/lạnh khác và concurrency khác. Một plan nhanh trên laptop không chứng minh production sẽ nhanh. Sau thay đổi, so cùng cửa sổ traffic:

• calls và total/mean/max execution time;
• rows, shared blocks, temp spill và WAL mỗi call;
• p95/p99 từ application tracing vì pg_stat_statements không phải histogram latency;
• lock wait, connection saturation, CPU và storage latency;
• write cost, disk size và maintenance cost của index mới.

Tối ưu thành công là giảm chi phí toàn hệ thống mà không chuyển vấn đề sang write latency, vacuum, replica lag hoặc query khác.

12. Quy trình điều tra lặp lại được

1. Xác định incident window, SLO và deployment liên quan.
2. Kiểm tra mốc reset rồi lấy delta pg_stat_statements.
3. Xếp hạng theo total time, mean/max, calls, reads, temp và WAL.
4. Chọn một query family cùng database/user cụ thể.
5. Đối chiếu application trace, lock wait và tham số đại diện.
6. Plain EXPLAIN trước; ANALYZE trong môi trường an toàn.
7. Dùng auto_explain sampled canary nếu plan chậm khó tái hiện.
8. Đưa ra một giả thuyết và thay đổi đảo ngược được.
9. Đo lại delta cùng tải, canary rồi mới rollout.

Checklist production

• Không ghi đè các entry khác trong shared_preload_libraries.
• Restart và CREATE EXTENSION đã được thực hiện đúng database.
• pg_stat_statements_info được theo dõi để thấy reset/deallocation.
• Dashboard dùng delta theo cửa sổ, không so counter tích lũy khác tuổi.
• Top query được xem theo total cost, per-call latency, calls, I/O, temp và WAL.
• Không giả định queryid ổn định qua major upgrade.
• EXPLAIN ANALYZE chỉ chạy sau khi đánh giá side effect và tải.
• auto_explain có threshold, sampling, canary và rollback.
• Parameter logging bị tắt hoặc giới hạn; log có quyền/retention phù hợp.
• Kết quả tối ưu được kiểm chứng bằng application SLO và chi phí database sau rollout.

Kết luận

pg_stat_statements trả lời nên tối ưu query family nào; auto_explain giúp bắt plan thật của lần chạy khó tái hiện; EXPLAIN giúp kiểm chứng nguyên nhân. Giá trị lớn nhất đến từ việc nối ba lớp bằng query identity và cửa sổ đo nhất quán, không phải bật mọi option log. Khi rollout có sampling, bảo vệ parameter, tránh side effect của ANALYZE và đo lại tổng chi phí, tối ưu PostgreSQL trở thành một vòng lặp có bằng chứng thay vì đoán index.