# DDIA Ch. 11: loading a batch job's output row-by-row is ~900x slower than bulk

## Concept

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.

## Provenance

*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.

## Prerequisites

The magnitude is about what a *durable commit* costs. One line each; the flagged
one carries the result.

1. **A durable commit forces an fsync (carries the result).** Committing a
   transaction means the database must flush the write to *stable storage* — an
   `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"](https://www.postgresql.org/docs/current/wal-async-commit.html)
   explains what a durable commit pays for.
2. **Batching amortizes one fsync over many rows.** Put N inserts in *one*
   transaction and there is *one* commit, so one `fsync` covers all N rows. It
   shows up as Method B's single `commit()`. Free resource: [SQLite — "Faster
   inserts / transactions"](https://www.sqlite.org/faq.html#q19) (the FAQ item on
   why per-statement commits are slow).
3. **Batch atomicity via output-then-swap.** A batch job should treat its output
   as immutable: build a fresh table, then atomically swap it in, so a failed run
   is simply discarded and re-run. It shows up as Method D's staging table +
   `RENAME`. Free resource: DDIA Ch. 11, §"Serving Derived Data" and §"The Output
   of Batch Workflows".
4. **Idempotence of reloads.** A load you can re-run without changing the result
   is *idempotent*. Row-by-row mutation of the live store is not (a retry
   double-writes); rebuild-and-swap is. It shows up in the retry collisions of
   Method C vs. the clean rerun of Method D. Free resource: DDIA Ch. 11,
   §"Fault tolerance" (deterministic, re-runnable tasks).

### Where to learn the prerequisites

- **Why per-row commits are slow (#1, #2):** the fsync-per-commit cost is the
  whole exercise — a durable commit is a device round-trip, and you either pay it
  N times or once.
- **Why output-then-swap (#3, #4):** DDIA Ch. 11 §"Serving Derived Data" — the
  batch job produces derived data it can always rebuild, so its output should be
  disposable and atomically swapped, never patched in place.

If only one is new, make it **#1** — the per-commit `fsync` is the entire reason
50,000 tiny commits cost 900x one big one.

## Environment

macOS 26.5.2 (Darwin 25.5.0), arm64; Python 3.15.0a8, standard library only
(`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)` generates the 50,000 records, so the
  workload is fixed every run.
- On-disk sqlite databases in a fresh temp dir (so `fsync` actually hits the
  device — an in-memory database would erase the gap; see the boundary).
- The row counts and the failure behavior (partial output, retry collisions) are
  exact and reproduce every run; the seconds shift by run, the ratio stays ~900x.

## Mental model

Same 50,000 rows, same final table — only *how you commit* differs.

- **Row-by-row (Method A)** — `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.
- **Bulk (Method B)** — `conn.executemany(INSERT, rows)` then one `conn.commit()`.
  Batching amortizes *one* fsync over all 50,000 rows. Same durability, ~1/50000th
  the flushes.
- **Row-by-row into the live store (Method C)** — because each row is committed
  as it's written, a crash leaves partial output *in the serving table*; the retry
  re-inserts from the start and collides with what's already there. Not idempotent.
- **Bulk-load-then-swap (Method D)** — build a *fresh* staging table, then swap it
  in atomically. A crash before the swap leaves the live table untouched, so the
  retry rebuilds from scratch and swaps cleanly. All-or-nothing, re-runnable.

The whole thing is one script, `code/bulk_load_vs_row_by_row.py`.

## Setup

```
cd study/ddia/ch11/code
python3 bulk_load_vs_row_by_row.py
```

Do **not** read the output yet — make each prediction first.

## Steps

### Step 1 — load 50,000 rows row-by-row

**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?

**Observe.**

```
A) row-by-row: one INSERT per record, each in its OWN transaction (commit-per-row):
    loaded 50,000 rows in 27.72 s
    = 1,804 rows/sec
```

**27.72 seconds** for 50,000 tiny inserts — about 1,800 rows/sec. Each insert is
trivial; each *commit* is not.

### Step 2 — load the same 50,000 rows in bulk

**Predict.** Same 50,000 rows, same table, but now one `executemany` and a single
`commit()`. How much faster — 2x? 10x?

**Observe.**

```
B) bulk: all rows in ONE transaction (executemany + single commit):
    loaded 50,000 rows in 0.029 s
    = 1,736,347 rows/sec
```

**0.029 seconds** — over 1.7 *million* rows/sec. The inserts were never the cost.

### Step 3 — the ratio

**Predict.** Divide them. Is row-by-row "a few times" slower, or something much
larger?

**Observe.**

```
ratio:
    row-by-row / bulk  =  27.72 s / 0.029 s  ~=  963x slower
    both loaded the identical 50,000 rows -- same result, ~963x the time.
```

**~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.)

### Step 4 — crash a row-by-row load, then retry it

**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?

**Observe.**

```
C) row-by-row load CRASHES after 30,000 rows, then the job RETRIES:
    partial output surviving the crash (already-committed rows): 30,000
    on retry, rows that collide with that partial output:        30,000
    -> the crashed run leaked 30,000 rows the retry cannot cleanly re-write;
       row-by-row mutation of the live store is NOT idempotent under retry.
```

**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.

### Step 5 — crash a bulk-load-then-swap at the same point

**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?

**Observe.**

```
D) bulk-load-then-swap CRASHES at the same point, then RETRIES:
    partial output in the live serving table after the crash: 0
    after a clean retry (rebuild staging, atomic swap):        50,000 rows
    -> the crash left the live table untouched (0 rows); the retry is
       all-or-nothing and idempotent -- exactly 50,000 rows, no duplicates.
```

**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.

## What you should see

- Row-by-row loads 50,000 rows in **~28 s** (~1,800 rows/sec).
- Bulk loads the identical 50,000 rows in **~0.03 s** (~1.7M rows/sec).
- The ratio is **~900x** (three orders of magnitude) — exact seconds vary, the
  ratio reproduces.
- A crashed row-by-row load leaves **30,000** rows of partial output and the retry
  **collides** with all 30,000 — not idempotent.
- A crashed bulk-load-then-swap leaves **0** partial rows and the retry lands
  exactly **50,000** — all-or-nothing and idempotent.

## Why

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.

### The boundary — where the 900x shrinks to nothing

The penalty is specifically *many small **durable** commits against a live store*.
Remove any of those three words and the gap collapses: (a) **commit in batches** —
one transaction every 10,000 rows instead of every row — and you pay ~5 fsyncs
instead of 50,000, recovering most of the speed with five commits. (b) **Drop the
durability** — `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.

### Go deeper

1. **Add the fast variants.** Load a third way with `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.
2. **Postgres bulk import.** Replace the sqlite bulk path with `COPY ... FROM` (or
   `\copy`) and compare it to a loop of `INSERT`s over a real connection; the
   fsync-amortization story is identical, and `COPY` adds streaming parse wins.
3. **Immutable output partitions.** Have the job write a *new* partition/table
   named by run id and flip a view or pointer to it, keeping the previous one for
   instant rollback — the production shape of "build fresh, then swap," and why
   derived-data pipelines favor rebuild-and-swap over in-place mutation.