How SQLite Works Internally: The B-Tree Storage Engine, WAL, and Why It Powers More Devices Than Any Other Database

SQLite is running right now on your phone, inside your browser, in your desktop apps, on the airplane entertainment system you used last week, and on hundreds of millions of IoT devices you will never interact with directly. It is the most deployed database in existence by a considerable margin. It is also one of the most misunderstood: engineers reach for it for small projects and embedded use, but rarely have a clear picture of what it is actually doing when they call db.prepare("SELECT ...").

That gap matters more now than it did three years ago. The rise of edge computing, single-region SQLite-based products on Cloudflare D1 and Turso, and replication tools like Litestream have made SQLite a credible choice for production workloads that would have been unthinkable in the “SQLite is only for local dev” era. Understanding its internals tells you exactly where those workloads fit and, equally important, where they break down.

---

The Architecture: One File, Four Layers

SQLite stores an entire database (schema, data, indexes, metadata) in a single file. The implementation underneath that file is organized into four layers that compose cleanly:

┌─────────────────────────────────┐
│         SQL Interface           │  SQL text in, result rows out
├─────────────────────────────────┤
│   Tokenizer → Parser → Code Gen │  produces VDBE bytecode program
├─────────────────────────────────┤
│    VDBE (Virtual Database Engine)│  executes bytecode against B-trees
├─────────────────────────────────┤
│       B-Tree Module             │  table B-trees and index B-trees
├─────────────────────────────────┤
│       Pager Module              │  page cache, transaction journal, WAL
├─────────────────────────────────┤
│       OS Interface (VFS)        │  file I/O, locking, shared memory
└─────────────────────────────────┘

Each layer has one job. The pager does not know what a B-tree is. The B-tree does not know what SQL means. This separation is the reason SQLite is so portable and so testable: you can swap the VFS implementation (and there is a published in-memory VFS, a WebAssembly VFS, and several others) without touching anything above it.

---

The File Format: Pages All the Way Down

Every SQLite database file is divided into fixed-size pages. The page size is set at database creation time, defaults to 4096 bytes, and cannot change afterward. All reads and writes operate in whole-page units. This is not a SQLite quirk; it mirrors how operating systems and storage devices work, so aligned page I/O avoids partial-write tearing.

The first 100 bytes of page 1 are the database header. It stores the magic string SQLite format 3\000, the page size, the file format version, the freelist head pointer, the schema cookie (incremented on every DDL change), and the encoding (UTF-8, UTF-16LE, or UTF-16BE). Reading those 100 bytes tells you everything about the database without parsing a single row.

Pages come in five types:

• Table interior pages: internal B-tree nodes for table data, storing separator keys and child page pointers.
• Table leaf pages: actual row storage.
• Index interior pages: internal B-tree nodes for indexes.
• Index leaf pages: index entries pointing back to rowids.
• Overflow pages: used when a column value is too large to fit in a single page. The cell in the leaf page stores the first fragment and a pointer to the overflow chain.

The freelist is a linked list of pages that have been released (from deletes) and are available for reuse. SQLite reclaims them before growing the file.

---

B-Tree Storage: Table Trees vs Index Trees

SQLite uses two distinct B-tree variants, and confusing them is a common source of misunderstanding.

Table B-Trees (Rowid Trees)

Every table is stored as a B+ tree keyed on the rowid. The rowid is a 64-bit signed integer that SQLite manages internally. When you declare INTEGER PRIMARY KEY, that column becomes an alias for the rowid and costs nothing extra. When you declare any other primary key type (including TEXT or composite keys), SQLite still creates an internal rowid and separately enforces your declared key via an index.

Leaf pages in a table B-tree store the full row record (all column values packed using a variable-length encoding). Interior pages store only separator keys and child page pointers. Range scans walk the leaf page linked list exactly the way you would expect from a B+ tree.

-- This is efficient: rowid lookup is a direct B-tree descent
SELECT * FROM events WHERE rowid = 42;

-- INTEGER PRIMARY KEY is a rowid alias: same cost
CREATE TABLE events (id INTEGER PRIMARY KEY, payload TEXT);
SELECT * FROM events WHERE id = 42;

-- This is NOT a rowid: SQLite creates a hidden rowid + a unique index
CREATE TABLE events (id TEXT PRIMARY KEY, payload TEXT);
-- The query now requires an index scan + a rowid lookup (two B-tree ops)
SELECT * FROM events WHERE id = 'evt_abc';

Index B-Trees

Index B-trees store key entries (the indexed column values plus the rowid) sorted by key. They do not store the full row. A query that uses an index descends the index B-tree to find matching rowids, then descends the table B-tree to fetch each row. Two B-tree lookups per row, unless the query can be satisfied by a covering index (all needed columns are in the index) and the table fetch is skipped entirely.

-- Covering index: no table B-tree lookup needed
CREATE INDEX idx_events_user_ts ON events (user_id, created_at);
SELECT user_id, created_at FROM events
WHERE user_id = ? ORDER BY created_at DESC LIMIT 10;
-- The query reads only the index pages. The table is never touched.

