pg_stat_statements: Dò tìm query chậm trong PostgreSQL · Pattern Quest
#postgresql
#performance
#sql
#spring-boot
#java
pg_stat_statements: Dò tìm query chậm trong PostgreSQLUsing pg_stat_statements to Spot Slow PostgreSQL Queries
Hướng dẫn bật và khai thác extension pg_stat_statements để phát hiện, phân tích và tối ưu các query chậm trong PostgreSQL, kèm ví dụ Spring Boot 3 và Java 21.A step‑by‑step guide to enable and use the pg_stat_statements extension for detecting, analysing and tuning slow PostgreSQL queries, with a Spring Boot 3 / Java 21 example.
Bạn đang xem dashboard, p99 latency tăng đột biến, nhưng log chỉ cho biết “slow query” mà không nói rõ câu SQL nào gây ra. Thường thì chúng ta chạy EXPLAIN ANALYZE trên từng query để đo, nhưng khi hệ thống có hàng ngàn endpoint, việc này trở nên tốn thời gian và dễ bỏ sót.
pg_stat_statements là một extension của PostgreSQL thu thập thống kê cho mọi câu lệnh SQL được thực thi. Nó lưu trữ tổng số lần chạy, thời gian trung bình, thời gian tối đa, số hàng trả về, và thậm chí chi phí ước tính. Nhờ vậy, bạn có thể
-- Kết nối với database dưới quyền superuser (thường là postgres)CREATE EXTENSION IF NOT EXISTS pg_stat_statements;-- Thêm vào postgresql.conf (hoặc qua ALTER SYSTEM) nếu chưa cóshared_preload_libraries = 'pg_stat_statements'
Sau khi sửa postgresql.conf, khởi động lại PostgreSQL. Khi extension đã được tải, một view tên pg_stat_statements sẽ xuất hiện.
pg_stat_statements là công cụ “đèn pin” giúp bạn nhìn rõ những “điểm chết” trong layer SQL mà không cần đo từng query thủ công. Khi kết hợp với Spring Boot, bạn có thể đưa nó vào pipeline observability, gửi alert khi một query vượt ngưỡng, và tự động reset sau mỗi đợt triển khai. Kết quả: giảm latency, tiết kiệm tài nguyên, và giảm stress khi phải “đốn đáy bể” mỗi khi có sự cố.
You glance at the dashboard, p99 latency spikes, and the logs only whisper "slow query" without naming the offending SQL. The usual reflex is to run EXPLAIN ANALYZE on suspected statements, but in a micro‑service landscape with dozens of endpoints that quickly becomes a manual nightmare.
pg_stat_statements is a PostgreSQL extension that silently records every SQL statement that touches the server. It aggregates call count, total execution time, average time, maximum time, rows processed, and even I/O costs. In short, it gives you a single source of truth for the most expensive queries with just a few SELECTs.
-- Connect as a superuser (usually postgres)CREATE EXTENSION IF NOT EXISTS pg_stat_statements;-- Add to postgresql.conf if it is not already loadedshared_preload_libraries = 'pg_stat_statements'
After editing postgresql.conf (or using ALTER SYSTEM), restart PostgreSQL. Once loaded, a view called pg_stat_statements appears in the public schema.
package com.example.monitor;import org.springframework.web.bind.annotation.GetMapping;import org.springframework.web.bind.annotation.PostMapping;import org.springframework.web.bind.annotation.RestController;import java.util.List;@RestControllerpublic class PgStatController { private final PgStatRepository repo; public PgStatController(PgStatRepository repo) { this.repo = repo; } @GetMapping("/monitor/slow-queries") public List<PgStatStatement> getTopSlow() { return repo.topSlow(10); } @PostMapping("/monitor/reset") public void resetStats() { repo.reset(); }}
Deploy the service and hit /monitor/slow-queries from Grafana, Postman, or a scheduled job. The JSON payload can be fed into alerting pipelines (e.g., Prometheus Alertmanager) to notify you when a query exceeds a predefined threshold.
SELECT * FROM order_item WHERE product_id = $1 AND created_at > $2;
calls = 15200
total_time = 84200 ms
mean_time = 5.5 ms
max_time = 120 ms
The high total_time tells you this statement dominates DB load. The missing index on product_id (and optionally created_at) is the usual culprit. Adding a composite index eliminates the full‑scan:
SQL
CREATE INDEX idx_order_item_product_created ON order_item(product_id, created_at);
After a reset and a few minutes of traffic, the same query drops to mean_time ≈ 0.8 ms and total_time shrinks by ~ 80 %.
pg_stat_statements acts as a flashlight that reveals hidden performance potholes in your PostgreSQL layer. By exposing its data through a lightweight Spring Boot endpoint, you turn raw statistics into actionable alerts and a repeatable tuning cycle. The payoff is lower latency, better resource utilization, and fewer “why is my service slow?” panic moments.