Tối ưu PostgreSQL: Index, Query Plan và những bài học từ production — hoatq.dev

cat blog/.md

Tối ưu PostgreSQL: Index, Query Plan và những bài học từ production

date: tags: postgresql, database, performance, backend

Bạn có một API response bình thường mất 50ms, bỗng một ngày đẹp trời nó nhảy lên 3 giây. Không ai đổi code, không ai đổi infra — chỉ đơn giản là database đã lớn hơn. Chào mừng bạn đến với thế giới tối ưu PostgreSQL.

Bài viết này mình chia sẻ những bài học thực tế từ việc tối ưu PostgreSQL trong các dự án production — không phải lý thuyết sách giáo khoa, mà là những thứ mình đã đau và đã sửa.

EXPLAIN ANALYZE — công cụ số 1 mà ít ai dùng đúng

Trước khi tối ưu bất kỳ query nào, bạn cần hiểu nó đang chạy như thế nào. EXPLAIN ANALYZE là câu trả lời.

EXPLAIN ANALYZE
SELECT * FROM orders
WHERE user_id = 'abc-123'
  AND status = 'completed'
ORDER BY created_at DESC
LIMIT 20;

Output sẽ trông giống thế này:

Limit  (cost=1234.56..1234.78 rows=20 width=256) (actual time=847.123..847.156 rows=20 loops=1)
  -> Sort  (cost=1234.56..1267.89 rows=13345 width=256) (actual time=847.120..847.140 rows=20 loops=1)
        Sort Key: created_at DESC
        Sort Method: top-N heapsort  Memory: 35kB
        -> Seq Scan on orders  (cost=0.00..1123.45 rows=13345 width=256) (actual time=0.025..823.456 rows=13278 loops=1)
              Filter: ((user_id = 'abc-123') AND (status = 'completed'))
              Rows Removed by Filter: 486722
Planning Time: 0.156 ms
Execution Time: 847.234 ms

Hai thứ cần chú ý ngay:

  1. Seq Scan — PostgreSQL đang quét toàn bộ bảng. Với 500K rows, đây là thảm hoạ
  2. Rows Removed by Filter: 486722 — quét gần 500K rows chỉ để lấy 13K rows phù hợp

Giải pháp hiển nhiên: thêm index.

Chọn đúng loại index

Không phải cứ thêm index là nhanh. Chọn sai loại index đôi khi còn chậm hơn không có index.

B-Tree — mặc định và đủ dùng 90% trường hợp

-- Index đơn
CREATE INDEX idx_orders_user_id ON orders(user_id);

-- Composite index — thứ tự cột QUAN TRỌNG
CREATE INDEX idx_orders_user_status_created
  ON orders(user_id, status, created_at DESC);

Quy tắc vàng cho composite index: Cột dùng trong WHERE = đặt trước, cột dùng trong ORDER BY đặt sau. Cột có selectivity cao (ít giá trị trùng) nên đứng đầu.

Với index trên, query ban đầu sẽ dùng Index Scan thay vì Seq Scan — từ 847ms xuống còn ~2ms.

Partial Index — index thông minh

Bảng orders có 500K rows nhưng chỉ 5% là status = 'pending'? Tạo partial index:

CREATE INDEX idx_orders_pending
  ON orders(user_id, created_at DESC)
  WHERE status = 'pending';

Index này nhỏ hơn 95% so với full index, chiếm ít storage và RAM hơn, nhưng query filter WHERE status = 'pending' vẫn nhanh như thường.

GIN Index — cho JSONB và full-text search

-- JSONB search
CREATE INDEX idx_products_metadata ON products USING gin(metadata);

-- Full-text search
CREATE INDEX idx_products_search ON products USING gin(to_tsvector('simple', name || ' ' || description));

GIN phù hợp khi bạn query vào JSON fields hoặc cần tìm kiếm text. Nhưng đừng dùng GIN cho equality check đơn giản — B-Tree nhanh hơn nhiều cho trường hợp đó.

5 sai lầm mình từng mắc phải

1. Index mọi thứ

Mỗi index thêm overhead cho INSERT/UPDATE/DELETE. Bảng có 10 index? Mỗi lần insert phải cập nhật 10 index. Mình từng thấy bảng có 15 index mà 8 cái không query nào dùng tới.

Kiểm tra index không được sử dụng:

SELECT
  schemaname, tablename, indexname,
  idx_scan as times_used,
  pg_size_pretty(pg_relation_size(indexrelid)) as index_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND schemaname = 'public'
ORDER BY pg_relation_size(indexrelid) DESC;

Index nào times_used = 0 sau vài tuần production? Cân nhắc xoá đi.

