SQLite Is All You Need

14 days ago

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.

Hasty Briefsbeta

backend development

We put a Redis server inside our runtime

SQLite Is All You Need