I believe you haven't had a chance to work on problems that require an actual database. Multi-user access, ACID support, unified API (odbc/jdbc), common query language... all of these would require many man-years to set properly with a custom solution.
> the update disasters, the incompatibility between different SQL engines etc all make it seem like an awful idea
What update disasters? If you meant by updating database versions, these aren't things you do frequently because the database is expected to be running 24/7. But Postgres and Mysql are already rock solid here. Wrt SQL engine incompatibilities, you usually set with a single database vendor in practice. If you suddenly start to switch databases in the middle of the project, something needs to be fixed with the process design, not database.
But the updating I would have expected to go more smoothly. If you make a point of using a software dedicated to managing data, I sure as hell would expect an update to go so smooth that I don't have to worry about or even notice it. In reality updates more often than not seem to come with undocumented errors. That is a constant source of frustration for me.
You are using it :) Reboot the server where the database runs or suddenly cut off the connection. Unless you have ACID-compatible storage, you'll have malformed data.
Plan for the future and use a database from the start. When your project/company expands and starts to use multiple applications/services (and that inevitably happens), you'll see (one of) the benefits of the database.
> But the updating I would have expected to go more smoothly
I'm not sure what you are talking about. Database updates are one of the smoothest (critical) software updates you'll find, assuming the database has a good track record.
You mention further down a "mystery box of performance", but if you understand what data structures it's using and how it uses them, then it's generally pretty straightforward. Mostly you can reason about what indices are available and how trees work (and e.g. whether it can walk two trees side-by-side to join data) to know what query plan it should make, and you can ask it to tell you what plan it makes and which indices it used. Likewise, if you have a query plan you want it to run (loop over this table, then use this column to look up in this table, etc.), you'll know what indices are needed to support that plan.
If people struggle with using a database correctly, they're really going to struggle with using something like a b-tree in a way where you don't corrupt your data in the event of a power loss or crash, or in a way where multiple threads don't clobber each other's updates or create weird in-between states (or you just use a global lock, but then you lose performance).
If you learn about the internals of a thing, especially when your background is in lower level dev like C++, then the use-cases are more obvious: you use the thing whenever you would've done what it does internally, but it gives you that functionality off-the-shelf and wrapped up in a way where you can write business logic without getting bogged down in details of tree-traversal and stuff. Once you get comfortable with it, you expand that to using it when you might not have done things exactly that way, but eh it's close enough and lower effort.
Sometimes truly equivalent SQL statements will be faster just because the optimizer is not perfect. e.g. I've had cases where I had a templated query with a GROUP BY some id, and then other code added on a HAVING for that same id, and I know it should be algebraically valid to push the HAVING into a WHERE so it runs before the GROUP BY (and filtering before the aggregation would be much cheaper), but mysql just didn't have that optimization. Dealing with this kind of thing can be annoying.
Other times you might have something like a compound index, and you might add a WHERE that you know for business reasons is redundant because the thing you're trying to filter on is not the first column in the index. Understanding why that works comes down to understanding what a compound index "looks like" as a tree. One thing that I imagine a database from the future could do is let you define logical implications like that (e.g. StateOrProvince = California implies Country = United States, or maybe deleted_at >= modified_at >= created_at, or a.id > b.id implies a.created_at > b.created_at) that it could use for query planning.
But in general, if you learn how it works, and then think of it as a way to not have to write that functionality yourself (but understand that the trade-off is some rigidity in your ability to customize it), it will make more sense, and you'll be able to become one of those wizards that just knows how to rewrite a query to something that ought to be the same, but is for some reason much faster.
I would happily work on query engines, it's just I don't recommend using bespoke ones unless absolutely necessary.
I think you'll come around eventually.
Because having hundreds or thousands of concurrent file handles across a data center is kind of hard.
Still then, a database will improve your data access, validate your data, and provide interop in case you need to use the file with different software.
It's also a very high quality serialization library, that replaces one of the most vulnerability-enabling layers of your software. (But then, I've just read you use Oracle, so maybe forget that one.)
Only a fool would put more trust in Oracle.
Is actually even more than that, has 100% MC/DC coverage [1], which is something that is not easily achievable for most projects.
[1] https://www.sqlite.org/testing.html#:~:text=In%20this%20way%....
If you don’t mind a bit of unsolicited advice: run.
Another key problem they solve is separating the logical representation from the on disk representation. You can evolve and and optimize storage without breaking anything.
The other problem with files is that you have a disconnect between in memory and on disk. You have to constantly serialize and deserialize. Sqlite has quite a bit of info on this: https://www.sqlite.org/appfileformat.html
The general idea is that its such a PITA to deal with all that so just let a database do it, and of course the consequence is an often leaky abstraction because computers suck.
For example, when making a bank transfer, the database guarantees that the update doesn't happen twice by accident.
You have to be really good at understanding how storage works, have a lot of time and resources to develop your own program to beat something like PostgreSQL. They have zillions of man-hours on you when you start, experience and knowledge. It's not impossible that you could find a case where a bespoke format would beat an established storage product, but over a range of cases, you most likely won't.
And, of course, there's a convenience aspect. Outside of niche technologies, SQL offers the richest language for data description and operations on data.
As for the inconsistencies between SQL implementations: in practice, it matters very little: most programs will never migrate between different SQL implementations anyways.
As for upgrades: it's a doubly-edged sword. You get a very expressive data format and it's not surprising that it's hard to upgrade. But, try to match the abilities of SQL in your own format, and you'll probably find out that it's hard to have generic tools for upgrading it too.
None of this means that you cannot do better than SQL databases. It'd be ridiculous to think there could be a way to prove that SQL is somehow the best we can get. It's just that it's very hard to do better. Especially if you want a universal tool
About data types, what limitations are you referring to? jsonb is well supported in many dbs and throwing random large binary blobs in the middle with your normal data is a bad idea with or without a relational database.
I can see the appeal to replace noSQL solutions though, a lot of people are using S3 (and other storage solutions) as a makeshift database lately
You can check these links:
I come from an industry that heavily uses custom binary file formats. And I'm still bewildered by the world of databases. They seem to solve many issues on the surface, but not really in pratice. The heavy limitations on data types, the update disasters, the incompatibility between different SQL engines etc all make it seem like an awful idea. I get the interop benefits, and maybe with extreme data volumes. But for anything else, what's the point? Genuinely asking