2. SELECT * thay vì chọn đúng cột

-- ❌ Lấy tất cả 30 cột dù chỉ cần 3
SELECT * FROM orders WHERE user_id = 'abc-123';

-- ✅ Chỉ lấy những gì cần
SELECT id, status, total_amount FROM orders WHERE user_id = 'abc-123';

Với SELECT *, PostgreSQL phải đọc full row từ heap table ngay cả khi index có đủ data. Chọn đúng cột cho phép PostgreSQL dùng Index Only Scan — nhanh hơn đáng kể vì không cần đọc table.

3. N+1 Query — kẻ giết performance thầm lặng

Đây là lỗi ORM kinh điển:

# ❌ N+1: 1 query lấy orders + N query lấy user cho mỗi order
orders = await session.execute(select(Order).limit(50))
for order in orders:
    user = await session.execute(
        select(User).where(User.id == order.user_id)
    )

51 queries cho 50 records. Với 1000 records? 1001 queries.

# ✅ Eager loading: 1-2 queries cho tất cả
from sqlalchemy.orm import selectinload

orders = await session.execute(
    select(Order)
    .options(selectinload(Order.user))
    .limit(50)
)

Nếu dùng raw SQL, JOIN hoặc subquery:

SELECT o.id, o.status, o.total_amount, u.name as user_name
FROM orders o
JOIN users u ON u.id = o.user_id
LIMIT 50;

4. Không dùng connection pooling

Mỗi connection PostgreSQL tốn 5-10MB RAM. 100 connections = 1GB RAM chỉ cho connections. Trong production, bắt buộc phải dùng connection pooler.

# docker-compose.yml — PgBouncer
pgbouncer:
  image: edoburu/pgbouncer
  environment:
    DATABASE_URL: postgres://user:pass@postgres:5432/mydb
    POOL_MODE: transaction
    DEFAULT_POOL_SIZE: 20
    MAX_CLIENT_CONN: 200

POOL_MODE: transaction cho phép 200 client connections nhưng chỉ dùng 20 PostgreSQL connections thực. Tiết kiệm RAM và tránh too many connections.

5. Thiếu VACUUM và ANALYZE

PostgreSQL dùng MVCC — mỗi UPDATE/DELETE tạo “dead tuples” (bản ghi cũ chưa xoá). Nếu không VACUUM, bảng phình to dần, query chậm dần.

-- Kiểm tra dead tuples
SELECT relname, n_dead_tup, n_live_tup,
  round(n_dead_tup::numeric / greatest(n_live_tup, 1) * 100, 1) as dead_ratio
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC;

PostgreSQL có autovacuum, nhưng config mặc định quá conservative cho bảng lớn:

-- Điều chỉnh autovacuum cho bảng hot
ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.01,    -- vacuum khi 1% rows dead (mặc định 20%)
  autovacuum_analyze_scale_factor = 0.005   -- analyze khi 0.5% rows thay đổi
);

Monitoring: Biết trước khi user phàn nàn

Đừng đợi đến khi API chậm mới đi check. Thiết lập monitoring chủ động:

-- Top 10 slow queries (cần bật pg_stat_statements)
SELECT
  calls,
  round(mean_exec_time::numeric, 2) as avg_ms,
  round(total_exec_time::numeric, 2) as total_ms,
  left(query, 80) as query_preview
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

pg_stat_statements là extension bắt buộc cho production. Nó track mọi query đã chạy cùng thống kê thời gian — giúp bạn tìm bottleneck trước khi nó thành incident.

-- Bật extension
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Tổng kết

PostgreSQL rất mạnh, nhưng nó không tự tối ưu cho bạn. Checklist ngắn gọn:

  1. Luôn dùng EXPLAIN ANALYZE trước khi kết luận query chậm vì lý do gì
  2. Composite index với thứ tự cột đúng giải quyết 80% vấn đề
  3. Partial index cho những query filter cố định — nhỏ gọn và hiệu quả
  4. Xoá index không dùng — chúng chỉ làm chậm write operations
  5. Tránh N+1 query — dùng eager loading hoặc JOIN
  6. Connection pooling là bắt buộc cho production
  7. Monitor với pg_stat_statements — biết trước, sửa trước

Tối ưu database không phải là việc làm một lần rồi quên. Dữ liệu tăng, pattern thay đổi, query mới xuất hiện — cần review định kỳ. Nhưng với những kiến thức cơ bản trên, bạn đã có thể xử lý phần lớn các vấn đề performance rồi.

Bạn đang chạy PostgreSQL cho project nào? Đã gặp vấn đề performance chưa? Chia sẻ với mình qua email nhé!

// reactions

← cd ../blog EOF
hoatq@dev : ~/blog $