How to open a 20 GB SQLite database in the browser

A SQLite database is one ordinary file — which makes it tempting to load into a web page, and impossible once it’s bigger than the page’s memory. This is a tour of the file format from the first byte, and then the trick that lets a browser tab query a 20 GB database in about a second: don’t load it at all.

One file, many pages

SQLite stores a whole database — every table, index, view and trigger — in one file, and that file is nothing more than an array of equal-sized pages. The page size is a power of two between 512 and 65,536 bytes (4,096 by default), and page n starts at byte (n − 1) × pageSize. Everything else is built from pages:

Page 1 is special twice over: its first 100 bytes are the database header, and the rest of it is the root of sqlite_schema — the table that lists every other object with its CREATE statement and root page number. Read page 1 and you can find everything.

The 100-byte header

Every field is big-endian. OmniViewer’s METADATA tab decodes all 23 of them with this (from sqlite-format.js):

const u16 = (b, o) => (b[o] << 8) | b[o + 1];
const u32 = (b, o) => ((b[o] << 24) >>> 0) + (b[o + 1] << 16) + (b[o + 2] << 8) + b[o + 3];

function parseHeader(b) {                        // b = the first 100 bytes
  if (new TextDecoder().decode(b.subarray(0, 16)) !== 'SQLite format 3\0') return null;
  const raw = u16(b, 16);
  return {
    pageSize: raw === 1 ? 65536 : raw,           // 65536 doesn’t fit in 16 bits
    writeVersion: b[18], readVersion: b[19],     // 1 = rollback journal, 2 = WAL
    reservedBytes: b[20],                        // per-page space for encryption/checksums
    changeCounter: u32(b, 24),
    pageCount: u32(b, 28),                       // trusted if versionValidFor == changeCounter
    freelistTrunk: u32(b, 32), freelistCount: u32(b, 36),
    schemaCookie: u32(b, 40), schemaFormat: u32(b, 44),
    textEncoding: u32(b, 56),                    // 1 UTF-8, 2 UTF-16le, 3 UTF-16be
    userVersion: u32(b, 60),                     // PRAGMA user_version
    applicationId: u32(b, 68),                   // PRAGMA application_id ("GPKG", "MPBX"…)
    versionValidFor: u32(b, 92),
    sqliteVersionNumber: u32(b, 96),             // 3045001 = SQLite 3.45.1 wrote it last
  };
}

A few of these tell you a lot about a file you’ve never seen: applicationId says whether it’s a GeoPackage or an MBTiles tileset; userVersion is usually the app’s migration number; the last field names the SQLite release that last wrote it; and pageCount × pageSize versus the real file size tells you whether the copy is truncated.

B-trees

Each table is a b-tree — more precisely a B+tree — sorted by its 64-bit rowid. Leaves hold the rows; interior pages hold, for each child, the largest rowid in that child’s subtree, plus a right-most pointer. Every b-tree page has the same layout: a small header, an array of 2-byte cell pointers growing down from the top, and the cells themselves packed up from the bottom of the page.

offset 0   flag      0x05 interior table, 0x0D leaf table (…0x02 / 0x0A for indexes)
       1   u16       first freeblock
       3   u16       number of cells
       5   u16       start of the cell-content area
       7   u8        fragmented free bytes
       8   u32       right-most child (interior pages only)
  8 or 12  u16[n]    cell pointers, in key order

Walking a table in rowid order is then a depth-first walk. This is the reader OmniViewer’s tests use to check the generator’s output (walkTable in sqlite-format.js, trimmed):

function walkTable(readPage, root, usable, onRow) {
  const stack = [root];
  while (stack.length) {
    const pgno = stack.pop();
    const page = readPage(pgno);
    const h = pgno === 1 ? 100 : 0;              // page 1 starts after the header
    const n = u16(page, h + 3);
    if (page[h] === 0x05) {                      // interior: push children right-to-left
      const kids = [];
      for (let i = 0; i < n; i++) kids.push(u32(page, u16(page, h + 12 + 2 * i)));
      kids.push(u32(page, h + 8));
      for (let i = kids.length - 1; i >= 0; i--) stack.push(kids[i]);
    } else {                                     // leaf: payload size, rowid, record
      for (let i = 0; i < n; i++) {
        let p = u16(page, h + 8 + 2 * i);
        const [payload, a] = readVarint(page, p); p += a;
        const [rowid, b] = readVarint(page, p); p += b;
        onRow(rowid, readPayload(readPage, page, p, payload, usable));
      }
    }
  }
}

The fan-out is what makes SQLite fast: a 4 KB interior page holds about 400 children, so four levels cover 400³ ≈ 64 million leaves. Our 20 GB test database has 5.1 million leaf pages under a root, 31 and then 12,539 interior pages — any row is four page reads away.

Records and varints

A row is stored as a record: a header of serial types, one per column, then the column values back to back. Sizes and types use SQLite’s varint — 1 to 9 bytes, 7 bits per byte, high bit meaning “more”, except the 9th byte which contributes all 8 bits:

