Design canvas — size a SQLite deployment

Workloads pick different knob combinations; each knob has a 'too low' and 'too high' failure mode you can name from a specific earlier scene.

Previously

You now know every layer — pages, B-trees, descents, splits, indexes, page cache, WAL, checkpoint. Time to pick knob settings for three real workloads and name the scene that backstops every choice.

Scene 10

Design canvas — size a SQLite deployment

Watch
Try it
Predict
Capture

Workload

OLTP

mixed read/write, durability matters

~5 k commit/s target

Throughput

~5 k commit/s

p99 Latency

~2 ms / commit

Durability

0 — fsync per commit

File Growth

stable; bounded WAL

Knobs (6)

page_size4 KB
journal_modeWAL
synchronousFULL
cache_size64 MB
# indexes2
wal_autocheckpoint1000 frames

btree-07-page-cache · cache_size too small for working set
2 MB cache + a 100-page hot set = LRU thrash; misses become real disk I/O.
btree-06-indexes · too many indexes for a write-heavy workload
Every INSERT updates every index B-tree; throughput drops ~linearly with index count.
btree-09-checkpoint · no auto-checkpoint with continuous writes
WAL never rewinds; the file grows on every commit until something else fires a checkpoint.
btree-08-wal-and-fsync · synchronous=OFF in a workload that needs durability
A power-loss can lose recent commits AND corrupt the DB; only choose OFF for rebuildable data.
btree-05a-delete-and-vacuum · VACUUM never run on a heavy-delete workload
Deletes free cells inside pages but don't shrink the file; the DB grows monotonically.
btree-08-wal-and-fsync · rollback journal blocks readers under writes
Switch to WAL when concurrent readers exist — DELETE journal serializes them behind the writer.
btree-02-pages · 1 KB pages misalign with the OS block
Pages smaller than the 4 KB OS block become partial-block reads, doubling I/O per page.
btree-02-pages · 64 KB pages waste bandwidth on point reads
Each lookup pulls 16x the bytes of a 4 KB page — fine for scans, painful for random reads.

workload: OLTP — every failure flag links to the scene it lives in

Open on a larger screen for the full design canvas.

Workload selector — three opinionated presets.

Knob panel — each row shows the scene id its failure mode lives in.

Simulation — throughput, latency, durability, WAL size.

Failure flags carry the scene id they trace to (e.g. btree-07-page-cache).

What to watch for

OLTP defaults pre-loaded. The verifier walks each knob and confirms it against its scene; meters settle into the green band before you take over.

Implementation

Highlighted lines are the ones running in the diagram right now.

verifier(workload, knobs)

every fired rule cites the scene its failure mode lives in

1def verifier(workload, knobs):
2    flags = []
3    if knobs.cache_size < workload.working_set:
4        flags.push('btree-07-page-cache')   # LRU thrash
5    if workload.write_heavy and knobs.indexes >= 3:
6        flags.push('btree-06-indexes')      # write-amp
7    if knobs.synchronous == OFF and workload.durable:
8        flags.push('btree-08-wal-and-fsync')
9    if (knobs.journal_mode == WAL and
10        knobs.wal_autocheckpoint in (OFF, HUGE) and
11        workload.has_writes):
12        flags.push('btree-09-checkpoint')   # WAL grows
13    if knobs.journal_mode == ROLLBACK and workload.concurrent_readers:
14        flags.push('btree-08-wal-and-fsync')
15    if knobs.page_size != OS_BLOCK:
16        flags.push('btree-02-pages')        # I/O amp
17    return flags

defaults_for(workload)

preset knob map — the 'opinionated good config' per workload

1def defaults_for(workload):
2    if workload == 'logging':
3        return Knobs(page_size=4KB, journal=WAL,
4                     synchronous=NORMAL, cache=64MB,
5                     indexes=0, wal_autockpt=1000)
6    if workload == 'reference':
7        return Knobs(page_size=4KB, journal=WAL,
8                     synchronous=FULL, cache=256MB,
9                     indexes=2, wal_autockpt=1000)
10    if workload == 'oltp':
11        return Knobs(page_size=4KB, journal=WAL,
12                     synchronous=FULL, cache=64MB,
13                     indexes=2, wal_autockpt=1000)

commit_path_under(knobs)

what scenes 8 + 9 do for the chosen journal + sync combo

1def commit_path_under(knobs):
2    mutate_page_in_cache()                  # btree-07
3    if knobs.journal_mode == ROLLBACK:
4        write_rollback_journal()            # readers wait
5        fsync_journal()
6        write_pages_in_place_to_db()
7    else:                                   # WAL
8        append_frame_to_wal()               # btree-08
9        if knobs.synchronous >= FULL:
10            fsync_wal()                     # per-commit cost
11        elif knobs.synchronous == NORMAL:
12            pass                            # fsync at checkpoint
13    if wal_frames() >= knobs.wal_autockpt:
14        checkpoint()                        # btree-09

Sources

Where this sits in Build a B-tree storage engine (SQLite-style)

Scene 10 of 11, in the Design canvas act — Size SQLite for write-heavy logging, read-heavy reference, OLTP.. Workloads pick different knob combinations; each knob has a 'too low' and 'too high' failure mode you can name from earlier scenes.

All 11 scenes in Build a B-tree storage engine (SQLite-style) · Every curriculum

Built with Arqly

Every scene in Build a B-tree storage engine (SQLite-style) builds on the one before it.All 11 Build a B-tree storage engine (SQLite-style) scenes