This problem gets substantially worse in geographically distributed databases, like Spanner or CockroachDB, where joining tables whose data is stored in different localities can incur a significant latency penalty (in the hundreds of milliseconds).
They both offer an interesting solution to the problem called interleaved tables. CockroachDB docs on the subject are here [0], and Spanner's docs are here [1].
Interleaved tables don't fix the fact that SQL will hand you back a flat table when you have hierarchical data, but they do fix the spatial locality problem. Here's an example, from the CockroachDB docs, of how users and their order data can be interleaved, exactly the way it sounds like you've wanted:
/customers/1
/customers/1/orders/1000
/customers/1/orders/1002
/customers/2
/customers/2/orders/1001
/customers/2/orders/1003
[0]: https://www.cockroachlabs.com/docs/stable/interleave-in-pare...[1]: https://cloud.google.com/spanner/docs/schema-and-data-model#...
Can you expand on this? I'm not sure I follow.
The only dataset that I have worked with where SQL limitations hit hard and fast was a 3D fMRI timeseries dataset, the type of data which isn't meant to be queried using SQL in the first place. Sure there's also certain graph-based datasets which you wouldn't want to use SQL but this is by far the exception and not the rule. You'd be surprised how many datasets can easily be ETL'ed and stored relationally, and even nested tree structures are generally nothing a few SQL views can't fix.
As for your "affects performance" claim - this has been made efficient for DBs like Redshift with compound/interleaved SORTKEYs and all/key DISTKEYs. And other modern warehouses like BigQuery, Snowflake, etc have their own solutions.
I think SQL is just hierarchical enough, not unlike the porridge in the Goldilocks fairy tale.
https://cloud.google.com/spanner/docs/schema-and-data-model
https://www.cockroachlabs.com/docs/stable/interleave-in-pare...
suddenly I have to store all items in a single DB row and read the whole thing to access any of them, giving me a new source of IO inefficiency
what if the target table is keyed by date and I'm selecting a small subset? JSONB will still force the DB to interact with the entire JSONB blob.
But generally speaking, at some point, sometimes you have to do the less ergonomic thing in the name of performance. I'm just of a mind to postpone that until it's worth the tradeoff. Often, you can architect in such a way that such a change won't cascade through the rest of your system.
SQL should offer some native way to do this
It affects performance too -- SQL wants storage locality by table, but applications often want locality by user.
App-level permission / roles is something that everyone runs into with a DB-backed app of a certain size, and it has always felt to me like better hierarchy design could help -- not just foreign keys but 'settings table is under users table', you have to query it via users.