I never stop being impressed at how often people will jump to odd, unsupportable, conclusions like, "using MySQL will make this thing faster".<p>I've seen it so many times over the years regarding users and email configurations. I can't count the number of times I've dropped into someone's badly behaving mail configuration and found they had MySQL hosting the users, and explained it was for "performance" reasons. Somehow they didn't grasp that /etc/passwd fits entirely in memory, and the map files Postfix uses for various lookups and stuff are already a database (just one specifically designed for the task at hand) and also fit entirely in memory. Putting that MySQL layer in there is <i>disastrous</i> if performance matters; it is orders of magnitude slower for any case I've seen...still plenty fast for most cases, but it's ridiculous that this idea gets cargo-culted around that if you store your mail users in MySQL your mail server will be faster.<p>A little knowledge is a dangerous thing, is what I'm trying to say, and people who know MySQL is "fast" may not know enough to know that it's not the right tool for the job in a lot of cases...and is probably <i>slower</i> for many use cases. I'm pretty confident this is one of those cases. SQLite is wicked fast on small data sets, and being smaller means more of it will fit in memory; I can't think of any way MySQL could be a more performant choice for this workload.<p>Also, I don't even want to try to imagine shipping an installable desktop application for non-technical users that relies on MySQL!
Wow, the article is such a fresh breath of air, primarily because the author demonstrates common sense.<p>He (they?) picked SQLite for all the correct reasons:<p>- best tool for the job for their situation;<p>- write-light and read-heavy;<p>- zero configuration;<p>- easy to embed;<p>- understanding that optimizing queries by far gives the best performance in the shortest amount of time.<p>As an aside, I'm currently using SQLite for Bacula and Postfix, and it's a joy to use; the only drawback I found so far is lack of REGEXP REPLACE in the SQL dialect which the database supports (must be loaded with .load /path/to/lib/libpcre.so, but it is not part of the language). I used the Oracle RDBMS for my PowerDNS deployments, but in retrospect, the way PowerDNS works, SQLite would have been an even better match. All in all, it is great to read that someone picked it for all the correct reasons, rather than some fashion trend, as is often the case in computer industry.
Premature optimization is evil, but <i>preemptive</i> optimization is necessary unless you want to paint yourself into a corner. I realized this after implementing a bitcoin full node.<p>In my bitcoin implementation, as an experiment, I tried storing the blockchain in sqlite, postgres, and leveldb. I gathered up a bunch of data from the first ~200k blocks of the blockchain and benchmarked all three databases. I queried for something like 30,000 utxos out of a set of a couple million. What took 300-400ms in leveldb took 1.6 seconds in postgres (on the repl. in my actual node it would have taken longer due to deserialization of the utxos). What took 1.6 seconds in postgres took over <i>30 seconds</i> in SQlite.<p>Now, you can tell me I did the benchmarks wrong, and "oh, if you just did this it would be faster!", but 30+ seconds is slower to an absolutely insane level. Needless to say, I went the key-value store route, but I was still astounded at how slow sqlite was once it got a few million records in the database.<p>I actually like sqlite, but when you know you're going to be dealing with 70gb of data and over 10 million records, preemptive optimization is the key. If I were the author, I would consider switching to postgres if there are over 500k-1m records to be expected. That being said, if they're partial to sqlite, SQLightning (<a href="https://github.com/LMDB/sqlightning" rel="nofollow">https://github.com/LMDB/sqlightning</a>) looks pretty interesting (SQLite with an LMDB backend).<p>edit: To clarify, these weren't particularly scientific benchmarks. This was me timing a very specific query to get an idea of the level of data management I was up against. Don't take my word for it.
While we're speaking of SQLite; one thing that has little exposure that could probably use more is that it now ships with Windows as a system DLL:<p><a href="https://engineering.microsoft.com/2015/10/29/sqlite-in-windows-10/" rel="nofollow">https://engineering.microsoft.com/2015/10/29/sqlite-in-windo...</a><p>Between that, and packages readily available on most Linux and BSD distros out there (and, in most cases, installed by default), it's well on its way to become a de facto standard system API for relational storage.
If to speak about desktop applications then any embedded DB will be unbeatable.<p>So I am speaking about embeddable DBs here.<p>Konstantin Knizhnik have implemented impressive set of various embedded DBs: <a href="http://garret.ru/databases.html" rel="nofollow">http://garret.ru/databases.html</a><p>Like his POST++ has direct mapping to C++ classes so if you use C++ then you don't need any ORM.<p>In my Sciter[1] Engine I am using his DyBase library [3] as a bult-in persistence for Sciter's script [2] (JavaScript++).<p>With the DyBase in script you have features similar to MongoDB (noSQL free-form DB) but without any need for ORM and DAL - you can declare some root object as be persistable and access those data trees as if they are JavaScript objects. The engine pumps objects from DB into memory when they are needed:<p><pre><code> var storage = Storage.open(...);
var dataRoot = storage.root; // all things inside are persitable
dataRoot.topics = []; // flat persistable list
dataRoot.topics.push({ foo:1, bar:2 }); // storing object
/* create indexed collection with string keys, keys can be unique or not */
dataRoot.titles = storage.createIndex(#string);
</code></pre>
DyBase has Python bindings too.<p>[1] <a href="http://sciter.com" rel="nofollow">http://sciter.com</a> - multiplatform HTML/CSS UI Engine for Desktop and Mobile Application<p>[2] TIScript - <a href="http://www.codeproject.com/Articles/33662/TIScript-Language-A-Gentle-Extension-of-JavaScript" rel="nofollow">http://www.codeproject.com/Articles/33662/TIScript-Language-...</a><p>[3] DyBase - <a href="http://www.garret.ru/dybase.html" rel="nofollow">http://www.garret.ru/dybase.html</a>
I was unfamiliar with this project and assumed it was a hosted service at first. Not so, this is a local application, so an embedded database makes sense.<p>It took until the very last paragraph for the blog post to make that point.
SQLite also does remarkably well with recovering from all manner of power loss / crashes / worst case scenarios. We created a "power loss" rig just to test this facility for one particular system. Really SQLite's biggest weakness is concurrency, and if your app needs that in any serious amount you probably ought to look elsewhere. If you're just dealing with occasional concurrency though SQLite shouldn't be dismissed out-of-hand.
For any database that isn't huge, a library embedded into your application is going to be faster than anything that has to communicate with a server over a socket connection. Though both execute SQL queries, SQLite is completely different than relational database servers and appropriate many places where running a full RDBMS is not. For example, you can't run MySQL or Postgres on the iPhone, but you can use SQLite.
> we’re read-heavy and write-light<p>> we have almost no indices, no principled denormalization<p>Sounds like an easy win. People are probably suggesting a database switch because they're finding issues with the current speed, but they're not using their current database to its full potential yet.
Sqlite is fine for small scale systems. It is not a "web scale" database, but not every web site is "web scale."
SQLite does have performance limits, and will break at certain load, but until that, it's okay.
For single user databases, like desktop applications, SQLite is awesome!
What the others bring to the table is concurrent sever performanc, user management, and such.
There's nothing surprising about this, right?
I'm a long time user and lover of SQLite, since way back when. Use it in a lot of our projects (web and Win32) that require local databases for logging etc.<p>Sure for larger or concurrent user access to a db, we use other databases, but nothing beats the 'zero footprint' install of SQLite. I even enjoyed the fact that earlier versions of SQLite forced me to think optimise my queries due to the lack of nested SELECTs.<p>SQLite still kind of reminds me of my early days in MySQL. I was recently trying to downgrade MySQL 5.7 to 5.6 after a slew of issues, which forced me to reminisce about how simple things used to be when MySQL was a lot like SQLite still is now...
Beet is an awesome program, you should really check it out if you still are among the minority of people who actually have a music collection and don't rent access from spotify/itunes/etc.<p>I'm glad to see this post; one of the reasons that I like beet so much is that everything is self-contained.
You're doing it right for your application! MySQL or PostgreSQL would most probably be slower and introduce a lot more overhead as they are client/server oriented systems. Don't listen to those armchair architects!
I don't get the point of this article. SQLite is fine, especially in an embedded database, but once you have concurrent access, it starts to suffer because it has very coarse grained locks, so a "real" database is better for a distributed single-DB design. It's more about using the right tool for the job, and the author seems to be talking himself out of some kind of guilt for SQLite being the right tool for him.
I can't blame them. I've been a huge fan of SQLite for years. Anytime I need storage it's my default choice unless there is a specific reason to use something else.<p>Another nice advantage of it is if you are distributing something that requires a small dataset[0][1]. If I give you both the code and the data already imported into a sqlite database, then you can use the code right away, or you can dump the data to a different database very easily.<p>[0] <a href="https://github.com/jedberg/wordgen" rel="nofollow">https://github.com/jedberg/wordgen</a><p>[1] <a href="https://github.com/jedberg/Postcodes" rel="nofollow">https://github.com/jedberg/Postcodes</a>
What people actually seriously suggest that a desktop application needs more than sqlite offers in the way of databases?<p>Desktop apps are like the sweet spot for sqlite. It's practically made for them.
I have used SQLite for similar use cases, but occasionally it's led to a corrupted db. I had a cron task writing to it once a day, but an issue with the scheduler led to 2 tasks one day with the latter one finishing before the former.<p>Of course I can add locking or something in my code, but I'd prefer to handle at a lower level — for example, have SQLite take the latest write without corrupting. I'm hoping someone has solved this problem with SQLite elegantly.
Some people are unfamiliar with the phrase "right tool for the job".<p>As the developers behind the project, I'd have to think the authors are in the best position to make the determination about which tool is appropriate.
For everyone loving SQLite, you should consider donating to them. I remember a post this last year about the maintainers working on it full time, but making much less than most of us probably do.
SQLite is Ok, but write access must be synchronized.
I used it for my Flask (Python) application and was forced to switch to PostgreSQL because of synchronization problems. I would prefer sticking with SQLite which was simpler to manage.<p>The author doesn't say a word about synchronization when writing to SQLite.
Classic HN bait.<p>You don't even need to read the comments to know what people will say:<p>"SQLite is a great fit for this type of application. It's a replacement for fopen people. fopen."<p>"What about ALTER TABLE?"<p>"It's just a toy database, it doesn't even support concurrent writers"<p>----- "WAL mode"<p>"Hey, golang, rqlite"<p>----- "Whoa I wrote something similar for a ..."<p>----- "Why would you use this? Just use postgres"<p>"SQLite is the best database ever"<p>"SQLite is the worst database ever"
I have written several "Use Sqlite!" posts that have made the rounds on hackernews... reading this watered down post, which is devoid of any new, surprising or usable info, it strikes me that repping SQLite has achieved meme status.<p>If you want tangible info you can actually use, read sqlites documentation. There's a wealth of information there.<p>Here are some of posts, for the Python crowd:<p><a href="http://charlesleifer.com/blog/five-reasons-you-should-use-sqlite-in-2016/" rel="nofollow">http://charlesleifer.com/blog/five-reasons-you-should-use-sq...</a><p><a href="http://charlesleifer.com/blog/using-the-sqlite-json1-and-fts5-extensions-with-python/" rel="nofollow">http://charlesleifer.com/blog/using-the-sqlite-json1-and-fts...</a><p><a href="http://charlesleifer.com/blog/my-list-of-python-and-sqlite-resources/" rel="nofollow">http://charlesleifer.com/blog/my-list-of-python-and-sqlite-r...</a>