Tối ưu PostgreSQL cho ứng dụng 1 triệu người dùng

Một trong những dự án thú vị nhất của chúng tôi là tối ưu PostgreSQL cho một nền tảng e-learning với 50.000 học viên và đang tăng trưởng nhanh. Database response time đã từng 800ms xuống còn 12ms chỉ với index optimization và query rewriting — không cần thay đổi infrastructure. Bài viết này chia sẻ những kỹ thuật chúng tôi thường dùng nhất.

Bắt đầu từ indexing — đây là nơi có ROI cao nhất. `EXPLAIN ANALYZE` là công cụ bắt buộc phải dùng trước khi tạo bất kỳ index nào. Tìm các query có Sequential Scan trên bảng lớn và có filter conditions. Partial indexes cực kỳ hữu ích cho các query filter theo trạng thái — ví dụ `CREATE INDEX ON orders (created_at) WHERE status = 'pending'` chỉ index những đơn hàng đang chờ xử lý, nhỏ hơn nhiều và nhanh hơn nhiều. Composite indexes cần chú ý thứ tự cột: cột có selectivity cao nhất (nhiều giá trị unique) nên đứng trước. Và đừng over-index — mỗi index tốn thêm disk space và làm chậm writes.

Connection pooling là cấu hình quan trọng thứ hai. PostgreSQL tạo một process mới cho mỗi connection — với 1.000 concurrent connections, bạn có 1.000 process. PgBouncer là giải pháp chuẩn: cấu hình pool_mode = transaction, max_client_conn = 10.000, default_pool_size = 50-100. Autovacuum cần được tune theo workload — với bảng có nhiều UPDATE/DELETE, tăng tần suất vacuum để tránh table bloat. Partition lớn tables theo range (thường là created_at) giúp query chỉ scan partition cần thiết thay vì toàn bộ bảng.

Với scale từ 500K+ users, cần xem xét thêm: read replicas để phân tải read queries (thường 70-80% traffic là reads), caching layer với Redis cho các query thường xuyên và ít thay đổi, materialized views cho các aggregation query phức tạp. Cuối cùng, monitoring không thể thiếu: pg_stat_statements để identify slow queries, pg_stat_user_tables để theo dõi vacuum/analyze và table bloat. DataDog hoặc Prometheus với postgres_exporter cho real-time metrics. Nhớ rằng: optimize sớm là gốc rễ của mọi cái ác — hãy đo lường trước, optimize sau khi có dữ liệu cụ thể.