5 ms·

SQL isn't hierarchical enough. Everyone who has tried to interpret a join result has experienced this: the DB is trying to pack a tree into a table and somethin

SQL isn't hierarchical enough. Everyone who has tried to interpret a join result has experienced this: the DB is trying to pack a tree into a table and something is lost in translation. 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.

acjohnson55 7y ago
I agree that there are times when I want to natively represent hierarchy, instead of having to think relationally. But you can get around this by storing hierarchical data in your tables in most RDBMSs, like JSONB in Postgres.
- awinter-py 7y ago
  I love JSONB but I don't expect it to perform well for unbounded things 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.
  - acjohnson55 7y ago
    In Postgres, JSONB can be indexed in some pretty useful ways. 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.
  - combatentropy 7y ago
    I use PostgreSQL and JSONB a lot, but JSONB is not the type of any column in any of my tables, only in views and functions.
oarabbus_ 7y ago
>SQL isn't hierarchical enough. Everyone who has tried to interpret a join result has experienced this: the DB is trying to pack a tree into a table and something is lost in translation. 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.
benesch 7y ago
> It affects performance too -- SQL wants storage locality by table, but applications often want locality by user. 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-parent.html https://www.cockroachlabs.com/docs/stable/interleave-in-pare... [1]: https://cloud.google.com/spanner/docs/schema-and-data-model#creating_a_hierarchy_of_interleaved_tables https://cloud.google.com/spanner/docs/schema-and-data-model#...
sebazzz 7y ago
I don't entirely agree. What I want is hierarchical views/indices for certain queries. How the object graph looks differs with each query.
- awinter-py 7y ago
  yes, agree, it's query-dependent, but ORMs have been unpacking joins into sublists for years SQL should offer some native way to do this
skybrian 7y ago
FYI, some SQL databases let you physically interleave rows from different tables, which would let you put all the data for a user in the same place. For example: https://cloud.google.com/spanner/docs/schema-and-data-model https://cloud.google.com/spanner/docs/schema-and-data-model https://www.cockroachlabs.com/docs/stable/interleave-in-parent.html https://www.cockroachlabs.com/docs/stable/interleave-in-pare...

acjohnson55 7y ago

I agree that there are times when I want to natively represent hierarchy, instead of having to think relationally. But you can get around this by storing hierarchical data in your tables in most RDBMSs, like JSONB in Postgres.

awinter-py 7y ago
I love JSONB but I don't expect it to perform well for unbounded things 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.
- acjohnson55 7y ago
  In Postgres, JSONB can be indexed in some pretty useful ways. 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.
- combatentropy 7y ago
  I use PostgreSQL and JSONB a lot, but JSONB is not the type of any column in any of my tables, only in views and functions.

oarabbus_ 7y ago

>SQL isn't hierarchical enough. Everyone who has tried to interpret a join result has experienced this: the DB is trying to pack a tree into a table and something is lost in translation. 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.

benesch 7y ago

> It affects performance too -- SQL wants storage locality by table, but applications often want locality by user. 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-parent.html https://www.cockroachlabs.com/docs/stable/interleave-in-pare... [1]: https://cloud.google.com/spanner/docs/schema-and-data-model#creating_a_hierarchy_of_interleaved_tables https://cloud.google.com/spanner/docs/schema-and-data-model#...

sebazzz 7y ago

I don't entirely agree. What I want is hierarchical views/indices for certain queries. How the object graph looks differs with each query.

awinter-py 7y ago
yes, agree, it's query-dependent, but ORMs have been unpacking joins into sublists for years SQL should offer some native way to do this

skybrian 7y ago

FYI, some SQL databases let you physically interleave rows from different tables, which would let you put all the data for a user in the same place. For example: https://cloud.google.com/spanner/docs/schema-and-data-model https://cloud.google.com/spanner/docs/schema-and-data-model https://www.cockroachlabs.com/docs/stable/interleave-in-parent.html https://www.cockroachlabs.com/docs/stable/interleave-in-pare...