How SQLite Works: B-Tree Storage, WAL Mode, and the Single-File Architecture That Powers Edge Computing

Most engineers treat SQLite as a toy: useful for tests, mobile apps, and local prototypes, but not for real workloads. That framing misses what SQLite actually is. It is the most widely deployed database engine in the world, running in every Android and iOS device, every browser (via the Web Storage layer), every macOS and Windows installation, and increasingly in edge compute environments where spinning up a Postgres cluster is not an option.

Understanding why SQLite works the way it does, and where it breaks down, requires looking at its storage engine, its transaction model, and the design decisions that make it simultaneously capable and constrained.

The Single-File Architecture

The most distinctive thing about SQLite is that an entire database lives in a single file. No server process, no shared memory segment required for normal operation, no network socket. The file is portable across operating systems and CPU architectures (with minor endianness caveats).

The file is divided into fixed-size pages. The default page size is 4096 bytes, matching the OS virtual memory page size on most platforms. You can set this at database creation time with PRAGMA page_size, but you cannot change it after data has been written. Every B-tree node, every overflow record, every freelist entry occupies exactly one page.

The first 100 bytes of page 1 are the database header. It stores the page size, file format version, encoding (UTF-8 or UTF-16), the schema cookie (incremented on every schema change), the number of pages in the file, and the freelist root page number. The schema cookie is what SQLite uses to detect when a cached compiled statement is stale after a CREATE TABLE or ALTER TABLE.

// Approximation of the SQLite file header layout
interface SqliteHeader {
  magic: string;         // "SQLite format 3\000", 16 bytes
  pageSize: number;      // 2 bytes; value 1 means 65536
  fileFormatWriteVersion: number;  // 1=rollback, 2=WAL
  fileFormatReadVersion: number;
  reservedBytes: number; // bytes reserved at end of each page
  maxEmbeddedPayloadFraction: number;  // must be 64
  minEmbeddedPayloadFraction: number;  // must be 32
  leafPayloadFraction: number;         // must be 32
  fileChangeCounter: number;  // 4 bytes; incremented on each write
  databasePageCount: number;  // 4 bytes
  freelistTrunkPage: number;  // first page of freelist trunk
  freelistPageCount: number;
  schemaCookie: number;       // 4 bytes; incremented on schema change
  schemaFormat: number;       // 1, 2, 3, or 4
  defaultPageCacheSize: number;
  largestRootBtreePage: number;  // for incremental vacuum
  textEncoding: number;          // 1=UTF-8, 2=UTF-16le, 3=UTF-16be
  userVersion: number;           // application-defined
  incrementalVacuumMode: number;
  applicationId: number;
  versionValidFor: number;
  sqliteVersionNumber: number;
}

When rows are deleted, their pages are added to the freelist. The freelist is a linked list of pages. The trunk page stores up to (pageSize / 4) - 2 leaf page numbers, then points to the next trunk. New allocations pull from the freelist before extending the file. This means DELETE-heavy workloads do not necessarily grow the file, but they do leave gaps. VACUUM rewrites the entire database to a new file and replaces the original, reclaiming all freelist pages and defragmenting the B-trees.

The B-Tree Storage Engine

SQLite uses two flavors of B-tree: table B-trees and index B-trees. Both are B+-tree variants where interior nodes store only keys and pointers, and all actual records live in leaf nodes.

Table B-trees are keyed on the rowid, a 64-bit signed integer. Every table without a WITHOUT ROWID clause has a table B-tree. If you declare INTEGER PRIMARY KEY, that column becomes an alias for the rowid and is stored only once. If you use any other primary key, SQLite creates a hidden rowid and the primary key becomes an index. WITHOUT ROWID tables use the primary key directly as the B-tree key and are efficient for tables that are always accessed by their primary key.

Index B-trees store (indexed-columns, rowid) as the key. A lookup on a non-rowid column first searches the index B-tree to retrieve the rowid, then does a second lookup in the table B-tree. This second lookup is called a table scan by rowid or a “lookup by rowid.” It is cheap (logarithmic), but covering indexes avoid it entirely by including all needed columns in the index.

-- Covering index: the query can be answered without touching the table B-tree
CREATE INDEX idx_orders_customer_status
  ON orders (customer_id, status, created_at);

-- This query is fully covered by the index above
SELECT status, created_at
FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC;

When a record is too large to fit in a single page, SQLite writes the first portion of the payload inline in the B-tree leaf and chains the remainder across overflow pages. Each overflow page stores a 4-byte pointer to the next overflow page followed by payload bytes. The inline portion is calculated to ensure that at least 4 records fit on a leaf page. Overflow pages are common with large TEXT or BLOB columns and can degrade sequential scan performance because the overflow chain requires random page reads.

