SQLite in Production Is Fine, Actually
The conventional wisdom when I was coming up was: SQLite is for development and testing. Postgres (or MySQL, depending on your tribe) is for production. You use SQLite to prototype, and then before you ship you switch to the "real" database, because SQLite doesn't scale and you can't connect multiple processes to it and it's not serious.
I held this view for a long time without ever really examining it. It was just part of the ambient orthodoxy — the kind of belief you absorb from enough Stack Overflow answers and conference talks that it starts feeling like knowledge.
Last year I ran a small production app on SQLite for six months. Here is what I found.
The app in question: a content management tool I built for myself — not RunPee, a separate project — that processes and stores a moderate amount of structured text data. Single server, single process, roughly a few hundred writes per day and a few thousand reads. Not high-volume by any stretch. But a real production workload with real data that I cared about.
I chose SQLite partly as an experiment and partly because the deployment story is dramatically simpler: no separate database service, no connection pooling, no credentials to manage, the database is a file. For a solo operator running one server, that simplicity has real value.
The results:
Performance was not a problem. Not even a little. For my workload, SQLite was faster than I expected — queries that I thought might need indexing were already fast, and the few I did index became fast immediately. The write throughput was nowhere near the limits. I never once waited on the database.
The file-is-the-database model turned out to be a feature, not a limitation. I can cp myapp.db backup-2026-03-01.db and that's my backup. I can open it with the SQLite CLI and run queries against production data directly without setting up any connections. I can scp the file to my laptop to debug something locally and I have an exact replica of production. The operational simplicity is real and it accumulates over time.
I did not encounter the "multiple processes can't connect" problem because I designed around it — one writer, reads through the same process or read-only connections. This is a real constraint, but for a lot of applications it's a constraint that doesn't bind. If you have multiple app servers or heavy concurrent write load, SQLite is the wrong tool. If you don't, the constraint is theoretical.
The one thing I missed from Postgres: extensions. I had a moment where I wanted a specific window function behavior and discovered it worked differently in SQLite. That was a fifteen-minute problem. Not a crisis.
The general point, beyond my specific case:
There's a category of technical decision where the "serious" choice carries a real cost in complexity, operational overhead, and maintenance burden, and where the "toy" choice would have actually been fine. We reach for the serious choice because it signals competence — to ourselves, to colleagues, to anyone who might someday read the architecture doc. Using Postgres says "I'm a professional." Using SQLite says something else.
This is backwards. The professional choice is the one that matches the tool to the requirement. If the requirement is a small, single-server application with moderate traffic and one developer maintaining it, SQLite is the professional choice. Adding Postgres adds a dependency, a service to manage, a connection pool to tune, credentials to rotate, and a whole failure mode (can't connect to the database) that SQLite doesn't have.
I've started applying this more broadly: when I reach for the standard heavy tool by default, I try to ask why. Sometimes the answer is "because this problem is actually at the scale where the heavy tool is warranted." Sometimes the answer is "because I'm used to it." Only one of those is a good reason.
Practical notes if you're considering this:
WAL mode (PRAGMA journal_mode=WAL) is essentially mandatory for concurrent reads — turn it on at startup and you'll be fine. The SQLite documentation is excellent and unusually honest about the tradeoffs, which is rare for database documentation. The litestream project handles continuous replication to S3 or similar if you want backup-as-a-service. And the datasette tool is worth knowing about for querying and exploring your data through a browser.
I'm not using SQLite for everything now. I'm still reaching for Postgres when the project warrants it. But I've stopped treating it as a development toy, and I've stopped feeling sheepish about recommending it for the cases where it fits.
The orthodoxy was wrong. That happens.