You have to be careful though, as SQLite doesn't support concurrent read/writes, so it blocks, which makes it rather difficult to scale up for websites, or at least it has in my experience with it.
I also always wondered why sqlite isn't used more in websites. Especially if you split heavy write workloads to a separate database file it scales quite far.
SQLite isn't single threaded - and it can support multiple readers very well.
The limitation with SQLite is that it doesn't support concurrent writes well - it needs to take a lock on the entire database to perform a write.
Writes are crazy fast (a few ms) so this often isn't a problem - but it does mean you wouldn't want to use it to build a site that has many people writing at once, like Hacker News for example.
For a site that has low (or no) writes, SQLite works really well even at a much larger scale - 100s of requests a second.
SQLite scales very well for reading data, actually. If your site is single-write:many-read (most are) it works brilliantly.
If you however cause writes when people open pages, look at content. Anything from hit counters to tracking behaviour. Then SQLite suddenly starts to scale pretty badly.
The same goes with how your processing works. Multiple processes that may all at some point cause writes? This is bad. Threads inside a process? That works.
I specifically cited concurrent writes. SQLite is fantastic for read-only data. Those websites are using SQLite for what it's designed for. But, if they were trying to use SQLite to do even dozens of writes per second, it would crash and burn.
SQLite is one of the world's most popular databases in production (I heard it's the most deployed database in the world, but I'm not entirely convinced) so it surely can't be awful at least..
For webapps, it's a mixed bag. I've run a few sites with low 6 figure pageviews per month (i.e. as big as probably 90% of all sites) on it without problems but in situations with high levels of concurrent writes, it's classically been advised against. If your app or framework of choice likes to timestamp every database record it touches or logs heavily to the database, be careful ^1.
(^1 - SQLite 3.7, which I've not played with yet, supports a write-ahead log mode which means writers and readers don't block each other. This could resolve a lot of potential performance snafus.)
It can handle a lot of reads, but you'll have problems with multiple concurrent writes (at least with Python). I run read-heavy production websites on SQLite just fine, though.
SQLite is absolutely fantastic, but I’ve twice used on sites where I would have used a real DB and twice needed to switch back to a real DB after a few months.
The concurrency model just doesn’t match up well with multiple web processes doing work.
If you are writing on every request, Murphy’s law says that too many of those 500 visitors will be loading pages at exactly the same time...
I have a few low volume sites backed by SQLite, but perhaps not in the manner you're getting at. Mine are all sites that have a process to insert/update or read the data, then generate static html from it, so I don't need to deal with multiple simultaneous connections to it.
SQLite works just fine for website backends. Writes are serialized, first from replicas to the central write leader (like in Postgres), and then with the WAL and transaction isolation.
TBH, it'll work for a lot of multi-process apps, so long as the app runs on a single server and the app doesn't do anything foolish like write to the database on every page hit. For read-mostly applications -- like a blog, wiki, or even a small web forum -- SQLite is a surprisingly adequate database.
I use SQLite in small and medium-traffic web apps (the largest one getting about 80000 hits daily) all the time with no concurrency issues whatsoever. My rule of thumb is that if you don't expect your app to require more than one server, sqlite will work just as fine as postgres or mysql.
Should be fine for reads. Sqlite.org is dynamic, pulling from sqlite data for ~20% of the pages, and it does fine with HN piling on. The single threaded would be an issue for writes, but I don't see why they would be doing writes for a page view. See https://www.sqlite.org/whentouse.html
The idea that SQLite shouldn't be use for web applications is about a decade out-of-date at this point.
But... actual guidance as to how to use it there is still pretty thin on the ground!
Short version: use WAL mode (which is not the default). Only send writes from a single process (maybe via a queue). Run your own load tests before you go live. Don't use it if you're going to want to horizontally scale to handle more than 1,000 requests/second or so (though vertically scaling will probably work really well).
I'd love to see more useful written material about this. I hope to provide more myself at some point.
SQLite is not a real solution for anything needing to support more than one user at a time. It's fine for things like managing bookmarks within firefox, but not for a full relational database replacement. The write access is single threaded (at least last time I used it).
Is anyone using SQLite for server/client web apps?
SQLite was not designed for the server/client model. However, the simplicity and speed of SQLite makes it appealing for small-to-medium dynamic websites.
True client/server databases like PostgreSQL bring with them advanced capabilities but also complexity in installation and maintenance.
Below are some extracts are from the SQLite FAQ: https://sqlite.org/whentouse.html
---
Websites
...The SQLite website uses SQLite itself, of course, and as of this writing (2015) it handles about 400K to 500K HTTP requests per day, about 15-20% of which are dynamic pages touching the database. Dynamic content uses about 200 SQL statements per webpage. This setup runs on a single VM that shares a physical server with 23 others and yet still keeps the load average below 0.1 most of the time.
Server-side database
...Developers report that SQLite is often faster than a client/server SQL database engine in this scenario. Database requests are serialized by the server, so concurrency is not an issue. Concurrency is also improved by "database sharding": using separate database files for different subdomains. For example, the server might have a separate SQLite database for each user, so that the server can handle hundreds or thousands of simultaneous connections, but each SQLite database is only used by one connection.
---
So, can SQLite work with dynamic sites that require writing to the database for many users at the same time?
- Has anyone had success doing this? How did you achieve it?
- Conversely, did anyone ditch SQLite and adopt a different client/server database e.g. PostgreSQL, MySQL etc?
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.
A toy that can serve the vast majority of DB use cases. I get it, you can't build a massive-scale project with SQLite, but that doesn't exactly make it a toy DB...
Every time I've used sqlite for any kind of throwaway web app, I've regretted it due to the almost complete lack of support for concurrent operations. Sqlite is for file formats, not anything that might have concurrent writes.
reply