Làm sao import 100M bản ghi CSV vào DB thật nhanh?

Question

IT Interview Practice · Accepted Answer

Dùng **đường bulk-load gốc** của database (`COPY` của Postgres, `LOAD DATA` của MySQL), chia file thành các chunk nạp **song song** vào một **staging table** đã **tắt** index và constraint, rồi rebuild index một lần và merge — và làm cả job **idempotent** để khi crash có thể chạy lại mà không nhân đôi dữ liệu.

```text
100M-row CSV
     │ split by byte offset
     ▼
[chunk 1] [chunk 2] ... [chunk N]     N parallel workers
     │        │             │
     └──── COPY / LOAD DATA (no indexes) ────▶ staging_table (UNLOGGED)
                                                    │
                                          rebuild indexes + validate
                                                    │
                                    INSERT ... SELECT (upsert) into target
```

## Vì sao INSERT từng dòng là công cụ sai

Một `INSERT` cho mỗi dòng phải trả một round-trip, một lần parse, một lần ghi WAL/redo và một lần cập nhật index — lặp lại **100 triệu lần**. Chỉ 1ms mỗi lần thôi cũng đã hơn một ngày. Đường bulk thắng nhờ phân bổ đều tất cả chi phí đó: `COPY` stream các dòng trong một message của protocol, gộp batch WAL, và bỏ qua bước lập kế hoạch cho từng câu lệnh. Trên phần cứng thật, một `COPY` đạt 100k–500k dòng/s so với vài nghìn của INSERT.

## Các thành phần chính

- **Staging table trước.** Nạp vào một bảng dùng-rồi-bỏ (`UNLOGGED` trong Postgres bỏ WAL hoàn toàn), đừng bao giờ nạp thẳng vào bảng live. Việc này cách ly quá trình load khỏi reader và cho phép validate trước khi publish.
- **Tắt index và constraint khi load.** Mỗi secondary index và kiểm tra FK bị đánh giá lại theo từng dòng. Drop chúng đi, load, rồi `CREATE INDEX` một lần — dựng index theo lô (đã sort) rẻ hơn nhiều so với 100M lần cập nhật tăng dần.
- **Chunk + song song.** Chia file theo byte offset thành N phần và chạy N stream `COPY`. Throughput tăng theo I/O và CPU cho tới khi bão hoà disk hoặc WAL. Chọn N theo số core/IOPS, không phải "càng nhiều càng tốt".
- **Idempotent + resumable.** Gắn mỗi chunk một id; ghi lại chunk nào đã commit. Khi khởi động lại, bỏ qua chunk đã xong. Publish bằng `INSERT ... ON CONFLICT DO NOTHING/UPDATE` (upsert) để chạy lại không bao giờ chèn trùng.

## Góc nhìn phỏng vấn

Người phỏng vấn dò xem bạn có biết **đường bulk tồn tại** và *vì sao* nó nhanh gấp nhiều bậc — không phải cú pháp COPY. Khá: "dùng COPY". Xuất sắc: "COPY vào staging table UNLOGGED, drop index, chunk song song, rebuild sau, upsert để publish, resume theo chunk id." Bẫy kinh điển là loop INSERT (hoặc `saveAll` của ORM) rồi ngạc nhiên vì mất hàng giờ; bẫy thứ hai là nạp thẳng vào bảng live và làm nghẽn production.

## Đánh đổi & kịch bản hỏng

- **UNLOGGED / không WAL nghĩa là không recovery khi crash** cho staging table — ổn vì nó vứt được, nhưng chí mạng nếu bạn làm vậy với bảng thật.
- **Song song quá cao** làm disk và WAL quá tải; qua điểm bão hoà throughput *giảm*. Hãy benchmark điểm tối ưu.
- **Rebuild index cần `maintenance_work_mem`/không gian temp**; dựng index 100M dòng có thể tràn ra disk.
- **CSV bẩn** (sai encoding, delimiter lẫn trong dữ liệu) làm hỏng cả một chunk — validate kiểu ngay ở staging, cách ly dòng lỗi, giữ luồng load chạy tiếp.

Vì sao INSERT từng dòng là công cụ sai

Các thành phần chính

Góc nhìn phỏng vấn

Đánh đổi & kịch bản hỏng

Câu hỏi liên quan