This is why SQLite’s query planner uses EXPLAIN QUERY PLAN to show you whether it is doing a table scan, an index scan, or a covering index scan.

---

The Pager: Transactions, Journals, and WAL

The pager sits between the B-tree module and the filesystem. Its job is to present the B-tree layer with a consistent, transactional view of pages, regardless of what is happening at the OS level below.

Rollback Journal Mode (the Default)

Before SQLite modifies any page, it copies the original page content to a rollback journal file (database.db-journal). If the process crashes mid-write, SQLite detects the leftover journal on next open and rolls back to the pre-transaction state. If the write completes, the journal is deleted. Reads and writes use exclusive file locks during writes, which means only one writer and zero concurrent readers can coexist during a write transaction. Readers block writers and writers block readers.

This is safe and simple. It is also why “SQLite does not support concurrent writes” became the canonical criticism.

WAL Mode

WAL (Write-Ahead Logging) mode changes the concurrency model substantially. Instead of writing modified pages back to the main database file, SQLite appends changed pages to a separate WAL file (database.db-wal). Readers read from the main database file. If a page they need has a newer version in the WAL, they read from the WAL instead. The WAL is periodically checkpointed back into the main database file.

-- Enable WAL mode (persists across connections)
PRAGMA journal_mode = WAL;

-- Set the checkpoint threshold (default: 1000 pages)
PRAGMA wal_autocheckpoint = 1000;

The practical effect: readers never block writers, and writers never block readers. Multiple concurrent readers are fully supported. Only one writer can be active at a time (SQLite is a single-writer system at its core), but that writer does not force readers to wait.

WAL reads require checking both the WAL file and the main database file. For read-heavy workloads with infrequent writes this is essentially free. For write-heavy workloads the WAL can grow large before checkpointing catches up, which increases read overhead as the planner scans more WAL frames.

The shared memory file (database.db-shm) coordinates WAL access across multiple processes using memory-mapped I/O. On embedded platforms where shared memory is unavailable, WAL mode is disabled.

---

The Query Planner and the VDBE

When you call db.prepare("SELECT ..."), SQLite runs a tokenizer and recursive descent parser to build an AST, then a code generator that walks the AST and emits VDBE (Virtual Database Engine) bytecode. VDBE is a register-based virtual machine with roughly 200 opcodes. Every query runs as a bytecode program.

You can inspect the bytecode directly:

EXPLAIN SELECT user_id, count(*) FROM events
WHERE created_at > '2026-01-01'
GROUP BY user_id;

The output is a numbered list of opcodes: OpenRead to open a cursor on a table or index, SeekGE to position the cursor at the first matching key, Column to read a column value from the current row, AggStep to accumulate a group, ResultRow to emit an output row, Halt to terminate. Reading VDBE output is the fastest way to understand what SQLite is actually doing for a given query, and it is far more readable than PostgreSQL’s EXPLAIN output for simple cases.

The query planner chooses access paths before code generation. It uses a cost-based model that considers estimated row counts (stored in sqlite_stat1 after ANALYZE), the available indexes, and the WHERE clause structure. The planner is simpler than PostgreSQL’s: it does not implement hash joins or sort-merge joins for general use. It evaluates nested loop strategies and uses its index selection heuristics to pick the cheapest plan. Running ANALYZE on tables with more than a few thousand rows is not optional if you want reliable plan selection.

---

Why SQLite Dominates Embedded and Edge Use Cases

SQLite has no server process. There is no network socket, no authentication handshake, no connection pool, no memory allocated for idle connections. The database is a file, and opening it is a file open. This eliminates an entire category of operational complexity.

The consequence for latency is significant. A local SQLite read on an NVMe drive is a few microseconds. A PostgreSQL query over loopback, even on the same machine, starts around 100 microseconds once you account for socket overhead, the backend process, and the network stack. For read-heavy applications where data fits in the page cache, SQLite can sustain millions of reads per second on modest hardware.

The edge deployment story builds on this. When a Cloudflare Worker or a Fly.io machine runs SQLite locally, every query is a file operation. No cross-region round trip. For workloads where reads vastly outnumber writes (read: most content serving, user settings, configuration, session storage), this collapses latency in ways that distributed SQL cannot match without complex caching layers.

---

Tradeoffs: SQLite vs the Field

Dimension	SQLite	PostgreSQL	MySQL InnoDB	DuckDB	Turso / libSQL	Cloudflare D1
Concurrency model	Single writer, WAL readers	MVCC, many writers	MVCC, many writers	Columnar OLAP, single writer	Single writer per shard	Single writer per DB
Write throughput	Low (file lock)	High	High	Very high (bulk)	Low	Low
Read throughput	Very high (in-process)	High (networked)	High (networked)	Very high (columnar)	High (local replica)	Medium (Cloudflare edge)
Network overhead	None (embedded)	TCP/socket	TCP/socket	None (embedded)	None (local), sync on write	HTTP (Cloudflare infra)
Replication	None built-in (Litestream, LiteFS)	Streaming WAL	Binary log	None built-in	Built-in (Turso sync)	Built-in (Cloudflare)
OLAP queries	Poor (row-store)	Adequate	Poor	Excellent (columnar)	Poor	Poor
Operational overhead	Zero	High	High	Zero	Low	Near-zero
Multi-tenancy	One file per tenant (simple)	Schema or DB per tenant	Schema or DB per tenant	Not applicable	One DB per tenant	One DB per tenant
Sweet spot	Embedded, edge, local-first, testing	General-purpose OLTP	General-purpose OLTP	Analytics, data pipelines	Edge with sync replication	Cloudflare Workers

