SQLite in production, seriously

SQLite runs on more devices than every other database combined. It is in every phone, every browser, most cars, and a large fraction of embedded systems. It is one of the most thoroughly tested pieces of software ever written — the test suite has roughly a hundred times more code than the library itself, with 100% branch coverage.

And a lot of engineers still classify it as "the thing you use before you get a real database."

when it is the right choice#

Read-heavy workloads on a single machine. SQLite has no network hop, no connection pool, no serialization. A query is a function call into memory-mapped pages. For reads it is frequently faster than Postgres on the same hardware by an order of magnitude, because the fastest network request is the one that does not happen.

Applications whose data fits on one disk. Which is most applications. A terabyte NVMe drive costs very little and holds a large amount of business data. The number of applications that genuinely need distributed storage is much smaller than the number that use it.

Anything where operational simplicity matters. No server to run, patch, monitor, back up separately, or fail over. Your backup is a file copy. Your staging environment is cp prod.db staging.db. Your local development environment is identical to production.

Edge deployment. Ship the database with the application. This is why the edge-runtime ecosystem has converged on SQLite-derived systems.

the write concurrency question#

The historical objection: SQLite serializes writes. One writer at a time.

This is true, and with WAL mode it is far less limiting than it sounds. Readers do not block writers and writers do not block readers. Only writer-versus-writer contends.

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

Set those four. The defaults are conservative for embedded use and wrong for a server application.

A single writer on NVMe handles thousands of transactions per second if the transactions are short. The failure mode is long-running write transactions blocking others, which is a thing you should not do in any database.

Be honest about your actual write rate. Most applications that think they are write-heavy are doing a few hundred writes per second at peak, which SQLite handles without noticing.

when it is the wrong choice#

• Multiple application servers needing write access to the same data. This is the real limit. The replication ecosystem (Litestream, LiteFS, rqlite, and the various hosted variants) addresses parts of it with real trade-offs, but if you genuinely need multi-writer, use Postgres.
• Very large datasets with complex analytical queries. The query planner is good and it is not Postgres's, and you do not get parallel query execution.
• Rich types and extensions. No native JSON operators as capable as Postgres's, no PostGIS, no pgvector. There are extensions; they are not the same ecosystem.
• Fine-grained access control. SQLite's security model is filesystem permissions. If you need row-level security, you need something else.

the deployment pattern that works#

Application and database on the same machine. Vertical scaling — modern hardware is very large. WAL mode. Continuous replication to object storage for durability. Read replicas if you need geographic distribution.

This handles a genuinely enormous amount of traffic. Not every application, and far more than the conventional architecture assumes.

the actual argument#

The default architecture for a web application — separate database server, connection pooling, network round trips, replication topology, a managed service bill — solves problems that most applications do not have, and it costs complexity that every application pays.

Start with the simplest thing that could work. For a very large class of applications, that is a single machine with a file on it, and you can move later if you need to.

Most never need to. The ones that do will have revenue by then.