Page splits occur when a B-tree leaf is full and a new record must be inserted. SQLite splits the leaf, redistributes records, and inserts a separator key in the parent interior node. If the parent is also full, the split propagates upward. In pathological cases, repeated splits at the root create a new root with two children and increase tree height. Balanced inserts (monotonically increasing rowids, as with auto-increment) fill pages efficiently and minimize splits. Random key inserts fragment the tree.

The Virtual Database Engine

SQL does not execute directly in SQLite. The compiler translates a SQL statement into bytecode for the Virtual Database Engine (VDBE), a register-based virtual machine.

The pipeline is: tokenizer -> parser (Lemon LALR parser) -> code generator -> VDBE. The code generator is where query planning happens: join order selection, index selection, and optimization passes including the query flattener (which merges subqueries into the parent query when safe to do so).

You can inspect the generated bytecode with EXPLAIN:

EXPLAIN SELECT name FROM users WHERE id = 1;

addr  opcode         p1    p2    p3    p4             p5  comment
0     Init           0     7     0                    0   Start at 7
1     OpenRead       0     2     0     1              0   root=2 iDb=0
2     Integer        1     1     0                    0   r[1]=1
3     SeekRowid      0     6     1                    0   intkey=r[1]
4     Column         0     1     2                    0   r[2]=users.name
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

SeekRowid is a direct B-tree key lookup by integer primary key. Index scans appear as SeekGE + IdxGT pairs. Full table scans use Rewind + Next. Understanding this bytecode is useful for debugging unexpected full scans, but EXPLAIN QUERY PLAN (which shows the logical plan) is usually sufficient for day-to-day index tuning.

The Pager and Page Cache

The pager sits between the B-tree engine and the OS file system. Its responsibilities are: reading and writing pages, managing the page cache (a fixed-size buffer pool), tracking dirty pages, and coordinating journal writes for crash recovery.

The page cache size is set with PRAGMA cache_size. The default is -2000, meaning 2000 kibibytes (approximately 2 MB). For server workloads, increasing this to -65536 (64 MB) or more reduces I/O significantly for read-heavy access patterns. Each dirty page is tracked with a dirty flag. When a transaction commits, the pager flushes dirty pages to the journal or WAL before writing them to the database file.

The pager also handles memory-mapped I/O via PRAGMA mmap_size. When enabled, SQLite maps a portion of the database file into the process address space and lets the OS page cache handle caching. This bypasses SQLite’s own page cache for reads and can improve throughput on read-heavy workloads, at the cost of less predictable memory usage.

WAL Mode vs Rollback Journal Mode

This is the design decision that most affects SQLite’s behavior under concurrent access.

Rollback journal mode is the default. Before modifying a page, the pager writes the original page content to a journal file (the -journal file next to the database). On commit, the journal is deleted or zeroed. On crash, SQLite replays the journal to restore the original pages. The key constraint: writers hold an exclusive lock on the database file during the write. Readers are blocked while a write is in progress.

WAL mode (write-ahead logging) inverts this. Instead of writing original pages to a journal before overwriting them, SQLite appends new page versions to a separate WAL file (.wal). The database file is never modified mid-transaction. Readers read from the database file plus any relevant WAL frames. Writers append new frames to the WAL without touching the database file.

-- Enable WAL mode (persists for the lifetime of the database file)
PRAGMA journal_mode = WAL;

-- Confirm
PRAGMA journal_mode;
-- Returns: wal

The critical benefit: readers never block writers, and writers never block readers. Multiple readers can operate concurrently with a single writer. The limitation is that there can only be one writer at a time. WAL mode adds a small amount of read overhead because readers must check whether a given page has a newer version in the WAL, which requires scanning the WAL index.

The WAL index (-shm file) is shared memory. It is a memory-mapped file that tracks which pages have frames in the WAL, the maximum valid frame for each transaction, and the read-lock table. If the -shm file is deleted, SQLite recreates it from the WAL on the next open. Because the WAL index uses shared memory, WAL mode requires that all connections to the database are on the same host. WAL mode is incompatible with databases on network filesystems (NFS, SMB).

Checkpointing transfers WAL frames back to the database file. SQLite runs checkpoints automatically when the WAL reaches 1000 pages (configurable via PRAGMA wal_autocheckpoint). There are four checkpoint modes:

