Tối Ưu Hóa Truy Vấn PostgreSQL: Từ Chậm Đến Dưới Mili-giây
Các kỹ thuật tối ưu PostgreSQL mà tôi sử dụng trên các database production. EXPLAIN ANALYZE, chiến lược đánh index, viết lại truy vấn, và những thay đổi chính xác giúp giảm thời gian truy vấn từ vài giây xuống micro-giây.
Tháng trước tôi nhận được tin nhắn Slack lúc 2 giờ sáng: "Dashboard bị timeout." Tôi SSH vào máy production, mở pg_stat_activity, và phát hiện một truy vấn duy nhất đang quét 14 triệu dòng để trả về 12 kết quả. Cách sửa là một partial index mất 30 giây để tạo. Truy vấn đó giảm từ 4,2 giây xuống 0,3 mili-giây.
Đó là điểm đặc biệt về hiệu năng PostgreSQL. Các vấn đề hầu như không bao giờ phức tạp lạ lùng. Chúng là các index bị bỏ sót, thống kê lỗi thời, các truy vấn hợp lý khi bảng có 10.000 dòng nhưng giờ đã có 10 triệu. Cách sửa thường đơn giản — một khi bạn biết tìm ở đâu.
Bài viết này là tất cả những gì tôi đã học về tối ưu truy vấn PostgreSQL từ việc vận hành các database production. Không có lý thuyết suông. Mọi kỹ thuật ở đây đã giúp tôi tiết kiệm thời gian thực trên các hệ thống thực.
Tư Duy Gỡ Lỗi: Đừng Đoán, Hãy Đo#
Sai lầm lớn nhất mà tôi thấy các lập trình viên mắc phải với truy vấn chậm là đoán mò. "Có lẽ chúng ta cần index cho cột đó." "Có lẽ JOIN chậm." "Có lẽ nên thêm RAM."
Ngừng đoán. PostgreSQL có một trong những bộ phân tích truy vấn tốt nhất của bất kỳ database nào. Hãy sử dụng nó.
EXPLAIN — Bản Thiết Kế#
EXPLAIN đơn giản cho bạn thấy PostgreSQL dự định làm gì, mà không thực sự chạy truy vấn:
EXPLAIN SELECT * FROM orders WHERE customer_id = 42; QUERY PLAN
---------------------------------------------------------------------------
Index Scan using idx_orders_customer_id on orders (cost=0.43..8.45 rows=1 width=128)
Index Cond: (customer_id = 42)
Điều này cho bạn biết kế hoạch — một index scan — và chi phí ước tính. Nhưng đó là dự đoán. PostgreSQL có thể hoàn toàn sai về số lượng dòng. Tôi đã thấy ước tính "1 dòng" trong khi kết quả thực tế là 50.000 dòng. Loại ước tính sai đó lan truyền thành những lựa chọn kế hoạch tồi tệ.
Sử dụng EXPLAIN đơn giản khi bạn muốn xem nhanh kế hoạch mà không thực sự chạy truy vấn. Điều này quan trọng khi bạn đang phân tích một DELETE sẽ thay đổi dữ liệu, hoặc một truy vấn mất 30 phút để chạy.
EXPLAIN ANALYZE — Sự Thật#
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42; QUERY PLAN
---------------------------------------------------------------------------
Index Scan using idx_orders_customer_id on orders (cost=0.43..8.45 rows=1 width=128)
(actual time=0.021..0.023 rows=1 loops=1)
Index Cond: (customer_id = 42)
Planning Time: 0.085 ms
Execution Time: 0.041 ms
Bây giờ bạn thấy thời gian thực thi thực tế và số lượng dòng thực tế. Đây là nơi việc gỡ lỗi thực sự diễn ra. So sánh rows=1 (ước tính) với rows=1 (thực tế) — chúng khớp nhau, vậy bộ lập kế hoạch đã đưa ra quyết định tốt. Khi chúng không khớp, đó là manh mối đầu tiên của bạn.
Cảnh báo: EXPLAIN ANALYZE thực sự chạy truy vấn. Nếu bạn đang phân tích một UPDATE hoặc DELETE, hãy bọc nó trong transaction:
BEGIN;
EXPLAIN ANALYZE DELETE FROM sessions WHERE expires_at < now();
ROLLBACK;EXPLAIN (ANALYZE, BUFFERS) — Bức Tranh Toàn Cảnh#
Đây là thứ tôi thực sự sử dụng 90% thời gian:
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.id, o.total, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.created_at > '2026-01-01'
AND o.status = 'completed'; QUERY PLAN
---------------------------------------------------------------------------
Hash Join (cost=425.00..1893.25 rows=2150 width=48)
(actual time=2.341..15.892 rows=2347 loops=1)
Hash Cond: (o.customer_id = c.id)
Buffers: shared hit=312 read=45
-> Bitmap Heap Scan on orders o (cost=12.50..1450.75 rows=2150 width=32)
(actual time=0.512..12.105 rows=2347 loops=1)
Recheck Cond: (created_at > '2026-01-01'::date)
Filter: (status = 'completed')
Rows Removed by Filter: 893
Heap Blocks: exact=189
Buffers: shared hit=195 read=45
-> Bitmap Index Scan on idx_orders_created_at (cost=0.00..11.96 rows=3043 width=0)
(actual time=0.287..0.287 rows=3240 loops=1)
Index Cond: (created_at > '2026-01-01'::date)
Buffers: shared hit=12
-> Hash (cost=287.50..287.50 rows=10000 width=24)
(actual time=1.753..1.753 rows=10000 loops=1)
Buffers: shared hit=117
-> Seq Scan on customers c (cost=0.00..287.50 rows=10000 width=24)
(actual time=0.008..0.892 rows=10000 loops=1)
Buffers: shared hit=117
Planning Time: 0.215 ms
Execution Time: 16.147 ms
Kết quả BUFFERS rất quan trọng. shared hit=312 nghĩa là 312 page đến từ buffer cache (RAM). read=45 nghĩa là 45 page phải đọc từ đĩa. Nếu bạn thấy nhiều read so với hit, shared_buffers của bạn có thể quá nhỏ, hoặc bảng quá lớn để nằm gọn trong cache.
Tôi cũng dùng FORMAT JSON khi cần dán kế hoạch vào explain.dalibo.com để trực quan hóa. Chế độ xem dạng cây giúp các kế hoạch phức tạp dễ đọc hơn nhiều:
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) SELECT ...;Các Tùy Chọn EXPLAIN Tôi Thực Sự Sử Dụng#
Đây là truy vấn chẩn đoán đầy đủ mà tôi chạy đầu tiên cho bất kỳ cuộc điều tra truy vấn chậm nào:
EXPLAIN (ANALYZE, BUFFERS, TIMING, VERBOSE)
SELECT ...;TIMING— thời gian theo từng node (mặc định bật vớiANALYZE, nhưng tôi ghi rõ ràng)VERBOSE— hiển thị danh sách cột đầu ra và tên bảng kèm schema
Đọc Kế Hoạch Truy Vấn Như Một Thám Tử#
Mọi kế hoạch truy vấn là một cây. PostgreSQL đọc từ dưới lên: các node thụt vào sâu nhất thực thi trước, truyền kết quả lên trên. Hiểu các loại scan là nền tảng của mọi thứ khác.
Seq Scan — Quét Toàn Bộ Bảng#
Seq Scan on orders (cost=0.00..25000.00 rows=1000000 width=128)
Sequential scan đọc mọi dòng trong bảng. Với bảng một triệu dòng, đó là mọi page trên đĩa.
Nhưng đây là điểm tinh tế: Seq Scan không phải lúc nào cũng xấu. Nếu bạn đang chọn 30% hoặc nhiều hơn của bảng, sequential scan thực sự nhanh hơn index scan vì I/O tuần tự nhanh hơn nhiều so với I/O ngẫu nhiên. PostgreSQL biết điều này. Nếu nó chọn Seq Scan khi bạn có index, hãy kiểm tra mệnh đề WHERE của bạn thực sự chọn lọc đến mức nào.
Khi nào nó là vấn đề: khi bạn chọn một phần nhỏ dòng từ bảng lớn và không có index.
Index Scan — Tra Cứu Có Mục Tiêu#
Index Scan using idx_orders_customer_id on orders (cost=0.43..8.45 rows=5 width=128)
Index Cond: (customer_id = 42)
Đi đến B-tree index, tìm các mục khớp, sau đó lấy các dòng thực tế từ heap của bảng. Mỗi lần lấy dòng là một thao tác I/O ngẫu nhiên đến heap. Điều này tuyệt vời cho các truy vấn có tính chọn lọc cao nhưng mỗi lần lấy từ heap đều có chi phí.
Index Only Scan — Đích Đến Lý Tưởng#
Index Only Scan using idx_orders_customer_total on orders (cost=0.43..4.15 rows=5 width=12)
Index Cond: (customer_id = 42)
Heap Fetches: 0
Đây là loại scan nhanh nhất. PostgreSQL lấy mọi thứ cần thiết chỉ từ index, không bao giờ chạm đến heap của bảng. Bạn thấy điều này khi tất cả các cột bạn SELECT và WHERE đều nằm trong index.
Điểm lưu ý: Heap Fetches: 0 nghĩa là visibility map đã được cập nhật. Nếu bảng của bạn có nhiều dead tuple (chưa được vacuum gần đây), PostgreSQL vẫn phải kiểm tra heap để xác minh khả năng hiển thị của dòng. Đây là một lý do autovacuum quan trọng cho hiệu năng, không chỉ cho dung lượng đĩa.
Bitmap Scan — Giải Pháp Trung Gian#
Bitmap Heap Scan on orders (cost=45.00..1250.00 rows=5000 width=128)
Recheck Cond: (created_at > '2026-01-01')
-> Bitmap Index Scan on idx_orders_created_at (cost=0.00..43.75 rows=5000 width=0)
Index Cond: (created_at > '2026-01-01')
Bitmap scan là câu trả lời của PostgreSQL cho vấn đề "quá nhiều dòng cho index scan, quá ít cho seq scan". Nó xây dựng một bitmap về các page nào chứa dòng khớp, sắp xếp chúng theo vị trí vật lý, sau đó lấy chúng theo thứ tự. Điều này chuyển đổi I/O ngẫu nhiên thành I/O tuần tự.
Bạn thường thấy bitmap scan khi hai hoặc nhiều index được kết hợp:
Bitmap Heap Scan on orders
Recheck Cond: ((status = 'completed') AND (created_at > '2026-01-01'))
-> BitmapAnd
-> Bitmap Index Scan on idx_orders_status
Index Cond: (status = 'completed')
-> Bitmap Index Scan on idx_orders_created_at
Index Cond: (created_at > '2026-01-01')
BitmapAnd này kết hợp hai index riêng biệt. Đó là PostgreSQL đang nói "Tôi không có composite index, nhưng tôi có thể kết hợp hai index đơn cột này." Nó hoạt động, nhưng một composite index đúng cách sẽ nhanh hơn.
Phát Hiện Ước Tính Sai#
Điều số một tôi tìm trong kế hoạch truy vấn là ước tính không khớp:
Nested Loop (cost=0.43..50.12 rows=1 width=128)
(actual time=0.025..4521.337 rows=48753 loops=1)
Bộ lập kế hoạch ước tính 1 dòng. Kết quả thực tế là 48.753 dòng. Đó là sai lệch năm bậc độ lớn. Bộ lập kế hoạch chọn Nested Loop vì nó nghĩ rằng đang join với 1 dòng. Với 48.753 dòng, Hash Join sẽ nhanh hơn nhiều bậc.
Các nguyên nhân phổ biến của ước tính sai:
- Thống kê lỗi thời: Chạy
ANALYZEtrên bảng - Các cột tương quan: Bộ lập kế hoạch giả định giá trị các cột là độc lập. Nếu
status = 'shipped'vàcreated_at > '2026-01-01'có tương quan (hầu hết đơn hàng gần đây đã được giao), bộ lập kế hoạch đánh giá thấp tính chọn lọc kết hợp - Hàm tùy chỉnh trong WHERE: Bộ lập kế hoạch sử dụng ước tính chọn lọc mặc định (thường 0,5% cho bằng, 33% cho khoảng) khi không thể phân tích hàm
- Truy vấn tham số hóa với kế hoạch chung: Sau 5 lần thực thi, PostgreSQL có thể chuyển sang kế hoạch chung không xét giá trị tham số thực tế
Khi bạn thấy ước tính sai, cách sửa thường là một trong: chạy ANALYZE, tạo thống kê mở rộng, viết lại truy vấn, hoặc dùng CTE làm hàng rào tối ưu hóa.
-- Tạo thống kê mở rộng cho các cột tương quan
CREATE STATISTICS orders_status_date (dependencies)
ON status, created_at FROM orders;
ANALYZE orders;Chiến Lược Index: Index Đúng Cho Công Việc Đúng#
Index không miễn phí. Mỗi index làm chậm ghi, tiêu tốn dung lượng đĩa, và cần được bảo trì. Mục tiêu không phải "đánh index mọi thứ" — mà là "đánh index chính xác những gì bạn cần."
B-tree — Con Ngựa Thồ Mặc Định#
B-tree là mặc định và xử lý được phần lớn các trường hợp. Nó hỗ trợ các toán tử bằng và khoảng (=, <, >, <=, >=, BETWEEN, IN, IS NULL).
CREATE INDEX idx_orders_created_at ON orders (created_at);Thứ tự cột của B-tree index cực kỳ quan trọng cho composite index. Cột ngoài cùng bên trái là sắp xếp chính, sau đó đến cột tiếp theo, và tiếp tục. Index trên (a, b, c) có thể trả lời hiệu quả:
WHERE a = 1WHERE a = 1 AND b = 2WHERE a = 1 AND b = 2 AND c = 3WHERE a = 1 AND b > 5WHERE a = 1 ORDER BY b
Nó không thể trả lời hiệu quả:
WHERE b = 2(bỏ qua cột đầu tiên)WHERE c = 3(bỏ qua hai cột đầu)WHERE a = 1 AND c = 3(khoảng trống ở giữa — điều kiệnađược dùng, điều kiệnccần lọc)
Hãy nghĩ về nó như danh bạ điện thoại được sắp xếp theo họ, rồi tên. Bạn có thể nhanh chóng tìm tất cả mục "Nguyễn", hoặc cụ thể "Nguyễn, Văn A". Nhưng bạn không thể nhanh chóng tìm tất cả mục "Văn A" mà không quét toàn bộ cuốn sách.
Quy tắc: đặt điều kiện bằng trước, sau đó điều kiện khoảng, rồi các cột sắp xếp.
-- Truy vấn: WHERE status = 'active' AND created_at > '2026-01-01' ORDER BY total DESC
-- Tốt: bằng trước, rồi khoảng, rồi sắp xếp
CREATE INDEX idx_orders_optimized ON orders (status, created_at, total);
-- Xấu: khoảng trước bằng
CREATE INDEX idx_orders_bad ON orders (created_at, status, total);Covering Index Với INCLUDE#
PostgreSQL 11+ cho phép bạn thêm các cột không phải khóa vào index bằng INCLUDE. Các cột này được lưu trong các leaf page nhưng không phải là phần của cấu trúc B-tree. Chúng cho phép index-only scan mà không làm phình cây:
-- Chúng ta thường xuyên chạy:
-- SELECT id, total, status FROM orders WHERE customer_id = 42;
CREATE INDEX idx_orders_customer_covering
ON orders (customer_id) INCLUDE (total, status);Bây giờ PostgreSQL có thể thỏa mãn toàn bộ truy vấn chỉ từ index. Nếu không có INCLUDE, nó sẽ cần lấy total và status từ heap. Sự khác biệt trên cold cache có thể rất ấn tượng — tôi đã thấy truy vấn giảm từ 50ms xuống 0,2ms chỉ bằng cách thêm cột INCLUDE.
Partial Index — Chỉ Index Những Gì Quan Trọng#
Đây là tính năng PostgreSQL yêu thích nhất của tôi và là tính năng mà hầu hết lập trình viên không biết.
-- Chỉ 2% đơn hàng là 'pending', nhưng chúng ta truy vấn chúng liên tục
CREATE INDEX idx_orders_pending ON orders (created_at)
WHERE status = 'pending';Index này nhỏ xíu so với index đầy đủ trên created_at. Nó chỉ chứa các dòng có status = 'pending'. Các truy vấn khớp mệnh đề WHERE sử dụng index nhỏ gọn, nhanh này. Các truy vấn không khớp bỏ qua nó.
Ví dụ thực tế từ production: Tôi có bảng sessions với 50 triệu dòng. Chỉ khoảng 200.000 đang hoạt động (chưa hết hạn). Index đầy đủ trên user_id là 1,2 GB. Partial index WHERE expires_at > now() chỉ 8 MB. Truy vấn giảm từ 12ms xuống 0,1ms vì toàn bộ index nằm gọn trong cache.
-- Một pattern phổ biến khác: soft delete
CREATE INDEX idx_users_active_email ON users (email)
WHERE deleted_at IS NULL;
-- Ràng buộc duy nhất chỉ trên bản ghi đang hoạt động
CREATE UNIQUE INDEX idx_users_unique_email ON users (email)
WHERE deleted_at IS NULL;Ví dụ cuối cùng cực kỳ hữu ích. Nó cho phép bạn có ràng buộc email duy nhất trên người dùng đang hoạt động trong khi vẫn cho phép cùng email xuất hiện trong các bản ghi đã xóa.
GIN Index — Tìm Kiếm Toàn Văn Và JSONB#
GIN (Generalized Inverted Index) là câu trả lời khi bạn cần tìm kiếm bên trong giá trị — mảng, tài liệu JSONB, hoặc vector tìm kiếm toàn văn.
-- Truy vấn chứa JSONB
CREATE INDEX idx_products_metadata ON products USING GIN (metadata);
-- Bây giờ cái này nhanh:
SELECT * FROM products WHERE metadata @> '{"color": "red"}';
-- Tìm kiếm toàn văn
CREATE INDEX idx_articles_search ON articles USING GIN (to_tsvector('english', title || ' ' || body));GIN index lớn và chậm khi xây dựng, nhưng cực nhanh khi truy vấn. Chúng đáng giá cho workload đọc nhiều với các truy vấn chứa phức tạp.
Với JSONB, còn có jsonb_path_ops nhỏ hơn và nhanh hơn cho truy vấn chứa @>, nhưng không hỗ trợ toán tử tồn tại (?, ?|, ?&):
CREATE INDEX idx_products_metadata_pathops
ON products USING GIN (metadata jsonb_path_ops);GiST Index — Kiểu Hình Học Và Khoảng#
GiST (Generalized Search Tree) xử lý các kiểu dữ liệu chồng lấp: hình dạng hình học, khoảng, tìm kiếm toàn văn (thay thế cho GIN, nhỏ hơn nhưng chậm hơn).
-- Tra cứu dải IP
CREATE INDEX idx_ip_ranges ON ip_blocks USING GiST (ip_range);
SELECT * FROM ip_blocks WHERE ip_range @> '192.168.1.50'::inet;
-- Khoảng thời gian (xung đột lịch trình)
CREATE INDEX idx_bookings_period ON bookings USING GiST (
tstzrange(start_time, end_time)
);
-- Tìm booking chồng lấp:
SELECT * FROM bookings
WHERE tstzrange(start_time, end_time) && tstzrange('2026-03-01 10:00', '2026-03-01 12:00');Hash Index — Hẹp Nhưng Hữu Ích#
Hash index chỉ hữu ích cho so sánh bằng. Kể từ PostgreSQL 10, chúng được ghi WAL-log và an toàn khi crash. Chúng nhỏ hơn B-tree cho cột rộng và nhanh hơn một chút cho bằng thuần túy:
-- Tốt cho tra cứu bằng trên cột text lớn
CREATE INDEX idx_sessions_token ON sessions USING HASH (session_token);
-- Cái này nhanh:
SELECT * FROM sessions WHERE session_token = 'abc123def456...';
-- Cái này KHÔNG THỂ sử dụng hash index:
SELECT * FROM sessions WHERE session_token LIKE 'abc%';Trong thực tế, tôi hiếm khi dùng hash index. B-tree xử lý bằng tốt rồi, và việc không thể truy vấn khoảng hoặc sắp xếp khiến hash index quá cứng nhắc cho hầu hết trường hợp sử dụng thực tế.
Khi Index Gây Hại#
Mỗi index đều có chi phí:
- Khuếch đại ghi: Mỗi INSERT cập nhật mọi index trên bảng. Bảng có 8 index nghĩa là 8 thao tác ghi bổ sung cho mỗi INSERT
- Chặn HOT update: Heap-Only Tuple (HOT) update là tối ưu quan trọng khi PostgreSQL có thể cập nhật dòng mà không cập nhật index, nhưng chỉ khi không có cột được đánh index nào thay đổi. Nhiều index hơn = nhiều khả năng chặn HOT update hơn
- Chi phí vacuum: Nhiều index hơn nghĩa là vacuum mất lâu hơn
- Chi phí lập kế hoạch: Nhiều index hơn nghĩa là bộ lập kế hoạch có nhiều tùy chọn hơn để đánh giá
Tôi thường xuyên kiểm tra index trên các bảng production:
-- Tìm index không dùng
SELECT
schemaname, tablename, indexname,
idx_scan,
pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
AND indexrelid NOT IN (
SELECT conindid FROM pg_constraint
WHERE contype IN ('p', 'u') -- loại trừ primary key và unique constraint
)
ORDER BY pg_relation_size(indexrelid) DESC;Tôi đã tìm thấy các index nhiều gigabyte được tạo trong một lần migration và không bao giờ dùng lại. Xóa chúng giúp tăng tốc ghi đáng kể.
-- Tìm index trùng lặp (cùng cột, cùng thứ tự)
SELECT
a.indexrelid::regclass AS index1,
b.indexrelid::regclass AS index2,
a.indrelid::regclass AS table_name
FROM pg_index a
JOIN pg_index b ON a.indrelid = b.indrelid
AND a.indexrelid < b.indexrelid
AND a.indkey = b.indkey;Vấn Đề Truy Vấn N+1#
Mọi lập trình viên ORM đều gặp phải điều này. Đây là vấn đề hiệu năng phổ biến nhất mà tôi gỡ lỗi.
N+1 Trông Như Thế Nào#
# Python / SQLAlchemy — cái bẫy kinh điển
orders = session.query(Order).filter_by(status='pending').all()
for order in orders:
# Điều này kích hoạt một truy vấn riêng cho MỖI đơn hàng
print(f"Customer: {order.customer.name}")Điều này tạo ra:
-- Truy vấn 1: Lấy tất cả đơn hàng pending
SELECT * FROM orders WHERE status = 'pending';
-- Truy vấn 2: Lấy khách hàng cho đơn hàng 1
SELECT * FROM customers WHERE id = 101;
-- Truy vấn 3: Lấy khách hàng cho đơn hàng 2
SELECT * FROM customers WHERE id = 102;
-- ...Truy vấn 502: Lấy khách hàng cho đơn hàng 500
SELECT * FROM customers WHERE id = 600;501 truy vấn thay vì 1 hoặc 2. Mỗi truy vấn riêng lẻ nhanh, có thể 0,5ms. Nhưng 501 cái cộng lại thành 250ms chỉ riêng thời gian database, cộng thêm độ trễ round-trip mạng cho mỗi truy vấn.
Phát Hiện N+1 Trong Log#
Cách nhanh nhất để bắt truy vấn N+1 là bật ghi log câu lệnh tạm thời:
-- Ghi log tất cả truy vấn mất hơn 0ms (tức là tất cả truy vấn)
SET log_min_duration_statement = 0;
-- Tốt hơn cho production: chỉ log truy vấn chậm
ALTER SYSTEM SET log_min_duration_statement = 50; -- ngưỡng 50ms
SELECT pg_reload_conf();Sau đó xem log. N+1 không thể nhầm lẫn — bạn sẽ thấy hàng trăm truy vấn giống hệt nhau với giá trị tham số khác nhau liên tiếp nhanh chóng.
Cách tiếp cận chính xác hơn cho môi trường phát triển:
-- Bật auto_explain cho truy vấn chậm
LOAD 'auto_explain';
SET auto_explain.log_min_duration = 100; -- 100ms
SET auto_explain.log_nested_statements = ON;
SET auto_explain.log_analyze = ON;Điều này ghi log đầy đủ EXPLAIN ANALYZE cho bất kỳ truy vấn nào trên 100ms, bao gồm truy vấn bên trong hàm.
Cách Sửa: Eager Loading Hoặc JOIN#
Cách tiếp cận ORM — bảo ORM tải dữ liệu liên quan trước:
# SQLAlchemy — joinedload lấy khách hàng trong cùng truy vấn
from sqlalchemy.orm import joinedload
orders = (
session.query(Order)
.options(joinedload(Order.customer))
.filter_by(status='pending')
.all()
)Cách tiếp cận SQL thuần — chỉ cần dùng JOIN:
SELECT o.*, c.name AS customer_name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'pending';Một truy vấn. Xong.
JOIN So Với Nhiều Truy Vấn#
Có cuộc tranh luận hợp lệ ở đây. Đôi khi hai truy vấn tốt hơn một JOIN:
-- Cách 1: JOIN đơn (có thể tạo dữ liệu trùng nếu one-to-many)
SELECT o.*, i.product_name, i.quantity
FROM orders o
JOIN order_items i ON i.order_id = o.id
WHERE o.status = 'pending';
-- Cách 2: Hai truy vấn (ít truyền dữ liệu hơn nếu đơn hàng có nhiều cột)
SELECT * FROM orders WHERE status = 'pending';
-- ứng dụng thu thập order_ids = [1, 2, 3, ...]
SELECT * FROM order_items WHERE order_id = ANY($1);Cách JOIN trùng lặp dữ liệu đơn hàng cho mỗi item. Nếu mỗi đơn hàng có 20 item và dòng đơn hàng rộng, đó là 20 lần truyền dữ liệu. Cách hai truy vấn gửi mỗi đơn hàng đúng một lần.
Quy tắc của tôi: dùng JOIN cho quan hệ one-to-one, cân nhắc truy vấn riêng cho one-to-many khi phía "one" có nhiều cột. Nhưng luôn benchmark — round-trip mạng của truy vấn thứ hai thường tốn nhiều hơn dữ liệu trùng lặp.
Các Kỹ Thuật Viết Lại Truy Vấn Phổ Biến#
Một số truy vấn chậm không phải vì thiếu index mà vì cách chúng được viết. Bộ lập kế hoạch của PostgreSQL giỏi, nhưng nó không phải phép thuật.
Subquery So Với JOIN So Với CTE#
Ba cách tiếp cận này có thể tạo ra các kế hoạch rất khác nhau:
-- Subquery trong WHERE — thường ổn, đôi khi tệ
SELECT * FROM orders
WHERE customer_id IN (
SELECT id FROM customers WHERE country = 'US'
);
-- JOIN — thường là lựa chọn tốt nhất
SELECT o.* FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE c.country = 'US';
-- CTE — được vật chất hóa trong PG 11 trở xuống, được tối ưu trong PG 12+
WITH us_customers AS (
SELECT id FROM customers WHERE country = 'US'
)
SELECT o.* FROM orders o
JOIN us_customers uc ON uc.id = o.customer_id;Trong PostgreSQL 12+, CTE thường được inline (bộ lập kế hoạch xử lý chúng như subquery), nên hiệu năng giống nhau. Nhưng trong PostgreSQL 11 trở xuống, CTE là hàng rào tối ưu hóa — bộ lập kế hoạch vật chất hóa chúng và không thể đẩy điều kiện qua. Nếu bạn vẫn dùng PG 11, tránh CTE cho truy vấn quan trọng về hiệu năng.
EXISTS So Với IN So Với JOIN#
Điều này xuất hiện liên tục:
-- EXISTS — dừng ở kết quả khớp đầu tiên, tuyệt vời để kiểm tra tồn tại
SELECT * FROM orders o
WHERE EXISTS (
SELECT 1 FROM returns r WHERE r.order_id = o.id
);
-- IN với subquery — xây dựng toàn bộ tập kết quả trước
SELECT * FROM orders
WHERE id IN (SELECT order_id FROM returns);
-- JOIN — có thể tạo trùng lặp nếu returns có nhiều dòng cho mỗi đơn hàng
SELECT DISTINCT o.* FROM orders o
JOIN returns r ON r.order_id = o.id;Với bảng lớn, EXISTS thường thắng vì nó ngắt mạch. Phiên bản IN phải xây dựng toàn bộ danh sách order_id trả hàng trước khi lọc. Phiên bản JOIN có thể tạo trùng lặp, cần DISTINCT thêm bước sắp xếp hoặc hash.
Mặc định của tôi: dùng EXISTS khi kiểm tra sự tồn tại của dòng liên quan. Nó rõ ràng về ngữ nghĩa nhất và thường nhanh nhất.
Nhưng có phản ví dụ. Nếu tập kết quả subquery nhỏ và bạn cần nó cho nhiều điều kiện:
-- Nếu subquery trả về ít dòng, IN hoàn toàn ổn
SELECT * FROM orders
WHERE customer_id IN (1, 2, 3, 4, 5);Tránh SELECT *#
Đây không chỉ là sở thích phong cách code. Nó có ảnh hưởng hiệu năng thực sự:
-- Xấu: lấy tất cả 25 cột bao gồm cột TEXT với hàng kilobyte dữ liệu
SELECT * FROM products WHERE category = 'electronics';
-- Tốt: chỉ lấy những gì bạn cần
SELECT id, name, price FROM products WHERE category = 'electronics';Với SELECT *:
- Không thể dùng index-only scan (tất cả cột cần nằm trong index)
- Truyền nhiều dữ liệu hơn qua mạng
- Dùng nhiều bộ nhớ hơn cho sắp xếp và hash
- Nếu ai đó thêm cột BYTEA 10MB sau này, các truy vấn hiện tại của bạn âm thầm chậm đi
Window Function So Với Subquery#
Window function là một trong những tính năng mạnh nhất của PostgreSQL và hầu như luôn vượt trội hơn subquery tương quan:
-- Chậm: subquery tương quan chạy một lần cho mỗi dòng
SELECT
o.*,
(SELECT COUNT(*) FROM order_items i WHERE i.order_id = o.id) AS item_count,
(SELECT SUM(amount) FROM payments p WHERE p.order_id = o.id) AS total_paid
FROM orders o
WHERE o.status = 'completed';
-- Nhanh: window function tính trong một lượt
SELECT
o.*,
COUNT(i.id) OVER (PARTITION BY o.id) AS item_count,
SUM(p.amount) OVER (PARTITION BY o.id) AS total_paid
FROM orders o
LEFT JOIN order_items i ON i.order_id = o.id
LEFT JOIN payments p ON p.order_id = o.id
WHERE o.status = 'completed';Một pattern phổ biến khác — lấy dòng mới nhất theo nhóm:
-- Chậm: subquery tương quan
SELECT * FROM notifications n
WHERE n.created_at = (
SELECT MAX(created_at) FROM notifications
WHERE user_id = n.user_id
);
-- Nhanh: DISTINCT ON (đặc thù PostgreSQL, cực kỳ hữu ích)
SELECT DISTINCT ON (user_id) *
FROM notifications
ORDER BY user_id, created_at DESC;
-- Cũng nhanh: window function ROW_NUMBER
SELECT * FROM (
SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
FROM notifications
) sub
WHERE rn = 1;DISTINCT ON là lựa chọn ưu tiên của tôi cho pattern này. Nó ngắn gọn, dễ đọc, và PostgreSQL tối ưu nó tốt với index phù hợp:
CREATE INDEX idx_notifications_user_latest
ON notifications (user_id, created_at DESC);Phân Trang Đúng Cách#
OFFSET là cái bẫy cho dataset lớn:
-- Trang 1: nhanh
SELECT * FROM products ORDER BY id LIMIT 20 OFFSET 0;
-- Trang 500: chậm — PostgreSQL phải quét và bỏ 9.980 dòng
SELECT * FROM products ORDER BY id LIMIT 20 OFFSET 9980;
-- Trang 5000: rất chậm — quét 99.980 dòng để trả về 20
SELECT * FROM products ORDER BY id LIMIT 20 OFFSET 99980;Cách sửa là phân trang kiểu keyset (còn gọi là phân trang dựa trên con trỏ):
-- Trang đầu tiên
SELECT * FROM products ORDER BY id LIMIT 20;
-- Trang tiếp: dùng id cuối cùng từ trang trước
SELECT * FROM products WHERE id > 12345 ORDER BY id LIMIT 20;Cách này luôn nhanh vì nó dùng index để nhảy trực tiếp đến vị trí đúng, bất kể bạn đang ở "trang" nào. Đánh đổi là bạn không thể nhảy đến số trang tùy ý, nhưng với giao diện cuộn vô tận hoặc "trang tiếp", phân trang keyset vượt trội tuyệt đối.
Với thứ tự sắp xếp phức tạp:
-- Phân trang keyset với nhiều cột sắp xếp
SELECT * FROM products
WHERE (price, id) > (29.99, 12345)
ORDER BY price, id
LIMIT 20;Cách này sử dụng so sánh giá trị dòng, mà PostgreSQL xử lý hiệu quả với composite index trên (price, id).
Thống Kê Bảng Và Vacuum#
Bộ lập kế hoạch truy vấn của PostgreSQL đưa ra quyết định dựa trên thống kê về dữ liệu của bạn. Thống kê sai dẫn đến kế hoạch sai. Đơn giản vậy thôi.
ANALYZE: Cập Nhật Thống Kê#
-- Phân tích một bảng
ANALYZE orders;
-- Phân tích toàn bộ database
ANALYZE;
-- Xem thống kê hiện tại cho một cột
SELECT
tablename, attname, n_distinct, most_common_vals, correlation
FROM pg_stats
WHERE tablename = 'orders' AND attname = 'status';Giá trị correlation đặc biệt thú vị. Nó nằm trong khoảng -1 đến 1 và đo lường mức độ thứ tự vật lý của các dòng khớp với thứ tự logic của cột. Correlation gần 1 hoặc -1 nghĩa là dữ liệu được sắp xếp vật lý theo cột đó, giúp range scan rất hiệu quả (I/O tuần tự). Correlation gần 0 nghĩa là I/O ngẫu nhiên cho truy vấn khoảng.
-- Tăng mục tiêu thống kê cho cột có phân phối lệch
ALTER TABLE orders ALTER COLUMN status SET STATISTICS 1000;
ANALYZE orders;Mục tiêu thống kê mặc định là 100, nghĩa là PostgreSQL lấy mẫu 300 * 100 = 30.000 dòng. Với cột có nhiều giá trị riêng biệt hoặc phân phối lệch, tăng lên 500 hoặc 1000 cung cấp cho bộ lập kế hoạch dữ liệu tốt hơn với chi phí thời gian ANALYZE dài hơn một chút.
Tinh Chỉnh Autovacuum#
Autovacuum làm hai việc: thu hồi dung lượng từ dead tuple (các dòng đã xóa hoặc cập nhật) và cập nhật thống kê. Trên các bảng bận, cài đặt autovacuum mặc định thường quá bảo thủ.
-- Kiểm tra trạng thái autovacuum
SELECT
relname,
n_live_tup,
n_dead_tup,
last_vacuum,
last_autovacuum,
last_analyze,
last_autoanalyze
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;Nếu bạn thấy bảng có hàng triệu dead tuple và lần vacuum cuối là hàng giờ trước, autovacuum của bạn đang tụt lại.
Với bảng có lượng thay đổi cao (như sessions, job queue, hoặc metrics), tôi đặt cài đặt autovacuum riêng cho từng bảng:
-- Autovacuum tích cực hơn cho bảng ghi nhiều
ALTER TABLE sessions SET (
autovacuum_vacuum_scale_factor = 0.01, -- vacuum khi 1% dead tuple (mặc định 20%)
autovacuum_analyze_scale_factor = 0.005, -- analyze khi 0,5% thay đổi (mặc định 10%)
autovacuum_vacuum_cost_delay = 2 -- vacuum nhanh hơn (mặc định 2ms trong PG mới)
);Phình Bảng#
Khi PostgreSQL cập nhật một dòng, nó không sửa đổi dòng tại chỗ — nó tạo phiên bản mới và đánh dấu cái cũ là dead. Vacuum thu hồi các dòng dead, nhưng dung lượng chỉ được tái sử dụng bởi bảng đó. File bảng trên đĩa không thu nhỏ lại.
Theo thời gian, bảng được cập nhật nhiều có thể bị phình đáng kể — bảng lớn hơn nhiều trên đĩa so với dữ liệu sống yêu cầu. Điều này nghĩa là nhiều page phải quét hơn, nhiều I/O hơn, nhiều áp lực cache hơn.
-- Ước tính phình bảng (phiên bản đơn giản)
SELECT
current_database(),
schemaname,
tablename,
pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) AS total_size,
pg_size_pretty(
pg_total_relation_size(schemaname || '.' || tablename) -
pg_relation_size(schemaname || '.' || tablename)
) AS index_size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname || '.' || tablename) DESC
LIMIT 20;Với bảng bị phình nghiêm trọng, giải pháp triệt để là VACUUM FULL — nó viết lại toàn bộ bảng. Nhưng nó khóa độc quyền, nên bạn không thể thực hiện trên bảng production đang hoạt động mà không có downtime. Cách tốt hơn là pg_repack, thực hiện tương tự mà không khóa:
pg_repack --table orders --no-kill-backend -d mydbConnection Pooling#
Đây là điều khiến nhiều lập trình viên ngạc nhiên: kết nối PostgreSQL rất tốn kém. Mỗi kết nối tạo một process mới (không phải thread), tiêu tốn khoảng 5-10 MB bộ nhớ, và có overhead fork không nhỏ.
max_connections mặc định là 100. Nếu bạn có application server với 20 worker, mỗi cái mở 5 kết nối, bạn đã đạt giới hạn. Thêm bộ xử lý background job, công cụ giám sát, và một migration đang chạy đâu đó, bạn sẽ gặp rắc rối.
Tại Sao Bạn Cần Connection Pooler#
Không có pooler, nếu ứng dụng cần xử lý 500 request đồng thời, bạn cần 500 kết nối PostgreSQL. Đó là 5 GB bộ nhớ chỉ cho overhead kết nối, và hiệu năng PostgreSQL giảm đáng kể vượt quá vài trăm kết nối do overhead quản lý process.
Với PgBouncer đặt trước PostgreSQL, 500 kết nối ứng dụng đó map thành khoảng 20 kết nối PostgreSQL thực tế. Pooler xếp hàng request khi tất cả kết nối database đang bận.
Cấu Hình PgBouncer#
[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb
[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
; Chế độ pool
pool_mode = transaction
; Kích thước pool
default_pool_size = 20
max_client_conn = 500
min_pool_size = 5
reserve_pool_size = 5
reserve_pool_timeout = 3
; Timeout
server_idle_timeout = 300
client_idle_timeout = 0
query_timeout = 30Chế Độ Transaction So Với Session#
- Chế độ transaction (
pool_mode = transaction): Kết nối được trả về pool sau mỗi transaction. Đây là thứ bạn muốn 95% thời gian. Tái sử dụng kết nối tối đa. - Chế độ session (
pool_mode = session): Kết nối được giữ cho toàn bộ phiên client. Dùng cái này nếu bạn cần prepared statement, lệnhSET,LISTEN/NOTIFY, hoặc các tính năng cấp session khác. - Chế độ statement (
pool_mode = statement): Kết nối được trả về sau mỗi câu lệnh. Quá hạn chế cho hầu hết ứng dụng — bạn thậm chí không thể dùng transaction tường minh.
Điểm lưu ý với chế độ transaction: bạn không thể dùng prepared statement (chúng là trạng thái cấp session), không thể dùng SET cho biến session, và LISTEN/NOTIFY sẽ không hoạt động như mong đợi. Hầu hết ORM có cách vô hiệu hóa prepared statement.
Với Node.js sử dụng driver pg:
const pool = new Pool({
host: '127.0.0.1',
port: 6432, // cổng PgBouncer, không phải cổng PostgreSQL
database: 'mydb',
max: 20, // khớp với default_pool_size của PgBouncer
statement_timeout: 30000,
// Vô hiệu hóa prepared statement cho chế độ transaction PgBouncer
prepare: false,
});Công Thức Kích Thước Pool#
Có một công thức phổ biến cho kích thước connection pool PostgreSQL tối ưu:
optimal_connections = (core_count * 2) + effective_spindle_count
Với server hiện đại có 4 core và SSD (1 effective spindle):
optimal = (4 * 2) + 1 = 9
Con số này có vẻ thấp phản trực giác. Nhưng PostgreSQL bị giới hạn bởi CPU trên hầu hết phần cứng hiện đại (SSD đủ nhanh để I/O hiếm khi là nút thắt). Nhiều kết nối hơn con số này dẫn đến overhead chuyển đổi ngữ cảnh thực sự làm chậm mọi thứ.
Trong thực tế, tôi thường đặt default_pool_size gấp 2-3 lần con số này để xử lý lưu lượng đột biến, với hiểu biết rằng khi tải cao điểm, một số truy vấn sẽ đợi trong hàng đợi PgBouncer thay vì tất cả đổ vào PostgreSQL cùng lúc.
Checklist Thực Tế: Các Bước Chính Xác Cho Mỗi Truy Vấn Chậm#
Đây là quy trình thực tế của tôi khi nhận được báo cáo "truy vấn này chậm". Tôi tuân theo các bước này theo thứ tự, mỗi lần.
Bước 1: Lấy Truy Vấn Thực Tế#
Không phải "endpoint chậm" — mà là SQL thực tế. Nếu bạn dùng ORM, bật ghi log truy vấn:
-- Tạm thời log tất cả truy vấn trên 100ms
ALTER SYSTEM SET log_min_duration_statement = 100;
SELECT pg_reload_conf();Hoặc kiểm tra pg_stat_statements cho các truy vấn hàng đầu theo tổng thời gian:
-- Top 10 truy vấn theo tổng thời gian thực thi
SELECT
queryid,
calls,
round(total_exec_time::numeric, 2) AS total_time_ms,
round(mean_exec_time::numeric, 2) AS avg_time_ms,
round((100 * total_exec_time / sum(total_exec_time) OVER ())::numeric, 2) AS pct,
query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;pg_stat_statements là extension có giá trị nhất cho hiệu năng PostgreSQL. Nếu bạn chưa chạy nó, cài ngay:
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;Bước 2: Chạy EXPLAIN (ANALYZE, BUFFERS)#
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) <truy vấn chậm>;Tìm:
- Ước tính dòng sai — dòng thực tế rất khác so với ước tính
- Seq Scan trên bảng lớn — có thể thiếu index
- Nested Loop với nhiều dòng — nên là Hash Join hoặc Merge Join
- Buffer read cao — cold cache hoặc bảng quá lớn
- Thao tác sắp xếp tràn ra đĩa — tăng
work_memhoặc thêm index cho sắp xếp
Bước 3: Kiểm Tra Thống Kê Bảng#
-- Thống kê được cập nhật lần cuối khi nào?
SELECT
relname,
last_analyze,
last_autoanalyze,
n_live_tup,
n_dead_tup
FROM pg_stat_user_tables
WHERE relname = 'orders';Nếu last_analyze đã cũ hoặc n_dead_tup cao so với n_live_tup, chạy:
ANALYZE orders;Sau đó chạy lại EXPLAIN ANALYZE. Nếu kế hoạch thay đổi, thống kê lỗi thời là vấn đề.
Bước 4: Kiểm Tra Index Hiện Có#
-- Những index nào tồn tại trên bảng này?
SELECT
indexname,
indexdef,
idx_scan,
idx_tup_read,
pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
JOIN pg_indexes ON pg_stat_user_indexes.indexrelname = pg_indexes.indexname
WHERE tablename = 'orders'
ORDER BY idx_scan DESC;Có thể index tồn tại nhưng không được sử dụng. Có thể thứ tự cột sai cho truy vấn của bạn.
Bước 5: Tạo Hoặc Sửa Đổi Index#
Dựa trên kế hoạch truy vấn, tạo index phù hợp. Kiểm tra bằng EXPLAIN ANALYZE trước và sau.
-- Tạo index đồng thời (không khóa bảng)
CREATE INDEX CONCURRENTLY idx_orders_customer_status
ON orders (customer_id, status)
WHERE status IN ('pending', 'processing');
-- Xác minh index được sử dụng
EXPLAIN (ANALYZE, BUFFERS) <truy vấn chậm>;Luôn dùng CONCURRENTLY trên bảng production. CREATE INDEX thông thường khóa toàn bộ bảng và chặn mọi thao tác ghi.
Bước 6: Cân Nhắc Viết Lại Truy Vấn#
Nếu index tồn tại và thống kê mới nhưng truy vấn vẫn chậm, xem xét bản thân truy vấn:
- Subquery có thể viết lại thành JOIN không?
OFFSETcó gây vấn đề không? Chuyển sang phân trang keyset- Bạn có đang chọn nhiều cột hơn cần thiết không?
- Subquery tương quan có thể thành window function không?
- CTE có ngăn bộ lập kế hoạch tối ưu không?
Bước 7: Kiểm Tra Cài Đặt Server#
Với các pattern truy vấn cụ thể, cài đặt server quan trọng:
-- work_mem: bộ nhớ cho sắp xếp và hash join (mỗi thao tác, không phải mỗi kết nối)
-- Mặc định là 4MB, quá thấp cho truy vấn phức tạp
SET work_mem = '64MB'; -- thử cái này và chạy lại EXPLAIN ANALYZE
-- effective_cache_size: cho bộ lập kế hoạch biết nên kỳ vọng bao nhiêu disk cache
-- Đặt ~75% tổng RAM
SHOW effective_cache_size;
-- random_page_cost: tỷ lệ I/O ngẫu nhiên so với I/O tuần tự
-- Mặc định là 4.0, đặt 1.1-1.5 cho SSD
SHOW random_page_cost;Nếu thay đổi work_mem sửa được vấn đề sort-to-disk, cân nhắc tăng nó toàn cục. Nhưng cẩn thận — nó là mỗi thao tác, không phải mỗi kết nối. Truy vấn phức tạp với 10 thao tác sắp xếp và work_mem = 256MB có thể dùng 2,5 GB cho một truy vấn duy nhất.
Bước 8: Giám Sát Sau Khi Sửa#
Đừng chỉ sửa rồi quên. Xác minh bản sửa giữ vững:
-- Reset pg_stat_statements để thấy dữ liệu mới sau thay đổi
SELECT pg_stat_statements_reset();
-- Kiểm tra lại sau một giờ/ngày
SELECT
queryid,
calls,
round(mean_exec_time::numeric, 2) AS avg_time_ms,
round(max_exec_time::numeric, 2) AS max_time_ms,
query
FROM pg_stat_statements
WHERE query LIKE '%orders%'
ORDER BY mean_exec_time DESC;Bonus: Thắng Nhanh Tôi Áp Dụng Cho Mỗi Database Mới#
Đây là các cài đặt và thực hành tôi áp dụng cho mọi database PostgreSQL mà tôi thiết lập, trước khi bất kỳ vấn đề hiệu năng nào phát sinh:
-- 1. Cài đặt pg_stat_statements
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
-- 2. Bật ghi log truy vấn chậm (50ms là ngưỡng của tôi)
ALTER SYSTEM SET log_min_duration_statement = 50;
-- 3. Cài đặt phù hợp SSD
ALTER SYSTEM SET random_page_cost = 1.1;
ALTER SYSTEM SET effective_io_concurrency = 200;
-- 4. Tăng work_mem từ mặc định thấp
ALTER SYSTEM SET work_mem = '32MB';
-- 5. Đặt effective_cache_size bằng 75% RAM
-- Cho server 16GB:
ALTER SYSTEM SET effective_cache_size = '12GB';
-- 6. Đặt shared_buffers bằng 25% RAM
-- Cho server 16GB:
ALTER SYSTEM SET shared_buffers = '4GB';
-- 7. Cải thiện khả năng phản hồi autovacuum
ALTER SYSTEM SET autovacuum_naptime = '10s';
-- Áp dụng thay đổi
SELECT pg_reload_conf();
-- Lưu ý: shared_buffers yêu cầu khởi động lạiVà truy vấn giám sát tôi chạy hàng tuần:
-- Bảng cần chú ý
SELECT
schemaname || '.' || relname AS table,
pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
n_live_tup,
n_dead_tup,
CASE WHEN n_live_tup > 0
THEN round(100.0 * n_dead_tup / n_live_tup, 1)
ELSE 0
END AS dead_pct,
last_autovacuum,
last_autoanalyze
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC;Suy Nghĩ Cuối Cùng#
Tối ưu truy vấn PostgreSQL không phải nghệ thuật huyền bí. Đó là một quy trình có hệ thống:
- Đo lường — đừng đoán. EXPLAIN (ANALYZE, BUFFERS) là người bạn tốt nhất của bạn.
- Hiểu kế hoạch — học cách đọc các loại scan, loại join, và ước tính dòng.
- Đánh index chiến lược — index đúng trên cột đúng, theo thứ tự đúng. Partial index và covering index là siêu năng lực ít được sử dụng.
- Viết truy vấn tốt hơn — EXISTS thay vì IN cho kiểm tra tồn tại, phân trang keyset thay vì OFFSET, window function thay vì subquery tương quan.
- Bảo trì database — tinh chỉnh autovacuum, cập nhật thống kê, connection pooling.
- Giám sát liên tục — pg_stat_statements cho bạn biết database dành thời gian ở đâu. Kiểm tra nó thường xuyên.
Sự khác biệt giữa truy vấn mất 4 giây và truy vấn mất 0,3 mili-giây hiếm khi là phần cứng. Hầu như luôn là kiến thức — biết tìm ở đâu và thay đổi gì. Và bây giờ bạn biết tìm ở đâu.