Tối ưu truy vấn PostgreSQL: N+1, index & connection pool · Pattern Quest
#postgresql
#performance
#spring-boot
#java
#sql
Tối ưu truy vấn PostgreSQL: N+1, index & connection poolOptimizing PostgreSQL queries: N+1, indexes and connection pool
Cách phát hiện và khắc phục N+1, dùng EXPLAIN ANALYZE để chọn index, và cấu hình connection pool cho PostgreSQL.Detect and fix N+1 queries, use EXPLAIN ANALYZE to pick the right index, and tune the connection pool for PostgreSQL.
Bạn vừa nhận được một ticket: "Trang danh sách đơn hàng trả về chậm >5 s". Log ở mức DEBUG cho thấy mỗi đơn hàng lại thực hiện một truy vấn phụ để lấy thông tin khách hàng. Đây là kiểu lỗi N+1 query – rất phổ biến khi dùng ORM và thường ẩn trong mã sạch. Khi không kịp phát hiện, nó nhanh chóng làm bùng lên connection pool exhaustion và gây lock chờ.
Trong Spring Boot + JPA, một findAll() trên Order sẽ trả về danh sách entity, nhưng nếu Order.customer được fetch = LAZY, mỗi lần truy cập order.getCustomer() lại bật một câu SELECT riêng. Để kiểm tra, bật spring.jpa.show-sql=true và xem log; hoặc dùng p6spy để ghi lại số lượng câu lệnh. Giải pháp thường là:
Sau khi loại bỏ N+1, truy vấn vẫn chậm? Thì đến lúc kiểm tra plan. Chạy:
SQL
EXPLAIN (ANALYZE, BUFFERS) SELECT o.id, c.nameFROM orders oJOIN customers c ON o.customer_id = c.idWHERE o.status = 'PENDING';
Kết quả sẽ cho biết có Seq Scan hay Index Scan, thời gian thực tế, và số page được đọc. Nếu thấy Seq Scan trên cột status, hãy tạo index:
SQL
CREATE INDEX idx_orders_status ON orders(status);
Đối với join, PostgreSQL tự dùng index trên khóa ngoại nếu tồn tại. Đừng quên đặt index cho cột thường dùng trong WHERE, ORDER BY hoặc GROUP BY. Kiểu index btree là mặc định, nhưng nếu truy vấn range trên timestamp, BRIN có thể tiết kiệm không gian.
Khi truy vấn được tối ưu, pool vẫn có thể bão hòa nếu cấu hình không phù hợp. Với HikariCP (default trong Spring Boot), các tham số quan trọng:
YAML
spring: datasource: url: jdbc:postgresql://db-prod:5432/app username: app_user hikari: maximum-pool-size: 30 # phù hợp với CPU cores * 2 minimum-idle: 5 idle-timeout: 300000 # 5 phút max-lifetime: 1800000 # 30 phút
Nếu ứng dụng chạy trên Kubernetes với replica 4, tổng connections tới DB sẽ là 30 * 4 = 120. Đảm bảo pgBouncer hoặc max_connections trong postgresql.conf lớn hơn con số này, thường đặt max_connections = 200 cho môi trường trung bình. Khi pool đầy, các request sẽ chờ ConnectionTimeout, dẫn tới p99 latency tăng cao.
Phát hiện N+1 ngay: bật SQL log, dùng EntityGraph hoặc fetch join.
EXPLAIN ANALYZE mọi query chậm >100 ms, tạo index phù hợp.
Kích thước pool dựa trên cores và replica, đồng thời tăng max_connections trên PostgreSQL.
Giám sát lock định kỳ, tránh UPDATE đồng thời trên cùng một khóa chính.
Kiểm tra lại sau mỗi thay đổi: chạy load test, đo p99 latency.
Áp dụng những bước này, bạn sẽ thấy thời gian đáp ứng giảm từ vài giây xuống dưới 200 ms, và hệ thống ổn định hơn khi traffic tăng cao.
You just got a ticket: "Order list page takes >5 s to render". The DEBUG logs reveal that each order triggers an extra query to fetch the customer record. This is the classic N+1 query problem – common when using an ORM and easily hidden behind clean code. If left unchecked, it quickly exhausts the connection pool and leads to lock contention.
In a Spring Boot + JPA app, a findAll() on Order returns a list of entities, but if Order.customer is LAZY, every order.getCustomer() call fires a separate SELECT. To spot it, enable spring.jpa.show-sql=true or use p6spy to count statements. The usual fix is to fetch the association in a single query:
After eliminating N+1, the query may still be slow. Time to inspect the execution plan. Run:
SQL
EXPLAIN (ANALYZE, BUFFERS) SELECT o.id, c.nameFROM orders oJOIN customers c ON o.customer_id = c.idWHERE o.status = 'PENDING';
The output tells you whether PostgreSQL performed a Seq Scan or an Index Scan, how long it took, and how many pages were read. If you see a Seq Scan on the status column, add an index:
SQL
CREATE INDEX idx_orders_status ON orders(status);
For joins, PostgreSQL automatically uses the index on the foreign‑key if it exists. Remember to index columns frequently used in WHERE, ORDER BY, or GROUP BY. The default btree works for most cases, but for large timestamp ranges a BRIN index can save space.
If you run 4 replicas in Kubernetes, total connections to the DB become 30 * 4 = 120. Ensure pgBouncer or the PostgreSQL max_connections setting exceeds that number; a typical value is max_connections = 200 for a medium‑size cluster. When the pool is full, requests wait for the connectionTimeout, inflating p99 latency.
