I've always been surprised WordPress didn't go with SQLite though - it'd have made deployment so much easier for 99% of users running a small, simple blog.
Most these sites are running a homepage, an about page, a contact page, and maybe one or two misc pages. They might have a blog that has two blog posts on it from nine years ago. But that is really it. MySQL is really overkill considering the scenario. SQLite's big "limitation" is non-concurrent writes. But this is rarely a problem with most Wordpress sites because they are single-author and they aren't updated very often. SQLite can handle plenty of reads to support even heavily trafficked websites.
Not to mention, SQLite's greatest advantage is portability. A single file contains your entire database. A non-technical user could transfer hosts or backup their data by copying their database file like it was a photo or an excel document. That's pretty incredible when you think about it.
I was a casual developer (i.e. not for work, just for personal) deep into the PicoCMS ecosystem for a couple years, a few years ago. I both started a site and helped a family convert an old static site to PicoCMS and really had no complaints. Re: frontend for the owner, I started with Pico Admin and made a bunch of modifications to it (including an image uploader) and the non-tech owner has no complaints and it's been working well since.
Nowadays for my own blog I'm into the whole SSG/JAM trend, but I'd still run a PicoCMS site any time, if the use case is right.
Perhaps not great for production since Wordpress automatically updates itself, and you would have to keep up with any changes. And not just for wordpress, but for any other plugins that use the database.
Edit: A single file fork (albeit 5k lines of PHP) of the plugin that looks interesting: https://github.com/aaemnnosttv/wp-sqlite-db
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.
And that's assuming you're making queries every time an event occurs versus persisting data at particular points in time.
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.
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.
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?
If my goal is building a website, I don't necessarily want to experiment with different technologies if I already know Postgres will work perfectly fine without adding much operational overhead and covering use cases I don't have yet, vs the unknown unknowns of using SQLite and maintaining it over time. Again, this is not about some problem with SQLite, but just me not having experience using it this way. Same reason why I wouldn't just add any database system I haven't used before, even if on paper it would be the "better tool for the job" for a particular use case, and sure would be an interesting learning experience.
In my opinion practicality and prior experience often beats what is strictly necessary or "best".
But not wanting to get out of your comfort zone is a completely valid stance to take. We do this for money after all.
E.g. I got interested SvelteJS exactly because I got jaded of how ridiculously complex many front end applications have gotten, albeit they are doing just barely more than fetching a JSON from a server and turning it into html. Then I got a really good use case for using it in production because the low end mobile phones and bad internet connections of our users were struggling with the very heavy SPA the company started off with.
For another project in the future, it might well be SQLite that is the more experimental part, but in the end it comes down to managing risks and benefits.
While one part of me would love to experiment with everything all the time, the other part likes to finish the work day on time to be able to have plenty time dedicated to non tech related things and that sleeps well at night being fairly sure that stuff is running smoothly