Benchmarking Without Lying to Yourself

Three things that make benchmarks lie

Almost every casual SQLite benchmark on the internet is wrong in at least one of these ways. Avoid them in your own measurements.

Cold vs warm cache — the first run reads from disk; subsequent runs hit OS page cache. Decide which case you're measuring and warm/cool deliberately.
Per-statement transactions — autocommit writes one fsync per row. A loop of 10k INSERTs with no transaction is not measuring SQLite's write speed; it's measuring your filesystem's sync latency.
Tiny dataset — every database is fast on 100 rows. Measure with realistic row counts; ideally a multiple of your expected production scale.

Principle: A benchmark that doesn't say which PRAGMAs were set, what the dataset shape was, and whether the cache was warm is not a benchmark. Always document those three things.

Code

A reasonable Python micro-benchmark·python

import sqlite3, time, os, statistics

def setup():
    if os.path.exists('bench.db'):
        os.remove('bench.db')
    conn = sqlite3.connect('bench.db')
    conn.execute('PRAGMA journal_mode = WAL')
    conn.execute('PRAGMA synchronous = NORMAL')
    conn.execute('CREATE TABLE t(id INTEGER PRIMARY KEY, v TEXT)')
    return conn

def bench_inserts(n: int) -> float:
    conn = setup()
    rows = [(f'v{i}',) for i in range(n)]
    start = time.perf_counter()
    with conn:
        conn.executemany('INSERT INTO t(v) VALUES (?)', rows)
    return time.perf_counter() - start

durations = [bench_inserts(10000) for _ in range(5)]
print(f'10k inserts: median {statistics.median(durations)*1000:.1f} ms')

Exercise

Build a benchmark harness that measures: (1) bulk insert throughput with executemany inside a transaction vs autocommit per row, (2) point-read throughput on a cold vs warm cache, (3) the same point-read with and without an index. Document every PRAGMA setting and the dataset shape. Make the script reproducible.

Benchmarking Without Lying to Yourself

Three things that make benchmarks lie

Code

External links

Exercise

Progress

Comments 0