Core Problem

Export trực tiếp (sync) → timeout / OOM / DB overload

Vì:

• Query nặng
• Serialize file (CSV/Excel) tốn CPU/RAM
• Network response quá lớn

Anti-pattern (đừng làm)

Client → API → Query 1M rows → build file → return

Hậu quả:

• Timeout
• Memory crash
• Block thread
• DB spike

Solution: Async Export Pipeline

High-level flow

User click Export
   ↓
API create export job
   ↓
Return job_id (ngay lập tức)
   ↓
Worker xử lý background
   ↓
Generate file (CSV)
   ↓
Upload S3 / storage
   ↓
Notify user (email / UI)

Step by step

1/ Create Export Job

{
  "job_id":"uuid",
  "status":"pending",
  "filters": {...}
}

• Lưu DB / Redis
• Trả response ngay (<100ms)

2/ Background Processing

Dùng:

• Queue: RabbitMQ / Kafka / SQS
• Worker: Node / Go / Python

3/ Fetch data theo batch

❌ Không dùng:

LIMIT1000000 OFFSET ...

✅ Dùng cursor-based / chunking

WHERE id> last_id
LIMIT1000

Loop:

fetch 1000 → write file → next batch

• Không load hết vào RAM
• Stable performance

4/ Stream write file

Không build file trong memory

✅ Dùng streaming

• CSV writer stream
• Append từng batch

5/ Store file

Upload:

• S3 / GCS / MinIO

⇒ Không trả file trực tiếp qua API

6/ Notify user ( Optional )

• Email download link
• WebSocket / polling status

Performance Optimization

1) DB Optimization

• Index đúng filter
• Read replica
• Snapshot data (tránh lock)

2) Parallel processing

• Split theo shard / time range
• Multiple workers

3) Rate limit

• Không cho spam export
• Queue control

Common Pitfalls

❌ DB overload

→ Fix: read replica / throttle

❌ Memory crash

→ Fix: streaming + batch

❌ Duplicate data

→ Fix: snapshot / consistent query

❌ Slow export

→ Fix: async + parallel