For most of my career I treated SQLite as a database for prototypes — something you'd use to sketch out a schema before "doing it for real" with Postgres. Last year I started using SQLite for actual side projects and never went back. It turns out the gap between "toy" and "production" with SQLite is smaller than I thought, and the operational benefits are significant when you're the only person on the team.
The mental shift
I'd absorbed the idea that real applications need a "real database" — one running as a separate process, with users and connection pools and replication. That's true for some applications. It's not true for most of mine.
My side projects are read-heavy, single-writer, and serve under a hundred users at peak. They run on a $5/month VPS. The "database server" question is a category error: I don't have a server farm. I have a Linux box with a disk. SQLite is a library that talks to a file on that disk. For my actual workload, that's not a downgrade — it's the right shape.
What changed the calculation
Three things, in roughly the order I learned them.
First, WAL mode. Out of the box SQLite uses a rollback journal that locks the database during writes. Switch to write-ahead logging with PRAGMA journal_mode = WAL and the database can have one writer concurrent with many readers. For a typical web app, that removes the lock-contention problem people associate with SQLite. It's one line of setup. I always set it.
Second, the integrity story. SQLite has the most paranoid test suite of any open-source database I've used. The codebase is famously thorough about edge cases. Files can be backed up by copying them. Atomic commits work even if you yank the power cord. The database is one file, which makes restores trivial — copy the file back. After years of dealing with Postgres pg_dump-and-restore drama for hobby projects, this is genuinely freeing.
Third, Litestream. This is the piece that closed the gap for me. Litestream replicates the SQLite WAL to S3 (or any S3-compatible storage) in real time. If my server dies, my data is in object storage. If I want a hot standby, I can stream-restore on a second box and swap DNS. The replication story I always assumed I needed Postgres for, I now have for SQLite, on a $5 server, with one config file.
What I gave up
Concurrent writers. SQLite serializes writes. For my workloads it doesn't matter — even at the upper end of my traffic, I'm doing dozens of writes per second, not thousands. If I were building an app where many users wrote heavily at once, this would matter.
Some Postgres-isms. JSONB indexing in Postgres is excellent. SQLite has JSON functions and you can index expressions, but the ergonomics are worse. Full-text search exists in SQLite (FTS5 is great) but isn't quite the same as Postgres's tsvector. The list of features Postgres has that SQLite doesn't is long, but for most of my apps I don't actually use them.
Network access. There's no SQLite server. If I want a different machine to query my database, I have to put an HTTP layer in front of it. For side projects this has never been a problem; the application and the database are on the same machine by design.
The deployment story
This is where SQLite becomes hard to argue with. My typical setup now:
- Deploy a Go or Node binary to a VPS.
- The binary opens a SQLite file in WAL mode.
- Litestream runs as a sidecar, streaming the WAL to S3.
- That's the whole infrastructure.
No managed database service. No connection pooler. No PgBouncer config. No "did I remember to vacuum." Backups are continuous and offsite by default. If the box dies, I spin up a new one and Litestream restores from S3. The full disaster recovery playbook is one paragraph long.
For projects where I expect my AWS bill to matter, the savings are real. A small managed Postgres on most clouds is fifteen to thirty dollars a month minimum. SQLite is free. S3 storage for the backups is pennies.
When I still pick Postgres
Anything with multiple writer processes that need to scale independently. Anything where the data needs to live somewhere other than the application server. Anything where I might end up with a team and want a queryable database that ops people are familiar with. Anything with a serious reliance on Postgres-specific features — extensions like PostGIS, advanced JSON ops, or sophisticated full-text needs.
For work projects, I almost always pick Postgres. The decision tree there is different — there's a team, there's an ops function, there's a production tier where SQLite's operational simplicity becomes someone else's operational fragility.
But for the projects where I am the team and the ops department and the on-call rotation, SQLite has earned its place as the default. And honestly, it's been pretty pleasant to drop the cargo cult of "you need Postgres for that" and just use the simpler thing.