SQLite / Advanced: concurrency, recovery, and application boundaries / Lesson 17

Workload performance and storage economics

Connect query count, transaction duration, storage layout, and maintenance cost to a measured SQLite workload instead of applying universal tuning recipes.

About 5 min readContent reviewed 2026-10-25

On this page

Lesson goals

Compare repeated lookups with a bounded batch.
Explain rowid versus WITHOUT ROWID design.
Interpret page reuse, VACUUM, and resource settings in context.

Measure the work that matters to the application

Define latency, throughput, query frequency, result volume, write rate, and acceptable resource use before tuning. SQLite avoids a separate database network hop, but repeated preparation, execution, row conversion, and redundant scans still cost work. N-plus-one patterns can therefore matter without proving that every batched query is faster for every dataset or cache state.

Use representative data distributions and the actual host API. A small in-memory example teaches semantics and call count, not filesystem latency or durability cost. Keep correct result sets as a prerequisite and compare write amplification, memory, and transaction duration as well as read speed. A change that helps one report can harm a more frequent mutation path.

Observe repeated calls and a single equivalent lookup

The first program traces only its own synthetic SELECT operations. Three single-key lookups and one ordered IN lookup produce the same names, while the observed SELECT call counts differ3 versus1. The placeholder list is generated from the bounded number of values, and values remain bound separately. The example makes no elapsed-time claim and contains no private data.

A batch still needs limits on parameter count, memory, result size, and application cancellation. An empty selection needs an explicit policy rather than blindly constructing malformed SQL. Reuse prepared operations where appropriate and avoid holding a write transaction while the caller processes a large response. Query-count reduction is one hypothesis to measure, not permission to build an unbounded mega-query.

python Compare observed calls without a speed claim

import sqlite3
from contextlib import closing

with closing(sqlite3.connect(":memory:")) as db:
    db.executescript("CREATE TABLE item(id INTEGER PRIMARY KEY,name TEXT); INSERT INTO item VALUES(1,'A'),(2,'B'),(3,'C');")
    statements = []
    db.set_trace_callback(statements.append)
    ids = [1, 2, 3]
    separate = [db.execute("SELECT name FROM item WHERE id=?", (key,)).fetchone()[0] for key in ids]
    first_count = sum(sql.startswith("SELECT") for sql in statements)
    statements.clear()
    placeholders = ",".join("?" for _ in ids)
    batched = [row[0] for row in db.execute(f"SELECT name FROM item WHERE id IN ({placeholders}) ORDER BY id", ids)]
    batch_count = sum(sql.startswith("SELECT") for sql in statements)
    db.set_trace_callback(None)
    print("names", batched)
    print("same", separate == batched)
    print("select_calls", first_count, batch_count)

Expected output

names ['A', 'B', 'C']
same True
select_calls 3 1

Both paths produce A,B,C. The local trace observes three SELECTs versus one; it does not measure total application cost.

Choose physical identity from actual key behavior

WITHOUT ROWID tables use their primary key as the underlying organization rather than maintaining an ordinary rowid table with separate primary-key lookup structure. They can suit some composite or non-integer primary-key workloads, but are not a universal size or speed improvement. Key width, access paths, payload, and host API requirements matter. Nullability and certain rowid-related behaviors also differ.

The second script creates one ordinary and one WITHOUT ROWID table, then inspects their reported wr flags through pragma_table_list on the tested runtime. It demonstrates layout identity only. A performance proposal must compare representative reads, writes, and storage, including secondary indexes that carry the primary key. Do not choose a design because one metadata flag sounds more compact.

sql Inspect rowid layout choices

CREATE TABLE ordinary(code TEXT PRIMARY KEY NOT NULL,value TEXT);
CREATE TABLE packed(code TEXT PRIMARY KEY NOT NULL,value TEXT) WITHOUT ROWID;
INSERT INTO ordinary VALUES('A','one');
INSERT INTO packed VALUES('A','one');
SELECT name,wr FROM pragma_table_list WHERE name IN('ordinary','packed') ORDER BY name;
SELECT code,value FROM packed ORDER BY code;

Expected output

name|wr
ordinary|0
packed|1

code|value
A|one

The metadata distinguishes ordinary wr0 from packed wr1. The application row remains A/one regardless of the layout comparison.

Separate reusable pages from file shrinkage

Deleting rows commonly makes pages reusable without immediately shrinking the main file. page_count and freelist_count describe different aspects of allocation, and a growing WAL has its own lifecycle. VACUUM can rebuild and compact a database with disk-space, locking, and transaction restrictions; it is not a harmless command to run after every deletion. Plan a maintenance window and recoverability before applying it to real data.

Cache settings, page size, auto_vacuum, synchronous mode, and checkpoint policy are separate controls with workload and durability consequences. Read their units and persistence carefully: a numeric setting is not always a byte budget or a connection-local preference. Do not weaken synchronization or disable journaling solely to obtain a favorable toy benchmark; that changes the failure model being measured.

Keep optimization reversible and evidence-backed

Record a baseline, change one justified factor, confirm identical business results, and measure under the same workload conditions. Compare common and rare parameter values, concurrency, cancellation, and write-heavy periods. Runtime and statistics changes can alter plans, so keep them part of the record rather than assuming a previous result is permanent.

Maintain a reason for every index and operational setting, with a clear owner and rollback criterion. A valid embedded design can outperform an overengineered one for its intended task, while sustained multiwriter or remote-file demands may call for another architecture. Performance engineering is explaining that fit with evidence, not proving SQLite or any other engine universally superior.

Common errors and fixes

A deleted database did not become smaller.

Why it happens: Page reuse was confused with immediate file truncation.
How to fix it: Inspect allocation and the documented maintenance/recovery options.
Prevent it next time: Plan capacity and maintenance rather than repeated ad hoc compaction.

A benchmark improved after durability was disabled.

Why it happens: The comparison changed the failure guarantee.
How to fix it: Compare equivalent durability requirements and disclose any setting change.
Prevent it next time: Treat journaling and synchronization as correctness settings.

A large batch exhausted memory or parameters.

Why it happens: Call-count reduction ignored resource budgets.
How to fix it: Bound chunks and preserve ordering/transaction semantics.
Prevent it next time: Test empty, normal, and maximum-size batches.

Check your understanding

1. Why is three SELECTs versus one not proof of a3× speedup?

Show worked solution

The operations can have different preparation, scanning, sorting, conversion, cache, and result costs. The example measures call count only. Use representative end-to-end measurements with identical results and resource guarantees to assess actual benefit.

2. When should WITHOUT ROWID be evaluated instead of chosen automatically?