SQLite Is All You Need
Source Entity
Hacker News
A technical experiment demonstrates that SQLite, utilizing STRICT tables and WAL mode, can power a social network of 50,000 users. The study found a single laptop could handle 315 million daily requests, suggesting SQLite is sufficient for most production needs.
Challenging the Database Status Quo: The Case for SQLite
In modern software engineering, there is a prevailing tendency toward "infrastructure over-engineering," where developers reflexively deploy heavy, client-server database systems like PostgreSQL or MySQL even for small-to-medium applications. The report "SQLite Is All You Need" challenges this paradigm by rigorously testing the limits of SQLite in a production-like environment. By building a functional social network, the experiment seeks to determine if a serverless, file-based database can handle the demands of a real-world user base without the overhead of complex containerized orchestration.
The Architecture of the Experiment
To test the viability of SQLite, the researchers constructed a social network featuring 50,000 users and one million posts, all contained within a single 343MB file. A critical component of this setup was the implementation of STRICT tables, following the recommendations of Evan Hahn. Historically, SQLite has been known for its flexible (and sometimes problematic) dynamic typing; however, STRICT tables enforce rigid data types, bringing SQLite closer to the reliability and predictability of traditional production databases. This architectural choice ensures that the "real, typed, production data" remains consistent, reducing the likelihood of runtime errors common in loosely typed systems.
Performance Breakthroughs via WAL Mode
One of the most striking revelations of the study is the sheer throughput achieved using a Node API on a standard laptop. The heaviest page in the application managed to serve 315 million requests per day. This level of performance is largely attributed to the use of Write-Ahead Logging (WAL) mode. In standard rollback journal mode, SQLite locks the entire database during writes, which creates a bottleneck. WAL mode, however, allows multiple readers to coexist with a single writer, significantly increasing concurrency and throughput. This demonstrates that for the vast majority of read-heavy applications, the perceived need for a dedicated database server is often a misconception rather than a technical requirement.
Simplifying the Deployment Stack
The experiment highlights a profound shift in operational complexity. The "one file, one process" model eliminates the need for the "Postgres container" typically required in modern cloud deployments. By removing the network layer between the application code and the database, the system eliminates network latency and the risk of connection pool exhaustion. This simplification not only reduces the CPU and RAM overhead on the host machine but also streamlines the backup and migration process, as the entire state of the application is encapsulated in a single portable file.
Understanding the Ceiling and Future Trends
While the results are flattering, the analysis acknowledges that there are "places it falls over." SQLite is inherently limited by its single-writer architecture; while WAL mode improves concurrency, it cannot compete with the distributed write capabilities of a cluster-based system. However, the trend in the industry is shifting toward "boring technology" and "local-first" software. As hardware continues to improve, the threshold at which a developer must migrate from SQLite to a client-server database is moving higher. This experiment suggests that many developers are migrating to complex systems far too early in their growth cycle.
Conclusion
The findings provide a compelling argument for the sufficiency of SQLite for the vast majority of web applications. By leveraging STRICT tables for data integrity and WAL mode for high-concurrency performance, developers can support tens of thousands of users and millions of records on modest hardware. The study serves as a reminder that architectural simplicity is often a feature, not a limitation, and that the right tool for the job is frequently the one that is already available and easiest to manage.