SQLite Is All You Need
a day ago
- #Performance
- #SQLite
- #Backend Development
- SQLite with WAL mode can handle a social network of 50,000 users, 1 million posts, and millions of interactions in a single 343MB file.
- The heaviest query, a timeline join with counts, served 3,654 requests per second on a laptop, equivalent to 315 million requests per day.
- WAL mode eliminates SQLITE_BUSY errors and allows concurrent reads and writes, unlike the older rollback journal which causes significant slowdowns.
- Limitations include: reads scale poorly with concurrent writes due to cache invalidation, only one global writer, no built-in failover, and it's unsuitable for high-write contention or extensive analytics.
- On a cheap VPS, a single core can still serve over 110 million timeline requests per day, making SQLite feasible for most applications.
- Recommendations: use STRICT tables for type safety, prioritize single-core speed and enough RAM for the database, avoid network storage, and consider dedicated cores for better p99 latency.
- Development benefits include: local database as a file, easy testing with isolated databases, simple deployments, and minimal operational overhead.
- Backups are straightforward file copies, and tools like Litestream enable continuous replication.
- Bun with bun:sqlite performed better on lightweight endpoints but worse on heavy queries compared to Node with better-sqlite3, emphasizing the need to measure specific workloads.
- The article argues that premature scaling with complex databases like Postgres is often unnecessary; SQLite suffices for most projects until clear scaling signals emerge.