SQLite on a static host

I didn’t believe it until I opened the network tab. The page is a static site on GitHub Pages, there’s a 670MB SQLite database sitting next to it as a plain file, and when I ran a query against it from the browser, the network tab showed a handful of requests and a few dozen kilobytes. Not 670MB. Not even one megabyte.

The demo is phiresky’s post, Hosting SQLite databases on Github Pages, from April, which I’d bookmarked and only properly read this month after it came up again in a conversation at work about cheap ways to publish a dataset. The database is the World Bank’s World Development Indicators, six tables and over 8 million rows. You can type SQL into the page and get results in about the time it takes a normal API call to come back.

The trick behind it is a stack of ordinary pieces, each of which has been around for years, and I think it’s worth going through them one at a time, because the combination says something nice about how SQLite was designed.

Piece one: SQLite in the browser

SQLite is a C library, and you can compile C to WebAssembly with Emscripten. sql.js has done that for years. It gives you a complete SQLite in a JavaScript object, and the catch has always been that it works on a database in memory. You download the whole file, hand it the bytes, and query away. Fine for a 2MB database, useless for a 670MB one.

Piece two: SQLite doesn’t read files

This is the part I didn’t know until I went digging. SQLite never calls open or read directly. Everything it does to storage goes through a layer called the VFS, the virtual file system, which is a struct of function pointers: open a file, read n bytes at offset x, write, sync, lock, get the file size, and so on. The default VFS on Unix calls pread, and the one on Windows calls ReadFile, but you can register your own, and people do, for encryption, for compression, for storing a database inside some other storage system.

So if you write a VFS whose “read n bytes at offset x” is implemented as an HTTP request for exactly those bytes, SQLite has no idea it isn’t reading a local disk. That’s what phiresky’s library, sql.js-httpvfs, does.

Piece three: range requests

HTTP has been able to ask for part of a file since HTTP/1.1. You send a header like Range: bytes=4096-5119, and a server that supports it answers 206 Partial Content with just those 1,024 bytes. Every static host and CDN I know of supports it, because that’s how video seeking and resumable downloads work. GitHub Pages does. S3 does. So does nginx serving files off a disk with no configuration at all.

Piece four: the file format

The reason this is fast, rather than just possible, is how SQLite lays out its file. The whole database is one file divided into fixed-size pages, 4,096 bytes by default. The first 100 bytes of page 1 are a header that starts with the string SQLite format 3 and a zero byte, and at offset 16 there’s a two-byte number that says what the page size is, which is the first thing SQLite reads when it opens a database.

Every table is a B-tree keyed on its rowid, with the rows themselves in the leaf pages, and every index is a separate B-tree whose entries are the indexed columns plus the rowid. (It’s the InnoDB arrangement from my B-trees post, where the table is the tree.) The schema itself lives in a table rooted at page 1, so opening a database means reading the header and the schema, and after that SQLite knows the root page of every table and index.

So a lookup by an indexed column, something like where indicator_code = 'SP.POP.TOTL', is a walk down the index B-tree from its root to a leaf, which gets a rowid, and then a walk down the table B-tree to the leaf holding that row. Each step is one page, one read, and with this VFS, one HTTP request. phiresky’s post traces exactly this kind of query and counts seven page reads: three for the schema (cached after the first query), two in the index, two in the table.

The depth is what makes it cheap. A B-tree with a fanout of 100 holds a million keys in three levels and a hundred million in four, so even on a big database you’re looking at a handful of reads per lookup. The 670MB file is about 686,000 pages of 1KB, and an indexed query touches single digits of them.

The tuning

He made two changes that matter.

The first is page size. He rebuilt the database with 1KB pages instead of the default 4KB (pragma page_size = 1024, then vacuum, which rewrites the file with the new size). Smaller pages mean each request brings back less data you didn’t need, at the cost of a slightly deeper tree and more requests for big reads. On a local disk you’d never shrink the page size, but over HTTP, where each request is tens of milliseconds and the data is tiny, it’s the right trade for point lookups.

The second is read-ahead. A query that scans a range, or a full-text search, reads a lot of consecutive pages, and one request per kilobyte would be miserable. So the VFS watches the read pattern, and when it sees reads going forward sequentially it doubles the size of the next request, and doubles again, so a long scan needs a number of requests that grows with the logarithm of its length rather than linearly. He tracks three of these “read heads” at once, since a query might be scanning an index and a table at the same time. In one of his examples, a query that read about 270KB took about 20 requests instead of 270. Full-text search with FTS5 works too, against an 8MB index, for about 70KB of transfer per search.

There’s one awkward bit I had to go look up. SQLite’s VFS interface is synchronous, a read call returns the bytes, while network I/O in the browser is asynchronous. The library runs SQLite in a web worker, and as far as I can tell from reading its source, it does the fetching with synchronous XHR, which browsers stopped tolerating on the main thread years ago but still allow inside a worker. The page talks to the worker with promises, so from the outside it’s all async.

Where it falls apart

It’s read-only, for a start. There’s no locking over HTTP and nowhere to write a journal. If the data changes, you rebuild the file and deploy it, and you need to bust caches when you do, because a client holding pages from the old file and fetching new pages from the new one would see a corrupted database. The library has a setting for exactly that.

Latency adds up fast. Each B-tree level is a round trip, and they’re sequential, because you can’t know which child page you need until you’ve read the parent. On a good connection that’s 20–50ms a hop, and seven of them is a perceptible pause. On a phone on a train it’s a lot worse.

And it punishes queries that don’t use an index far harder than a normal database does. On a server, a full table scan of 670MB is a few seconds of disk. Here it’s downloading the database. The README makes a point of saying indexes matter even more than usual, and there’s an option to cap the number of bytes a query may read, so a bad query fails instead of quietly pulling down the file. I’d set that on anything public.

I tried it on a dataset I had lying around from a project in the data science certificate a few years ago, county-level data that I’d loaded into SQLite at the time because R was choking on joins. About 300MB. It took an hour to build the file with sensible indexes and the 1KB page size, and the result was a page I could drop on any static host that answers lookups by county in well under a second. Without the indexes, the first query I tried read for a long time before I gave up on it.

Every piece of this is old. The VFS layer has been in SQLite for a long time, range requests are from the 90s, Emscripten has been around for years. The same range-request idea is behind Cloud Optimized GeoTIFFs, where map clients read just the tiles they need out of a huge image, and it’s how a zip reader can list a remote archive by fetching the directory at the end. What’s new is someone noticing that SQLite’s file format and its pluggable storage layer make it a near-perfect fit, so a whole queryable database can be a static file with no server behind it.*

* Static hosting is also very hard to take down and costs nothing at this size, which is why I’m considering it for a couple of internal reference datasets that currently live behind a tiny API server nobody wants to maintain.