Is SQLite good enough (sometimes)?

Published on March 12, 2023

Back in 2017, an article entitled “Is PostgreSQL good enough?” was published and made the point that PostgreSQL was indeed capable of handling most of the tech needs of a web app project. At the time, we were working on the first version of Vegan Realm and we were looking for a search solution that was better and faster than doing SQL queries with the LIKE operator. PostgreSQL was enough for us, and we’ve used it in several projects since then.

But could SQLite be good enough? We shared how we moved from AWS to Linode and gave tips on how to start small to avoid unnecessary fees. SQLite was at the center of our strategy and so far, it proved to be good enough for our needs. 

Calendarium and the website you are currently reading were easy to support. They need a SQL database to persist data and SQLite has all the SQL features needed to power a web application. The only thing it’s not good at is sharing the database between multiple servers. We don’t need multiple servers right now and we are confident that we can grow a lot while keeping our current infrastructure. The SQLite website has a good writeup about when to use and not to use SQLite. The “website” section says:

SQLite works great as the database engine for most low to medium traffic websites (which is to say, most websites). The amount of web traffic that SQLite can handle depends on how heavily the website uses its database. Generally speaking, any site that gets fewer than 100K hits/day should work fine with SQLite. The 100K hits/day figure is a conservative estimate, not a hard upper bound. SQLite has been demonstrated to work with 10 times that amount of traffic.

From Appropriate Uses For SQLite, on SQLite.org

When we revamped Vegan Realm recently, we weren’t convinced that it was a good candidate for SQLite. We wanted the search to be fast and the ranking to be precise. PostgreSQL full-text search capabilities had served us well in that regard. We read about the SQLite FTS5 Extension which is the full-text search implementation of SQLite and decided to experiment with it. Our use case was a perfect fit once more and we went live with SQLite has the driver of our little search engine.

As we write this, we are planning our next web application. Once again, we will start with SQLite has the data persisting layer. We think SQLite is good enough, at least when you start something new. It removes a layer of complexity and opens options for cheap hosting solutions.