But that's the thing: All that is nice on paper. Of course the relational nature is nice. Of course the disk representation is nice. But I never feel like that's worth the trouble day in day out. The costs, the mystery box of performance, the insane statements, the extra hardware, the updates, the strange data types, the box of tricks everyone needs to make them behave. But I've been made aware that our usecases suck, so I'll attribute most to that.

The problems are not specific to SQL though - if you want to store and manage huge amounts of data you're going to need some specialized data structures, some way to manage disks paging and memory, manage consistency, isolation, durability, and atomicity of your changes.

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.

It's more than a PITA, it's almost impossible to implement ACID correctly. It's very very hard to do. There almost no point in implementing that in house when we have sqlite3.