Part I: The mental model
SQL text │ tokenize.c (SQL → tokens) ▼ Lemon parser (parse.y → grammar actions) │ ▼ code generator + planner │ select.c, where.c (choose scan vs seek, pick indexes) ▼ VDBE bytecode program (vdbe.c: a register machine) │ ▼ B-tree cursors (btree.c: tables and indexes as B-trees) │ ▼ pager (pager.c: page cache + transactions) │ ▼ journal / WAL (pager.c, wal.c: atomicity + durability) │ ▼ VFS (os_unix.c, os_win.c: open/read/write/lock) │ ▼ one ordinary file on disk
The one-sentence identity: SQLite is a SQL compiler stacked
on a transactional storage engine, shipped as a C library that
lives inside your process and keeps the whole database in one
ordinary file. There is no server, no socket, no
configuration. Your application calls a function; the function
eventually calls read() and write() on
one file.
The stack above is not a diagram invented for teaching; it is the literal module structure of the source. The top half is a compiler: SQL text becomes tokens, tokens become a parse tree, the tree becomes a program for a bytecode virtual machine called the VDBE, the Virtual Database Engine. The bottom half is a storage engine: the VDBE manipulates B-tree cursors, the B-tree layer asks the pager for fixed-size pages, and the pager is where atomicity and durability live, either through a rollback journal or a write-ahead log. At the very bottom, the VFS layer abstracts the operating system, which is a large part of why SQLite runs everywhere from phones to aircraft.
Every layer talks only to the layer below it through a small, stable interface. The VDBE never touches a file; the B-tree never calls the OS; the pager does not know what a B-tree is. Hold this picture and the rest of the chapter is just filling in the boxes. If you want the same territory approached from requirements rather than code, my databases note and the key-value store design write-up pair well with this page.
Part II: Using it
Install and first session
You almost certainly have it already, and if not it is one package away:
sudo apt install sqlite3 # Debian/Ubuntu
brew install sqlite # macOS (Apple also ships one at /usr/bin/sqlite3)
sqlite3 app.dbsqlite> CREATE TABLE notes(id INTEGER PRIMARY KEY, body TEXT);
sqlite> INSERT INTO notes(body) VALUES ('hello');
sqlite> SELECT * FROM notes;
1|hello
sqlite> .quit
That created a real database. ls -la app.db shows a
small file, 8192 bytes on my machine: two 4096-byte pages, one for
the header and schema, one for the table's B-tree root. If you want
to feel how self-contained the project is, download the
amalgamation from sqlite.org and build the whole engine plus its
command-line shell with one compiler invocation:
gcc shell.c sqlite3.c -lpthread -ldl -lm -o sqlite3From code
The natural language here is Python, because the standard library
ships the engine; import sqlite3 works on a fresh
interpreter with no install step:
import sqlite3
con = sqlite3.connect("app.db")
con.execute("INSERT INTO notes(body) VALUES (?)", ("from python",))
con.commit()
for row in con.execute("SELECT id, body FROM notes"):
print(row)The mistakes everyone makes first
Mistake one: committing every row. Each committed
transaction must reach durable storage before SQLite returns, which
means an fsync per commit, and an fsync costs
milliseconds. Inserting rows one commit at a time is therefore
slower by two to three orders of magnitude than batching them; Lab
1 below measures a 400x difference on my machine. Wrong versus
right:
# wrong: one fsync per row
for i in range(10_000):
con.execute("INSERT INTO t VALUES (?)", (i,))
con.commit()
# right: one transaction, one fsync
with con: # opens a transaction, commits on exit
con.executemany("INSERT INTO t VALUES (?)", ((i,) for i in range(10_000)))
Mistake two: building SQL with string formatting.
Beyond the injection risk, formatted literals defeat statement
reuse; the ? placeholder lets one compiled statement
serve every value:
# wrong
con.execute(f"SELECT * FROM notes WHERE body = '{user_input}'")
# right
con.execute("SELECT * FROM notes WHERE body = ?", (user_input,))
Mistake three: assuming declared types are enforced.
SQLite columns have type affinity, not rigid types: unless
you created the table with STRICT (available since
3.37), inserting the string '42' into an INTEGER
column stores the integer 42, and inserting 'banana'
stores the text 'banana' without complaint. This
flexibility is by design, but it surprises people arriving from
PostgreSQL.
Mistake four: expecting foreign keys to work. For
historical compatibility, foreign key enforcement is off by default
and must be enabled per connection with
PRAGMA foreign_keys = ON;. Silent orphan rows in a
fresh project are almost always this.
Mistake five: fearing the file. Beginners treat
the database file as fragile. It is the opposite: the format is
stable, documented byte by byte, and the project pledges support
through 2050. Copy it, back it up with
.backup or VACUUM INTO, ship it to users
as an application file format. What you must not do is copy it
while a write transaction is in flight without using the
backup API, or put it on a network filesystem, which Part III
covers.
Part III: When it is the right tool
SQLite's own documentation says it best: it does not compete with
client/server databases, it competes with fopen().
It is the right tool when the database and the application live on
the same machine: application state on phones and desktops, edge
and IoT devices, embedded caches, test fixtures, data analysis on
files you can email, and, increasingly, small-to-medium websites,
since a single WAL-mode SQLite database comfortably serves sites
with many thousands of requests per day when reads dominate.
The named alternatives, and when they win: PostgreSQL when many machines or many concurrent writers need the data, when you need user management and network access control, or when you want rich types and extensions; SQLite has exactly one writer at a time, and that is a hard design boundary. DuckDB when the workload is analytical: it deliberately mirrors SQLite's embedded, single-file ergonomics but stores columns rather than rows, so aggregations over millions of rows run orders of magnitude faster. RocksDB when you need a raw key-value store with extreme write throughput and no SQL at all; its LSM-tree makes the opposite trade from SQLite's B-tree, favoring writes over reads.
The architecture-shaped warning is the classic one, and for SQLite it is not an analogy, it is the literal rule: do not put a writable SQLite database on a network filesystem. SQLite's locking depends on POSIX advisory locks, which many NFS implementations get wrong, and WAL mode additionally requires genuinely shared memory between all connections, which is impossible across machines. The failure mode is not an error message; it is silent corruption under concurrent writes.
SAFE DANGEROUS
app ──▶ sqlite3.so ──▶ local disk app on host A ─┐
(same machine, app on host B ─┼─▶ NFS ──▶ file
locks work) app on host C ─┘
(broken locks, shared-memory
WAL impossible, corruption)
If several machines need the same data, that is the signal you have outgrown the embedded model: put the data behind a server database, or replicate SQLite explicitly with a tool built for it (LiteFS and Litestream exist for exactly this niche).
Part IV: The full life of one query
The canonical operation for this chapter is the simplest useful query in the schema we already made:
SELECT body FROM notes WHERE id = ?We will follow it through every layer, naming the actual source files. Everything below was checked against the current source tree and the outputs against SQLite 3.51.0.
Stage 1: sqlite3_prepare_v2, the front door
Your language binding, whatever it is, ends up calling
sqlite3_prepare_v2(), implemented in
src/prepare.c (the public API surface lives largely in
src/main.c). Preparing means compiling: the function
takes SQL text and returns a sqlite3_stmt, which is
not a handle to a plan tree but a fully compiled bytecode program.
The _v2 suffix matters: unlike the original
sqlite3_prepare(), it retains the SQL text so the
statement can be transparently recompiled if the schema changes
underneath it, and it reports errors from the statement itself
rather than only from the connection. New code should always use
_v2 (or _v3, which adds flags such as
SQLITE_PREPARE_PERSISTENT for statements you intend
to cache).
Stage 2: the tokenizer, tokenize.c
src/tokenize.c is a hand-written scanner, not
generated, because the authors found a hand-rolled one faster and
smaller than lex output. It walks the text and produces a stream
of tokens: TK_SELECT, an identifier
body, TK_FROM, an identifier
notes, TK_WHERE, id,
TK_EQ, and TK_VARIABLE for the
?. An unusual inversion worth noticing: in most
systems the parser calls the tokenizer for the next token; in
SQLite the tokenizer drives, pushing tokens into the parser one at
a time.
Stage 3: the Lemon parser, parse.y
The grammar lives in src/parse.y, processed not by
yacc or bison but by Lemon, SQLite's own parser generator, whose
source is tool/lemon.c. Lemon exists because the
project wanted a push parser (which is what lets the tokenizer
drive), better memory behavior, and a grammar syntax less prone to
the classic yacc mistakes. The parser's reduce actions build an
abstract syntax tree of C structs: our statement becomes a
Select object holding an expression list
(body), a source list (notes), and a
WHERE expression tree (id = ?) built from
Expr nodes defined in src/expr.c.
Stage 4: planner and code generation, select.c and where.c
There is no separate "optimizer pass" producing a plan data
structure that a later pass consumes; planning and code generation
are interleaved. sqlite3Select() in
src/select.c orchestrates, and for the FROM/WHERE part
it calls into the WHERE-clause processor: src/where.c
(analysis and cost-based choice of strategy, the heart of the
query planner), src/whereexpr.c (breaking the WHERE
clause into terms usable by indexes), and
src/wherecode.c (emitting the loop bytecode). For our
query the analysis is short: the WHERE term is an equality on
id, and because id was declared
INTEGER PRIMARY KEY it is the rowid, the
B-tree key itself. The planner therefore skips index
consideration entirely and emits a direct rowid seek, the cheapest
lookup SQLite has.
Stage 5: the VDBE program
You can see the compiled program yourself. SQLite has two related
introspection commands and the distinction is worth being precise
about: EXPLAIN prints the actual VDBE bytecode,
instruction by instruction, while EXPLAIN QUERY PLAN
prints a short human-level summary of the planner's strategy
choices. You read EXPLAIN QUERY PLAN daily to
check whether an index is used; you read full
EXPLAIN when you want to understand the machine.
Here is the real output on 3.51.0:
sqlite> EXPLAIN QUERY PLAN SELECT body FROM notes WHERE id = ?;
QUERY PLAN
`--SEARCH notes USING INTEGER PRIMARY KEY (rowid=?)
sqlite> EXPLAIN SELECT body FROM notes WHERE id = ?;
addr opcode p1 p2 p3 p4 p5 comment
---- ------------- ---- ---- ---- ------------- -- -------------
0 Init 0 7 0 0 Start at 7
1 OpenRead 0 2 0 2 0 root=2 iDb=0; notes
2 Variable 1 1 0 0 r[1]=parameter(1)
3 SeekRowid 0 6 1 0 intkey=r[1]
4 Column 0 1 2 0 r[2]= cursor 0 column 1
5 ResultRow 2 1 0 0 output=r[2]
6 Halt 0 0 0 0
7 Transaction 0 0 1 0 1 usesStmtJournal=0
8 Goto 0 1 0 0
Reading it as a story: Init jumps to address 7, where
Transaction starts a read transaction and
Goto jumps back to address 1 (prologue at the end is
a VDBE convention). OpenRead opens read cursor 0 on
the B-tree whose root is page 2, the notes table.
Variable copies bound parameter 1 into register 1.
SeekRowid positions cursor 0 on the row whose rowid
equals register 1, jumping to address 6 (Halt) if no
such row exists. Column extracts column 1
(body) into register 2, and ResultRow
yields registers 2 through 2 as one result row, suspending the
program and returning SQLITE_ROW to the caller. There
is no loop: an equality on the primary key can match at most one
row, and the bytecode shows it. The opcode reference generated
from the comments in the source is at sqlite.org/opcode.html.
Stage 6: sqlite3_step and the interpreter loop, vdbe.c
Execution begins when you call sqlite3_step()
(API side in src/vdbeapi.c). The engine's heart is
sqlite3VdbeExec() in src/vdbe.c, one
giant switch statement over opcodes inside a for loop, tens of
thousands of lines long and among the most readable big functions
in systems programming because every opcode's documentation is a
comment right above its case. The VDBE is a register machine (an
array of Mem cells), not a stack machine; earlier
SQLite versions were stack-based and the project rewrote it,
reason enough to trust that the design is deliberate.
Stage 7: the B-tree cursor, btree.c
SeekRowid calls into src/btree.c, about
eleven thousand lines implementing the one on-disk structure
SQLite has. Tables are B-trees keyed by 64-bit rowid with row data
in the leaves; indexes are B-trees whose keys are records of the
indexed columns. The cursor descends from the root page: binary
search among the cells of each interior page chooses a child
pointer, repeat until a leaf, then binary search the leaf for the
rowid. For a table of a few thousand rows this is one or two page
touches; for millions, three or four, which is the whole point of
B-trees: fanout in the hundreds keeps trees shallow. The internal
data structures are declared in src/btreeInt.h, which
opens with an excellent long comment on the file format.
Stage 8: the pager, pager.c
When the B-tree needs page 2, it does not read the file; it asks
the pager (src/pager.c) via
sqlite3PagerGet(). The pager maintains the page
cache (default around 2 MB, tunable with
PRAGMA cache_size), enforces the locking protocol,
and decides whether the page's current truth lives in the main
database file or, in WAL mode, in the write-ahead log. On a cache
hit, the read touches no I/O at all, and steady-state point
queries on a hot database run entirely from memory.
Stage 9: journal and WAL involvement in a read
Even a pure read participates in the transaction machinery. In
rollback-journal mode the pager takes a SHARED lock on the file,
which blocks any writer from committing while we read, and checks
for a leftover "hot journal" from a crashed writer, rolling it
back first if found. In WAL mode (src/wal.c) the
reader instead records an end mark, the last committed frame in
the -wal file at the moment the read began, and for
every page consults the wal-index in the -shm file:
if the page has a committed copy in the WAL at or before the end
mark, read it from there; otherwise read the main file. That end
mark is a snapshot, and it is why WAL readers never block writers.
Part V returns to this in depth.
Stage 10: the VFS, os_unix.c
The pager's file operations go through the VFS, a struct of
function pointers (xOpen, xRead,
xWrite, xSync, xLock)
implemented for POSIX in src/os_unix.c and for
Windows in src/os_win.c. On Linux,
xRead is ultimately a pread() of one
4096-byte page. This thin seam is what porting SQLite means:
implement one struct and the entire engine runs on your RTOS,
your flash controller, your browser's WASM sandbox.
Stage 11: the way back up
The page's bytes flow up: the pager hands the B-tree a pointer
into its cache; the cursor locates the cell for our rowid;
Column decodes the record format (a header of serial
types, then the values; SQLite computes the offset of column 1 and
decodes only what it needs); ResultRow makes the
value available; sqlite3_step() returns
SQLITE_ROW; your binding calls
sqlite3_column_text() and hands you
'hello'. The next sqlite3_step() resumes
the program at Halt and returns
SQLITE_DONE. One query, ten layers, and every one of
them a file you can open and read.
Part V: Internals deep dives
Deep dive 1: B-trees and the on-disk format
The file format (documented exhaustively at
sqlite.org/fileformat2.html) begins with a
100-byte database header. The first 16 bytes are the
magic string "SQLite format 3\000"; a big-endian
2-byte page size lives at offset 16 (a power of two from 512 to
32768, with the value 1 meaning 65536; 4096 is the default);
offsets 18 and 19 hold the write and read format versions (1 for
rollback journaling, 2 for WAL); offset 28 holds the database size
in pages; offsets 32 and 36 describe the freelist of unused pages.
Lab 6 reads this header with xxd.
After the header, the file is nothing but fixed-size pages, and almost every page is a B-tree page of one of exactly four types, identified by its first byte:
0x02 interior index page (keys + child pointers)
0x05 interior table page (rowids + child pointers, no data)
0x0a leaf index page (keys only)
0x0d leaf table page (rowid + full row record per cell)
table "notes" index on notes(body)
[0x05 interior] [0x02 interior]
/ | \ / | \
[0x0d] [0x0d] [0x0d] [0x0a] [0x0a] [0x0a]
rows rows rows (body,rowid) keys only
A rowid table stores each row as a record in a table B-tree leaf,
keyed by the 64-bit rowid; an index stores records of the indexed
columns plus the rowid in an index B-tree, so every index
lookup that needs other columns must do a second descent into the
table B-tree. A WITHOUT ROWID table skips the rowid
entirely and stores the whole row in an index-type B-tree keyed by
the declared PRIMARY KEY, which is a real win when the natural key
is not an integer and you would otherwise pay for both trees.
Values inside records are encoded with variable-length integers
and serial types, so a NULL costs zero body bytes and small
integers cost one or two.
Now the famous trap. In a rowid table,
INTEGER PRIMARY KEY, spelled exactly that way, makes
the column an alias for the rowid: it is the B-tree key, lookups
on it are direct seeks, and it costs nothing extra. Any other
spelling, including INT PRIMARY KEY, does not.
INT PRIMARY KEY creates an ordinary column plus a
separate unique index B-tree, so every lookup pays the
two-descent price, and thanks to a historical quirk preserved for
file-format compatibility, that primary key column can even
contain NULLs unless you also declare it NOT NULL.
One keyword of difference changes the physical layout of the
table.
Deep dive 2: the pager and rollback journaling
The pager's contract to the B-tree is simple: give me page N, let
me modify pages inside a transaction, and guarantee that a crash
at any instant leaves the database as if the transaction either
fully happened or never happened. The classic mechanism is the
rollback journal. Before the first modification to any page, the
pager copies that page's original content into
app.db-journal. Commit then proceeds in a strict
dance:
write original pages ──▶ journal file fsync journal (undo log is now durable) write modified pages ──▶ database file fsync database delete journal ◀── THIS is the commit point
The deletion of the journal is the atomic commit: if power fails before it, the next process to open the database finds a "hot journal" and rolls the original pages back; after it, the transaction is simply done. Alongside this the pager runs a locking ladder (SHARED for readers, RESERVED for a writer that intends to commit, EXCLUSIVE during the actual write-back), which is why in rollback mode a writer eventually blocks all readers for a moment. Two fsyncs per commit is also the mode's performance signature, and the main thing WAL improves.
Deep dive 3: WAL mode
PRAGMA journal_mode=WAL; inverts the scheme,
following sqlite.org/wal.html: instead of preserving old content
in a journal and updating the database in place, committed
changes are appended as page frames to app.db-wal
and the main database file is not touched at all. A commit
is one append plus one fsync. Two auxiliary files appear next to
the database: -wal holds the frames, and
-shm holds the wal-index, a shared-memory hash table
that lets readers find "the newest committed copy of page N at or
before my snapshot" without scanning the log.
Readers get snapshots for free: each read transaction remembers
its end mark in the WAL and simply ignores every frame after it,
which is why a long-running reader sees a frozen, consistent view
while writers keep committing (Lab 4 demonstrates this in ten
lines of Python). The WAL cannot grow forever, so a checkpoint
periodically copies frames back into the main database; by
default SQLite auto-checkpoints when the WAL passes 1000 pages,
about 4 MB at the default page size, and a checkpoint can only
reclaim frames no active reader still needs, which is why leaving
a read transaction open indefinitely makes the -wal
file grow without bound. When the last connection closes cleanly,
it runs a final checkpoint and deletes the -wal and
-shm files, a detail that confuses everyone the
first time the files "disappear" (Lab 3).
And the correction that belongs in bold:
WAL does not mean multiple writers. SQLite in every mode
allows exactly one writer at a time; WAL means readers and the
one writer no longer block each other. A second connection
trying to write while a write transaction is open still gets
SQLITE_BUSY (Lab 5), and the standard treatment is
PRAGMA busy_timeout plus keeping write transactions
short. WAL also does not work over network filesystems, since the
wal-index genuinely requires shared memory among all connecting
processes.
Deep dive 4: indexes and the query planner
The planner in where.c is cost-based: for each table
in a query it enumerates the ways to access it (full scan, rowid
seek, each usable index) and picks the cheapest combination.
Three rules cover most practical situations.
The left-to-right rule. A composite index on
(a, b, c) is a B-tree sorted by a, then
b, then c. The planner can use a prefix
of equality constraints followed by at most one range:
WHERE a=? AND b=? AND c>? uses all three columns,
but WHERE b=? alone cannot use the index at all, and
WHERE a>? AND b=? uses it only for
a, filtering b row by row. Column order
in a composite index is a design decision, not a formality.
Covering indexes. If every column a query needs
is present in the index (remembering that a rowid-table index
implicitly contains the rowid), SQLite skips the second descent
into the table B-tree entirely, and
EXPLAIN QUERY PLAN says so:
SEARCH notes USING COVERING INDEX idx_notes_body
(body=?) is real output from Lab 2. Adding one or two
trailing columns to an index purely to make hot queries covering
is a legitimate, common optimization.
ANALYZE feeds the cost model. Running
ANALYZE writes table and index statistics into
sqlite_stat1 (row counts and average selectivity per
index), which the planner reads to choose between plausible
plans. Without statistics it falls back on fixed assumptions that
can misfire on skewed data. The pragmatic modern habit is
PRAGMA optimize; when closing connections, which
runs ANALYZE as needed. When the planner still picks wrong, read
the plan, check sqlite_stat1, and remember that the
planner can only use what the schema gives it; the "Next
Generation Query Planner" writeup at sqlite.org/queryplanner-ng.html
is the definitive tour.
A note on the amalgamation and the testing culture
Two project habits explain how a database this widely deployed
changes so confidently. The amalgamation: for release, the
hundred-odd files of src/ are concatenated into one
sqlite3.c of roughly a quarter million lines, turning
the entire engine into two files that drop into any build, with
the single-translation-unit build measured by the project as
five to ten percent faster thanks to cross-module inlining. The
testing: per sqlite.org/testing.html, the project maintains about
590 times as much test code as library code across four
independent harnesses; the proprietary TH3 harness achieves 100%
branch and 100% MC/DC coverage of the core library,
the standard used for avionics; the SQL Logic Test suite runs 7.2
million queries cross-checked against PostgreSQL, MySQL, SQL
Server, and Oracle; and the dbsqlfuzz fuzzer performs about one
billion test mutations per day. The tests are the specification,
which is what makes both aggressive internal change and blind
user upgrades safe.
Part VI: Reading the repository
Read the tree, not the amalgamation: clone the GitHub mirror and
work in src/, about a hundred C files, with the
architecture document at sqlite.org/arch.html as your map. Every
file named below was verified to exist in the current tree.
Stage 0, no code yet. Read sqlite.org/arch.html, sqlite.org/fileformat2.html (at least the header and B-tree sections), and sqlite.org/opcode.html. Questions you should be able to answer: what are the seven or so layers of the stack, in order? What are the four B-tree page types? What is the difference between EXPLAIN and EXPLAIN QUERY PLAN?
Stage 1, the front door. Read
src/main.c (skim, it is the API surface),
src/prepare.c, src/tokenize.c, and the
grammar in src/parse.y. Questions: what does
sqlite3_prepare_v2 return and why is
_v2 preferred? Who calls whom between tokenizer and
parser in SQLite, and why is that unusual? What does Lemon
generate from parse.y?
Stage 2, planning and code generation. Read
src/select.c, then src/where.c with
src/whereInt.h beside it, then
src/wherecode.c and src/whereexpr.c, and
src/build.c for how DDL creates the schema objects
the planner consults. Questions: where does the planner decide
between SCAN and SEARCH? How does a WHERE clause become loop
bytecode? What statistics does the cost model consult and where do
they come from (src/analyze.c)?
Stage 3, the machine. Read the opcode switch in
src/vdbe.c with sqlite.org/opcode.html open, plus
src/vdbeaux.c (program construction) and
src/vdbeapi.c (the step/column/bind API). Questions:
is the VDBE a stack or register machine? What happens across two
consecutive sqlite3_step() calls on a
multi-row query? How does ResultRow suspend the
program?
Stage 4, storage. Read the big comment at the top
of src/btreeInt.h, then src/btree.c
alongside the file format document. Questions: how does a cursor
descend to a rowid? What happens on a page split? How do overflow
pages work for large rows?
Stage 5, transactions. Read
src/pager.c, then src/wal.c, then
src/os_unix.c. Questions: what exact operation is
the commit point in rollback mode and in WAL mode? What is in the
wal-index? What does the VFS interface abstract, and what would
you implement to port SQLite?
Where not to start: the amalgamation
(sqlite3.c is a release artifact, not a reading
text), src/vdbe.c cold (without Stage 1 and 2 you
have no idea why the programs look like they do), and the test
corpus (fascinating, but it is millions of lines). The
command-line shell, built from src/shell.c.in, is a
good side read: one large file exercising the public API the way
your own code would.
Part VII: Hands-on labs
Each lab is a few minutes, uses only the shell and Python, and pins one concept from the deep dives. Outputs shown are from my Linux machine with SQLite 3.51.0; your numbers will differ but the shape will not.
Lab 1: fsync is the unit of cost (transaction batching)
import sqlite3, time
con = sqlite3.connect("bench.db")
con.execute("CREATE TABLE t(x)")
t0 = time.time()
for i in range(500):
con.execute("INSERT INTO t VALUES (?)", (i,))
con.commit() # one fsync per row
t1 = time.time()
con.execute("BEGIN")
for i in range(500):
con.execute("INSERT INTO t VALUES (?)", (i,))
con.commit() # one fsync total
t2 = time.time()
print(f"per-row commits: {t1-t0:.2f}s one txn: {t2-t1:.4f}s")
My run: per-row commits: 3.69s one txn: 0.0085s,
a factor of about 400. Observe: the work is identical; only the
number of durable commit points changed. This is Deep dive 2 made
visible, and it is the single most common SQLite performance bug
in the wild.
Lab 2: watch the plan change shape
sqlite> EXPLAIN QUERY PLAN SELECT id FROM notes WHERE body='hello';
QUERY PLAN
`--SCAN notes
sqlite> CREATE INDEX idx_notes_body ON notes(body);
sqlite> EXPLAIN QUERY PLAN SELECT id FROM notes WHERE body='hello';
QUERY PLAN
`--SEARCH notes USING COVERING INDEX idx_notes_body (body=?)
Observe two things: SCAN became SEARCH, and the index is
COVERING because the query needs only body (the key)
and id (the rowid, stored in every index entry), so
the table B-tree is never touched. Then run full
EXPLAIN before and after and watch
SeekRowid-style seeking replace a
Rewind/Next loop.
Lab 3: see the WAL files, and see them vanish
sqlite3 app.db "PRAGMA journal_mode=WAL; INSERT INTO notes(body) VALUES('walrow');"
ls app.db* # run this from a SECOND terminal while a connection is open
# app.db app.db-shm app.db-wal
Keep one shell connection open and list the directory: the
-wal and -shm files sit beside the
database. Close the last connection and list again: gone, because
the final connection checkpoints and deletes them. If you only
ever look after your process exits, you will wrongly conclude WAL
"isn't on". Also try PRAGMA wal_checkpoint(TRUNCATE);
and watch the -wal file drop to zero bytes.
Lab 4: hold a read snapshot while another connection writes
import sqlite3
r = sqlite3.connect("bench.db"); w = sqlite3.connect("bench.db")
r.execute("PRAGMA journal_mode=WAL")
r.execute("BEGIN")
print("reader sees:", r.execute("SELECT count(*) FROM t").fetchone()[0])
w.execute("INSERT INTO t VALUES (999)"); w.commit()
print("reader still sees:", r.execute("SELECT count(*) FROM t").fetchone()[0])
r.commit()
print("after ending txn:", r.execute("SELECT count(*) FROM t").fetchone()[0])
My output: 1000, 1000,
1001. The reader's end mark froze its view of the
WAL; the writer committed anyway; ending the read transaction
moved the mark. That is snapshot isolation in three prints.
Lab 5: produce SQLITE_BUSY on purpose
import sqlite3
a = sqlite3.connect("bench.db", timeout=0)
b = sqlite3.connect("bench.db", timeout=0)
a.execute("BEGIN IMMEDIATE") # a takes the write lock
try:
b.execute("BEGIN IMMEDIATE") # b wants it too
except sqlite3.OperationalError as e:
print("second writer got:", e) # -> database is locked
One writer at a time, WAL or not. Now set
timeout=5 (Python's spelling of
busy_timeout) on connection b, commit
on a from another thread, and watch b
proceed instead of failing: the production fix is short write
transactions plus a busy timeout, not retry loops around every
statement.
Lab 6: read the file header with xxd
$ xxd -l 100 app.db
00000000: 5351 4c69 7465 2066 6f72 6d61 7420 3300 SQLite format 3.
00000010: 1000 0101 0040 2020 0000 0002 0000 0002 .....@ ........
00000020: 0000 0000 0000 0000 0000 0001 0000 0004 ................
...
Decode it against Deep dive 1: bytes 0-15 are the magic string;
bytes 16-17 are 0x1000 = 4096, the page size; bytes
18-19 are 01 01 here, meaning rollback-journal
format (they read 02 02 after
PRAGMA journal_mode=WAL on a fresh database); bytes
28-31 give the page count. You have now read a database file
with your eyes, which permanently changes your relationship with
the phrase "binary format".
Part VIII: Questions and model answers
Understanding checks. Try to answer before reading; each answer is the short version a working engineer should be able to give.
1. What actually is a prepared statement in SQLite?
A compiled bytecode program for the VDBE register machine.
sqlite3_prepare_v2 runs tokenizer, parser, planner,
and code generator; sqlite3_step runs the resulting
program in the interpreter loop in vdbe.c. EXPLAIN
prints that program verbatim.
2. Why is SQLite's tokenizer/parser relationship unusual? The tokenizer drives: it pushes tokens into a push parser generated by Lemon, SQLite's own parser generator, rather than the parser pulling tokens as with yacc. Lemon also gives the project a grammar format and memory behavior it controls completely.
3. What is the difference between EXPLAIN and EXPLAIN QUERY PLAN? EXPLAIN dumps the full VDBE opcode program; EXPLAIN QUERY PLAN prints a summary of planner strategy per table (SCAN vs SEARCH, which index, covering or not). Use the plan summary for daily index work and the bytecode when you need ground truth about execution.
4. Why is INTEGER PRIMARY KEY special and INT PRIMARY KEY not? In a rowid table, INTEGER PRIMARY KEY (that exact type name) aliases the rowid, the actual B-tree key, so lookups are single-descent seeks. INT PRIMARY KEY creates an ordinary column with a separate unique index B-tree, costing a second descent per lookup, and by historical quirk the column may even hold NULLs unless declared NOT NULL.
5. When should a table be WITHOUT ROWID? When its natural primary key is not a single integer (composite keys, text keys) and rows are not huge: the row data moves into the primary-key B-tree itself, eliminating the hidden rowid tree and one descent per primary-key lookup.
6. Walk through commit in rollback-journal mode. Where is the commit point? Original page images are written and fsynced to the journal, changed pages are written and fsynced to the database, then the journal is deleted. The journal deletion is the commit point: a crash before it triggers rollback from the hot journal; after it, the transaction is durable.
7. What does WAL mode change, and what does it not change? It changes where committed data goes: appended to the -wal file instead of written into the database, so commits are one sequential append plus one fsync, and readers, pinned to an end-mark snapshot, no longer block or get blocked by the writer. It does not change the writer count: still exactly one at a time.
8. Why does a long-lived read transaction make the -wal file grow? A checkpoint may only fold frames into the database once no active reader's snapshot still depends on the prior state, so an open reader pins the WAL and checkpoints cannot fully complete until it ends.
9. What is the -shm file? The wal-index: a shared-memory hash structure that maps page numbers to their newest committed frames in the WAL so readers avoid scanning the log. It requires true shared memory among all connections, which is one reason WAL cannot work across a network filesystem.
10. Your app intermittently throws "database is locked".
Diagnose it. Two writers are colliding, or a writer is
colliding with rollback-mode readers. Check journal mode (move
to WAL if readers are involved), set
PRAGMA busy_timeout so contenders wait instead of
failing instantly, and hunt for long write transactions or
connections left in an open transaction by a framework.
11. A query ignores your new composite index on (a, b).
Why might that be correct? If the query constrains only
b, the left-to-right rule makes the index unusable.
If it constrains a loosely, the planner's statistics
(from ANALYZE, in sqlite_stat1) may price a full scan cheaper
than a seek plus many table descents. Read EXPLAIN QUERY PLAN
before assuming a bug.
12. What is a covering index and how do you spot one in
use? An index containing every column a query touches
(including the implicit rowid), letting SQLite answer from the
index B-tree alone. EXPLAIN QUERY PLAN says
USING COVERING INDEX.
13. When is PostgreSQL the right call instead of SQLite? Whenever more than one machine needs the data, when sustained concurrent writes matter (SQLite serializes writers), or when you need network access control, roles, and server-side extensions. SQLite's own line is that it competes with fopen(), not with client/server databases.
14. When is DuckDB the right call instead of SQLite? Analytical scans and aggregations: DuckDB keeps SQLite's embedded ergonomics but is columnar and vectorized, so OLAP queries over millions of rows are dramatically faster, while SQLite remains better for transactional point reads and writes.
15. Why is putting a writable SQLite file on NFS dangerous? SQLite's transaction safety leans on POSIX advisory locks that network filesystems frequently implement incorrectly, and WAL additionally needs shared memory across processes, impossible across machines. The failure mode is silent corruption, not an error.
16. How can a database engine this widely deployed change its internals so freely? Because the tests are the specification: roughly 590 times as much test code as library code, TH3 with 100% branch and MC/DC coverage, millions of cross-engine logic-test queries, and about a billion fuzzer mutations daily. Any refactor that survives that battery is, operationally, correct.
Part IX: Design lessons
Lesson 1: a bytecode boundary decouples deciding from doing. Compiling SQL to VDBE programs cleanly splits the half of the system that chooses what to do from the half that touches storage, and makes execution inspectable (EXPLAIN) and cacheable (prepared statements). The same move appears in the JVM and CPython, in eBPF, in regex engines, and in every system that compiles a plan once and runs it many times.
Lesson 2: strict layering with one-way interfaces is what makes a codebase readable decades later. VDBE to B-tree to pager to VFS, each speaking only downward through a small interface, is why individual files here are readable in isolation. This is the storage-engine shape everywhere: compare RocksDB's memtable/SST/env layering.
Lesson 3: make the OS boundary a data structure. The VFS is a struct of function pointers, so "porting SQLite" means implementing one struct, and testing can inject a crash-simulating VFS. Any system that must run on hardware you have never seen should steal this: JDBC drivers, LLVM targets, and network abstraction layers are the same idea.
Lesson 4: tests are what purchase the freedom to change. SQLite's 100% MC/DC harness and daily billion-mutation fuzzing are not bureaucracy; they are the asset that lets a three-person team rewrite the query planner under billions of devices. The transferable rule: your rate of safe change is bounded by the strength of your verification, not the skill of your reviewers.
Lesson 5: distribution format is a feature. The amalgamation reduces "adopt a database engine" to "add two files to the build", and that friction removal, more than any benchmark, is why SQLite is in everything. The single-header library culture in C and C++ and the static-binary culture in Go learned the same lesson.
Lesson 6: stable file formats compound. A documented on-disk format, unchanged in essentials since 2004 and pledged readable to 2050, made SQLite a de facto archival standard (the US Library of Congress lists it as a recommended storage format). Formats outlive code; design them like it.
Part X: Memorization framework
The one-sentence summary: SQLite compiles SQL into bytecode for a virtual machine whose cursors walk B-trees, which ride on a transactional pager, which journals or WAL-logs pages through a portable VFS into a single file.
SQL → Parse → Plan → VDBE → B-tree → Pager → WAL/Journal → VFS → OS
The chain mapped to source files:
Parse tokenize.c, parse.y (Lemon) Plan select.c, where.c, wherecode.c, analyze.c VDBE vdbe.c, vdbeaux.c, vdbeapi.c B-tree btree.c, btreeInt.h Pager pager.c WAL/Journal wal.c (+ journal logic in pager.c) VFS os_unix.c, os_win.c
Memorize these blocks:
File format: 100-byte header starting
"SQLite format 3\0"; page size at offset 16, default
4096; four B-tree page types 0x02, 0x05, 0x0a, 0x0d; tables keyed
by rowid, indexes keyed by columns plus rowid; INTEGER PRIMARY
KEY aliases the rowid, INT PRIMARY KEY does not.
Transactions: rollback journal saves old pages, commit point is journal deletion, two fsyncs; WAL appends new pages, commit is one append plus fsync, readers snapshot at an end mark, auto-checkpoint at 1000 pages, one writer always.
Planner: composite indexes work left to right, equalities then one range; covering index means no table descent; ANALYZE fills sqlite_stat1 for the cost model.
Trust: ~590x test-to-library code ratio, TH3 at 100% branch and MC/DC coverage, ~1 billion fuzz mutations per day, SLT cross-checks 7.2 million queries against four other engines (all per sqlite.org/testing.html).