function readVarint(b, o) {
  let v = 0;
  for (let i = 0; i < 8; i++) {
    const c = b[o + i];
    v = v * 128 + (c & 0x7f);
    if (!(c & 0x80)) return [v, i + 1];
  }
  return [v * 256 + b[o + 8], 9];
}

That table is why SQLite’s types surprise people: the declared column type isn’t stored anywhere in the row — each value carries its own type. The column only has an affinity, a preference applied on insert. OmniViewer’s ELI12 buttons explain each one: INTEGER, REAL, TEXT, BLOB and NUMERIC (dates, booleans, decimals).

Overflow pages and the lock-byte page

A row bigger than roughly a quarter of a page keeps its first part in the leaf and continues in a linked list of overflow pages (4 bytes of next-page number, then data). How much stays local is a formula every reader and writer must agree on exactly:

function localPayloadSize(payload, usable) {     // table-leaf cells
  const X = usable - 35;
  if (payload <= X) return payload;              // fits: no overflow
  const M = Math.floor(((usable - 12) * 32) / 255) - 23;
  const K = M + ((payload - M) % (usable - 4));
  return K <= X ? K : M;
}

And one page is never used at all: the lock-byte page, the page containing byte offset 0x40000000 (1 GiB). SQLite’s Windows locking takes byte-range locks there, so the format reserves it; a database with a b-tree page at that spot is malformed. It only matters for files over 1 GiB — which is exactly how we learned about it: our first 20 GB test file passed every check until a full scan crossed page 262,145.

Why 20 GB is hard in a browser

The usual way to open SQLite in a web page — sql.js, or the official WASM build with an in-memory database — is to read the whole file into a Uint8Array and hand it to SQLite. That stops working somewhere between a few hundred megabytes and 2 GB: a 32-bit WebAssembly heap is at most 4 GB, and a browser tab gives up well before that. Copying the file into the Origin Private File System first avoids the memory limit but costs a full write of 20 GB before the first query, and the same again in disk space.

But SQLite never needed the whole file. It asks the operating system for n bytes at offset o, one page at a time, and a b-tree lookup touches a handful of pages. So the answer is to put a tiny operating system underneath it.

A VFS over the File

SQLite reaches the disk through a VFS (virtual file system): a table of C function pointers — xOpen, xRead, xFileSize, xLock and friends. The official WASM build lets JavaScript supply them. And a dedicated Web Worker has something the main thread doesn’t: FileReaderSync, which reads a slice of a File synchronously — exactly what SQLite’s synchronous C API expects. The heart of OmniViewer’s sqlite-worker.js:

import sqlite3InitModule from './vendor/sqlite/sqlite3.mjs';

const BLOCK = 256 * 1024;                        // read in 256 KB blocks, LRU-cached
const reader = new FileReaderSync();
const cache = new Map();

function block(idx) {
  let b = cache.get(idx);
  if (b) { cache.delete(idx); cache.set(idx, b); return b; }  // LRU touch
  b = new Uint8Array(reader.readAsArrayBuffer(file.slice(idx * BLOCK, (idx + 1) * BLOCK)));
  cache.set(idx, b);
  if (cache.size > 192) cache.delete(cache.keys().next().value);   // ~48 MB
  return b;
}

const io = new capi.sqlite3_io_methods();
sqlite3.vfs.installVfs({ io: { struct: io, methods: {
  xRead(pFile, pDest, n, offset) {               // SQLite: “n bytes at offset”
    const dest = wasm.heap8u().subarray(Number(pDest), Number(pDest) + n);
    let pos = Number(offset), o = 0;
    while (o < n && pos < file.size) {
      const b = block(Math.floor(pos / BLOCK)), s = pos % BLOCK;
      const take = Math.min(b.length - s, n - o);
      dest.set(b.subarray(s, s + take), o); o += take; pos += take;
    }
    if (o < n) { dest.fill(0, o); return capi.SQLITE_IOERR_SHORT_READ; }
    return 0;
  },
  xFileSize(pFile, pOut) { wasm.poke64(pOut, BigInt(file.size)); return 0; },
  xDeviceCharacteristics: () => 0x2000,          // SQLITE_IOCAP_IMMUTABLE
  xWrite: () => capi.SQLITE_READONLY,
  xLock: () => 0, xUnlock: () => 0, xSync: () => 0, /* … */
} } });

Three details make it robust:

• Immutable. Reporting SQLITE_IOCAP_IMMUTABLE tells SQLite the file can’t change underneath it: no locks, no hot-journal check, no -wal lookup. Together with a read-only open and PRAGMA query_only, nothing can write.
• WAL files open too. Header bytes 18–19 of a write-ahead-log database are 2; the VFS presents them as 1, so SQLite reads the main file as a plain database instead of looking for a -wal that wasn’t dropped.
• Read big when scanning, small when seeking. Every FileReaderSync call is a round trip to the browser process, so a full scan in 256 KB steps spends its time on call overhead. A miss just past the current stream doubles the read (256 KB, 512 KB, … 16 MB); any other miss — a b-tree descent — reads one block and leaves the stream alone.
• URLs work the same way. For ?url= loads the block reader is a synchronous XMLHttpRequest with a Range header — also legal in a worker — so a remote database is queried without being downloaded.

