News for hackers. Stuff that matters.
Re:Towards Multiverse Databases by awinter-py on Monday June 17, 2019 @12:15PM (#20204466)
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.

Re:Towards Multiverse Databases by benesch on Monday June 17, 2019 @02:50PM (#20206147)
> 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-pare...

[1]: https://cloud.google.com/spanner/docs/schema-and-data-model#...

Re:Towards Multiverse Databases by oarabbus_ on Monday June 17, 2019 @02:01PM (#20205691)
>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.

Re:Towards Multiverse Databases by skybrian on Monday June 17, 2019 @03:15PM (#20206366)
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://www.cockroachlabs.com/docs/stable/interleave-in-pare...

Re:Towards Multiverse Databases by acjohnson55 on Monday June 17, 2019 @12:49PM (#20204849)
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.
Re:Towards Multiverse Databases by awinter-py on Monday June 17, 2019 @05:30PM (#20207575)
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.

Re:Towards Multiverse Databases by acjohnson55 on Monday June 17, 2019 @08:11PM (#20208597)
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.

Re:Towards Multiverse Databases by combatentropy on Tuesday June 18, 2019 @01:38AM (#20210063)
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.
Re:Towards Multiverse Databases by sebazzz on Monday June 17, 2019 @02:53PM (#20206182)
I don't entirely agree. What I want is hierarchical views/indices for certain queries. How the object graph looks differs with each query.
Re:Towards Multiverse Databases by awinter-py on Monday June 17, 2019 @05:27PM (#20207553)
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