It was a good write up. I've used SQLite to store small molecule structures for several research projects and it has never let me down.
One item I had hoped to read, which often is mentioned on SQLite reviews, occurs in the area of "When would SQLite not be a good choice?", specifically: "very large amount of traffic... Very large data-sets." I have always hoped for some (even wild) estimates of when that 'very large-ness' occurs. because in my hands, 17 million small molecule (inchi) structures don't even cause it to break a sweat. Will i hit a wall some day?
You are a long way from the practical limits. I have used SQLite databases with billions of rows that were still able to retrieve hundreds of arbitrary rows in less than second. We used it to store time-series performance data and generate charts. We had tens of thousands of these databases (not all with so many rows) and they performed their job admirably.
The one thing that SQLite cannot handle performantly is deleting large numbers of rows (millions) - so don't plan on deleting any data from your tables once they get that big. It appeared to me from a cursory examination of the code that B-tree rebalancing was happening after deleting each row which makes the big deletes very expensive. We got around this problem by sharding our data into a new table and a new database for each week and then mounting all of the databases necessary for a query. When we wanted to delete data we just deleted the database file with the corresponding shard. Obviously that only worked for our particular time series data.
Anyway, the bottom line is that SQLite is more scalable and has better performance than people give it credit for.
> An SQLite database is limited in size to 140 terabytes (247 bytes, 128 tibibytes). And even if it could handle larger databases, SQLite stores the entire database in a single disk file and many filesystems limit the maximum size of files to something less than this. So if you are contemplating databases of this magnitude, you would do well to consider using a client/server database engine that spreads its content across multiple disk files, and perhaps across multiple volumes.
Very large amount of traffic:
> SQLite usually will work great as the database engine for low to medium traffic websites (which is to say, 99.9% of all websites). The amount of web traffic that SQLite can handle depends, of course, 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.
As a read-only database, sqlite doesn't have too many limits, since the storage mechanism is flexible enough to allow fast queries. The problem comes when you start writing, and reads have to wait for them.
Also, (I believe) it was this talk http://www.youtube.com/watch?v=ZvmMzI0X7fE that mentions sqlite being used in adobe lightroom, and it being faster to access thumbnails from an sqlite database.
Thank you so much for the comment, I'm glad to hear you've had good luck with SQLite in production. I switched all my personal stuff over to it a month or two ago, haven't looked back.
