SQLite in production, on purpose

The case for SQLite is usually made on performance, and that is the least interesting part of it. A single file on a local SSD is fast, but so is Postgres on the same machine. The reason we reach for it on content-shaped sites is operational: there is no second process to supervise, no connection pool to size, no network between the application and its data, and a backup is a file.

That is a real reduction in the number of things that can be broken at 3 a.m., and it is worth something. It is also not free.

Turn on WAL, and know what it does not give you

Write-ahead logging is the difference between “readers block writers” and a database several people can use. It is one pragma, and it should be set before anything else. What it does not give you is concurrent writers: there is still exactly one, and a second one waits. For a site whose writes come from a back office that is a non-issue. For a site whose writes come from its visitors it is the whole design, and this is the point at which the answer is Postgres.

An absolute path, always

This is the one that bit us, and it is worth the paragraph. A relative SQLite URL resolves against the process working directory, and SQLite’s answer to “that file is not there” is to create an empty one. There is no error. The site came up, rendered, returned 200 for everything, and had no team, no projects and no content — because a deployment wrapper had started the process from one directory up.

An empty database is the worst possible failure mode, because it looks exactly like a working system with nothing in it. Every path we hand SQLite is now absolute and comes from an environment variable with an absolute default, and the application refuses to start against a database with no schema in it.

Back up with the API, not with cp

Copying a live SQLite file with cp gives you a file that is usually fine, which is a category of backup nobody should accept. With WAL on, a copy taken mid-write can miss the WAL entirely. Use VACUUM INTO or the backup API, which take a consistent snapshot of a live database, and then copy that off the machine. Ours runs hourly and the restore is tested monthly, because a backup nobody has restored is a belief, not a backup.

Know the sentence that ends the argument

SQLite stops being the right answer the moment you need a second machine writing to the same data. Not the moment traffic grows — a single file on decent hardware will serve more read traffic than most companies will ever have — but the moment the topology changes. Being able to say precisely where that line is, before you are standing on it, is what makes the choice engineering rather than fashion.