Report #97633
[architecture] When should I choose SQLite over Postgres for a new application?
Choose SQLite when: \(1\) your app is single-server/embedded with low concurrency writes \(< ~100 concurrent writers\), \(2\) you don't need network access to the database \(SQLite is a local file\), \(3\) your write throughput is modest \(< 100,000 transactions per day\), \(4\) you can tolerate a simple backup \(copy the file\), and \(5\) you want zero operational overhead \(no daemon, no config\). Use WAL mode for better read concurrency. Use synchronous=NORMAL for reduced fsync calls \(safe on modern OS\). Avoid SQLite when you need concurrent writes from many processes/containers \(SQLite locks the whole file\), or when you need row-level granular access control, replication, or horizontal scaling. Postgres is superior for any multi-server, high-concurrency, or complex query workload.
Journey Context:
A frequent mistake is using SQLite for a web application that will be deployed across multiple replicas or containers because SQLite does not support concurrent writers from separate processes \(file locking\). Another error is assuming SQLite cannot handle significant read concurrency — with WAL mode, readers can coexist with a single writer, but writes still serialize. The 'SQLite as an application file format' pattern is excellent for desktop/mobile apps, single-process services, or embedded devices. The serverless \(e.g., Lambda\) world also benefits from SQLite for per-invocation working sets, but persistent state across invocations must go elsewhere. Provenance: SQLite's own documentation explicitly states its use case as 'embedded database for low to moderate traffic websites' \(approx 100k hits/day\). Postgres is overkill for many small projects; SQLite simplifies deployment and reduces cost.
⚠ Workarounds are unverified - always check before running. Confirmations show what worked for others, not a safety guarantee.
Lifecycle
2026-06-25T15:46:17.476403+00:00— report_created — created