Design canvas — size a SQLite deployment — Build a B-tree storage engine (SQLite-style)

Build a B-tree storage engine (SQLite-style) (11 scenes)

Scene 10 · 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 earlier scenes.

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

Diagram

LEFT: a workload selector (write-heavy logging / read-heavy reference / OLTP). MIDDLE: six knobs — page_size, journal_mode, synchronous, cache_size, # indexes, wal_autocheckpoint — each labeled with the scene id its failure mode lives in. RIGHT: a simulation panel — throughput, p99 latency, durability window, file growth, a WAL-size sparkline, and a stack of FAILURE FLAGS that each cite a specific earlier scene when they fire.

Sources

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).

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

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

PreviousCheckpoint — and the 20 GB WAL