JSON file on disk might be a reasonable competitor here. This scales poorly, but sometimes you don't need to scale up to something as crazy as full-blown SQLite.
I would also say that the second you need any sort of writing or mutating in any sort of production environment SQLite is also simpler than the file system!
Race conditions in file system operations are easy to hit, and files have a lot more moving pieces (directory entries, filenames, file descriptors, advisory locks, permissions, the read/write API, etc) than a simple SQL schema
Whenever I see “full-blown” next to “SQLite” there is usually some sort of misconception. It’s often simpler and more reliable than the next best option!
I've also found that ripgrep performs surprisingly well with folders containing large numbers of text files - this makes raw json on disk more convenient for many simple usecases which don't require complex queries.
Imagining a moderately large JSON object, a lot of them actually, not hard to read or write exactly but they do carry some common references.
SQLite can: only rewrite the changed fields with a query, and transact the update across the objects. The filesystem can do atomic swaps of each file, usually, if nothing goes wrong. SQLite would also be faster in this case, but I'm more motivated by the ACID guarantees.
SQLite does increase complexity in the sense that it's a new dependency. But as far as dependencies go, SQLite has bindings in almost all languages. IMO even for simple usecases, it's worth using it from the start. Refactors are easier down the line than with a bespoke JSON/yaml/flat encoding.
Read only configuration, sure, JSON (etc) file makes sense. The second you start writing to it, probably not.
And here's what SQLite does to ensure that in case of application or computer crash database contains either new or old data, but not their mix or some other garbage: https://sqlite.org/atomiccommit.html
1. We knew what we were doing with respect to fsync+rename to guarantee transactions. I bet you the vast majority of people who go to do that won’t (SQLite abstract you from needing to understand that)
2. We ended up switching to SQLite anyway and that was a painful migration.
Just pick SQLite to manage data and bypass headaches. There’s a huge ecosystem of SQL-based tools that can help you manage your growth too (eg if you need to migrate from SQLite to Postgres or something)
Much less so since the introduction of WAL mode 12 years ago, tho.
However much of the lore around writes remains from the “rwlock” mode where writers would block not just other writers but other readers as well.
That would kill your system at very low write throughputs, since it would stop the system entirely for however long it took for the write transaction to complete.
If the system is concurrent-writes-heavy it remains an issue (since they’ll be serialised)
https://stackoverflow.com/questions/35804884/sqlite-concurre... has some pretty interesting numbers in it. Particularly the difference between using WAL mode vs not. The other aspect to consider is that the concurrent writes limitations apply per database file. Presumably having a database file per tenant improves aggregate concurrent write throughput across tenants? Though at the end of the day the raw disk itself will become a bottleneck, and at least the sqlite client library I'm using does state that it can interact with many different database files concurrently, but that it does have limitations to around ~150 database files. Again, depending on the architecture you can queue up writes so they aren't happening concurrently. I'd be very interested to know given all the possible tools/techniques you could throw at it just how far you could truly push it and would that be woefully underpowered for a typical OLTP system, or would it absolutely blow people's minds at how much it could actually handle?
When it comes to SQLite performance claims I tend to see two categories of people that make quite different claims. One tends to be people who used it without knowing its quirks, got terrible performance out of it as a result, and then just wrote it off after that. The other tends to be people who for some reason or other learned the proper incantations required to make it perform at it's maximum throughput and were generally satisfied with its performance. The latter group appear to be people who initially made most of the same mistakes as the former, but simply stuck it out long enough to figure it out like this guy: https://www.youtube.com/watch?v=j7WnQhwBwqA&ab_channel=Xamar...
I'm building an app using SQLite at the moment, but am not quite up to the point in the process where I've got the time to spent swapping it out for Postgres and then benchmarking the two, but I dare say I will, as I have a hard time trusting a lot of the claims people make about SQLite vs Postgres and I'm mainly doing it as a learning exercise.
Also, if the SQL that's needed in an application is more "enterprise" style, then SQLite isn't there (yet). ;)
- More than one machine needs to read from the database
- More than one machine needs to write to the database
- The database is too large to hold on one machine
If not, some jobs will benefit from a better perf insurance policy than "I could probably get this into a profiler if I needed to."
It works fine for single user applications.
SQLite is my go-to database for application configuration and even non-enterprise web applications.
The sqlite library is tiny. Often smaller than some JSON parsers!
Stripped of all comments, the current "amalgamation" build of sqlite (which is the one most projects use) has 178k lines and 4.7MB of code. If your JSON parsers are anywhere near 1/10th of that size then they're seriously overengineered ;).
The tags I invented were essentially highly-optimized key-value stores that worked really well. I then discovered that these same KV stores could also be used to form relational tables (a columnar store). It could be used to store both highly structured data or the more semi-structured data found in things like Json documents where every value could be an array. Queries were lightning fast and it could perform analytic operations while still performing transactions at a high speed.
My hobby turned into a massive project as I figured out how to import/export CSV, Json, Json lines, XML, etc. files to/from tables easily and quickly. It still has a lot of work to go before it is a 'full-blown' database, but it is now a great tool for cleaning and analyzing some pretty big tables (tested to 2500 columns and 200M rows).
It is in open beta at https://didgets.com/download and there are a bunch of short videos that show some of the things it can do on my youtube channel: https://www.youtube.com/channel/UC-L1oTcH0ocMXShifCt4JQQ