คุณจะ import record จาก CSV 100M รายการเข้า DB ให้เร็วได้อย่างไร?

Question

IT Interview Practice · Accepted Answer

ใช้ **native bulk-load path** ของ database (Postgres `COPY`, MySQL `LOAD DATA`), แยกไฟล์เป็น chunk ที่โหลด **แบบขนาน** เข้า **staging table** ที่ปิด index และ constraint **ไว้** จากนั้น rebuild index หนึ่งครั้งแล้ว merge — และทำให้ทั้งงาน **idempotent** เพื่อให้ crash สามารถ resume ต่อได้โดยไม่สร้าง row ซ้ำ

```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
```

## ทำไม INSERT ทีละ row จึงเป็นเครื่องมือที่ผิด

`INSERT` แบบ naive ต่อ row จ่าย round-trip, การ parse, การเขียน WAL/redo และการ update index **100 ล้านครั้ง** แม้แค่ 1ms ต่อครั้งก็เกินหนึ่งวัน bulk path ชนะด้วยการเฉลี่ยต้นทุนทั้งหมดนี้: `COPY` stream row ในข้อความ protocol เดียว, batch WAL และข้าม per-statement planning บนฮาร์ดแวร์จริง `COPY` เดียวทำได้ 100k–500k row/วินาที เทียบกับไม่กี่พันสำหรับ INSERT

## ชิ้นส่วนที่เคลื่อนไหว

- **Staging table ก่อน** โหลดลงตารางที่ทิ้งได้ (`UNLOGGED` ใน Postgres ข้าม WAL ทั้งหมด) ไม่ใช่ลงตาราง live ตรง ๆ สิ่งนี้แยกการโหลดออกจากผู้อ่านและให้คุณ validate ก่อน publish
- **ปิด index และ constraint ระหว่างโหลด** ทุก secondary index และ FK check ถูกประเมินใหม่ต่อ row drop มันทิ้ง, โหลด, แล้ว `CREATE INDEX` หนึ่งครั้ง — การ build index แบบ bulk (เรียงแล้ว) ถูกกว่าการ update ทีละน้อย 100M ครั้งมาก
- **Chunk + parallelise** แยกไฟล์ตาม byte offset เป็น N ชิ้นแล้วรัน N `COPY` stream throughput scale ตาม I/O และ CPU จนกว่าคุณจะทำให้ disk หรือ WAL อิ่มตัว จับคู่ N กับ core/IOPS ไม่ใช่ "มากที่สุดเท่าที่ทำได้"
- **Idempotent + resumable** ติด tag แต่ละ chunk ด้วย id บันทึกว่า chunk ไหน commit แล้ว ตอน restart ให้ข้าม chunk ที่เสร็จ publish ด้วย `INSERT ... ON CONFLICT DO NOTHING/UPDATE` (upsert) เพื่อให้ re-run ไม่ insert ซ้ำ

## มุมมองการสัมภาษณ์

ผู้สัมภาษณ์ตรวจว่าคุณรู้ว่า **bulk path มีอยู่** และ *ทำไม* มันเร็วกว่าหลายเท่าตัว — ไม่ใช่ syntax ของ COPY ดี: "ใช้ COPY" เยี่ยม: "COPY เข้า UNLOGGED staging table, drop index, chunk ขนาน, rebuild ทีหลัง, upsert เพื่อ publish, resume ได้ด้วย chunk id" กับดักคลาสสิกคือวน INSERT (หรือ ORM `saveAll`) แล้วแปลกใจที่ใช้เวลาหลายชั่วโมง กับดักที่สองคือโหลดลงตาราง live ตรง ๆ และบล็อก production

## ข้อแลกเปลี่ยน & failure

- **UNLOGGED / no-WAL หมายถึงไม่มี crash recovery** สำหรับ staging table — โอเคเพราะมันทิ้งได้ แต่หายนะถ้าคุณทำกับตารางจริง
- **Parallel สูงเกินไป** ทำให้ disk และ WAL รวน เกินจุดอิ่มตัว throughput *ลดลง* benchmark หาจุดที่พอดี
- **การ rebuild index ต้องใช้ `maintenance_work_mem`/temp space** การ build index 100M row สามารถล้นลง disk
- **CSV สกปรก** (encoding ผิด, delimiter ฝังใน) ทำให้ทั้ง chunk ล้มเหลว — validate type ใน staging, กัก reject ไว้, ให้การโหลดเดินหน้าต่อ

ทำไม INSERT ทีละ row จึงเป็นเครื่องมือที่ผิด

ชิ้นส่วนที่เคลื่อนไหว

มุมมองการสัมภาษณ์

ข้อแลกเปลี่ยน & failure

คำถามที่เกี่ยวข้อง