Transaction Batching and Write Throughput

Learning outcomes

SQLite can execute many row changes quickly, but a transaction commit is more than “finish this INSERT.” Depending on journal mode and durability settings it can involve journal/WAL records, synchronization, lock transitions, and filesystem work. Committing every row therefore asks SQLite to complete the durability boundary repeatedly. Batching changes that belong to one recoverable unit can transform throughput without weakening the durability setting.

One row is not automatically one transaction

If 5,000 sensor readings arrive in one import file, the business recovery unit might be the whole file, a 500-row chunk, or each reading. Those choices have different failure semantics. Performance work may change transaction boundaries only when the new boundary still satisfies the application’s recovery contract.

Why commit cost matters

In rollback-journal mode, SQLite protects original pages and coordinates database-file updates. In WAL mode, committed page changes are appended to the WAL and later checkpointed. The exact sync pattern depends on synchronous, journal mode, VFS, and storage. The safe general conclusion is not a multiplier—it is that transaction commit can include persistence work that individual row changes inside the transaction do not repeat.

Prepared statement reuse belongs in the write path

Application drivers normally prepare a parameterized statement and bind different values. This avoids SQL string construction and keeps untrusted values out of SQL syntax. Some high-level APIs cache statements internally; others require explicit preparation. Measure the driver you deploy.

INSERT INTO perf_import(    device_id, observed_at, metric, value_real, payload) VALUES (?, ?, ?, ?, ?);

In Python, executemany() is a convenient way to send repeated parameter sets. In the SQLite C API, the equivalent mental model is prepare once, bind, sqlite3_step(), reset/clear bindings, repeat.

Benchmark: autocommit-style row commits versus one batch

The script uses separate disposable database files so the two runs do not inherit freelist/cache state from each other. It uses the same schema, values, journal mode, and synchronous policy.

from pathlib import Pathimport sqlite3, timeROWS = [    (i % 100, f"2026-08-12T10:{i%60:02d}:{i%60:02d}Z", "temp", 20.0 + (i % 25)/10, None)    for i in range(2000)]DDL = """CREATE TABLE perf_import(  id INTEGER PRIMARY KEY,  device_id INTEGER NOT NULL,  observed_at TEXT NOT NULL,  metric TEXT NOT NULL,  value_real REAL NOT NULL,  payload TEXT)"""SQL = "INSERT INTO perf_import(device_id,observed_at,metric,value_real,payload) VALUES (?,?,?,?,?)"def configure(con, mode="DELETE"):    assert con.execute(f"PRAGMA journal_mode={mode}").fetchone()[0].lower() == mode.lower()    con.execute("PRAGMA synchronous=FULL")def per_row(path):    path.unlink(missing_ok=True)    con = sqlite3.connect(path, isolation_level=None)    configure(con)    con.execute(DDL)    t0 = time.perf_counter()    for row in ROWS:        con.execute("BEGIN")        con.execute(SQL, row)        con.execute("COMMIT")    elapsed = time.perf_counter() - t0    count = con.execute("SELECT count(*) FROM perf_import").fetchone()[0]    con.close()    return elapsed, countdef one_batch(path):    path.unlink(missing_ok=True)    con = sqlite3.connect(path, isolation_level=None)    configure(con)    con.execute(DDL)    t0 = time.perf_counter()    con.execute("BEGIN")    con.executemany(SQL, ROWS)    con.execute("COMMIT")    elapsed = time.perf_counter() - t0    count = con.execute("SELECT count(*) FROM perf_import").fetchone()[0]    con.close()    return elapsed, countfor name, fn in (("per-row", per_row), ("batch", one_batch)):    seconds, count = fn(Path(f"{name}.sqlite"))    assert count == len(ROWS)    print(name, "seconds=", round(seconds, 3), "rows/s=", round(count/seconds))

Your measured ratio is the result. On some filesystems it may be dramatic; on others less so. Do not turn a local ratio into a universal SQLite promise.

Batch size is a workload decision

If one transaction contains a million rows, commit overhead is amortized well—but the writer may hold resources for a long time, rollback can be expensive, readers may be affected differently by journal mode, the WAL can grow, and retrying the entire unit after an application failure may be unacceptable.

Controlled rollback-versus-WAL comparison

Journal mode changes concurrency and I/O shape, so compare it as a separate experiment after the batching comparison. Hold row count, synchronous level, schema, batch size, and host constant.

Run A: journal_mode=DELETE, synchronous=FULL, chunk=500Run B: journal_mode=WAL,    synchronous=FULL, chunk=500For each run record:  - total elapsed and rows/s  - database, -wal, and -journal sizes during/after run  - number of commits  - checkpoint behavior for WAL  - correctness count/checksum  - concurrent-reader behavior if that is part of the real workloadDo not conclude "WAL is faster" from one isolated write-only result.

Failure boundaries and idempotent imports

A retryable import needs a stable request/job identity. If the process dies after a chunk commits but before the caller receives confirmation, simply replaying all input can duplicate data unless uniqueness or a request ledger makes the operation idempotent.

CREATE TABLE import_job(    job_id       TEXT PRIMARY KEY,    source_name  TEXT NOT NULL,    status       TEXT NOT NULL CHECK(status IN ('running','complete','failed')),    row_count    INTEGER NOT NULL DEFAULT 0);-- Application transaction for a chunk can update job progress together-- with imported rows. A UNIQUE natural/request key can prevent duplicates.

Performance tuning is not allowed to erase recovery semantics. A 10× faster importer that duplicates 2% of rows after retries is broken.

Lab: choose a batch from evidence

Test chunk sizes on YOUR machine:  1, 10, 100, 500, 2000, all rowsKeep fixed:  journal_mode, synchronous, schema, values, destination filesystemRecord:  median job time across repeated clean runs  commits per job  rows/s  max transaction duration  database/WAL size  correctness count/checksum  failure replay unit  impact on a concurrent reader/writer if relevantSelect the smallest batch that meets throughput while preservinglatency, contention, and retry requirements.

Checkpoint

Bridge to PRAGMAs

Transaction boundaries often produce the largest safe write improvement because they change repeated work without weakening the guarantee. Lesson 3 now examines PRAGMAs—but one category at a time, with the same “what guarantee or resource changed?” discipline.

Authoritative references

• SQLite transactions
• SQLite atomic commit
• SQLite WAL
• SQLite speed comparison (historical methodology)
• SQLite BLOB/filesystem benchmark methodology