A batch job produced 50,000 derived records. Load them into a fresh sqlite store one INSERT-plus-commit() per row and it takes 27.72 s; load them in a single bulk transaction and it takes 0.029 s — the identical rows, ~963x the time. The gap is the commits, not the inserts: row-by-row pays a durable-write (fsync) tax 50,000 times, bulk pays it once. And row-by-row mutation of the live store leaks partial output on a crash, which bulk-load-then-swap does not.
A batch job has produced 50,000 derived records and now has to load them into a serving store. Do it the naive way — one INSERT per record, each commit()ed in its own transaction, the pattern you get by "just calling the DB client inside the job's inner loop" — and it takes 27.72 s. Do it as a single bulk load — all rows in one transaction via executemany, one commit() — and it takes 0.029 s. Same 50,000 rows, same final table, ~963x the time. The gap isn't the inserts; it's the commits: the row-by-row path pays a durable-write (fsync) tax 50,000 times, the bulk path pays it once. And the row-by-row path has a second problem — because it mutates the live table incrementally, a crash mid-load leaks partial output, and the retry duplicates it. Bulk-load-into-a-fresh-table-then-swap keeps the batch job's clean all-or-nothing guarantee.
Designing Data-Intensive Applications, 2nd ed. (Kleppmann & Riccomini, 2025), Chapter 11 — Batch Processing, §"Serving Derived Data":
[Writing to the database one record at a time] is often orders of magnitude slower than the normal throughput of a batch task.
The book's fix is to have the batch job build a brand-new database as its output and load it in bulk, then swap it into the serving tier — never writing to the production store one row at a time from inside the job. Here you measure the "orders of magnitude" for yourself, then watch the partial-output failure that makes the row-by-row pattern wrong even before it's slow.
The magnitude is about what a durable commit costs. One line each; the flagged one carries the result.
fsync — before it returns, so the data survives a crash. One fsync is milliseconds of real device latency, dwarfing the microseconds of the insert itself. It shows up as the 50,000 per-row commits in Method A. Free resource: PostgreSQL — "Asynchronous Commit" explains what a durable commit pays for.fsync covers all N rows. It shows up as Method B's single commit(). Free resource: SQLite — "Faster inserts / transactions" (the FAQ item on why per-statement commits are slow).RENAME. Free resource: DDIA Ch. 11, §"Serving Derived Data" and §"The Output of Batch Workflows".fsync is the entire reason 50,000 tiny commits cost 900x one big one.
sqlite3); no Docker; timings are real wall-clock and vary run-to-run — the large speedup RATIO is the reproducible result, exact seconds are illustrative. Deterministic data (random.Random(42)) fixes the 50,000-record workload; on-disk sqlite databases in a fresh temp dir so fsync actually hits the device. The row counts and failure behavior are exact and reproduce every run; the seconds shift, the ~900x ratio stays.
Same 50,000 rows, same final table — only how you commit differs.
conn.execute(INSERT) then conn.commit(), for every single row. Each committed insert pays a durability (fsync) tax: the DB flushes to stable storage before the commit returns. 50,000 rows → ~50,000 flushes.conn.executemany(INSERT, rows) then one conn.commit(). Batching amortizes one fsync over all 50,000 rows. Same durability, ~1/50000th the flushes.The whole thing is one script, code/bulk_load_vs_row_by_row.py.
cd study/ddia/ch11/code
python3 bulk_load_vs_row_by_row.pyDo not read the output yet — make each prediction first.
Predict. The job loads 50,000 small records into a fresh sqlite table with one INSERT per record, each in its own commit(). Roughly how long — a fraction of a second? A couple of seconds?
27.72 seconds for 50,000 tiny inserts — about 1,800 rows/sec. Each insert is trivial; each commit is not.
Predict. Same 50,000 rows, same table, but now one executemany and a single commit(). How much faster — 2x? 10x?
0.029 seconds — over 1.7 million rows/sec. The inserts were never the cost.
Predict. Divide them. Is row-by-row "a few times" slower, or something much larger?
~963x — three orders of magnitude, for the identical result. That is the book's "orders of magnitude slower than the normal throughput of a batch task," measured. (Exact seconds vary run-to-run; the ~900x ratio is what reproduces.)
Predict. The row-by-row job crashes after writing 30,000 of the 50,000 rows, then retries from the start. How many rows survive the crash, and what does the retry hit when it re-inserts row 1?
30,000 rows survive, and the retry collides with all 30,000. Every row was already committed, so the crash left partial output in the live table; the retry can't re-write it cleanly. The job is not idempotent.
Predict. Now the disciplined version: build a fresh staging table, swap it in atomically. It crashes at the same point — after staging 30,000 rows, before the swap — then retries. How many rows are in the live serving table right after the crash, and after the retry?
Zero partial output; the retry lands exactly 50,000 rows. The staging work was never committed and never swapped in, so the crash left the live table untouched. The retry rebuilds and swaps cleanly — all-or-nothing, re-runnable.
A durable commit is not free: to promise the data survives a crash, the database must flush the write all the way to stable storage and wait for the device to acknowledge — an fsync. That flush is a physical round-trip to the disk, measured in milliseconds; the INSERT that precedes it is a few microseconds of in-memory work. So when you commit() after every row, the cost of the load is essentially N fsyncs: 50,000 × (one device round-trip). At ~1,800 rows/sec, each row is costing ~0.55 ms — that's the fsync, not the insert.
Batching changes the arithmetic completely. Put all 50,000 inserts in one transaction and there is exactly one commit, so one fsync makes all 50,000 rows durable at once. The per-row work is unchanged; the durability tax is paid once instead of 50,000 times. The speedup is therefore approximately the number of commits you eliminated — you collapsed ~50,000 flushes into ~1, and the measured ~900x is that amortization (a bit under 50,000 because the bulk path still does real per-row insert work and some fixed overhead). This is exactly why every database ships a bulk path (COPY, LOAD DATA, executemany in one transaction): the point of it is to amortize the commit.
The second failure is independent of speed and arguably worse. Committing per row means each row is durably in the live serving table the instant it's written. A job that dies after 30,000 rows has therefore mutated the production store with partial output, and a retry — which re-runs from the start — writes those 30,000 rows a second time. It is not idempotent: re-running the "same" job does not give the same result. The batch-processing discipline avoids this entirely by treating output as immutable: build a fresh table off to the side, and only when it's completely built, swap it in with an atomic RENAME. A crash before the swap leaves zero trace in the live table (the uncommitted staging work simply evaporates), so the retry rebuilds from scratch and lands exactly 50,000 rows. All-or-nothing, and re-runnable as many times as you like.
PRAGMA synchronous=OFF, or an in-memory :memory: database that never fsyncs — and even commit-per-row runs fast, because there's no device round-trip to pay; but you've traded away crash-safety to get there. (c) It's the commit that's expensive, not the statement: 50,000 executes in one transaction is cheap, 50,000 transactions is not. So the rule of thumb is not "inserts are slow" — it's "durable commits are slow, so amortize them," which is the whole reason batch jobs bulk-load and swap rather than trickle rows into production.
PRAGMA synchronous=OFF or into :memory:, and watch commit-per-row nearly catch up to bulk — proving the cost was the fsync, not the SQL. Then reason about what durability you gave up.COPY ... FROM (or \copy) and compare it to a loop of INSERTs over a real connection; the fsync-amortization story is identical, and COPY adds streaming parse wins.Sources: DDIA 2e, Ch. 11, §"Serving Derived Data", §"The Output of Batch Workflows", §"Fault tolerance".