Tối Ưu Hóa Truy Vấn PostgreSQL: Từ Chậm Đến Dưới Mili-giây | Akousa

                                    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)

                                    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

                                    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

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 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')

sql

-- 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);

sql

-- 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;

sql

-- 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));

sql

-- 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');

sql

-- 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%';

sql

-- 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;

sql

-- 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;

python

# 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}")

sql

-- 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;

sql

-- 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();

python

# 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()
)

sql

-- 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);

sql

-- 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;

sql

-- 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;

sql

-- 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';

sql

-- 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';

sql

-- 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;

sql

-- 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;

sql

-- 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';

sql

-- 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;

sql

-- 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)
);

sql

-- Ướ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;

ini

[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 = 30

javascript

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,
});

sql

-- 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;

sql

-- 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;

sql

-- 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>;

sql

-- 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;

sql

-- 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;

sql

-- 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ại

sql

-- 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;

Tư Duy Gỡ Lỗi: Đừng Đoán, Hãy Đo

EXPLAIN — Bản Thiết Kế

EXPLAIN ANALYZE — Sự Thật

EXPLAIN (ANALYZE, BUFFERS) — Bức Tranh Toàn Cảnh

Các Tùy Chọn EXPLAIN Tôi Thực Sự Sử Dụng

Đọc Kế Hoạch Truy Vấn Như Một Thám Tử

Seq Scan — Quét Toàn Bộ Bảng

Index Scan — Tra Cứu Có Mục Tiêu

Index Only Scan — Đích Đến Lý Tưởng

Bitmap Scan — Giải Pháp Trung Gian

Phát Hiện Ước Tính Sai

Chiến Lược Index: Index Đúng Cho Công Việc Đúng

B-tree — Con Ngựa Thồ Mặc Định

Covering Index Với INCLUDE

Partial Index — Chỉ Index Những Gì Quan Trọng

GIN Index — Tìm Kiếm Toàn Văn Và JSONB

GiST Index — Kiểu Hình Học Và Khoảng

Hash Index — Hẹp Nhưng Hữu Ích

Khi Index Gây Hại

Vấn Đề Truy Vấn N+1

N+1 Trông Như Thế Nào

Phát Hiện N+1 Trong Log

Cách Sửa: Eager Loading Hoặc JOIN

JOIN So Với Nhiều Truy Vấn

Các Kỹ Thuật Viết Lại Truy Vấn Phổ Biến

Subquery So Với JOIN So Với CTE

EXISTS So Với IN So Với JOIN

Tránh SELECT *

Window Function So Với Subquery

Phân Trang Đúng Cách

Thống Kê Bảng Và Vacuum

ANALYZE: Cập Nhật Thống Kê

Tinh Chỉnh Autovacuum

Phình Bảng

Connection Pooling

Tại Sao Bạn Cần Connection Pooler

Cấu Hình PgBouncer

Chế Độ Transaction So Với Session

Công Thức Kích Thước Pool

Checklist Thực Tế: Các Bước Chính Xác Cho Mỗi Truy Vấn Chậm

Bước 1: Lấy Truy Vấn Thực Tế

Bước 2: Chạy EXPLAIN (ANALYZE, BUFFERS)

Bước 3: Kiểm Tra Thống Kê Bảng

Bước 4: Kiểm Tra Index Hiện Có

Bước 5: Tạo Hoặc Sửa Đổi Index

Bước 6: Cân Nhắc Viết Lại Truy Vấn

Bước 7: Kiểm Tra Cài Đặt Server

Bước 8: Giám Sát Sau Khi Sửa

Bonus: Thắng Nhanh Tôi Áp Dụng Cho Mỗi Database Mới

Suy Nghĩ Cuối Cùng

Bài viết liên quan

How to Compress Images Without Losing Quality: The Complete 2026 Guide

Xác Thực Hiện Đại năm 2026: JWT, Sessions, OAuth và Passkeys