SQLite is very limited because of its threading model, imo it's not usable outside of the single app model where you have a single user.
That number will obviously depend on the read/write ratio of any given website; but it's hard to imagine any website where [EDIT the number maximum number of concurrent users] is actually "1". And for many, that will be in the thousands or hundreds of thousands.
FWIW the webapp I use to help organize my community's conference has almost 0 cpu utilization with 50 users. Using sqlite rather than a separate database greatly simplifies administration and deployment.
I can imagine a static website where the content is read-only for users and is only editable by admins/developers/content managers through some CMS.
Suppose, on the other hand, that a single user generated around a 1% write utilization when they were actively using the website (which still seems pretty high to me). You could probably go up to 120 concurrent users quite easily. And given that not all of your users are going to be online at exactly the same time, you could probably handle 500 or 1000 total users.
Of course applications like a blog will have far more reads than writes. It just really varies depending on the type of application.
Remember, the person I was replying to claimed SQLite was "not useable outside of the model... where you have a single user". Yes, if you need 100k concurrent users doing a 1ms transaction every second, SQLite isn't for you. But 1000 concurrent users is a lot more than 1.
Although on reflection, what they may have meant for "single user" is a single process (perhaps with multiple threads). That sounds reasonable to me: people just don't realize how much you can actually do with a single multi-threaded process running on a modern server.
Only if your readers and writers are cleanly segregated.
Most languages and web frameworks don't have SQLite drivers out of box (or have extremely bad ones). Unlike SQLite, most databases don't really care about distinction between read-only and writable connections. So there is a good chance, that you will always open writable connection by default, because this is what your framework/ORM does. Furthermore, seemingly read-only web middleware often ends up writing to database on each request for one reason or another. If you try to reuse/pool connections (which is also important under high load), you need to be wary of keeping open writable connections in cache — again, something that does not matter to all major databases other than SQLite.
I was involved in maintenance of a small web app (db size < 5 Mb), written in Django, that had to serve ~1000 dynamic requests per second (the contents of each response were dependent on IP address of caller). The app worked with PostgreSQL, albeit poorly, but immediately ground to halt under load with SQLite — which was our default database choice for historical reason.
We ended up briefly caching results of most database queries in memory, which removed most of load from database (we also did a lot of other optimizations, but this was the decisive one). Eventually the app was able to withstand up to 9000 requests per second, but none of that was an achievement of SQLite — we just evaded database, Django and Python altogether on majority of requests.
While we are on this topic, the most widespread OS in the world, Android, also has extremely low-quality SQLite drivers — despite shipping SQLite as default database for many years. Android has a broken-by-design Cursor implementation (the devs admitted it themselves [1]), that always tries to count query results, even if you don't call getCount(). And a broken connection cache, that does not support read-only connections [2] (that method used to have a "TODO", but eventually they forgot, why they wanted it, so they removed it).
1: https://medium.com/androiddevelopers/large-database-queries-...
2: https://android.googlesource.com/platform/frameworks/base/+/...
Also, for your small app were you using WAL mode? Multiple readers only works in WAL journaling mode.
Our Django setup needs multiple processes to work around the Grand Interpreter Lock. Disabling connection reuse in Django config slightly changed the behavior we observed, but didn't solve the performance problem.
Note that WAL and synchronous flags must be set appropriately. Out of the box and using the standard "one connection per query" meme will handicap you to <10k inserts per second even on the fastest hardware.
The trick for extracting performance from SQLite is to use a single connection object for all operations, and to serialize transactions using your application's logic rather than depending on the database to do this for you.
The whole point of an embedded database is that the application should have exclusive control over it, so you don't have to worry about the kinds of things that SQL Server needs to worry about.
SQLite is not a direct replacement for SQL Server, but with enough effort it can theoretically handle even more traffic in your traditional one-database-per-app setup, because it's not worrying about multiple users, replication, et. al.
It's not the right approach because it's hard to get right, you want to offload that to the DB.
Also, the only reason we ever want to lock a SQLiteConnection is to obtain a consistent LastInsertRowId. With the latest changes to SQLite, we don't even have to do this anymore as we can return the value as part of a single invocation.
Thats... amazing! What is your setup like?
SQLite is perfectly capable of supporting multiple, parallel reads.
SQLite must serialize writes, which makes a highly parallel write-heavy workload not good for it. However, with WAL enabled writes do not block reads.
Basically highly-parallel read loads with low write counts (low-enough that serializing them doesn't lead to unacceptable slow down of writes) or with loads where latency is acceptable in writes (but not reads) is a perfect use case for SQLite. And it turns out that a lot of web services are heavily asymmetrically biased towards reads.
And that's assuming you're making queries every time an event occurs versus persisting data at particular points in time.
Edit: Sometimes you have to lie and lead people down the wrong path to enlightenment... ;)
;)
Nobody said you have to use a single database/file. Obviously, you are going to want to spend a couple minutes thinking about referential integrity. But how often do you delete records in your web app?
If your deployment environment has serious constraints, I'm sure you could make it work. The product you deliver would be SQLite + custom DBI layer to hide SQLite's limitations.
It would be a lot more work, and not be as robust or scalable, compared to a more traditional selection. But I can imagine cases where it would be appropriate.
Per user.
While reads are more like once every day per user.
All of those connections might need to write, and this is where SQLite gets tricky to implement at scale.
I love SQLite! It's perfect for many use cases, but not all. Fortunately, Postgres is also excellent.