Use the database's native bulk-load path (Postgres COPY, MySQL ), split the file into chunks loaded into a with indexes and constraints , then rebuild indexes once and merge — and make the whole job so a crash can resume without duplicating rows.
LOAD DATA100M-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
A naive INSERT per row pays a round-trip, a parse, a WAL/redo write and an index update 100 million times. At even 1ms each that is over a day. Bulk paths win by amortising all of this: COPY streams rows in one protocol message, batches WAL, and skips per-statement planning. On real hardware a single COPY does 100k–500k rows/s versus a few thousand for INSERT.
UNLOGGED in Postgres skips WAL entirely), never straight into the live table. This isolates the load from readers and lets you validate before publishing.CREATE INDEX once — building an index in bulk (sorted) is far cheaper than 100M incremental updates.COPY streams. Throughput scales with I/O and CPU until you saturate disk or WAL. Match N to cores/IOPS, not "as many as possible".INSERT ... ON CONFLICT DO NOTHING/UPDATE (upsert) so re-running never double-inserts.The interviewer is checking whether you know the bulk path exists and why it is orders of magnitude faster — not COPY syntax. Good: "use COPY". Great: "COPY into an UNLOGGED staging table, indexes dropped, parallel chunks, rebuild after, upsert to publish, resumable by chunk id." The classic trap is looping INSERTs (or an ORM saveAll) and being surprised it takes hours; the second trap is loading directly into the live table and blocking production.
maintenance_work_mem/temp space; a 100M-row index build can spill to disk.A library of IT interview questions with detailed answers — from Junior to Senior.
Donate