Loading...
Loading...
We reach for Postgres by default. Sometimes that is overkill.
There is a version of software development where you spin up a Postgres container before you write a single line of application code. I have done this many times. It feels responsible.
But for a lot of apps, SQLite works fine, and the operational simplicity is real.
SQLite is a file. Your database is a single .db file sitting next to your application. There is no connection pool to configure, no separate process to keep running, no port to expose. Backups are cp myapp.db myapp.db.bak.
For read-heavy workloads, SQLite can handle a surprising amount of traffic. The SQLite documentation itself says "appropriate for most websites." That is not marketing copy, it is a technical claim backed by how reads work: each read acquires a shared lock, so many readers can run concurrently.
The write story is more complicated. SQLite uses a single writer at a time, which means write-heavy applications with many concurrent users will hit contention. In WAL mode (which you should enable), reads and writes do not block each other, but you still get one writer.
Full-text search in Postgres is much more complete. tsvector, GIN indexes, dictionary-based stemming, ranked results. SQLite has FTS5, which is decent, but Postgres covers more cases.
JSON support in Postgres has matured well. If your schema is heavily denormalized or document-like, jsonb columns with indexing are quite capable. SQLite can store JSON but the query support is more limited.
And obviously: if you need multiple application servers hitting the same database, SQLite does not work. A file database cannot be shared over a network (well, not safely).
I start with SQLite for anything that runs as a single instance: personal tools, side projects, internal tools with modest usage, prototypes. I reach for Postgres when I need horizontal scaling, heavy writes from concurrent clients, or advanced query features.
A lot of production apps that are running Postgres would be fine on SQLite. The switch is usually not worth making once you are already deployed, but if you are starting something new and you do not have a clear reason to need Postgres yet, SQLite is worth considering.