Ta yaya za ka import records miliyan 100 na CSV zuwa DB da sauri?

Question

IT Interview Practice · Accepted Answer

Yi amfani da **native bulk-load path** na database (Postgres `COPY`, MySQL `LOAD DATA`), raba file zuwa chunks da ake load **a parallel** zuwa **staging table** da indexes da constraints **disabled**, sannan ka sake gina indexes sau ɗaya kuma ka merge — kuma ka mai da dukan aikin **idempotent** domin crash ya iya resume ba tare da kwafin rows ba.

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

## Me ya sa row-by-row INSERT kayan aiki ne mara kyau

`INSERT` mara kyau ga kowace row yana biyan round-trip, parse, WAL/redo write da index update **sau miliyan 100**. A ko 1ms kowanne wannan sama da rana ɗaya ne. Bulk paths suna nasara ta hanyar amortising duk wannan: `COPY` yana stream rows a protocol message ɗaya, yana batch WAL, kuma yana tsallake per-statement planning. A ainihin hardware `COPY` ɗaya yana yin rows 100k–500k/daƙiƙa vs 'yan dubbai ga INSERT.

## Sassa masu motsi

- **Staging table da farko.** Load zuwa throwaway table (`UNLOGGED` a Postgres yana tsallake WAL gaba ɗaya), ba kai tsaye zuwa live table ba. Wannan yana ware load daga readers kuma yana bar ka validate kafin publish.
- **Disable indexes da constraints a lokacin load.** Kowane secondary index da FK check ana sake tantance shi ga kowace row. Drop su, load, sannan `CREATE INDEX` sau ɗaya — gina index a bulk (sorted) ya fi arha fiye da 100M incremental updates.
- **Chunk + parallelise.** Raba file da byte offset zuwa pieces N kuma ka gudanar da `COPY` streams N. Throughput yana scale da I/O da CPU har sai ka cika disk ko WAL. Daidaita N zuwa cores/IOPS, ba "da yawa gwargwadon iko ba".
- **Idempotent + resumable.** Tag kowane chunk da id; rikodin waɗanne chunks suka commit. A restart, tsallake done chunks. Publish da `INSERT ... ON CONFLICT DO NOTHING/UPDATE` (upsert) domin sake gudanarwa kada ya double-insert.

## Ra'ayin hira

Mai yin hirar yana bincike ko ka san **bulk path yana wanzuwa** kuma *me ya sa* yake da sauri sau da dama — ba COPY syntax ba. Mai kyau: "amfani da COPY". Mai kyau sosai: "COPY zuwa UNLOGGED staging table, indexes dropped, parallel chunks, rebuild after, upsert to publish, resumable by chunk id." Tarkon gargajiya shi ne looping INSERTs (ko ORM `saveAll`) kuma ka yi mamaki cewa yana ɗaukar sa'o'i; tarko na biyu shi ne loading kai tsaye zuwa live table kuma ka toshe production.

## Sulhu & hanyoyin gazawa

- **UNLOGGED / no-WAL yana nufin babu crash recovery** ga staging table — yayi kyau domin disposable ne, mai kisa idan ka yi shi ga real table.
- **Parallel mai yawa sosai** yana thrash disk da WAL; sama da saturation throughput yana *faɗuwa*. Benchmark sweet spot.
- **Sake gina indexes yana buƙatar `maintenance_work_mem`/temp space**; 100M-row index build na iya spill zuwa disk.
- **Dirty CSV** (bad encoding, embedded delimiters) yana gaza dukan chunk — validate types a staging, quarantine rejects, ci gaba da load.

Me ya sa row-by-row INSERT kayan aiki ne mara kyau

Sassa masu motsi

Ra'ayin hira

Sulhu & hanyoyin gazawa

Tambayoyi masu alaƙa