Overview
Use SQLite when one process writes the database. That covers most internal tools, single-tenant apps deployed on a Fly or Hetzner box, mobile apps, and CLI state stores. Switch to Postgres the moment two processes need to write the same table, or the app is multi-tenant with cross-tenant queries. Litestream and libsql changed the calculus around durability; SQLite is now a serious production choice for a much wider set of apps. See sqlite for the SQLite rule set and postgres for Postgres.
When SQLite wins
SQLite is the right pick when concurrency is bounded, the data fits on one disk, and the deployment story benefits from one file.
- Single-writer apps: a Rails-style monolith on Fly with one machine, an Astro blog with a comments DB, an internal admin tool. One process, one writer, no contention.
- Local-first: desktop apps, CLI tools, mobile apps, and PWAs ship SQLite as the on-device store and sync upstream.
- Edge: libsql on Turso replicates a SQLite database to every region; reads are local, writes go to the primary.
- Operational cost: zero. No server, no port, no connection pool, no replication topology. The database is
app.db. - Backups with Litestream stream WAL frames to S3 or R2 every few seconds; recovery is
litestream restoreplus the latest snapshot. - Performance: 100k reads per second per core, 10k to 50k writes per second on a single writer. Faster than Postgres for many embedded workloads because there is no network hop.
- Schema migrations are one transaction; no replica lag, no logical replication choreography.
When Postgres wins
Postgres is the right pick when more than one process writes, the app is multi-tenant, or the workload needs features SQLite does not have.
- Multi-process writes: Postgres uses MVCC and row-level locks; SQLite serializes writers at the database level. Two workers writing the same SQLite file will queue.
- Concurrent connections: Postgres with pgbouncer handles thousands; SQLite is fine with tens, painful past a few hundred.
- Cross-host: any app running on more than one machine that writes to the database wants Postgres. SQLite over a network filesystem is a footgun.
- Feature surface: pgvector, PostGIS, partial indexes on expressions, materialized views, logical replication, full-text search with
tsvector. SQLite has FTS5 and JSON1 but the gap is real. - Strict types in older SQLite: pre-3.37 the type system was advisory; even with
STRICTtables today the type coercion rules are more permissive than Postgres. - Hot standby and read replicas: Postgres has mature streaming replication. SQLite uses Litestream or libsql, which serve different shapes of read scale.
Trade-offs at a glance
| Dimension | SQLite | Postgres |
|---|---|---|
| Writers | One per database | Many; row-level locking |
| Deploy unit | One file | Server, port, credentials |
| Hosting cost | Disk only | Managed service or VM |
| Replication | Litestream or libsql | Native streaming, logical |
| Concurrency | Excellent for reads, serialized writes | Excellent for both |
| Extensions | FTS5, JSON1, R-Tree, pgvector via libsql | pgvector, PostGIS, partman, TimescaleDB |
| Backups | cp app.db or Litestream | pg_dump, base backup, WAL archive |
| Connection pool | Not needed | pgbouncer at scale |
| Edge replication | Turso (libsql) replicates per region | Neon, Aurora with regional reads |
| Test runtime | In-memory :memory: in tests | Testcontainers or shared dev DB |
| Schema strictness | STRICT tables; still flexible | Strong by default |
Migration cost
Both directions are mechanical once the data model is clean.
- SQLite to Postgres: dump with
sqlite3 app.db .dump, transform withpgloaderor a hand-written script, and reapply. Most SQL ports directly; rewrite anyAUTOINCREMENTtoIDENTITYand anyINTEGER PRIMARY KEYdefaults to explicit types. Plan one engineer-week for a 50-table app plus another for connection handling and tests. - Postgres to SQLite: works for small apps that outgrew the operational cost of Postgres. Drop unsupported types (arrays, ranges, JSONB containment operators) or rewrite with JSON1. Plan one to two engineer-weeks plus a careful look at concurrency.
- Cheaper path: deploy SQLite with Litestream as the prototype. If concurrency becomes the bottleneck, migrate; the schema rewrite is small if you stuck to SQL standard features.
Recommendation
- New SaaS where the team owns the infrastructure: SQLite with Litestream on one box until a real concurrency limit shows up. Then Postgres.
- Single-tenant per-customer deployment (one DB per customer): SQLite. The footprint is unbeatable.
- Multi-tenant SaaS with cross-tenant queries: Postgres on day one; the migration off SQLite would be painful.
- Local-first app or mobile sync: SQLite. The only realistic answer.
- Anything that wants pgvector, PostGIS, or
tsvector: Postgres, unless you use libsql with pgvector compatibility. - Existing Postgres operational depth and no SQLite story: stay on Postgres; the cost saving is rarely worth the rewrite.
- See postgres-prod for production-grade Postgres operation.