What each query costs

With the whole file on disk and SQLite choosing which pages to read, cost is simply pages touched. Measured in Chromium on our 20 GB test database (181.7 million rows, 5.2 million pages), on the machine that built this page:

The full scan is bounded by how fast the browser hands a worker the bytes of a File — on this machine about 150 MB/s through FileReaderSync, where native SQLite reading the same file from the OS cache runs count(*) in 17 seconds. Two lessons follow. First, anything that uses the rowid or an index is instant at any size. Second, SQLite’s query planner is the thing to learn: SELECT min(rowid), max(rowid) FROM t scans the whole table (the min/max shortcut applies only to a lone min() or max()), while SELECT (SELECT min(rowid) FROM t), (SELECT max(rowid) FROM t) is two b-tree descents. OmniViewer’s row estimates use the second form — the first one read all 20 GB in an early version. The QUERY tab’s Explain button shows the plan, flagging every full SCAN.

A query that really must read everything can’t be interrupted from outside — SQLite is running synchronously in its worker. So Cancel terminates the worker and starts a fresh one, which re-opens the file (page 1 and the schema: milliseconds) before it serves the next query. Queries also run in a second worker, separate from the table browser, so a long scan never freezes scrolling.

Scrolling 180 million rows

A table view that says “row 1 of 175,269,376” can’t use LIMIT n OFFSET k — SQLite implements OFFSET by stepping over k rows, so row 170 million would read 19 GB. It uses the rowid instead. Two b-tree descents give the smallest and largest rowid; the scrollbar maps linearly onto that range; each repaint asks for the rows on screen, starting from the rowid under the top edge:

SELECT rowid, * FROM "events" WHERE rowid >= :anchor ORDER BY rowid LIMIT 30

That’s one descent plus one or two leaf pages per screen, wherever you are. Browsers cap an element’s height at a few million pixels, so past ~8 million the spacer is scaled down and the wheel and arrow keys move by rows rather than pixels. Views and WITHOUT ROWID tables have no rowid, so they fall back to OFFSET after an exact count.

Writing a 20 GB database without SQLite

To test all this we needed big databases, so the generator writes the format directly — no SQLite library, in the browser or under Node. Rows arrive in rowid order, so the table b-tree can be built bottom-up and streamed: leaves fill left to right and are written the moment they’re full; each finished leaf hands (page number, largest rowid) to a per-level interior builder, which does the same one level up. Every page takes the next page number as it’s written, so the file comes out strictly sequentially — except page 1, whose schema needs every table’s root page and is written last, at offset 0.

async addRow(rowid, record) {
  const local = localPayloadSize(record.length, usable);
  const cell = varintLen(record.length) + varintLen(rowid) + local + (local < record.length ? 4 : 0);
  if (8 + 2 * (this.nCells + 1) > this.content - cell) await this.flushLeaf(); // page full
  const overflow = local < record.length ? await this.writeOverflow(record, local) : 0;
  this.content -= cell;                          // cells pack down from the page end
  let p = this.content;
  p += putVarint(this.page, p, record.length);
  p += putVarint(this.page, p, rowid);
  this.page.set(record.subarray(0, local), p);
  if (overflow) put32(this.page, p + local, overflow);
  put16(this.page, 8 + 2 * this.nCells++, this.content);   // the cell pointer
}

Memory holds one page per level, so 20 GB costs what 20 KB does; our 20 GB file took 211 seconds (about 100 MB/s) under Node. Two traps we hit and fixed: an interior level that ends right after a page fills would leave a page with zero cells, so the last full page is held back and lends its final child; and allocation has to step over the lock-byte page. Every shape the generator offers passes PRAGMA integrity_check in the test suite.

Limits

• SQLite’s: about 281 TB per database (4,294,967,294 pages × 64 KB), 2,000 columns per table by default, 1 GB per TEXT or BLOB value, 2⁶⁴ rows.
• The browser’s: none on file size — a File is a handle, not a buffer. Sorting or grouping a huge unindexed result builds temporary b-trees in WebAssembly memory, which is capped at a few GB; add a LIMIT or filter on an indexed column.
• Encryption: SQLCipher and SEE databases don’t start with “SQLite format 3”, so they’re shown as raw bytes only.
• WAL: only the main file is read; committed changes still sitting in a separate -wal file aren’t included.
• Read-only, for now: the immutable VFS is what makes a 20 GB file safe to open in a tab. Editing would need a real write path (a copy in OPFS, or the File System Access API with journaling).