Batch size: find the knee

Measure your machineNeeds Python

Problem

A commit to a database on disk ends with an fsync: a wait until the data is durable on the device, which costs far more than inserting one row. Committing every row makes the fsync the whole cost; committing batches divides it among the rows. But a batch that waits only for size can wait a long time when traffic is light: a batch of 1,000 at 20 rows per second keeps its first row waiting 50 seconds. A time limit fixes that: flush when the batch is full or old, whichever comes first.

Insert 200,000 rows into SQLite (or PostgreSQL) with batch sizes 1, 10, 100, 1,000 and 10,000, with one commit per batch. Plot rows per second against batch size. Then add a time limit: simulate trickle traffic at 20 rows/s and show that a size-only batch of 1,000 leaves the last rows waiting about 50 seconds, while "size OR age ≥ 200 ms" bounds the wait.

The script inserts 20,000 rows for batch size 1 (it is too slow for 200,000).

The question. Where does insert throughput stop rising with batch size, and how long does a row wait under trickle traffic with a size-only batch and with a size-or-age batch?

Predict

How many times more rows per second does a batch of 10,000 insert than a commit per row?

Under 10×10 to 100×100 to 1,000×Over 1,000×

Run it

1Install uv once. It fetches Python and the lab's packages for you.
Terminal
```
curl -LsSf https://astral.sh/uv/install.sh | sh
```

2Get the script. It is the whole lab: read it, then download it.

batch-size-find-the-knee.py52 linesDownload

View the code

batch-size-find-the-knee.py

# /// script
# requires-python = ">=3.10"
# ///
# Copyright (c) 2026 Aayush Sharma · SPDX-License-Identifier: MIT
# https://thedataengineering.com/lab-code/LICENSE.txt
"""Batch size: find the knee.

200,000 rows inserted into SQLite on disk with one commit per batch, for
batch sizes from 1 to 10,000. Then trickle traffic at 20 rows/s: a
size-only batch of 1,000 against "size OR age >= 200 ms".
"""
import argparse, json, os, sqlite3, tempfile, time

p = argparse.ArgumentParser(description=__doc__.splitlines()[0])
p.add_argument("--rows", type=int, default=200_000, help="Rows per batch size (batch size 1 inserts 20,000).")
p.add_argument("--sizes", type=lambda s: [int(v) for v in s.split(",")], default=[1, 10, 100, 1000, 10000], help="Batch sizes.")
p.add_argument("--trickle", type=float, default=20, help="Rows per second of trickle traffic.")
p.add_argument("--max-age-ms", type=float, default=200, help="Age limit of the size-or-age policy.")
p.add_argument("--json", help="Also write the results to this JSON file.")
args = p.parse_args()
path = os.path.join(tempfile.gettempdir(), "batch-knee.db")
import atexit; atexit.register(lambda: os.path.exists(path) and os.remove(path))   # leave nothing behind

def bench(batch, n):
    db = sqlite3.connect(path); db.execute("DROP TABLE IF EXISTS t"); db.execute("CREATE TABLE t(id INTEGER, v TEXT)"); db.commit()
    t0 = time.perf_counter()
    for i in range(0, n, batch):
        db.executemany("INSERT INTO t VALUES (?,?)", [(j, "x" * 50) for j in range(i, i + batch)]); db.commit()
    dt = time.perf_counter() - t0; db.close()
    return n / dt

rows = []
for b in args.sizes:
    r = bench(b, 20_000 if b == 1 else args.rows)
    rows.append({"batch": b, "rows_per_s": r}); print(f"batch {b:>6,}: {r:>10,.0f} rows/s")

# trickle traffic, simulated clock: how long does the last row of a batch wait?
arrivals = [i / args.trickle for i in range(1000)]
size_only = 999 / args.trickle                                 # the first row waits for 999 more
waits, start = [], arrivals[0]
for t in arrivals:
    if t - start >= args.max_age_ms / 1000: start = t          # the age limit flushed the previous batch
    waits.append(t - start)
size_or_age = max(waits) + 1 / args.trickle
print(f"trickle at {args.trickle:g} rows/s: size-only batch of 1,000 makes its first row wait {size_only:.1f} s; "
      f"size OR age >= {args.max_age_ms:g} ms bounds every wait at {size_or_age * 1000:.0f} ms")

if args.json:
    with open(args.json, "w") as f:
        json.dump({"tables": {"batches": rows, "summary": [{"size_only_wait_s": size_only, "size_or_age_wait_ms": size_or_age * 1000,
                   "knee_ratio": rows[-1]["rows_per_s"] / rows[0]["rows_per_s"]}]}}, f)

3Run it in the folder you saved it to, with the defaults first. Then change a knob from the table below and run it again.
Terminal
```
uv run batch-size-find-the-knee.py
uv run batch-size-find-the-knee.py --help     # every knob
```

Knobs
Knob · default	What it changes
--rows200,000	Rows per batch size (batch size 1 inserts 20,000).
--sizes1, 10, 100, 1000, 10,000	Batch sizes.
--trickle20	Rows per second of trickle traffic.
--max-age-ms200	Age limit of the size-or-age policy.

Our run

Insert throughput by batch size (one commit per batch)Apple M2 · 8 cores · 8 GB

Data table

Insert throughput by batch size (one commit per batch)
Rows per commit	Rows / s
1	3,410
10	31.4K
100	259K
1K	1.12M
10K	1.68M

macOS 26.6.2 · Python 3.13.3 · run 2026-10-20

Output · uv run batch-size-find-the-knee.py

batch      1:      3,412 rows/s
batch     10:     31,374 rows/s
batch    100:    258,810 rows/s
batch  1,000:  1,120,527 rows/s
batch 10,000:  1,678,527 rows/s
trickle at 20 rows/s: size-only batch of 1,000 makes its first row wait 50.0 s; size OR age >= 200 ms bounds every wait at 250 ms

What we found

Throughput rose from 3,410 rows per second with a commit per row to 1.12M at 1,000 and 1.68M at 10,000: steep up to 1,000, then flattening, as expected (492× between the ends). Under trickle traffic at 20 rows per second, a size-only batch of 1,000 kept its first row waiting 50 s; size or age bounded every wait at 250 ms.

Read it

Micro-batching is batching with a time limit. The size sets throughput under load; the age limit sets the worst latency when traffic is light. A batcher with only one of the two is tuned for half of its traffic.