Khi ứng dụng mở rộng quy mô lên hàng triệu người dùng hoạt động (Active Users), cơ sở dữ liệu quan hệ thường trở thành điểm nghẽn nghiêm trọng nhất (Primary Bottleneck). Bài viết này đúc kết các kỹ thuật tối ưu hóa cơ sở dữ liệu PostgreSQL từ thực chiến sản xuất, tập trung vào chiến lược Indexing chuyên sâu, quản trị Connection Pool với PgBouncer và kỹ thuật phân vùng bảng (Declarative Table Partitioning).

1. Phân Tích Kế Hoạch Thực Thi Với EXPLAIN (ANALYZE, BUFFERS)

Công cụ quan trọng nhất để tìm ra nguyên nhân câu truy vấn SQL chậm chạp chính là EXPLAIN (ANALYZE, BUFFERS). Không chỉ hiển thị cây thực thi giả định, tham số BUFFERS còn chỉ rõ số lượng Disk Page được đọc từ RAM (shared_buffers) hay phải nạp trực tiếp từ ổ cứng SSD (read).

EXPLAIN (ANALYZE, BUFFERS, COSTS, VERBOSE)
SELECT o.id, o.user_id, o.total_amount, o.created_at
FROM orders o
WHERE o.user_id = 105492
  AND o.status = 'COMPLETED'
  AND o.created_at >= '2026-01-01 00:00:00'
ORDER BY o.created_at DESC
LIMIT 20;

2. Chiến Lược Đánh Index Chuyên Sâu (Advanced Indexing)

2.1 B-Tree Composite Index Theo Quy Tắc ESR (Equality, Sort, Range)

Khi tạo chỉ mục phức hợp (Composite Index) cho các câu lệnh chứa nhiều điều kiện lọc và sắp xếp, thứ tự các cột trong index bắt buộc phải tuân theo quy tắc:
1. E (Equality): Các cột so sánh bằng (user_id = 105492, status = 'COMPLETED').
2. S (Sort): Các cột trong mệnh đề ORDER BY (created_at DESC).
3. R (Range): Các cột so sánh khoảng (created_at >= ...).

-- Composite Index tối ưu cho truy vấn đơn hàng
CREATE INDEX CONCURRENTLY idx_orders_user_status_created 
ON orders (user_id, status, created_at DESC);

2.2 Partial Index (Chỉ Mục Một Phần)

Thay vì index toàn bộ 50 triệu bản ghi, chúng ta chỉ lập chỉ mục cho các trạng thái thường xuyên được truy vấn, giúp giảm 80% kích thước index trên RAM:

-- Chỉ index các đơn hàng đang chờ xử lý (chiếm < 2% tổng bảng)
CREATE INDEX CONCURRENTLY idx_orders_unprocessed 
ON orders (created_at) 
WHERE status IN ('PENDING', 'PROCESSING');

3. Quản Lý Kết Nối Với PgBouncer Connection Pooler

PostgreSQL sử dụng mô hình đa tiến trình (Process-based Model). Mỗi kết nối client mở mới sẽ tiêu tốn khoảng 2MB - 10MB RAM và chi phí fork tiến trình từ hệ điều hành. Khi có 5,000 requests đồng thời, server sẽ cạn kiệt CPU do hiện tượng tranh chấp tài nguyên (Lock Contention & Context Switch).

Cấu Hình PgBouncer Mẫu Cho Hệ Thống High-Throughput

[databases]
showtech_db = host=127.0.0.1 port=5432 dbname=showtech_prod pool_size=50

[pgbouncer]
listen_port = 6432
listen_addr = 0.0.0.0
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 10000
default_pool_size = 40
min_pool_size = 10
reserve_pool_size = 5
max_db_connections = 100

4. Kỹ Thuật Phân Vùng Bảng (Declarative Table Partitioning)

Khi bảng order_events vượt mốc 100 triệu dòng, B-Tree index không thể nằm trọn trong RAM. Giải pháp là phân vùng bảng theo thời gian (Range Partitioning theo tháng):

-- Bảng cha (Partitioned Root Table)
CREATE TABLE order_events (
    id BIGSERIAL,
    order_id UUID NOT NULL,
    event_type VARCHAR(50) NOT NULL,
    payload JSONB,
    created_at TIMESTAMPTZ NOT NULL,
    PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);

-- Các bảng con theo tháng
CREATE TABLE order_events_2026_09 PARTITION OF order_events
    FOR VALUES FROM ('2026-09-01 00:00:00+00') TO ('2026-10-01 00:00:00+00');

CREATE TABLE order_events_2026_10 PARTITION OF order_events
    FOR VALUES FROM ('2026-10-01 00:00:00+00') TO ('2026-11-01 00:00:00+00');

5. Tổng Kết Checklist Tối Ưu PostgreSQL Production

• [x] Đánh chỉ mục với từ khóa CREATE INDEX CONCURRENTLY để không khóa bảng khi hệ thống đang chạy.
• [x] Đặt shared_buffers = 25% tổng RAM của Server.
• [x] Đặt effective_cache_size = 75% tổng RAM của Server.
• [x] Sử dụng PgBouncer Transaction Pooling trước tầng API.
• [x] Thiết lập Auto-vacuum chủ động tránh hiện tượng Transaction ID Wraparound và Table Bloat.