Why I Stopped Writing Database Rows One at a Time

A data-migration pipeline I owned was correct, well-tested, and slow enough that a full run took long enough to become a real operational problem — long enough that a failure partway through meant re-running work that had already succeeded, and long enough that "how long will this take" became a question people had to plan around instead of ignore.

Where the time actually went

The pipeline was correct because it processed records the straightforward way: loop over each row, look up what it depends on, write it, move to the next one. That's the easiest version to write and the easiest version to reason about, and it's also the version where every single row pays the full round-trip cost of a database call, individually, even though the database is perfectly capable of accepting thousands of rows in one statement.

What changed, concretely

The rewrite wasn't a single trick — it was applying the same idea, set-based operations instead of row-by-row ones, everywhere the shape of the work allowed it. Bulk-loading historical records switched to the database's native streaming bulk-copy path instead of individual inserts. Writes that might already exist switched to a single upsert-on-conflict statement instead of a check-then-insert-or-update round trip per row. Batch updates against many rows at once used a values-table join instead of one UPDATE per row. And anywhere the target state already matched the source, the pipeline skipped the write entirely instead of re-writing data that hadn't changed.

What this actually bought

None of these changes altered what the pipeline produced — the output was identical, verified against the row-by-row version on real data before the rewrite replaced it. What changed was that a job that used to take long enough to worry about now finishes fast enough that a failure partway through costs minutes of re-run instead of hours, and the whole migration stopped being something that needed to be scheduled around like a fragile, multi-hour event. The lesson wasn't "premature optimization is fine, actually" — it was that once a straightforward implementation becomes the actual operational bottleneck, the fix is usually not a clever algorithm, it's just asking the database to do the same work in fewer, larger round trips.