Because people find it useful, it seems its easy to forget it was called SQLite for a good reason.
The original purpose was to have lite embeddable sql for apps where it simply did not make sense to have to set up an additional piece of infrastructure (eg mysql). E.g. as a store for app config, as an "app file format", or even for lite qtys of relational data where setting up a mysql would be too much of a hassle.
Thinking SQLite should compete with eg MySQL on large data sets, complex queries, high concurrency etc (ie all the cases where one would normally expect to set up a separate database server), to me just seems way outside of SQLite's intended purpose.
As to HDD crunching; I venture if SQLite is used for the domains it is intended for (eg as app file format), this should not be an issue if developers think about how they're using it.
E.g. if one is in the habit of doing a tmp-write, fsync, atomic rename for every atomic change an app would usually make to its files, then one would expect significant HDD crunching anyway if one is doing this with extreme granularity.
Ie with SQLite used as app backing store, the question should be what the extent of a transaction is, before one calls commit()
For these intended domains, I'm not convinced that in-memory pre-caching for the sake of batching/deferring writes is the way to do it at all, but rather to think in terms of the commit() corresponding to the point at which the app otherwise would want to atomically update its file.
However, from what I've seen - even while you do a transaction, SQLite seems to write a "journal" file (which is still I/O and still slow).
I'm also not saying mySQL should be used for app file format (that would be pretty dumb), but rather challenging the original article's premise that SQLite (as mp3) is one-fits-all solution. There are many solutions for many situations and SQLite is far from being universal solution.
I don't think anyone is saying that it is one size fits all. To extend the MP3 analogy, SQLite data is very portable, and serverless. MySQL is a server (in my experience), so you need to have that process always running, more analogous to a streaming audio server. I think that makes SQLite a very different kind of beast, more like a file format than a database server.
The original purpose was to have lite embeddable sql for apps where it simply did not make sense to have to set up an additional piece of infrastructure (eg mysql). E.g. as a store for app config, as an "app file format", or even for lite qtys of relational data where setting up a mysql would be too much of a hassle.
Thinking SQLite should compete with eg MySQL on large data sets, complex queries, high concurrency etc (ie all the cases where one would normally expect to set up a separate database server), to me just seems way outside of SQLite's intended purpose.
As to HDD crunching; I venture if SQLite is used for the domains it is intended for (eg as app file format), this should not be an issue if developers think about how they're using it.
E.g. if one is in the habit of doing a tmp-write, fsync, atomic rename for every atomic change an app would usually make to its files, then one would expect significant HDD crunching anyway if one is doing this with extreme granularity.
Ie with SQLite used as app backing store, the question should be what the extent of a transaction is, before one calls commit()
For these intended domains, I'm not convinced that in-memory pre-caching for the sake of batching/deferring writes is the way to do it at all, but rather to think in terms of the commit() corresponding to the point at which the app otherwise would want to atomically update its file.