SQLite

SQLite is not a smaller PostgreSQL. It is a different tool that happens to speak SQL: a library rather than a server, storing a whole database in one file on disk.

Why we still choose it

Because for a great many jobs a server is pure overhead. Build-time indexes, local caches, desktop and handset applications, analytics over a dataset that fits comfortably on one machine, test fixtures, and anything that has to work on a field device with no network — SQLite handles all of these with no process to run, no port to filter and no credential to rotate.

It is also the most thoroughly tested software most people will ever use. The project maintains an extraordinary test suite with full branch coverage, which is why it ships inside effectively every phone, browser and aeroplane in service.

And it is in the public domain — not merely permissively licensed. There is no notice to reproduce and no licence that can change. For a company whose argument is that software should be yours to keep, that is about as strong as the guarantee gets.

Where it fits in our work

Local indexes for retrieval over your documents, on-device storage for the Linux handsets, build-time data for Jekyll sites, and the analysis we do during a survey — where loading somebody’s invoice history into one file and querying it beats standing up a server for two days’ work.

The honest limit

One writer at a time. SQLite handles concurrent readers well and, with WAL mode, mixed read-write loads on a single machine, but it is not built for many concurrent writers across several application servers. When that is the shape of the problem, the answer is PostgreSQL, and we do not try to be clever about it.

It is also easy to reach for out of laziness. Running Nextcloud on SQLite, for instance, is the single most common cause of “Nextcloud is slow” — the right tool used in the wrong place.