• PASSIVE: copies WAL frames to the database file without blocking any readers or writers. Frames with active readers are skipped. The WAL may not be fully checkpointed.
• FULL: waits for all current readers to finish, then copies all frames. Returns after completing the checkpoint.
• RESTART: like FULL, but also waits for all readers to finish reading from the WAL before resetting the WAL write position to the beginning. This bounds WAL file growth.
• TRUNCATE: like RESTART, but physically truncates the WAL file to zero bytes after checkpointing.

// Checkpoint from application code using better-sqlite3
import Database from "better-sqlite3";

const db = new Database("/data/app.db");
db.pragma("journal_mode = WAL");
db.pragma("wal_autocheckpoint = 1000");

// Force a RESTART checkpoint (good for scheduled maintenance)
const result = db.pragma("wal_checkpoint(RESTART)") as Array<{
  busy: number;
  log: number;
  checkpointed: number;
}>;

// busy=0 means no readers blocked the checkpoint
// log=total frames in WAL
// checkpointed=frames successfully copied
console.log(result[0]);

Transaction Isolation and the Five Locking States

SQLite implements serializable isolation by default. The five locking states are: UNLOCKED, SHARED, RESERVED, PENDING, and EXCLUSIVE.

A read transaction acquires a SHARED lock. Multiple SHARED locks can coexist. A write transaction starts by acquiring a RESERVED lock (only one connection can hold RESERVED at a time), writes to the journal or WAL, then escalates to PENDING (blocks new SHARED lock acquisitions), then to EXCLUSIVE (waits for existing SHARED locks to release), and finally flushes changes to disk. In WAL mode, the locking model is simpler: writers only acquire a write lock, and readers use the WAL index’s read-lock slots.

SQLite exposes three transaction types:

• BEGIN DEFERRED (default): acquires no lock until the first read or write. The first read acquires SHARED; the first write tries to acquire RESERVED.
• BEGIN IMMEDIATE: acquires RESERVED immediately. Guarantees that the transaction will be able to write without contention once it starts, but still allows other readers.
• BEGIN EXCLUSIVE: acquires EXCLUSIVE immediately. Blocks all other connections, readers included.

// Use IMMEDIATE for write transactions that must not fail mid-flight
const transfer = db.transaction((fromId: number, toId: number, amount: number) => {
  const debit = db.prepare("UPDATE accounts SET balance = balance - ? WHERE id = ?");
  const credit = db.prepare("UPDATE accounts SET balance = balance + ? WHERE id = ?");
  debit.run(amount, fromId);
  credit.run(amount, toId);
});

// Wrap in IMMEDIATE to prevent SQLITE_BUSY mid-transaction
db.exec("BEGIN IMMEDIATE");
try {
  transfer(1, 2, 100);
  db.exec("COMMIT");
} catch (err) {
  db.exec("ROLLBACK");
  throw err;
}

When a connection encounters a locked database, it returns SQLITE_BUSY immediately by default. Set busy_timeout to make SQLite retry for a given number of milliseconds before returning the error. For server workloads, a value between 3000 and 10000 milliseconds is reasonable.

PRAGMA busy_timeout = 5000;

Query Planning

SQLite’s query planner is simpler than PostgreSQL’s but not naive. It uses the sqlite_stat1, sqlite_stat2, sqlite_stat3, and sqlite_stat4 tables to store histogram statistics for each index. These statistics drive cardinality estimates that inform index selection and join order.

The ANALYZE command populates these statistics by scanning each index. Without it, SQLite falls back to heuristics based on declared column types and UNIQUE constraints.

-- Run after bulk loads or significant data changes
ANALYZE;

-- Analyze a specific table
ANALYZE orders;

-- PRAGMA optimize runs ANALYZE selectively on tables that need it
-- Designed to be called on every database close in application code
PRAGMA optimize;

The query flattener is an optimization pass that merges subqueries into the parent FROM clause when the subquery has no aggregates, DISTINCT, ORDER BY, or LIMIT. This allows the planner to choose a better join order across what appeared to be separate queries.

The where-clause analyzer decomposes WHERE conditions into a set of terms, matches each term against available indexes, and constructs a cost model comparing index scan cost versus full table scan cost. The cost of an index scan is estimated as the number of index lookups plus the number of rowid lookups. When a covering index is available, the rowid lookup cost is zero.

SQLite at the Edge

SQLite’s single-file, serverless architecture is a natural fit for edge compute environments, where you cannot run a Postgres cluster in each of 300 global PoPs.

Cloudflare D1 embeds SQLite directly in Workers. Each D1 database is a SQLite file stored in Cloudflare’s object storage layer and replicated to edge locations. Writes go through a primary, reads can be served from edge replicas. The API maps directly to SQLite semantics.

