SQLite Is a Perfect Little Machine

Open almost any device within arm’s reach of you right now and there is a good chance it is running SQLite. Your phone’s contacts, your browser’s cookie jar and history, most of the apps on your desktop, the firmware in your router, and — if you self-host anything — probably a third of the containers in your compose file. It is by most estimates the single most widely deployed piece of database software on the planet, and it achieved that through a deliberate, sustained refusal to add most of the features you would expect a database to have. No user accounts. No network listener. No replication. One file, one process talking to it at a time for writes, and a design that has not fundamentally changed since 2004.

I want to make the case that this restraint is the actual engineering achievement, not a limitation SQLite happens to have. Most software grows a feature every release because features are legible progress and restraint is not. SQLite’s maintainers have spent two and a half decades doing the opposite, and the result is a database engine you can hold an accurate mental model of in your head, which is a property almost nothing else in your stack can claim.

What “just a file” is actually doing

Calling SQLite “just a file” undersells what is happening inside that file. It is a single binary file containing a B-tree per table and per index, page-organised (4096 bytes by default, matching the filesystem’s own page size so reads and writes align cleanly), with a rollback journal or write-ahead log tracking in-flight transactions. Every table’s rows live sorted by rowid inside its B-tree, and every index is its own separate B-tree mapping indexed values back to rowids. This is architecturally the same idea Postgres or MySQL’s InnoDB use internally; SQLite just skips the server process that normally sits in front of that structure and lets your application link against the storage engine directly.

That single decision — no server process — is what makes SQLite fast for the workloads it is suited to and wrong for the ones it is not. There is no socket round trip, no connection pool, no separate process to context-switch into for every query. A read is a function call into a library already loaded in your process’s address space. For a self-hosted app with one writer and infrequent traffic — which describes most of what actually runs in a homelab — that is strictly faster than talking to Postgres over a Unix socket, because you have removed an entire layer of indirection that existed to solve a concurrency problem you do not have.

The single-writer model, and why it is not a scandal

The fact people bring up first when dismissing SQLite is that only one write transaction can be in flight at a time, database-wide. That is true and it is also the least interesting fact about SQLite’s concurrency, because in write-ahead-log mode readers never block writers and writers never block readers — only writer-versus-writer contention is serialised.

1
2
3

PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
PRAGMA busy_timeout = 5000;

Those three pragmas, set once when you open the database, are most of what separates SQLite’s stock defaults from what it is actually capable of. WAL mode moves writes into a separate append-only log file that readers can ignore, checkpointing back into the main database file periodically rather than making every reader wait on every writer, which is how SQLite’s rollback-journal mode (still the default in the raw C library, though most wrappers now flip it) behaves. synchronous = NORMAL trades a small window of durability risk on an OS crash — not an application crash — for a meaningful write-latency improvement, which is a reasonable trade for almost every self-hosted service that isn’t a ledger. busy_timeout tells SQLite to retry quietly for up to five seconds when it hits a locked database rather than immediately returning SQLITE_BUSY to your application code, which is the single setting most responsible for the “SQLite can’t handle concurrent writes” folklore, because plenty of software ships without setting it at all.

Where the single-writer model actually bites

None of this means SQLite is a universal substitute for a server-based database, and pretending otherwise is how people end up disappointed. A workload with genuinely concurrent writers hammering the same table from a dozen application processes will queue behind each other even in WAL mode, because the underlying constraint — one write transaction at a time — does not go away. If your access pattern looks like that, you have a workload SQLite was never trying to serve, and Postgres earns its overhead. I have written before about how much of what a database actually does for you at query time is index maintenance rather than storage, and that logic applies inside SQLite exactly as it does inside anything bigger — an unindexed foreign key will make SQLite feel slow for the same reason it makes anything else feel slow.

The other place it genuinely struggles is network filesystems. SQLite relies on POSIX file locking to coordinate access, and NFS, most SMB configurations and a fair few container bind-mount setups on non-local storage implement those locks unreliably or not at all, which produces silent corruption rather than a clean error. If your compose file bind-mounts a SQLite file from a network share, you are one flaky lock away from a database that “just” starts returning malformed rows. Keep SQLite files on local disk, and if you need the data available elsewhere, replicate it — don’t share the file directly.

Backing it up honestly

Because it is one file, the instinct is to back it up like any other file, but a SQLite database with WAL enabled is really two or three files at any given moment — the main database, the -wal file, and sometimes a -shm shared-memory index — and copying only the first one mid-write gets you a database missing its most recent transactions. The correct approaches are either a proper VACUUM INTO / .backup command, which SQLite performs atomically with respect to its own locking, or a tool built for exactly this problem:

1

sqlite3 /data/app.db ".backup /backups/app-$(date +%F).db"

For continuous, off-box protection rather than periodic snapshots, Litestream tails the WAL file and streams each committed transaction to object storage in near-real time, which gets you close to point-in-time recovery for a database that has no replication story of its own. It is a genuinely elegant solution to a problem that only exists because SQLite so deliberately declined to solve it internally.

The restraint shows up in the file format too

The part of SQLite’s design I find most admirable is the on-disk file format’s own promise, arguably more than the engine that reads it: the project commits to reading and writing the same file format at least until the year 2050, and the format is documented in enough detail that anyone could write an independent reader without SQLite’s own source code. That is a deliberate rejection of the versioned, migration-gated formats most databases and most application file formats settle into. A database file you wrote in 2015 opens correctly in a build from this year with zero conversion step, which matters enormously the moment you are thinking about a decade of self-hosted data rather than a single deployment.

That same discipline is why SQLite became the default embedded format for entirely unrelated ecosystems rather than staying a niche C library. Browsers adopted it for local storage because it is small enough to statically link and reliable enough not to need a maintenance team. Photo and note-taking apps adopted it because a single portable file is trivially backed up, moved between devices, or inspected with a generic tool when something goes wrong, none of which is true of a bespoke binary format. Even Postgres and MySQL deployments frequently sit next to a SQLite file somewhere in the same stack — a local cache, a job queue, a test fixture — because reaching for the heavier engine for a job this small would be an admission that you don’t trust the little file to do its job, and in my experience that distrust is almost always unearned.

A migration story worth telling honestly

I moved a self-hosted bookmarking tool off Postgres and onto SQLite a while back, mostly out of curiosity about whether the folklore held up, and the honest result was underwhelming in the best possible way: nothing broke, the container count in my compose file dropped by one, and query latency for the single-user workload it actually serves improved slightly because there was no longer a socket round trip on every page load. The only adjustment that mattered was remembering to set the three pragmas from earlier before running any real traffic through it, because the defaults genuinely are that much more conservative than the engine’s real capability. If you are on the fence about whether a given self-hosted app needs Postgres at all, that gap between default and tuned SQLite is worth closing before you conclude the answer is yes.

Troubleshooting the usual complaints

“Database is locked” errors almost always trace back to one of two causes: a missing busy_timeout, covered above, or a long-running read transaction (someone left a REPL connection open mid-query, or an ORM checked out a connection and never returned it) holding a lock past when the application expected it released. Checking PRAGMA wal_checkpoint(PASSIVE); will tell you whether the WAL file is growing unbounded because nothing is checkpointing it, which happens when a reader holds a transaction open indefinitely and prevents the checkpoint from running past that point in the log.

A WAL file that keeps growing rather than checkpointing back into the main database is the second most common complaint, and the fix is almost always the same long-held-open connection problem, or an application performing large numbers of small transactions instead of batching writes — SQLite’s per-transaction fsync overhead in FULL synchronous mode is real, and batching a thousand inserts into one transaction rather than a thousand separate ones is routinely a hundred-times speedup, because the cost is dominated by the commit, not the insert itself.

A third, quieter complaint is a database that grows far larger on disk than the data it contains looks like it should need. This is almost always page fragmentation from years of deletes and updates leaving holes in the B-tree that SQLite does not automatically reclaim. VACUUM rebuilds the file from scratch and reclaims that space, at the cost of holding an exclusive lock and needing roughly as much free disk space again as the database currently occupies while it works, so it is worth scheduling during a maintenance window rather than discovering the hard way that it blocks every writer for the several minutes a multi-gigabyte file takes to rebuild.

Is it worth trusting for real work

For the overwhelming majority of what a homelab runs — a bookmark manager, a feed reader, a link shortener, a personal wiki, a status page’s own history — yes, unreservedly, provided you set the three pragmas above and keep the file on local disk. The ceiling is real but it is much higher than the folklore suggests, and the number of self-hosted apps that actually need Postgres’s concurrency model is smaller than the number that ship with it purely because it was the default in a tutorial somewhere. SQLite earns the “perfect little machine” label because it does one well-defined job, refuses every temptation to become something bigger, and has stayed reliable enough for twenty-five years that Apple, Google and Mozilla all trusted it with data far more important than your homelab’s. That restraint is rarer in software than genuine cleverness, and it is why the file sitting quietly in your container’s data volume is doing more competent engineering than most of the services built on top of it.