DuckDB is worth calling out explicitly: it shares the embedded, no-server architecture with SQLite but uses a columnar storage format optimized for analytical queries (aggregations, window functions, full table scans). If your workload is OLAP and you reach for SQLite, you are getting the wrong tool. If it is OLTP point reads and small writes, DuckDB is the wrong tool. They are not competitors; they serve different query shapes.

---

Production Considerations

Always use WAL mode for anything with concurrent readers. Rollback journal mode with multiple readers is not a concurrency issue you should debug in production. One PRAGMA journal_mode = WAL call at database creation or first open and the problem does not exist.

Set PRAGMA synchronous = NORMAL with WAL, not FULL. FULL calls fsync after every write. NORMAL only syncs at checkpoint boundaries. With WAL, NORMAL is safe: a crash between writes loses only the in-progress transaction, not committed data. The throughput difference is significant on spinning disk and meaningful on NVMe.

Use PRAGMA busy_timeout to avoid SQLITE_BUSY errors when a writer holds the lock and another writer arrives. A value of 5000 (5 seconds) gives enough headroom for typical write transactions without hanging indefinitely.

import Database from "better-sqlite3";

const db = new Database("./data/app.db");

db.pragma("journal_mode = WAL");
db.pragma("synchronous = NORMAL");
db.pragma("busy_timeout = 5000");
db.pragma("foreign_keys = ON");

// Use prepared statements. SQLite recompiles SQL on every exec() call.
// Prepared statements pay the parse + plan cost once.
const getUser = db.prepare("SELECT * FROM users WHERE id = ?");
const insertEvent = db.prepare(
  "INSERT INTO events (user_id, type, payload, created_at) VALUES (?, ?, ?, ?)"
);

// Batch inserts in a transaction. Without a transaction, SQLite
// syncs to disk after every INSERT (even with NORMAL synchronous).
// 1000 inserts individually: ~1000 fsyncs. In a transaction: 1.
const insertMany = db.transaction(
  (rows: Array<{ userId: string; type: string; payload: string }>) => {
    const now = new Date().toISOString();
    for (const row of rows) {
      insertEvent.run(row.userId, row.type, row.payload, now);
    }
  }
);

Run ANALYZE after bulk loads and on a schedule. The query planner relies on statistics in sqlite_stat1. A fresh database with accurate statistics plans correctly. A database that grew from 0 to 50 million rows without ANALYZE may be using a plan optimized for 1000 rows.

Litestream for replication. SQLite has no built-in replication. Litestream streams WAL frames to S3 (or any S3-compatible store) as they are written, providing near-real-time offsite backup and point-in-time recovery. The recovery story is: download the base snapshot and replay WAL frames up to any point in time.

# litestream.yml
dbs:
  - path: /data/app.db
    replicas:
      - type: s3
        bucket: my-app-backups
        path: sqlite/app.db
        region: us-east-1
        sync-interval: 1s
        retention: 72h
        retention-check-interval: 1h

Litestream runs as a sidecar process. It does not modify the database file. It reads WAL frames from the same shared-memory structure SQLite uses for WAL coordination.

One database per tenant at the edge. The multi-tenant story for SQLite-backed edge deployments is one file per tenant, not row-level isolation in a shared database. Cloudflare D1 and Turso are both built on this model. It simplifies backup, migration, and isolation. The operational cost (managing thousands of database files) is handled by the platform layer.

Watch the WAL file size. If writes outpace checkpointing, the WAL file grows unbounded and reads slow down as the planner scans more WAL frames. Monitor WAL size or set PRAGMA wal_autocheckpoint to a value that keeps the WAL under a few hundred megabytes. Forced checkpoints (PRAGMA wal_checkpoint(FULL)) during maintenance windows are a valid safety valve.

Testing is the best use case no one talks about. SQLite in-memory mode (":memory:") gives you a fully isolated, schema-correct database per test with zero setup and teardown overhead. Tests that would take 30 seconds against a Postgres container take under a second against in-memory SQLite.

function createTestDb(): Database.Database {
  const db = new Database(":memory:");
  db.pragma("foreign_keys = ON");
  // Run your migrations
  runMigrations(db);
  return db;
}

---

SQLite’s dominance is not accidental. It is the result of a design that makes the right tradeoffs for an enormous class of workloads: no server, no configuration, no operational overhead, and a storage engine that is correct, well-tested, and fast for the query patterns it was built for. The single-writer limit is real, but for most workloads the reads-per-write ratio is high enough that WAL mode makes it irrelevant. When you understand the pager, the B-tree split between table and index trees, and the WAL frame model, the operational decisions become obvious rather than guesswork.