5 ms·
> SQLite is extremely reliable and durable for large amounts of data (up to 140TB). It is considered one of the most well-engineered and well-tested software so
by johnisgood 7y ago
> SQLite is extremely reliable and durable for large amounts of data (up to 140TB). It is considered one of the most well-engineered and well-tested software solutions today, with 711x more test code than implementation code.
I keep seeing this statement. Why is it considered one of the most well-engineered software?
- hanche 7y agoA small (?) part of the answer is likely to be found in the massive amount of testing: https://sqlite.org/testing.html https://sqlite.org/testing.html
- tempguy9999 7y agohttps://sqlite.org/about.html https://sqlite.org/about.html The bit about testing.
- platz 7y agoI would like not to have to deal with SQLITE_BUSY errors for once. It even throws when trying to obtain a connection. It got so bad I had to put a mutex around obtaining a sqlite connection.
- breakingcups 7y agoIt's possible you are using it wrong.
- firebacon 7y agoThe less flippant explanation is that SQLite can only handle a single writer at any time and when you try to access it with two concurrent writers (or a concurrent reader and writer in some modes) it will by default return a "BUSY" error instead of blocking. So, if you're were getting unexpected "BUSY" erorrs than, yes, you would be using it incorrectly. However, AIUI, you are always expected to see some amount of BUSY errors during normal, concurrent operation and have to deal with them explicitly. So the fact that you're seeing BUSY errors alone doesn't mean you're doing anything wrong... To use SQLite correctly from multiple processes, you have to do one of two things: - Add explicit code to retry on BUSY errors everywhere you do SQL queries - Serialize all access the database, e.g. by using a mutex GP appears to have chosen the second option.
- PudgePacket 7y agoNot a direct answer but it's certainly the most used, which correlates highly with getting a lot of dev attention :) https://sqlite.org/mostdeployed.html https://sqlite.org/mostdeployed.html
- firebacon 7y agoThat completely depends on the chosen metric/definition though. For example, you could consider the most used database the one that handles the most queries per day. Not so clear who is the winner now. The claim that SQLite is one of the 5 most widely deployed pieces of software worldwide is completely unsubstantiated and most likely untrue once you count in things like ICU, the linux kernel, etc.
- sigzero 7y agoAre you deliberately misreading the linked page? Most Widely Deployed and Used Database Engine That last part is rather important.
- firebacon 7y agoNo, I'm referring to the second section. > MOST WIDELY DEPLOYED SOFTWARE MODULE OF ANY KIND? -- SQLite is probably one of the top five most deployed software modules of any description. Other libraries with similar reach include: zlib... blah blah blah It stops just short of claiming it's the most deployed software in the world...
- typon 7y agoBarring an actual study with data, I don't think their claim is that far fetched.
- quietbritishjim 7y ago> ICU, the linux kernel, etc. It seems unlikely that either of these are more widely deployed than SQLite, especially the Linux kernel. Probably the vast majority of deployments of the Linux kernel include SQLite. Add in all the non-Linux devices (e.g. the billions of iOS and Windows devices) and that's probably far more than the number of Linux installations without SQLite. I don't know much about ICU, and perhaps you're right that it's one of the most used bits of software. But I can certainly imagine embedded devices that never communicate with users and wouldn't need ICU but do need to store data.
- firebacon 7y agoAbout the well-tested bit: Per it's own documentation, SQLite has a massive test suite. [*Not all of] the test suite is actually open source though, so the overlap between commenters selling you on how well tested SQLite is and those that have seen how the sausage is made is probably [close to] zero. However, pointing this or any of the other practical shortcomings of SQLite out on hacker news is blasphemy and will invariably get you downvoted into oblivion by people who (apparently) never ran into them.
- ritchiea 7y agoWhy are portions of the test suite closed source?
- firebacon 7y agoThey charge money for it... Note that I'm not saying all of the test suite should be open source. It's clearly a valid/cool business model and SQLite is, for a lot of use cases, an excellent piece of software that I have often used myself. The criticism was honestly more directed towards the cherry-picking of facts that sometimes happens in discussions on hacker news, especially those related to SQLite.
- mytherin 7y agoPart of the SQLite test cases are open source (the logic tests), but indeed unfortunately the biggest part are not. You can purchase a license for these tests though, so saying nobody has seen them is likely incorrect. Either way, the fact that it is DO-178B certified for use on aircraft should tell you something about the reliability of it. It is certainly more reliable than most other software, OSS or otherwise. I am curious what practical problems you encountered with SQLite. Could you perhaps expand on that?
- smush 7y agoWhat are some other shortcomings of SQLite? I'm genuinely curious even though the below probably sounds like some text from the ~Rust~ Sqlite evangelism strike force. With the default settings which I semi-affectionately refer to as paranoid mode, an untuned database can start to have worse performance after getting 500,000+ records going. Then things like indexes, RediSQL, and WAL mode start being more necessary rather than just best-practices. But if you set your pragmas correctly and so on, SQLite scales up just fine. I haven't done a large scale Sqlite base simply due to caution around needing to use a 'real' database in production like SQL server or Postgres, Maria etc. Sqlite is excellent for ephemeral databases to be created, seeded with test data, run tests against, and deleted in repeatable automated testing. Based on the link to the Expensify article, it sounds like Sqlite can scale up even better than Sql server under some circumstances. But I have barely tried using it in production because of that aforementioned caution. What are some pitfalls to watch out for?
- mkj 7y agoIt's well thought through and commented. Read for example the commented design decisions of https://github.com/sqlite/sqlite/blob/master/src/pager.c https://github.com/sqlite/sqlite/blob/master/src/pager.c or os_unix.c
- patelajay285 7y agoHi, I'm the author of this library. This is a pretty common opinion, the SQLite code base is rigorously well tested for performance and correctness. See: https://www.sqlite.org/testing.html https://www.sqlite.org/testing.html https://www.sqlite.org/hirely.html https://www.sqlite.org/hirely.html