SQLite vs PostgreSQL: When to Use Which (I've Built Both)

Every few months a "SQLite is dead" or "you should use Postgres" post goes around. Both sides are right, and both are wrong. This is how I actually decide, as a developer who has run SQLite in production for years and PostgreSQL through managed services.

What SQLite is genuinely great at

SQLite is not a toy. It is the most deployed database engine on earth for a reason, and for a large class of web services it is the right choice.

• Zero operations. There is no server, no daemon, no connection pool tuning, no pg_ctl, no upgrade ceremony. The database is a file your app opens.
• Simple, reliable backups. In WAL mode you back up three files (.db, -wal, -shm) — or take a consistent snapshot with VACUUM INTO. Compare that to orchestrating pg_dump + WAL archiving.
• Good enough concurrency. With WAL mode and synchronous=NORMAL, a web service doing mostly-reads and modest writes handles a real workload just fine. I run services on this stack that never think about the database.
• Invisible in the stack. No separate process to monitor, secure, or fail over. For a small team or a solo developer, that is a feature, not a compromise.

Where SQLite genuinely struggles

Be honest about the failure modes before you pick it:

• Multiple concurrent writers. SQLite has a single writer at a time. Reads scale well; writes serialize. If your product has many writers (say, a collaborative editor or a high-volume ingestion pipeline), you will hit the wall.
• Horizontal scale. You cannot shard SQLite across machines the way you shard a client-server database. The file model is single-node by design.
• Some operations are just wrong tool for it. Heavy analytics over huge tables, JSON-at-scale, or anything that wants a replica on the other side of the planet will fight you.
• Backup discipline is on you. Because it is "just a file," people treat it like one — and silently lose WAL data or copy only half the files. The database is safe; the humans around it often are not.

The decision framework I actually use

I do not ask "which is better?" I ask five questions:

1. How many concurrent writers? More than a handful doing writes simultaneously → Postgres.
2. Will it ever need to run on more than one machine? Yes → Postgres. Not planned → SQLite stays.
3. What is the data size? If a SQLite file comfortably fits your data with headroom, it is fine. If you are thinking in hundreds of GB or analytical scans, reconsider.
4. Who has to operate it? One person who already knows the app? SQLite's zero-ops is priceless. A team with DB ops already in the budget? Postgres's tooling wins.
5. What does "down" mean to you? If a single-node outage is unacceptable and you need replicas/failover, that is Postgres territory by definition.

If the answers point to SQLite, stop feeling guilty. If they point to Postgres, use it through a managed service and never run it yourself if you can avoid it.

The honest middle ground

A lot of teams run SQLite as the engine inside a single service and add Postgres only when a specific query pattern demands it. That is a legitimate architecture, not a cop-out. Know what each engine is for, measure your actual workload, and ignore the tribal posts.

Before you build, estimate what your database will actually weigh on disk — the estimator below uses your row count, row size, and index count to give you a number instead of a guess.