Checklist tối ưu khi sử dụng PostgreSQL

I. Kiến trúc tổng thể

1. Luôn dùng Connection Pooling (bắt buộc)

PostgreSQL rất yếu khi xử lý nhiều connection trực tiếp.

➡ Giải pháp:

• PgBouncer (bắt buộc với high traffic)

Mode khuyến nghị:

pool_mode = transaction
max_client_conn = 5000
default_pool_size = 50

2. Không để backend mở quá nhiều connection

II. Database Config

3. Memory tuning chuẩn production

shared_buffers = 25% RAM
work_mem = 16–64MB
maintenance_work_mem = 512MB – 2GB
effective_cache_size = 70–75% RAM

4. WAL & checkpoint tuning

wal_buffers = 16MB
checkpoint_timeout = 15min
checkpoint_completion_target = 0.9

➡ Giảm I/O spike → tránh lag toàn hệ thống.

III. Autovacuum – NGUYÊN NHÂN #1 gây chậm Postgres

5. Autovacuum aggressive hơn default

autovacuum_max_workers = 5
autovacuum_naptime = 30s
autovacuum_vacuum_cost_limit = 3000

6. Tuning theo từng bảng hot

ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.02,
  autovacuum_analyze_scale_factor = 0.01
);

IV. Query & Index – 70% hiệu năng nằm ở đây

7. Index theo đúng pattern truy vấn

Query chúng ta hay có:

WHERE user_id = ?
WHERE status = ?
WHERE created_at >= ?
ORDER BY created_at DESC
JOIN user_id

➡ Index chuẩn:

CREATE INDEX idx_orders_user_created 
ON orders (user_id, created_at DESC);

8. Index cho JSONB

CREATE INDEX idx_meta_gin 
ON events USING GIN (meta jsonb_path_ops);

9. Tránh SELECT *

SELECT id, name, price FROM products;

10. Luôn dùng EXPLAIN ANALYZE

EXPLAIN ANALYZE SELECT ...

V. Framework-level Optimization

11. Eloquent: tránh N+1 queries

User::with('orders')->get();

12. Chunk + Cursor

User::chunk(1000, function($users) {});
User::cursor()->each(...);

13. DB connection pooling

Laravel → PgBouncer → Postgres

KHÔNG connect thẳng Postgres

🅱 NestJS

14. Prisma / TypeORM pool tuning

Prisma:

DATABASE_URL="postgresql://...?connection_limit=20"

TypeORM:

extra: { max: 20 }

15. Query raw cho heavy report

prisma.$queryRaw`
SELECT ...
`

🅲 NextJS

16. Global DB client reuse

global.prisma ||= new PrismaClient();

17. Edge caching + ISR

• Redis cache

• CDN caching

VI. Logging & Observability

18. Log slow queries

log_min_duration_statement = 300ms

19. Enable pg_stat_statements

shared_preload_libraries = 'pg_stat_statements'

CREATE EXTENSION pg_stat_statements;

20. Top slow query tracking

SELECT * FROM pg_stat_statements ORDER BY total_time DESC LIMIT 10;

VII. Caching Layer (BẮT BUỘC HIGH TRAFFIC)

Data	TTL
Homepage	30–120s
Product list	60–300s
API JSON	10–60s
Report	5–30 phút

VIII. High Traffic Advanced Tuning

22. Partition table cho bảng lớn

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

23. Partial index

CREATE INDEX idx_orders_active 
ON orders(created_at) 
WHERE status = 'active';

24. Covering index

CREATE INDEX idx_orders_cover 
ON orders(user_id) 
INCLUDE (status, total_price);