Database · Performance

PostgreSQL Optimization: 10 Kỹ Thuật Tối Ưu Cho Production

📅 06/12/2025⏱ 16 phút đọc🏷️ Database, PostgreSQL

PostgreSQL xử lý trillion requests/ngày cho Instagram, Spotify, Apple, và Notion. DB-Engines ranking 2025 xếp PostgreSQL là #1 open-source RDBMS và đang tăng trưởng nhanh nhất trong top 5. BanhCuonFlow chọn PostgreSQL vì: ACID compliance, JSONB support tuyệt vời, extensibility mạnh mẽ (PostGIS, pgvector, pg_cron), community-driven, và hoàn toàn free. Nhưng PostgreSQL không tự optimize — default config dành cho laptop 512MB RAM. Production cần tuning. 10 kỹ thuật dưới đây giúp query nhanh 10-100x.

10 Kỹ Thuật Tối Ưu

① EXPLAIN ANALYZE: Hiểu Query Execution

Trước khi optimize, phải hiểu query chậm ở đâu. EXPLAIN ANALYZE SELECT * FROM tasks WHERE status = 'active'; cho thấy: Sequential Scan (full table scan — bad) hay Index Scan (good)? Actual rows vs estimated rows — sai lệch lớn nghĩa là stale statistics, cần ANALYZE. Thêm BUFFERS option để xem I/O pattern. Golden rule: "Seq Scan" trên bảng >10K rows → cần index.

② Indexing Strategy

B-tree: Default, phù hợp =, <, >, BETWEEN, ORDER BY — dùng cho 90% cases. GIN: Full-text search, JSONB queries, array contains. Partial index: CREATE INDEX idx ON tasks(status) WHERE is_deleted = false; — index nhỏ hơn 80%, nhanh hơn. Composite index: thứ tự columns quan trọng — high-selectivity column đặt trước. BanhCuonFlow: composite indexes cho top queries (departmentId + status + createdAt).

③ Connection Pooling

PostgreSQL tạo 1 process/connection (fork model) — 100 connections = 100 OS processes = high memory + context switching overhead. Giải pháp: PgBouncer — lightweight proxy, transaction pooling mode trả connection về pool ngay sau transaction kết thúc. BanhCuonFlow: EF Core pooling (app level) + PgBouncer (DB level) = max 50 PG connections serve 500+ concurrent users.

④ VACUUM & ANALYZE

PostgreSQL MVCC: UPDATE/DELETE không xóa row, chỉ mark invisible → "dead tuples" tích lũy. VACUUM dọn dẹp, ANALYZE cập nhật statistics cho query planner. autovacuum enabled by default nhưng cần tuning cho bảng heavy-write: autovacuum_vacuum_scale_factor = 0.05 (5% thay vì 20% default). BanhCuonFlow: monitor pg_stat_user_tables.n_dead_tup, alert khi bloat > 20%.

⑤ Batch Operations

❌ 1000 INSERT riêng lẻ = 1000 round-trips, 1000 transaction commits. ✅ 1 INSERT với 1000 rows = 1 round-trip. Speed: 10-50x faster. EF Core: AddRange() + SaveChanges() tự batch. Bulk update: ExecuteUpdate() (EF Core 7+) thay vì load → modify → save. Large batches: chunk thành 1000 rows/batch tránh long transactions.

⑥ Select Only What You Need

❌ SELECT * trả 30 columns khi chỉ cần 3 → waste I/O, memory, network. ✅ Projection: SELECT id, title, status. Đặc biệt quan trọng khi table có JSONB hoặc TEXT lớn. EF Core: .Select(t => new { t.Id, t.Title }) generates optimized SQL và giảm memory footprint vì không track full entity.

⑦ Pagination Đúng Cách

OFFSET pagination chậm ở page lớn: OFFSET 100000 → skip 100K rows. Page 5000 chậm gấp 5000x page 1. Keyset pagination: WHERE created_at < @lastSeen ORDER BY created_at DESC LIMIT 20; — constant speed mọi page vì dùng index seek. BanhCuonFlow: keyset cho chat messages (millions of rows), OFFSET cho task lists (< 1000 items).

⑧ postgresql.conf Tuning

Default config cho laptop — production cần tuning: shared_buffers = 25% RAM. work_mem = 256MB-1GB (sorts/joins — per-operation). effective_cache_size = 75% RAM. maintenance_work_mem = 512MB (VACUUM faster). random_page_cost = 1.1 (SSD thay vì 4.0 HDD default). PGTune auto-generate config dựa trên hardware.

⑨ Partitioning

Bảng > 100 triệu rows → partition để query chỉ scan data liên quan. Range (by date — audit_logs by month). List (by status, region). Hash (even distribution). Ví dụ: partition audit_logs by month → query tháng 12 scan 1/12 data, VACUUM nhanh hơn, old partitions có thể detach/archive. PG 12+ supports partition pruning tự động.

⑩ pg_stat_statements: Find Slow Queries

Extension built-in: track mọi queries với stats (calls, total_exec_time, mean_exec_time, rows, shared_blks_hit/read). Sort by total_exec_time DESC → top resource-consuming queries. Optimize 5 queries chậm nhất → 80% improvement (Pareto). BanhCuonFlow: enable pg_stat_statements, review weekly, alert khi mean_exec_time > 500ms.