data10 phút đọc

Tối ưu hiệu năng PostgreSQL: Index, Query Plan và Configuration

Tối ưu PostgreSQL cho production — chiến lược index, đọc EXPLAIN ANALYZE, connection pooling, partitioning và cấu hình postgresql.conf.

Bởi Ventra Rocket

·Đăng ngày 20 tháng 2, 2026

#PostgreSQL#Database#Performance#SQL#Indexing

PostgreSQL mặc định khá conservative. Hướng dẫn này covers chiến lược index, tối ưu query, và configuration tuning Ventra Rocket áp dụng cho production.

1. EXPLAIN ANALYZE

Luôn đo lường trước khi tối ưu:

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT u.name, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.status = 'active'
GROUP BY u.id, u.name
ORDER BY order_count DESC LIMIT 20;

Các chỉ số cần chú ý:

Seq Scan → thiếu index trên bảng lớn (thường xấu)
Index Scan → đang dùng index (tốt)
actual time → thời gian thực
Sai số lớn giữa ước tính và thực tế → thống kê cũ

2. Chiến lược Index

-- Composite index — cột có selectivity cao nhất đứng đầu
CREATE INDEX idx_orders_status_date ON orders(status, created_at DESC);

-- Partial index — chỉ index dữ liệu cần thiết
CREATE INDEX idx_active_users ON users(email) WHERE status = 'active';

-- Covering index — tránh table lookup
CREATE INDEX idx_orders_covering ON orders(user_id)
  INCLUDE (total_amount, status, created_at);

3. Tối ưu Query

-- CTE cho query phức tạp
WITH monthly_revenue AS (
  SELECT
    DATE_TRUNC('month', created_at) AS month,
    SUM(total) AS revenue
  FROM orders
  WHERE status = 'completed'
    AND created_at >= NOW() - INTERVAL '12 months'
  GROUP BY 1
)
SELECT month, revenue FROM monthly_revenue ORDER BY month;

4. Connection Pooling với PgBouncer

[pgbouncer]
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 20

5. Partitioning theo thời gian

CREATE TABLE orders (
  id BIGSERIAL,
  created_at TIMESTAMPTZ DEFAULT NOW()
) PARTITION BY RANGE (created_at);

CREATE TABLE orders_2026 PARTITION OF orders
  FOR VALUES FROM ('2026-01-01') TO ('2027-01-01');

6. Cấu hình postgresql.conf

shared_buffers = 4GB          # 25% RAM
effective_cache_size = 12GB   # 75% RAM
work_mem = 64MB
maintenance_work_mem = 1GB
random_page_cost = 1.1        # SSD
autovacuum_vacuum_scale_factor = 0.01

Kết luận

Hiệu năng PostgreSQL 80% là về indexing, 20% là configuration. Ventra Rocket đã giảm query time từ vài giây xuống millisecond trên bảng 100 triệu+ row.