Measure First: Timers, Query Plans, Dataset Shape, and Repeatable Benchmarks

Learning outcomes

Performance engineering starts before the stopwatch. A fast result on a tiny laptop database can be irrelevant to a production workload whose rows, transaction boundaries, durability requirements, cache state, concurrency, or filesystem differ. In this lesson you will build an evidence record that another engineer can reproduce and challenge.

Start with a question, not a setting

“Make SQLite faster” is not a benchmark question. A useful question identifies the exact operation and acceptable tradeoffs. For example: Can FieldNotes return the newest 20 readings for one device in under our application latency budget on a representative 200,000-row local database while preserving the existing durability policy?

A reusable FieldNotes benchmark dataset

The chapter uses a disposable perf_reading table. The row shape is intentionally simple enough to understand but large enough for planner choices and batching costs to become visible on ordinary hardware.

DROP TABLE IF EXISTS perf_reading;CREATE TABLE perf_reading(    reading_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);-- The benchmark harness inserts many rows before creating this index.CREATE INDEX idx_perf_device_timeON perf_reading(device_id, observed_at DESC);

The access pattern—not the existence of a column—justifies the index. Chapter 10 already established that an index has read benefits and write/storage costs. Here we measure those costs in the workload that matters.

Capture query-plan evidence

Timing a query without its plan makes regressions difficult to diagnose. A query can remain fast on a small dataset even after it stops using an index. Conversely, a changed plan is not automatically worse; measure it.

EXPLAIN QUERY PLANSELECT reading_id, observed_at, value_realFROM perf_readingWHERE device_id = 17ORDER BY observed_at DESCLIMIT 20;

With the composite index present, a current build will normally report a SEARCH using idx_perf_device_time. If your exact output differs, preserve the output: EXPLAIN QUERY PLAN text is a debugging interface and can change across SQLite releases.

CLI timing is useful, but it is not the whole benchmark

The current sqlite3 shell provides .timer on|off. It is excellent for interactive comparisons because it puts elapsed timing beside the statement you are inspecting. It is a shell tool, not SQL, and its elapsed measurement includes shell work around SQLite API calls.

.version.timer on.eqp onSELECT reading_id, observed_at, value_realFROM perf_readingWHERE device_id = 17ORDER BY observed_at DESCLIMIT 20;.eqp off.timer off

Do not copy one displayed time into a capacity plan. Repeat the operation and use the same output mode, result size, host load, and database state. If output formatting dominates a query that returns thousands of rows, measure the application path too.

Host-language benchmark harness

Python’s perf_counter_ns() gives a monotonic high-resolution timer suitable for elapsed-time comparisons. The harness below uses a file-backed database because journal synchronization and filesystem behavior are part of the workload. It records median and minimum rather than presenting one lucky run.

from pathlib import Pathimport platform, sqlite3, statistics, timeDB = Path("fieldnotes_perf.sqlite")con = sqlite3.connect(DB)print("python:", platform.python_version())print("sqlite:", sqlite3.sqlite_version)print("source:", con.execute("select sqlite_source_id()").fetchone()[0])for name in ("journal_mode", "synchronous", "cache_size", "temp_store", "mmap_size"):    print(name, con.execute(f"pragma {name}").fetchone()[0])sql = """SELECT reading_id, observed_at, value_real         FROM perf_reading         WHERE device_id=?         ORDER BY observed_at DESC LIMIT 20"""plan = con.execute("EXPLAIN QUERY PLAN " + sql, (17,)).fetchall()print("plan:", plan)def timed_read(device_id, trials=15):    samples = []    expected = None    for _ in range(trials):        t0 = time.perf_counter_ns()        rows = con.execute(sql, (device_id,)).fetchall()        samples.append((time.perf_counter_ns() - t0) / 1e6)        expected = rows    assert len(expected) <= 20    return statistics.median(samples), min(samples), samplesmedian_ms, best_ms, samples = timed_read(17)print("median_ms:", round(median_ms, 3))print("best_ms:", round(best_ms, 3))print("samples_ms:", [round(x, 3) for x in samples])con.close()

The first trial often includes work that later trials do not: page faults, filesystem cache population, statement preparation, or CPU-frequency changes. That is why the raw sample list belongs in the evidence record.

Warm cache, cold cache, and honest labels

SQLite has its own page cache, the operating system has a filesystem cache, and storage devices may have additional caches. Reopening a SQLite connection clears SQLite’s connection page cache but does not guarantee the OS has forgotten the file. A true cold-storage benchmark is platform-specific and can be intrusive.

Name the condition you actually measured. “Cold query” should not mean “I reopened Python.”

Why microbenchmarks mislead

A microbenchmark can deliberately isolate one operation, which is useful. The mistake is extrapolating it to an application that adds transactions, fsync/FlushFileBuffers, network calls, JSON serialization, lock waits, UI work, long readers, or a much larger data distribution.

Lab: create the evidence package

Create a disposable file with at least tens of thousands of readings. Time inserts, create the composite index, inspect the hot read plan, then time repeated reads. Do not compare your absolute milliseconds to this course: your machine is the subject.

Benchmark ID: ch18-l1-fieldnotesQuestion: latest-20 read + baseline insert costRecord before run:  [ ] sqlite_version() and sqlite_source_id()  [ ] PRAGMA compile_options  [ ] journal_mode / synchronous / cache_size / temp_store / mmap_size  [ ] schema and indexes  [ ] row count and distribution by device_id  [ ] database/WAL file sizes  [ ] OS, filesystem, storage medium, runtime versionFor each candidate change:  [ ] make one intentional change  [ ] verify row counts/business result  [ ] capture EXPLAIN QUERY PLAN  [ ] run repeated trials  [ ] store raw samples, median, and tail observations  [ ] decide keep/revert from evidence

Checkpoint

Bridge to write throughput

With a measurement discipline in place, Lesson 2 investigates the most common SQLite write-performance mistake: treating every row as its own durable business transaction. We will change transaction boundaries first—without changing correctness requirements—and measure the result.

Authoritative references

• SQLite command-line shell (.timer/.stats)
• SQLite EXPLAIN QUERY PLAN
• SQLite query planner
• SQLite release chronology