Perhaps this author shouldn't publish their advice about databases. I fully accept that people don't know everything, and I fully support them exploring new things and falling on their faces (if you don't, you aren't really trying). But I object to them offering advice at the same time: the following is from the followup article:<p><a href="https://www.hendrik-erz.de/post/should-you-use-sqlite" rel="nofollow">https://www.hendrik-erz.de/post/should-you-use-sqlite</a><p><i>The first argument [in the prior article] that is completely bogus is about the speed: I argued that, if you’re not careful, the access times of the database will be actually slower than to simply use the file system.<p>Now, obviously there is a large flaw in this argument if you know anything about databases. Specifically, I never thought about the option to just create additional indices for columns I frequently addressed.</i><p>!
Not sure why this got posted when the follow-up claws back much of it and leaves a pretty weak conclusion: <a href="https://www.hendrik-erz.de/post/should-you-use-sqlite" rel="nofollow">https://www.hendrik-erz.de/post/should-you-use-sqlite</a>
> Do you know how large the biggest hard disks or SSDs are nowadays? [...] no single disk will have 281 Terabytes of space. And that is what you need: Since SQLite files are single, continuous files on your file system, they have to be stored on one, physical hard drive. You could theoretically split them up, but then they wouldn’t work anymore until you pieced them together again.<p>> So the practical limit of the size of an SQLite file is much lower than the theoretical limit.<p>I agree with the conclusion, but the argument is horrible. 281 TB are easily doable on a single file system (not hard drive, which is entirely irrelevant) is very much doable. You can actually build a rather cheap consumer system that will do that. It won't be good, but the problem is not the continuous allocation of space.
SQLite works great when used correctly. E.g. if you split metadata and blobs into different tables, it'll be super fast even without any indexes. Real life example:
<a href="https://news.ycombinator.com/item?id=39793805">https://news.ycombinator.com/item?id=39793805</a>
Pack archive format, which is magic byte to distinguish as separate format + sqlite database + zstd compression for data chunks. Inside there are no indexes, three tables - file info (like name), with proper parent_id logic, data chunks and relationship between these two.
Article is from 2021, an update to the article published 2022:<p><i>In this article I explain where and why I was wrong, and share the real reasons why I think we shouldn't use SQLite for research: A lack of skills and time.</i><p><a href="https://www.hendrik-erz.de/post/should-you-use-sqlite" rel="nofollow">https://www.hendrik-erz.de/post/should-you-use-sqlite</a>
And this is why you keep the academics away from industrial systems. Dentists curse: because they're so knowledgeable about their own area, they un-self-critically transfer that confidence clean outside of their own domain, and then having a nice engineering conversation becomes impossible as their ego becomes involved in the opinion.
SQLite is one of those things that I hear people on hear advocating a lot but have never personally encountered anyone using other than embedded iOS/Android work. I’m talking businesses with 8 or 9 digit revenue, not side projects with 1000 users. I’m talking about teams in the hundreds of developers, not single person projects. I’m talking many services as dependencies, not just one. I’m talking extensive and robust redundancy, monitoring and observability. The list goes on.<p>There’s a reason for that.<p>- single writer even with WAL<p>- missing plenty of alter table functions<p>Those alone discount it for serious work. Yes, there are workarounds, but why workaround when you can work with Postgres or others that don’t require all of the hassle?<p>What’s amusing as well as the zealotry around it too. People getting worked up about pointing out obvious flaws. Sad. When did tech become religion?<p>FWIW SQLite is great for embedded applications, and it’s where it belongs.
This article makes two main arguments, both of which are (sorry to be blunt) just plain dumb.<p>The first point is that the claim that "SQLite can support up to 281TB in a single database" is wrong, because in practice you can't get a single disk that big, and therefore SQLite is a bad choice for storing 16GB of data.<p>The second point is that without indexing, retrieving individual data items is very inefficient. Therefore a big distributed MySQL cluster (which supports indexing) is better than a single SQLite database (which also supports indexing).<p>Most of the rest of the text only serves to beat around the bush and distract from how nonsensical the core arguments are.
This podcast with SQLite's Richard Hipp was fascinating as I didn't know the backstory and deeper history:<p><a href="https://corecursive.com/066-sqlite-with-richard-hipp/" rel="nofollow">https://corecursive.com/066-sqlite-with-richard-hipp/</a>
Horrific confession time: I have several .sqlite files that exceed 1.5TB in size and consist of a single table with a single field called JSON (guess what it contains!).<p>Early days, I just used 'JSON LIKE '%json-substring-match%' for queries, and that <i>did</i> get a bit slow after a while, but mostly the syntax was just really gross when doing aggregations. Fortunately, these days, you can do:<p><pre><code> CREATE INDEX IDX_Foo_Bar ON Foo (json_extract(JSON, '$.Bar'));
</code></pre>
...which makes subsequent SELECT queries on that function nice and quick again.<p>This has been going on since (checks create time on one .sqlite file) July 2018, with pretty much zero perf or downtime issues.
You also shouldn’t use sticky headers that take quarter of a mobile screen and do nothing apart from screaming h1 in your face. Couldn’t finish neither article because of that.
Didn't even tried to run `create index` or to learn how to use a database system first, let alone design a schema. Just went straight to "nope, this is why SQLite is bad".<p>I don't think querying 8 GB of data without an index is going to be efficient in MySQL either.<p>(Disclaimer: SQLite fan here, but I read this article with close attention because I'm always interested in knowing SQLite pain points. The conclusion was a slap of just nothing)
TLDR: Guy notices that queries on unindexed columns are slow on a relatively large (8GB) SQLite database. Rather than fix his database design by adding additional indices, guy concludes that SQLite is bad and you shouldn't use SQLite.