Turso (libSQL) forks SQLite into libSQL, which adds a client-server mode, HTTP-based wire protocol, embedded replicas, and multi-tenancy. An embedded replica keeps a local SQLite copy in sync with a remote primary using a replication stream. Reads are local (zero network latency), writes go to the primary. The model is eventually consistent for reads from the embedded replica.

LiteFS (from Fly.io) is a FUSE filesystem that intercepts SQLite WAL writes and streams them to a replica over the LiteFS replication protocol. It provides a primary-follower topology where all writes must go to the primary node, and followers receive changes via the replicated WAL stream. Applications run unmodified because LiteFS operates at the filesystem layer.

These projects all share the same insight: SQLite’s WAL is a natural replication unit. Each WAL frame is an atomic page-level change with a transaction boundary. Streaming frames is cheaper than replicating row-level changes through a logical replication protocol.

Tradeoffs

Dimension	SQLite	PostgreSQL	MySQL	DuckDB
Concurrency model	Single writer, multiple readers (WAL)	MVCC, many concurrent writers	MVCC, many concurrent writers	Single writer (OLAP-optimized)
Write throughput	Low to medium (serialized writes)	High	High	Not optimized for OLTP writes
Deployment complexity	Zero (file on disk)	Moderate (server process, config)	Moderate (server process, config)	Zero (embedded library)
Edge compatibility	Native (serverless, single file)	Poor (requires persistent server)	Poor (requires persistent server)	Poor (not designed for edge)
Analytical query support	Basic	Good (parallel query, partitioning)	Moderate	Excellent (columnar, vectorized)
Replication options	LiteFS, libSQL, D1 (application-level)	Streaming replication, logical replication	Binary log replication, GTID	Not applicable (analytical use)
Full-text search	FTS5 (trigram, BM25)	tsvector, pg_trgm	FULLTEXT index	Not built-in
Ecosystem maturity	Excellent (40+ years, ubiquitous)	Excellent	Excellent	Growing (3-5 years)

Production Considerations

Set journal_mode=WAL for any server workload. The default rollback journal serializes readers and writers. WAL removes that constraint and is strictly better for concurrent access, with the caveat that it requires same-host connections.

Configure busy_timeout. Without it, any write contention returns SQLITE_BUSY immediately and crashes your request. A 5-second timeout is a safe starting point. Monitor how often your application hits this timeout to detect write contention early.

Run PRAGMA optimize on application close or on a schedule. It runs targeted ANALYZE passes only where statistics are stale. Without fresh statistics, the query planner makes poor index choices on skewed data distributions.

Use WITHOUT ROWID tables for narrow, primary-key-accessed tables. If you have a table that is always accessed by its primary key and the primary key is compact (integer or short string), WITHOUT ROWID eliminates the rowid indirection and packs records more efficiently into B-tree leaves.

Avoid large BLOBs in the main database file for hot workloads. Overflow pages fragment reads. Store large binary objects on the filesystem or object storage and keep only references in SQLite. The sqlite3_blob_open API allows streaming reads of BLOBs without loading them fully into memory if you must store them inline.

SQLite is the wrong choice when: you have multiple application servers all writing concurrently to the same database (the single-writer constraint becomes a bottleneck), you need multi-primary replication, you have analytical queries over billions of rows (use DuckDB or a columnar store), or your database needs to be on a network filesystem (WAL mode is incompatible with NFS).

The single-writer limitation is the one that most often catches teams by surprise. A Node.js application with a connection pool of 10 workers writing to SQLite will serialize all writes through SQLite’s internal mutex. If your write rate is low (under a few hundred per second), this is fine. If you are processing high-volume event streams or handling burst write traffic from many concurrent users, you will exhaust busy_timeout under load.

The Architecture Behind the Ubiquity

SQLite’s design choices compound into something unusual: a database that is simultaneously the right choice for embedded devices, mobile apps, test suites, and edge compute, but the wrong choice for high-concurrency multi-writer OLTP systems. The single-file layout makes distribution trivial. The WAL makes concurrent reads practical. The VDBE makes the query engine portable across platforms without native compilation. The zero-configuration deployment removes entire categories of operational failure.

The recent wave of edge database infrastructure (D1, Turso, LiteFS) is not adding capabilities SQLite lacks. It is building the replication and distribution layer on top of a storage engine that was already correct. WAL frames are a natural unit of replication. A single-file database is a natural unit of tenancy. The edge database ecosystem is, at its core, SQLite plus a replication protocol plus a routing layer. Understanding SQLite internals means you understand the foundation that all of these systems are built on.