10 ms·

SQL needed structure

148 points by todsacerdoti 1y ago

sgarland 1y ago
The only reason this post has any truth is that the IMDB dataset is horrendously unnormalized. It also points out at the end that you can in fact use a combination of aggregations to return the desired JSON object. Even then, if you really wanted to, you can absolutely make a tree-like structure in SQL in a variety of ways: adjacency lists, nested sets, closure tables, etc.
- mcphage 1y ago
  > you can absolutely make a tree-like structure in SQL in a variety of ways: adjacency lists, nested sets, closure tables, etc. That's kinda the problem—rather there being 1 way to make a tree-like structure in SQL, that works correctly, there's a lot of ways to do it, and they all have different tradeoffs, and they're all a bit obnoxious.
  - sgarland 1y ago
    There are multiple ways to do just about anything, in any programming language.
  - deepsun 1y ago
    And the article says that yes, you need various different looks at the same data. I would say UX is a beast, once a UX person tells me what they need SQL query comes up easily. Figuring out what they need is typically the hard part.
    - sgarland 1y ago
      It’s a lot easier if they tell you what they need when you’re designing the schemata. Unfortunately, IME this isn’t done, and so you wind up with tons of bolted-on attributes later, throwing any hope of normalization out the window.
    - mcphage 1y ago
      > once a UX person tells me what they need SQL query comes up easily. Figuring out what they need is typically the hard part. If they needed only one thing, sure. However, often UX needs to look at the same data in very different ways, and an efficient representation for one need might not be at all efficient for the next (which may not even be known as a need for several years).
bccdee 1y ago
My favourite way of doing this is with movieIds as (select id from Movie where title = $1), actorIds as (select Actor.id from Actor join ActorMovie on [...] where ActorMovie.Movie in movieId), alsoActedIn as (select id from ActorMovie where actor in actorId), movieResults as (select * from Movie where id in movieIds), actorResults as (select * from Actor where id in actorIds), alsoActedInResults as (select * from Movie join ActorMovie on [...] where ActorMovie.id in alsoActedIn) select * from movieResults full outer join actorResults on false full outer join alsoActedInResults on false; Not every database supports "full outer join on false," and sometimes you have to add "as not materialized" to the "with" subqueries in order for it to be performant, but it works in Postgres, and you end up with a results table that looks something like this: MovieId,MovieTitle,ActorId,ActorName,MovieId,MovieTitle,ActorMovie.actor 0,"Indiana Jones",null,null,null,null,null null,null,0,"Harrison Ford",null,null,null null,null,null,null,0,"Indiana Jones",0 null,null,null,null,1,"Star Wars",0 null,null,null,null,2,"The Fugitive",0 With a bit of client-side logic, it's easy to transform that into this: [{ id: 0, title: "Indiana Jones", actors: [{ id: 0, name: "Harrison Ford", alsoActedIn: [ {id: 1, title: "Star Wars"}, {id: 2, title: "The Fugitive"}, ] }] }] without the need to use json as a middleman. This pattern has saved me from some truly awful query logic.
- gerad 1y ago
  couldn't you do a union all instead of outer join on false?
  - bccdee 1y ago
    Nope, because unions merge columns. `select * from Actor union select * from Movies` gives you [(0, "Harrison Ford"), (0, "Indiana Jones")], which is a problem because we can't tell which rows are actors and which rows are movies. What we need is [(0, "Harrison Ford", null, null), (null, null, 0, "Indiana Jones")].
  - lixtra 1y ago
    Yes, you could use union. But then you have to pad the columns of the other tables with NULLs to arrive at the same output and carefully count. And we all hate counting.
mamcx 1y ago
The things `SQL` fail to do that enable this sort of things are: * Store relation-on-cell: All the trick of so called `nosql` is that they can do nested data. THAT IS ALL * Then, `join` is already inline * Then, instead of the very poorly named `GROUP BY` it could has REAL `GROUP BY`! That is all is need at the core
deleted 1y ago
[deleted]
ciconia 1y ago
This is a problem that ActiveRecord-style ORM's have failed to solve. One possible solution is writing queries like in the article that transform normalized data from the DB into a JSON structure. Another possibility is to design an ORM that is actually capable of taking a set of relations and dynamically building queries with joins, then transforming the returned rows into tree like structures. This is a very interesting area for exploring a new kind of ORM. I find the whole "CRUD monkey" shtick preached by DHH et al quite lacking for expressing the kind of hierarchical data structures developers need.
- reaanb2 1y ago
  A better solution would be to move away from object-oriented domain modeling towards SQL-speaking entity-component systems.
dcreater 1y ago
Terrible title. SQL is the very definition of structured data. I understand the author means SQL needed tiers/hierarchy/grouping but titling it as he has done just contributes to information pollution / future training data adulteration
cousin_it 1y ago
Nobody mentioned nested data in BigQuery? It's a superset of SQL where fields can be arrays of structs (so basically nested tables) and there's some extra syntax to nest/unnest things, so you can often get everything you wanted in one result set.
- foota 1y ago
  Yes, see https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#select_as_struct https://cloud.google.com/bigquery/docs/reference/standard-sq... and https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#unnest_operator https://cloud.google.com/bigquery/docs/reference/standard-sq.... Very pleasant compared to normal SQL. Table-valued functions (I think these are not exclusive to GoogleSQL?) are also very nice. Every time I see scripts with a long block of with statements part of me dies inside.
- deepsun 1y ago
  PostgreSQL (what article author uses) supports nested data (see Composite Types). You an even define a type yourself for type safety.
- mccanne 1y ago
  For sure... the use of JSON here seems orthogonal to Jamie's point of constructing complex nested values in SQL with a single backend query. Modern SQLs support all this as native types (presuming the result can fit in a homogeneous relational type, which it can in this case), e.g., Jamie's query can be written with a record expression (duckdb dialect): with theTitle as ( from title.parquet where tconst = 'tt3890160' ), principals as ( select array_agg({id:principal.nconst,name:primaryName,category:category}) from principal.parquet, person.parquet where principal.tconst = (from theTitle select tconst) and person.nconst = principal.nconst ), characters as ( select array_agg(c.character) as characters, p.u.name from principal_character.parquet c join (select unnest((from principals)) as u) p on c.character is not null and u.id=c.nconst and c.tconst=(select tconst from theTitle) group by p.u ) select { title: (select primaryTitle from theTitle), director: list_transform( list_filter((from principals), lambda elem: elem.category='director'), lambda elem: elem.name), writer: list_transform( list_filter((from principals), lambda elem: elem.category='writer'), lambda elem: elem.name), genres: (select genres from theTitle), characters: (select array_agg({name:name,characters:characters}) from characters), } as result And if you query typeof on the result, you'll get: STRUCT( title VARCHAR, director VARCHAR[], writer VARCHAR[], genres VARCHAR, characters STRUCT( "name" VARCHAR, characters VARCHAR[] )[] )
deepsun 1y ago
> If there was no director in the database then this query would return 0 rows. Well, LEFT JOIN is the basic SQL knowledge, surprising the author doesn't know that. > because sql wasn't designed to produce hierarchical data Well, SQL was designed _because_ of the shortcomings of hierarchical DBMSes. Also, author uses PostgreSQL, so could easily use ARRAY_AGG(), and Composite Types to get the exact answer they need in one query, no need for hacks. They can even make their own custom type for type safety, if needed.
- School-Cotton 1y ago
  I promise Jamie Brandon knows about left joins.
  - mcdonje 1y ago
    So then it's a bad faith example.
    - School-Cotton 1y ago
      I'm not Jamie but I think the point is just that this is an awkward way to represent one-to-many relationships. Even with left joins things are even clunkier because you no longer have the clunky but straightforward n * m formula for number of rows, you have something even more confusing: n * max(1, m)
cryptonector 1y ago
^F case-insensitive search for: - cte - recursive - with (in SQL code, not prose) - connect (as in Oracle's CONNECT BY) nothing. Nothing! SQL deals with hierarchical data just fine. You just have to learn about how (recursive CTEs).
- bwestergard 1y ago
  I've recently been learning about Datalog, which is excellent for all the queries that you'd want to do with recursive CTEs, but has the added benefit of making it much easier to factor one's code into discrete units (relations) for reusability.
anewhnaccount2 1y ago
I think EdgeDB/gel maybe addresses this problem. It has nice syntax for returning hierarchical resultsets from a relational database.
procaryote 1y ago
Queries like these select primaryTitle, person.primaryName from title, principal, person where title.tconst = 'tt3890160' and title.tconst = principal.tconst and principal.nconst = person.nconst and principal.category = 'director'; are much nicer to write as select primaryTitle, person.primaryName from title join principal on principal.tconst = title.tconst join person on person.nconst = principal.nconst where title.tconst = 'tt3890160' and principal.category = 'director'; Because you separate the filter from the joining, and you're much less likely to forget a join condition If ambiguity of column names are low enough you could also use `join person using(nconst)`
procaryote 1y ago
Having built a few high load things with underlying SQL, I've found that in practice, big joins don't scale well, and caching becomes pretty important. It often becomes more interesting to find what bits are reused in several places and can get broken out into a useful cache (like person id → person name, thumbnail url, person url, …) than making a custom query for each view That completely changes how you do queries as you can cut a bunch of joins. Instead you get a partial data set from the query that you then throw on a function or service that populates all the referenced ids from a cache, and backfills (and caches) any missing ones (preferably with a single query for a list of people in the example).
tedggh 1y ago
In this particular case I much rather use an ORM with caching of view models. I understand the article and the problem, but it doesn’t seem to be a good case for the proposed solution here. This data doesn’t change often and it’s not critical for a user to get the latest data when immediately available. So why you want to write complex, hard to test and debug SQL code when you can have that logic neatly handled in the backend? It is far easier to just build the page structure once in your backend code, cache it, and serve it from memory. The row-explosion problem disappears because you’re not re-running the join logic constantly. The “too many queries” problem disappears because the expensive work was already done when you filled the cache. And the ORM inconsistency worry doesn’t apply because you build the page in a single pass and keep that snapshot until the cache expires.
tucnak 1y ago
> All that's left to do now is... the same joins, but inside the backend web server. Because we have to re-assemble these flat outputs into the structure of the page. This is NOT the case with modern SQL, as it supports JSON. The typical Postgres use-case would be to produce denormalised views for the specific relationships and/or aggregates in JSON form. LATERAL, the correlated sub-query, is really convenient in avoiding double-counting. The rest of the article deals with object-mapping, which is really a poor man's VIEW. Time and time again, software engineers reinvent the data-access wheel out of ignorance to database capabilities.
- MaxMonteil 1y ago
  This sounds very interesting, I've been using SQL more lately and am constantly impressed by what it can just do. My thinking is everything I could get done by the DB avoids heavier and maybe slower application code. Do you have some resources or material I could check to learn more?
  - taffer 1y ago
    Putting business logic in the database: https://sive.rs/pg https://sive.rs/pg Example code: https://github.com/sivers/store https://github.com/sivers/store This talk is more Oracle specific but with some minor changes you can apply the same ideas to postgres: https://www.youtube.com/watch?v=GZUgey3hwyI https://www.youtube.com/watch?v=GZUgey3hwyI Personally I am using an architecture similar to https://sive.rs/pg https://sive.rs/pg in my personal projects and I am very happy with it. It is important that you put data and procedures/views in different schemas so that you can use migrations for your data but automatically delete and recreate the procedures/views during deployment. Also use pgtap or something similar for testing.
    - reaanb2 1y ago
      That's a cool approach that could work well if you don't need realtime data validation such as in a UI. I would love to find a solution that allows the same validation rules to be used in the DBMS as well as in the backend and frontend code.
sakesun 1y ago
When something in SQL becomes cumbersome or difficult to work with, people often tend to create entirely new solutions rather than improving the existing ones. Few developers seem interested in refining SQL itself.
- bmn__ 1y ago
  The hurdle to entry is too damn high. If you want to have your day ruined, let an ISO WG participant explain to you the social and monetary capital needed just to be able to make the most timid of a suggestion for refining the language.
mg 1y ago
I am becoming more and more comfortable with storing everything in JSON. With its JSON arrow operators and indexed expressions, SQLite makes a really nice JSON document store. Assume you have a "cars" table with nothing but a "cars" column that is a JSON blob. Then selecting the models of all cars is just: SELECT cars->>'model' FROM cars I wish MariaDB would follow suit and also support arrow operators. You can do it in MariaDB, but it becomes more cumbersome: SELECT JSON_UNQUOTE(JSON_EXTRACT(cars, '$.model')) FROM cars
- tgv 1y ago
  If that's all, I still recommend classical columns. Now if you have array values and/or values with varying structures which you don't need to query (frequently), JSON makes sense.
- mr_toad 1y ago
  > SELECT cars->>'model' FROM cars Can ‘model’ be a variable, or does it have to be a constant literal?
  - CGamesPlay 1y ago
    It’s any expression, so a parameter or shudder column would work. https://sqlite.org/json1.html#jptr https://sqlite.org/json1.html#jptr
greggyb 1y ago
Coming from a data engineering and business analytics background, the terms "structured" and "unstructured" seem to be swapped in meaning here. I'd expect to talk about anything in a DB as "structured data". Flexible serialization formats such as JSON or XML are "semi-structured". And something from e.g., an office document is "unstructured". This is not a complaint or criticism. I understand the article just fine. It's just interesting how different perspectives can affect what words mean (:
- sinfulprogeny 1y ago
  Kleppman[1] calls it schema-on-read (json, xml) and schema-on-write (typed columns in an RDB). I like it over structured/unstructured, it's a bit more specific. [1] https://martin.kleppmann.com/2017/03/27/designing-data-intensive-applications.html https://martin.kleppmann.com/2017/03/27/designing-data-inten...
  - greggyb 1y ago
    Yes, I agree with that preference. I don't love the verbiage of "structured" / "unstructured" in either usage (the article's or that which I shared).
- thaumasiotes 1y ago
  > Coming from a data engineering and business analytics background, the terms "structured" and "unstructured" seem to be swapped in meaning here. Mark Rosewater likes to write about his personal concept of "linear" Magic: the Gathering decks, which are decks in which the cards tend to pick up synergy bonuses from each other, so that having two of them together is considerably more powerful than you'd expect from the strength of those two cards individually. This always bothers me because it is the opposite of the more normal use of "linear" relationships, in which everything contributes exactly as much to an aggregate as it's worth individually.
- stevage 1y ago
  I think the SQL sense is more that "structured" means "it contains internal structure" (like a tree), whereas a table without JSON is free from additional structure apart from its own table structure.
Mikhail_Edoshin 1y ago
There was a discussion about a relevant paper here at HN: "What if an SQL statement returned a database?" https://news.ycombinator.com/item?id=38606146 https://news.ycombinator.com/item?id=38606146
roenxi 1y ago
The real counterargument here is that the DB systems that stored nested data mostly lose in competition with relational systems over time as it turns out they don't satisfy the large number of people who use data for things other than UIs - they aren't suitable for long term storage and don't separate concerns in a way that allows generalists like database engineers to optimise how data gets too and from a storage location into memory. And, in practice a lot of these frontenders end up storing a JSON blob with either no nesting or just one level of nesting that looks a lot like a database schema built by someone being stubborn about not normalising data. In some sense that is fine, databases seem to be learning to just treat simple JSON blobs as normalised data but it is hard to make that as efficient as a guaranteed schema. Plus larger companies often end up having to hire a dedicated team of engineers to properly normalise data so other people can use it.
maweki 1y ago
Isn't the author just proposing GraphQL?
- andyferris 1y ago
  Not at all. GraphQL has the API servers (database clients) marshalling the data into heirarchies (nested data) and managing consistent results (e.g. streaming updates). The article is arguing that the database should be more capable of understanding queries that request nested data (and separately Jamie has spoken in favour of databases that encourage end clients getting up-to-date/internally-consistent results). My two cents is that it's a crutch having SQL rely on JSON instead of it's own first-class nested data (which would be any given value (field, aka cell) can itself be a relation - at least postgres has array values but they are challenging to index/use keys programmatically).
hbrundage 1y ago
I agree and would take it one step further — the structure of the joins is something that should most often come from the schema, not the query. In the same way that the attributes of an entity should be modelled out ahead of time, the relationships between the entities should be as well, and that yields more productive querying and better performance. We built a GraphQL / SQL hybrid expression language that does just this here: https://docs.gadget.dev/guides/data-access/gelly#gelly https://docs.gadget.dev/guides/data-access/gelly#gelly , curious what yall think.
- reaanb2 1y ago
  I disagree. From a fact-oriented modeling perspective, relationships among entities are already well-modelled and represented in tables. There's a reason the relational model calls tables relations - they relate things. The mistake here is viewing tables/rows as representing entities, FK constraints as representing relationships, and viewing entities as containers of attributes rather than the subjects of facts. Joining tables is composing complex facts from simple ones, and is the opposite of normalization which is decomposing complex facts into simpler ones. The ability to join tables on arbitrary conditions is fundamental to the ability to ask a DBMS complex questions and have it respond with all the facts that match that question.
stared 1y ago
I used to work bit with MongoDB (over 10 years ago) and it did wonders in making me fall back into love with relational databases. I finally saw the light of Codd! SQL, as a language is clunky, true. It can be patched here are there, either by PipeSQL or by various ORMs. I agree, that it would be wonderful to have standardized tooling for generating JSON like in the post. Yet, with relational databased you can separate concerns of: what is your data and what you want to display. If you use JSON-like way to store data, it can do the job until you want to change data or queries.
- mrlongroots 1y ago
  Agree: flat/relational structure is highly efficient to store, query planning/optimization/scaling become a lot easier, the language is clunky but relational algebra is beautiful, but... I think pushing multiple joins to what is still a relational database and getting a complex output isn't the worst idea in the world, as a higher-level layer on top of a regular database. On the other hand, "it needs four queries/RTTs" is not the worst thing in the world. It needn't be the goal of a system to achieve theoretical minimum performance for everything. Let those who truly have the problem in prod push the first patch.
- arnsholt 1y ago
  My stint with MongoDB was brief, but I too came away with a deeper appreciation of SQL bases. I feel it's a bit like a good type system: yes, there absolutely is an upfront cost, but over time it really does save you from stupid runtime problems. It's especially important when the bugs are in the data storage layer, because if a bug causes bad data to be written to your database, not only do you need to find and fix the bug, you also have to figure out how to deal with (possibly lots of) bad data. Also, modern SQL is an incredibly powerful language. Good SQL can save you from lots of dumb data munging code if you know how to wield your database properly. Especially for analytical queries, but also more typical application code IMO.
  - anonymars 1y ago
    I have spent many years beating the drum that a few minutes spent on constraints today save many, many, many hours of painful data cleanup later And in fact, good constraints also can improve query performance. If the optimizer knows this column is unique, or that column is guaranteed to have a corresponding value in the joined table, it can do all sorts of tricks...
silon42 1y ago
I've seen 'decorellation' mentioned... 95% of the time I am fighting the query optimizer is to get the correlation and a loop over few items working instead of a table scan.
jbverschoor 1y ago
Time to start taking that those CS classes before blogging
- kjfarm 1y ago
  This response comes across as unnecessarily toxic and quip-y, which is a shame because there's real substance here. The author clearly understands database design, referencing concepts like normal forms and Boyce-Codd Normal Form (BCNF). Even if I don't agree with the conclusion, I have to respect anyone who takes the time to lay out their argument. Challenging ideas are what help our community grow.
- kerbobotat 1y ago
  Time to start proofreading your comments before posting.
- eatonphil 1y ago
  Guessing from the relevant masters thesis linked on his site and his time at materialize and tigerbeetle, Jamie's been working on databases for at least like 15 years? At least personally when Jamie says something I listen. :)
h4ch1 1y ago
I mean for highly connected data shouldn't the solution be to ideally use a graph database from the get go instead of generating JSON aggregates in the database? Don't get me wrong, JSONB is great but I see a lot of people simply slap on a JSON column to anything that requires more complexity without trying to first solve it through RDBMS relations.
- nrjames 1y ago
  I loaded this same IMDB dataset into a Kuzu DB the other day, just for fun. It was an interesting project but I would agree that this dataset in particular lends itself to a graph database. Kuzu started to choke with > 10M nodes & 10M edges but I was doing a naive insert & probably could fit the entire dataset in without trouble using their direct CSV import.
  - zw17 1y ago
    Interesting points here. I’ve found the “graph DB vs. relational DB” discussion usually gets framed as an either/or, but there’s a middle ground. A lot of teams already have their data sitting in Postgres, Mongo, or a lakehouse. Spinning up a separate graph database just for traversals often means duplicating data, building pipelines, and keeping two systems in sync. That’s fine if you need deep graph algorithms, but for many workloads it’s overkill. What some folks are exploring now is running graph queries directly on top of their existing data, without having to ETL into a dedicated graph DB. You still get multi-hop traversal and knowledge graph use cases, but avoid the “yet another database” tax. So yeah...graph databases are great, but they’re not the only way to model or query graphs anymore.
- zozbot234 1y ago
  PostgreSQL makes a perfectly fine graph database, at least for general workloads. You're not going to get vastly improved performance from a specialized solution unless you're doing very specific things like math-heavy network analysis that PostgreSQL is still a bad fit for.
redwood 1y ago
This is the core reason why MongoDB is popular. And while the HN crowd fell into a trough of dissolutionment with it 10 years ago since it wasn't ready then, a bunch of folks found success with it more recently. At my company we had legacy implementations of it and modern ones and the juxtaposition was significant.
- smohare 1y ago
  I worked with MongoDB a decade ago and also now. One of the worst systems I’ve ever had to deal with. Not necessarily because of the db itself, but because of the implications of its use. It serves an incredibly narrow purpose. Everywhere I’ve worked at has had a team practically dedicated to moving data from a document store to a relational db so it could be actually usefully used for analytics and across disparate systems.
zigzag312 1y ago
I think data definition schema needs to have better type system which not only defines database schema, but contains all necessary data and allows annotations to be able to generate quality language specific code from it. Single source of truth for data structure, which is used both by database and code.
- kevindamm 1y ago
  I see some value in that, but in my experience it's actually the queries that inform much of what the table shema and indexes should look like. Especially when joins and/or ordering are involved, it's the details about what the application wants to extract from the DB that are important, and I'd rather have those details at the code's call site not at the schema definition, because if/when they change or are removed it will be clearer from the context.
  - zigzag312 1y ago
    What I'm proposing doesn't change that. It's just to information necessary to keep strongly typed data in sync between the database and dtos. To help with serialization and mapping. Compile time type safety. When querying db for schema you don't get enough information to be able to generate very good code in all cases. Language-first way (define schema in language and generate database schema/migrations) ties you to just one language. And usually these tools don't support using all database features.
ivanb 1y ago
One limitation of JSON is its limited set of types. For example, for decimal numbers one has to resort to stringly typing representation because DB connection libraries assume that JSON numbers are floating point. Note that JSON numbers are just sequences of digits, nothing more. There is no attached precision semantic. Another example is UUIDs. Instead of transferring 16 bytes, the libraries deal with wasteful string representation. I'm sure you can bring another examples. Nonetheless, for majority of data JSON as DB output format is alright.
- jamii 1y ago
  Yeah, json is annoying because of the limited types, but postgres arrays/rows are annoying because of the lack of sum/union types (if your UI has a heterogenous list of elements). The OLAP world has much nicer type systems eg https://duckdb.org/docs/stable/sql/data_types/union.html https://duckdb.org/docs/stable/sql/data_types/union.html.
phiresky 1y ago
I think relational databases are great, but this is my biggest problem with SQL, even before the ridiculous syntax. A query like select users.*, orders.* from users left join orders on orders.user_id = users.id Should always have returned a structure like: type SingleReturnRow = { users: {id: ..., }[], orders: {user_id: ..., id: ..., }[]} type Return = SingleReturnRow[] Mangling the columns together and _removing_ groupings that naturally appear is just so unnecessary. I don't think a larger change in the query language would even be needed. Even better of course would be a return value like type SingleReturnRow = { user: User, orders: Order[] } But I see how that would require a fundamental change in the language. Of course in PG now you can use select users.*, json_agg(orders.*) as orders from users left join orders on orders.user_id = users.id group by users.id but using JSON as intermediate steps just feels unnatural.
- reaanb2 1y ago
  See https://arxiv.org/abs/2312.00638 https://arxiv.org/abs/2312.00638 for one proposal to address this
- johannes1234321 1y ago
  It follows the relational algebra model. Relations (aka Tables) go in, relations (aka Tables) come out. This makes some things really nice. However I proposed a hierarchical result for such cases a long time to our database, but couldn't convince enough people. json_agg came later at there all the machinery is there, it would "just" require exposing this to the protocol and adapting all clients to understand that data format ...
  - refset 1y ago
    The world deserves something better than json_agg. For XTDB we coined NEST_ONE and NEST_MANY: https://xtdb.com/blog/the-missing-sql-subqueries https://xtdb.com/blog/the-missing-sql-subqueries The output format is either raw Arrow DenseUnions (e.g. via FlightSQL) or Transit via a pgwire protocol extension type.
  - andyferris 1y ago
mcdonje 1y ago
This was enlightening. I've often wondered why ORMs exist, and it turns out a bunch of devs don't understand tabular data. I was going to say they don't understand SQL, but apparently it goes deeper than that. Like, left joins have been around since SQL-92, so the query not returning a result when there is no director is a skill issue. Nowadays, you can run a JSON or XML query and have the DB return your results in a hierarchical format.
- deleted 1y ago
  [deleted]
- kragen 1y ago
  The author of this blog post writes in https://www.scattered-thoughts.net/writing/reflections-on-a-decade-of-coding/ https://www.scattered-thoughts.net/writing/reflections-on-a-...: > - In a small team (averaging ~3 people, I think?) in a little under a year, we built a SQL query planner that passed the >6 million tests in the sqlite logic test suite. > - I figured out how to decorrelate arbitrary SQL subqueries (I wasn't the first person to figure this out, but I got there independently). > - I demonstrated a mode of consistency failure in various popular streaming systems that I believe was not widely understood at the time. I think it is likely that the reason you disagree with him is not that he "don't understand tabular data" and has "a skill issue". Unless you're secretly Michael Stonebraker? You write: > Nowadays, you can run a JSON or XML query and have the DB return your results in a hierarchical format. You may not have noticed this, but the post you are commenting on explains how to run a JSON query and have the DB return your results in a hierarchical format.
  - mcdonje 1y ago
    My take was charitable because if he knows better, then he's making a bad faith argument, and I didn't want to accuse him of that. I didn't read beyond the article. I only responded to what was in the post. He didn't use capabilities SQL has had for decades, made a bad query, and used that to build his argument about how bad SQL is. The ORM tables could've been written in one query that returns a tabular format of hierarchical data, which the front end could use. What does that look like? It looks like the kind of underlying tabular format JSON & XML queries require. He didn't do that with his non-JSON examples. Yes, the framework would need to turn it into an object. Yes, there is data duplication in some fields. While not ideal, it's still better than the ORM example because you're not splitting it up into multiple queries. His JSON example undercuts his main argument. The DB is fully capable of returning the representation he wants. It's also more complex than necessary with all the JSONB_AGGs in the subqueries. Then he says you can see the duct tape. So yeah, it reads like a front end person who never really learned modern SQL. Or maybe it's someone who knows better who is making a bad faith argument. Either way, it's just a bad take.
Spixel_ 1y ago
I don't get the transaction bit. At least with postgres, a transaction doesn't guarantee that all statements in it see the data at the same point in time (actually, it's not even guaranteed for subqueries). Also, often, the transactional database servers is more difficult to scale than application servers so from a technical standpoint, it makes sense to do this glue work in app code.
- TheTaytay 1y ago
  Doesn’t it guarantee consistency from the time the transaction started (assuming read committed isolation)? It guarantees you won’t see something “later” than when your transaction began. https://www.postgresql.org/docs/current/transaction-iso.html https://www.postgresql.org/docs/current/transaction-iso.html I’m likely misunderstanding what you mean by time.
  - Spixel_ 1y ago
    Read committed (which is the default), doesn't guarantee that. See "Nonrepeatable Read" and "Phantom Read" which are both possible in your documentation page.
- andyferris 1y ago
  > a transaction doesn't guarantee that all statements in it see the data at the same point in time This depends on the transaction isolation level. If you use snapshot or serializable this should be the case (but you may have aborted transactions due to optimistic concurrency).
  - Spixel_ 1y ago
    You are right, but note that the default isolation level is "Read committed" in postgres.
- Jweb_Guru 1y ago
  SSI scales fine for most workloads as long as you correctly mark your read-only transactions.
mrkeen 1y ago
The author didn't see fit to mention that you just flip the arrows. Classes/Structs know about their children. Relations know about their parents. If you want crazier m*n relationships you use an association table. Did the author just not know? Or he didn't see it worthy of dismissal? > Doing this transformation by hand is tedious and error-prone. We call this tedium "the object-relational mismatch" but it isn't really about objects or relations. It really is. > The fundamental problem is that fitting complex relationships to human vision usually requires constructing some visual hierarchy, but different tasks require different hierarchies. Yes. Different tasks require different hierarchies. One particular way of doing things should not baked into your 1970s relational model.
andyferris 1y ago
Yes - I have to agree. Codd was right in that if you want transactional semantics that are both quick and flexible, you'll need to _store_ your data in normalized relations. The system of record is unwieldly otherwise. The article is right that this idea was taken too far - queries do not need to be restricted to flat relations. In fact the application, for any given view, loves heirarchical orginization. It's my opinion that application views have more in common with analytics (OLAP) except perhaps latency requirements - they need internally consistent snapshots (and ideally the corresponding trx id) but it's the "command" in CQRS that demands the normalized OLTP database (and so long as the view can pass along the trx id as a kind of "lease" version for any causally connected user command, as in git push --force-with-lease, the two together work quite well). This issue is of course that SQL eshews hierarchical data even in ephemeral queries. It's really unfortuante that we generate jsonb aggregates to do this instead of first-class nested relations a la Dee [1] / "third manifesto" [2]. Jamie Brandon has clearly been thinking about this a long time and I generally find myself nodding along with the conclusions, but IMO the issue is that SQL poorly expresses nested relations and this has been the root cause of object-relation impedence since (AFAICT) before either of us were born. [1] https://github.com/ggaughan/dee https://github.com/ggaughan/dee [2] https://www.dcs.warwick.ac.uk/~hugh/TTM/DTATRM.pdf https://www.dcs.warwick.ac.uk/~hugh/TTM/DTATRM.pdf
- reaanb2 1y ago
  In my view, the O/R impedance mismatch derives from a number of shortcomings. Many developers view entities as containers of their attributes and involved only in binary relationships, rather than the subjects of n-ary facts. They map directly from a conceptual model to a physical model, bypassing logical modeling. They view OOP as a data modeling system, and reinvent network data model databases and navigational code on top of SQL.
  - mcphage 1y ago
    In that case, the mismatch is between "What developers need" and "What SQL provides".
    - 1y ago
kragen 1y ago
An interesting thing about "A Relational Model of Data for Large Shared Data Banks" (the stone tablets handed down from Codd https://www.seas.upenn.edu/~zives/03f/cis550/codd.pdf https://www.seas.upenn.edu/~zives/03f/cis550/codd.pdf) is that it starts out by considering N-ary relations whose values may themselves be relations, and only then switches to considering "normalized" relations whose values are not relations: > A relation [table] whose domains [column types] are all simple [not relations] can be represented in storage by a two-dimensional column-homogeneous array of the kind discussed above. Some more complicated data structure is necessary for a relation with one or more nonsimple domains. For this reason (and others to be cited below) the possibility of eliminating nonsimple domains appears worth investigating.⁴ There is, in fact, a very simple elimination procedure, which we shall call normalization. But non-normalized relations support the kind of nested structure the eminent Dr. Brandon wants, without resorting to JSON or abandoning the strong uniform typing we have with SQL. Darwen & Date's The Third Manifesto https://www.dcs.warwick.ac.uk/~hugh/TTM/DTATRM.pdf https://www.dcs.warwick.ac.uk/~hugh/TTM/DTATRM.pdf includes group and ungroup operations which translate back and forth between normalized and non-normalized relations. I've been playing around with some of these ideas using some rather horrific atrocities perpetuated against Lua operator overloading in http://canonical.org/~kragen/sw/dev3/cripes.lua http://canonical.org/~kragen/sw/dev3/cripes.lua. I haven't added joins or ungrouping to it yet, but I think it might be a prototype of something promising for this kind of use case.
gethly 1y ago
I am using event sourcing with cqrs and that means i also utilise projections, which the db itself can replicate less efficiently as a "view" or virtual table. So one can technically create a projection/view that is tailor-made for a query that needs to display some data. Of course it is no often possible to retrieve all the data with a single select command. So joins and multiple queries are simply inherent to complexity of data we store nowadays. Anyway, years ago, i have moved to a db model where every entity is stored as a blob in a dedicated column and every additional column, beside id, is indexed. So there is no wasted space and a ton of columns that only hold data but are not used for filtering. I can run data-efficient queries that yield a list of ids of blobs to load or the blobs themselves and then i extract any necessary data out of those blobs(entities) on the application level. So the database us purely a blob store + few fast indices.
- hdjrudni 1y ago
  I'm sure it does work well if the database is perfectly tailored to your app. I'm skeptical that it can handle every new use case that might come up -- i.e. if you need to query for something hidden in a blob. Sounds precisely like NoSQL. It works until it doesn't.
  - gethly 1y ago
    If new index is needed, you can just add it and rebuild the projection, it's no problem.
- kragen 1y ago
  What's your application?
  - gethly 1y ago
    I use it on gethly.com and previously i used the same approach on an internal application for domain registrar that handles the EPP. So far no issues. Also using those blobs allows me to easily add encryption layer on application level.
ThinkBeat 1y ago
Would this not become a bit simpler if you populate the sql schema like this? +-------------------+ | | | | +------------------+ | Genere | | | | | | | +-------------------+ | Movies | | | | | +-------------------+ | | | | | | | Actors | +------------------+ | | | | +-------------------+ +
vbilopav 1y ago
What an awful query example! This should and is simple. Here: select jsonb_build_object( 'title', title, 'genres', genres, 'actors', json_agg( jsonb_build_object( 'name', actor_name, 'characters', actor_characters ) ), 'directors', directors, 'writers', writers ) from ( select t.primaryTitle as title, t.genres, actor_person.primaryName as actor_name, array_agg(pc.character) as actor_characters, array_agg(director_person.primaryName) as directors, array_agg(writer_person.primaryName) as writers from title t left join principal actor on t.tconst = actor.tconst and actor.category = 'actor' left join person actor_person on actor.nconst = actor_person.nconst left join principal_character pc on actor.nconst = pc.nconst and actor.tconst = pc.tconst left join principal director on t.tconst = director.tconst and director.category = 'director' left join person director_person on director.nconst = director_person.nconst left join principal writer on t.tconst = writer.tconst and writer.category = 'writer' left join person writer_person on writer.nconst = writer_person.nconst where t.tconst = 'tt3890160' group by t.tconst, actor_person.primaryName, t.primaryTitle, t.genres, actor.ordering, director_person.primaryName, writer_person.primaryName order by actor.ordering asc ) main group by title, genres, directors, writers; Here: https://i.postimg.cc/zB1Bgg1L/movies.png https://i.postimg.cc/zB1Bgg1L/movies.png

148 points by todsacerdoti 1y ago

The only reason this post has any truth is that the IMDB dataset is horrendously unnormalized. It also points out at the end that you can in fact use a combination of aggregations to return the desired JSON object. Even then, if you really wanted to, you can absolutely make a tree-like structure in SQL in a variety of ways: adjacency lists, nested sets, closure tables, etc.

mcphage 1y ago
> you can absolutely make a tree-like structure in SQL in a variety of ways: adjacency lists, nested sets, closure tables, etc. That's kinda the problem—rather there being 1 way to make a tree-like structure in SQL, that works correctly, there's a lot of ways to do it, and they all have different tradeoffs, and they're all a bit obnoxious.
- sgarland 1y ago
  There are multiple ways to do just about anything, in any programming language.
- deepsun 1y ago
  And the article says that yes, you need various different looks at the same data. I would say UX is a beast, once a UX person tells me what they need SQL query comes up easily. Figuring out what they need is typically the hard part.
  - sgarland 1y ago
    It’s a lot easier if they tell you what they need when you’re designing the schemata. Unfortunately, IME this isn’t done, and so you wind up with tons of bolted-on attributes later, throwing any hope of normalization out the window.
  - mcphage 1y ago
    > once a UX person tells me what they need SQL query comes up easily. Figuring out what they need is typically the hard part. If they needed only one thing, sure. However, often UX needs to look at the same data in very different ways, and an efficient representation for one need might not be at all efficient for the next (which may not even be known as a need for several years).

bccdee 1y ago

My favourite way of doing this is with movieIds as (select id from Movie where title = $1), actorIds as (select Actor.id from Actor join ActorMovie on [...] where ActorMovie.Movie in movieId), alsoActedIn as (select id from ActorMovie where actor in actorId), movieResults as (select * from Movie where id in movieIds), actorResults as (select * from Actor where id in actorIds), alsoActedInResults as (select * from Movie join ActorMovie on [...] where ActorMovie.id in alsoActedIn) select * from movieResults full outer join actorResults on false full outer join alsoActedInResults on false; Not every database supports "full outer join on false," and sometimes you have to add "as not materialized" to the "with" subqueries in order for it to be performant, but it works in Postgres, and you end up with a results table that looks something like this: MovieId,MovieTitle,ActorId,ActorName,MovieId,MovieTitle,ActorMovie.actor 0,"Indiana Jones",null,null,null,null,null null,null,0,"Harrison Ford",null,null,null null,null,null,null,0,"Indiana Jones",0 null,null,null,null,1,"Star Wars",0 null,null,null,null,2,"The Fugitive",0 With a bit of client-side logic, it's easy to transform that into this: [{ id: 0, title: "Indiana Jones", actors: [{ id: 0, name: "Harrison Ford", alsoActedIn: [ {id: 1, title: "Star Wars"}, {id: 2, title: "The Fugitive"}, ] }] }] without the need to use json as a middleman. This pattern has saved me from some truly awful query logic.

gerad 1y ago
couldn't you do a union all instead of outer join on false?
- bccdee 1y ago
  Nope, because unions merge columns. `select * from Actor union select * from Movies` gives you [(0, "Harrison Ford"), (0, "Indiana Jones")], which is a problem because we can't tell which rows are actors and which rows are movies. What we need is [(0, "Harrison Ford", null, null), (null, null, 0, "Indiana Jones")].
- lixtra 1y ago
  Yes, you could use union. But then you have to pad the columns of the other tables with NULLs to arrive at the same output and carefully count. And we all hate counting.

mamcx 1y ago

The things `SQL` fail to do that enable this sort of things are: * Store relation-on-cell: All the trick of so called `nosql` is that they can do nested data. THAT IS ALL * Then, `join` is already inline * Then, instead of the very poorly named `GROUP BY` it could has REAL `GROUP BY`! That is all is need at the core

deleted 1y ago

[deleted]

ciconia 1y ago

This is a problem that ActiveRecord-style ORM's have failed to solve. One possible solution is writing queries like in the article that transform normalized data from the DB into a JSON structure. Another possibility is to design an ORM that is actually capable of taking a set of relations and dynamically building queries with joins, then transforming the returned rows into tree like structures. This is a very interesting area for exploring a new kind of ORM. I find the whole "CRUD monkey" shtick preached by DHH et al quite lacking for expressing the kind of hierarchical data structures developers need.

reaanb2 1y ago
A better solution would be to move away from object-oriented domain modeling towards SQL-speaking entity-component systems.

dcreater 1y ago

Terrible title. SQL is the very definition of structured data. I understand the author means SQL needed tiers/hierarchy/grouping but titling it as he has done just contributes to information pollution / future training data adulteration

cousin_it 1y ago

Nobody mentioned nested data in BigQuery? It's a superset of SQL where fields can be arrays of structs (so basically nested tables) and there's some extra syntax to nest/unnest things, so you can often get everything you wanted in one result set.

foota 1y ago
Yes, see https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#select_as_struct https://cloud.google.com/bigquery/docs/reference/standard-sq... and https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#unnest_operator https://cloud.google.com/bigquery/docs/reference/standard-sq.... Very pleasant compared to normal SQL. Table-valued functions (I think these are not exclusive to GoogleSQL?) are also very nice. Every time I see scripts with a long block of with statements part of me dies inside.
deepsun 1y ago
PostgreSQL (what article author uses) supports nested data (see Composite Types). You an even define a type yourself for type safety.
mccanne 1y ago
For sure... the use of JSON here seems orthogonal to Jamie's point of constructing complex nested values in SQL with a single backend query. Modern SQLs support all this as native types (presuming the result can fit in a homogeneous relational type, which it can in this case), e.g., Jamie's query can be written with a record expression (duckdb dialect): with theTitle as ( from title.parquet where tconst = 'tt3890160' ), principals as ( select array_agg({id:principal.nconst,name:primaryName,category:category}) from principal.parquet, person.parquet where principal.tconst = (from theTitle select tconst) and person.nconst = principal.nconst ), characters as ( select array_agg(c.character) as characters, p.u.name from principal_character.parquet c join (select unnest((from principals)) as u) p on c.character is not null and u.id=c.nconst and c.tconst=(select tconst from theTitle) group by p.u ) select { title: (select primaryTitle from theTitle), director: list_transform( list_filter((from principals), lambda elem: elem.category='director'), lambda elem: elem.name), writer: list_transform( list_filter((from principals), lambda elem: elem.category='writer'), lambda elem: elem.name), genres: (select genres from theTitle), characters: (select array_agg({name:name,characters:characters}) from characters), } as result And if you query typeof on the result, you'll get: STRUCT( title VARCHAR, director VARCHAR[], writer VARCHAR[], genres VARCHAR, characters STRUCT( "name" VARCHAR, characters VARCHAR[] )[] )

deepsun 1y ago

> If there was no director in the database then this query would return 0 rows. Well, LEFT JOIN is the basic SQL knowledge, surprising the author doesn't know that. > because sql wasn't designed to produce hierarchical data Well, SQL was designed _because_ of the shortcomings of hierarchical DBMSes. Also, author uses PostgreSQL, so could easily use ARRAY_AGG(), and Composite Types to get the exact answer they need in one query, no need for hacks. They can even make their own custom type for type safety, if needed.

School-Cotton 1y ago
I promise Jamie Brandon knows about left joins.
- mcdonje 1y ago
  So then it's a bad faith example.
  - School-Cotton 1y ago
    I'm not Jamie but I think the point is just that this is an awkward way to represent one-to-many relationships. Even with left joins things are even clunkier because you no longer have the clunky but straightforward n * m formula for number of rows, you have something even more confusing: n * max(1, m)

cryptonector 1y ago

^F case-insensitive search for: - cte - recursive - with (in SQL code, not prose) - connect (as in Oracle's CONNECT BY) nothing. Nothing! SQL deals with hierarchical data just fine. You just have to learn about how (recursive CTEs).

bwestergard 1y ago
I've recently been learning about Datalog, which is excellent for all the queries that you'd want to do with recursive CTEs, but has the added benefit of making it much easier to factor one's code into discrete units (relations) for reusability.

anewhnaccount2 1y ago

I think EdgeDB/gel maybe addresses this problem. It has nice syntax for returning hierarchical resultsets from a relational database.

procaryote 1y ago

Queries like these select primaryTitle, person.primaryName from title, principal, person where title.tconst = 'tt3890160' and title.tconst = principal.tconst and principal.nconst = person.nconst and principal.category = 'director'; are much nicer to write as select primaryTitle, person.primaryName from title join principal on principal.tconst = title.tconst join person on person.nconst = principal.nconst where title.tconst = 'tt3890160' and principal.category = 'director'; Because you separate the filter from the joining, and you're much less likely to forget a join condition If ambiguity of column names are low enough you could also use `join person using(nconst)`

procaryote 1y ago

Having built a few high load things with underlying SQL, I've found that in practice, big joins don't scale well, and caching becomes pretty important. It often becomes more interesting to find what bits are reused in several places and can get broken out into a useful cache (like person id → person name, thumbnail url, person url, …) than making a custom query for each view That completely changes how you do queries as you can cut a bunch of joins. Instead you get a partial data set from the query that you then throw on a function or service that populates all the referenced ids from a cache, and backfills (and caches) any missing ones (preferably with a single query for a list of people in the example).

tedggh 1y ago

In this particular case I much rather use an ORM with caching of view models. I understand the article and the problem, but it doesn’t seem to be a good case for the proposed solution here. This data doesn’t change often and it’s not critical for a user to get the latest data when immediately available. So why you want to write complex, hard to test and debug SQL code when you can have that logic neatly handled in the backend? It is far easier to just build the page structure once in your backend code, cache it, and serve it from memory. The row-explosion problem disappears because you’re not re-running the join logic constantly. The “too many queries” problem disappears because the expensive work was already done when you filled the cache. And the ORM inconsistency worry doesn’t apply because you build the page in a single pass and keep that snapshot until the cache expires.

tucnak 1y ago

> All that's left to do now is... the same joins, but inside the backend web server. Because we have to re-assemble these flat outputs into the structure of the page. This is NOT the case with modern SQL, as it supports JSON. The typical Postgres use-case would be to produce denormalised views for the specific relationships and/or aggregates in JSON form. LATERAL, the correlated sub-query, is really convenient in avoiding double-counting. The rest of the article deals with object-mapping, which is really a poor man's VIEW. Time and time again, software engineers reinvent the data-access wheel out of ignorance to database capabilities.

MaxMonteil 1y ago
This sounds very interesting, I've been using SQL more lately and am constantly impressed by what it can just do. My thinking is everything I could get done by the DB avoids heavier and maybe slower application code. Do you have some resources or material I could check to learn more?
- taffer 1y ago
  Putting business logic in the database: https://sive.rs/pg https://sive.rs/pg Example code: https://github.com/sivers/store https://github.com/sivers/store This talk is more Oracle specific but with some minor changes you can apply the same ideas to postgres: https://www.youtube.com/watch?v=GZUgey3hwyI https://www.youtube.com/watch?v=GZUgey3hwyI Personally I am using an architecture similar to https://sive.rs/pg https://sive.rs/pg in my personal projects and I am very happy with it. It is important that you put data and procedures/views in different schemas so that you can use migrations for your data but automatically delete and recreate the procedures/views during deployment. Also use pgtap or something similar for testing.
  - reaanb2 1y ago
    That's a cool approach that could work well if you don't need realtime data validation such as in a UI. I would love to find a solution that allows the same validation rules to be used in the DBMS as well as in the backend and frontend code.

sakesun 1y ago

When something in SQL becomes cumbersome or difficult to work with, people often tend to create entirely new solutions rather than improving the existing ones. Few developers seem interested in refining SQL itself.

bmn__ 1y ago
The hurdle to entry is too damn high. If you want to have your day ruined, let an ISO WG participant explain to you the social and monetary capital needed just to be able to make the most timid of a suggestion for refining the language.

mg 1y ago

I am becoming more and more comfortable with storing everything in JSON. With its JSON arrow operators and indexed expressions, SQLite makes a really nice JSON document store. Assume you have a "cars" table with nothing but a "cars" column that is a JSON blob. Then selecting the models of all cars is just: SELECT cars->>'model' FROM cars I wish MariaDB would follow suit and also support arrow operators. You can do it in MariaDB, but it becomes more cumbersome: SELECT JSON_UNQUOTE(JSON_EXTRACT(cars, '$.model')) FROM cars

tgv 1y ago
If that's all, I still recommend classical columns. Now if you have array values and/or values with varying structures which you don't need to query (frequently), JSON makes sense.
mr_toad 1y ago
> SELECT cars->>'model' FROM cars Can ‘model’ be a variable, or does it have to be a constant literal?
- CGamesPlay 1y ago
  It’s any expression, so a parameter or shudder column would work. https://sqlite.org/json1.html#jptr https://sqlite.org/json1.html#jptr

greggyb 1y ago

Coming from a data engineering and business analytics background, the terms "structured" and "unstructured" seem to be swapped in meaning here. I'd expect to talk about anything in a DB as "structured data". Flexible serialization formats such as JSON or XML are "semi-structured". And something from e.g., an office document is "unstructured". This is not a complaint or criticism. I understand the article just fine. It's just interesting how different perspectives can affect what words mean (:

sinfulprogeny 1y ago
Kleppman[1] calls it schema-on-read (json, xml) and schema-on-write (typed columns in an RDB). I like it over structured/unstructured, it's a bit more specific. [1] https://martin.kleppmann.com/2017/03/27/designing-data-intensive-applications.html https://martin.kleppmann.com/2017/03/27/designing-data-inten...
- greggyb 1y ago
  Yes, I agree with that preference. I don't love the verbiage of "structured" / "unstructured" in either usage (the article's or that which I shared).
thaumasiotes 1y ago
> Coming from a data engineering and business analytics background, the terms "structured" and "unstructured" seem to be swapped in meaning here. Mark Rosewater likes to write about his personal concept of "linear" Magic: the Gathering decks, which are decks in which the cards tend to pick up synergy bonuses from each other, so that having two of them together is considerably more powerful than you'd expect from the strength of those two cards individually. This always bothers me because it is the opposite of the more normal use of "linear" relationships, in which everything contributes exactly as much to an aggregate as it's worth individually.
stevage 1y ago
I think the SQL sense is more that "structured" means "it contains internal structure" (like a tree), whereas a table without JSON is free from additional structure apart from its own table structure.

Mikhail_Edoshin 1y ago

There was a discussion about a relevant paper here at HN: "What if an SQL statement returned a database?" https://news.ycombinator.com/item?id=38606146 https://news.ycombinator.com/item?id=38606146

roenxi 1y ago

The real counterargument here is that the DB systems that stored nested data mostly lose in competition with relational systems over time as it turns out they don't satisfy the large number of people who use data for things other than UIs - they aren't suitable for long term storage and don't separate concerns in a way that allows generalists like database engineers to optimise how data gets too and from a storage location into memory. And, in practice a lot of these frontenders end up storing a JSON blob with either no nesting or just one level of nesting that looks a lot like a database schema built by someone being stubborn about not normalising data. In some sense that is fine, databases seem to be learning to just treat simple JSON blobs as normalised data but it is hard to make that as efficient as a guaranteed schema. Plus larger companies often end up having to hire a dedicated team of engineers to properly normalise data so other people can use it.

maweki 1y ago

Isn't the author just proposing GraphQL?

andyferris 1y ago
Not at all. GraphQL has the API servers (database clients) marshalling the data into heirarchies (nested data) and managing consistent results (e.g. streaming updates). The article is arguing that the database should be more capable of understanding queries that request nested data (and separately Jamie has spoken in favour of databases that encourage end clients getting up-to-date/internally-consistent results). My two cents is that it's a crutch having SQL rely on JSON instead of it's own first-class nested data (which would be any given value (field, aka cell) can itself be a relation - at least postgres has array values but they are challenging to index/use keys programmatically).

hbrundage 1y ago

I agree and would take it one step further — the structure of the joins is something that should most often come from the schema, not the query. In the same way that the attributes of an entity should be modelled out ahead of time, the relationships between the entities should be as well, and that yields more productive querying and better performance. We built a GraphQL / SQL hybrid expression language that does just this here: https://docs.gadget.dev/guides/data-access/gelly#gelly https://docs.gadget.dev/guides/data-access/gelly#gelly , curious what yall think.

reaanb2 1y ago
I disagree. From a fact-oriented modeling perspective, relationships among entities are already well-modelled and represented in tables. There's a reason the relational model calls tables relations - they relate things. The mistake here is viewing tables/rows as representing entities, FK constraints as representing relationships, and viewing entities as containers of attributes rather than the subjects of facts. Joining tables is composing complex facts from simple ones, and is the opposite of normalization which is decomposing complex facts into simpler ones. The ability to join tables on arbitrary conditions is fundamental to the ability to ask a DBMS complex questions and have it respond with all the facts that match that question.

stared 1y ago

I used to work bit with MongoDB (over 10 years ago) and it did wonders in making me fall back into love with relational databases. I finally saw the light of Codd! SQL, as a language is clunky, true. It can be patched here are there, either by PipeSQL or by various ORMs. I agree, that it would be wonderful to have standardized tooling for generating JSON like in the post. Yet, with relational databased you can separate concerns of: what is your data and what you want to display. If you use JSON-like way to store data, it can do the job until you want to change data or queries.

mrlongroots 1y ago
Agree: flat/relational structure is highly efficient to store, query planning/optimization/scaling become a lot easier, the language is clunky but relational algebra is beautiful, but... I think pushing multiple joins to what is still a relational database and getting a complex output isn't the worst idea in the world, as a higher-level layer on top of a regular database. On the other hand, "it needs four queries/RTTs" is not the worst thing in the world. It needn't be the goal of a system to achieve theoretical minimum performance for everything. Let those who truly have the problem in prod push the first patch.
arnsholt 1y ago
My stint with MongoDB was brief, but I too came away with a deeper appreciation of SQL bases. I feel it's a bit like a good type system: yes, there absolutely is an upfront cost, but over time it really does save you from stupid runtime problems. It's especially important when the bugs are in the data storage layer, because if a bug causes bad data to be written to your database, not only do you need to find and fix the bug, you also have to figure out how to deal with (possibly lots of) bad data. Also, modern SQL is an incredibly powerful language. Good SQL can save you from lots of dumb data munging code if you know how to wield your database properly. Especially for analytical queries, but also more typical application code IMO.
- anonymars 1y ago
  I have spent many years beating the drum that a few minutes spent on constraints today save many, many, many hours of painful data cleanup later And in fact, good constraints also can improve query performance. If the optimizer knows this column is unique, or that column is guaranteed to have a corresponding value in the joined table, it can do all sorts of tricks...

silon42 1y ago

I've seen 'decorellation' mentioned... 95% of the time I am fighting the query optimizer is to get the correlation and a loop over few items working instead of a table scan.

jbverschoor 1y ago

Time to start taking that those CS classes before blogging

kjfarm 1y ago
This response comes across as unnecessarily toxic and quip-y, which is a shame because there's real substance here. The author clearly understands database design, referencing concepts like normal forms and Boyce-Codd Normal Form (BCNF). Even if I don't agree with the conclusion, I have to respect anyone who takes the time to lay out their argument. Challenging ideas are what help our community grow.
kerbobotat 1y ago
Time to start proofreading your comments before posting.
eatonphil 1y ago
Guessing from the relevant masters thesis linked on his site and his time at materialize and tigerbeetle, Jamie's been working on databases for at least like 15 years? At least personally when Jamie says something I listen. :)

h4ch1 1y ago

I mean for highly connected data shouldn't the solution be to ideally use a graph database from the get go instead of generating JSON aggregates in the database? Don't get me wrong, JSONB is great but I see a lot of people simply slap on a JSON column to anything that requires more complexity without trying to first solve it through RDBMS relations.

nrjames 1y ago
I loaded this same IMDB dataset into a Kuzu DB the other day, just for fun. It was an interesting project but I would agree that this dataset in particular lends itself to a graph database. Kuzu started to choke with > 10M nodes & 10M edges but I was doing a naive insert & probably could fit the entire dataset in without trouble using their direct CSV import.
- zw17 1y ago
  Interesting points here. I’ve found the “graph DB vs. relational DB” discussion usually gets framed as an either/or, but there’s a middle ground. A lot of teams already have their data sitting in Postgres, Mongo, or a lakehouse. Spinning up a separate graph database just for traversals often means duplicating data, building pipelines, and keeping two systems in sync. That’s fine if you need deep graph algorithms, but for many workloads it’s overkill. What some folks are exploring now is running graph queries directly on top of their existing data, without having to ETL into a dedicated graph DB. You still get multi-hop traversal and knowledge graph use cases, but avoid the “yet another database” tax. So yeah...graph databases are great, but they’re not the only way to model or query graphs anymore.
zozbot234 1y ago
PostgreSQL makes a perfectly fine graph database, at least for general workloads. You're not going to get vastly improved performance from a specialized solution unless you're doing very specific things like math-heavy network analysis that PostgreSQL is still a bad fit for.

redwood 1y ago

This is the core reason why MongoDB is popular. And while the HN crowd fell into a trough of dissolutionment with it 10 years ago since it wasn't ready then, a bunch of folks found success with it more recently. At my company we had legacy implementations of it and modern ones and the juxtaposition was significant.

smohare 1y ago
I worked with MongoDB a decade ago and also now. One of the worst systems I’ve ever had to deal with. Not necessarily because of the db itself, but because of the implications of its use. It serves an incredibly narrow purpose. Everywhere I’ve worked at has had a team practically dedicated to moving data from a document store to a relational db so it could be actually usefully used for analytics and across disparate systems.

zigzag312 1y ago

I think data definition schema needs to have better type system which not only defines database schema, but contains all necessary data and allows annotations to be able to generate quality language specific code from it. Single source of truth for data structure, which is used both by database and code.

kevindamm 1y ago
I see some value in that, but in my experience it's actually the queries that inform much of what the table shema and indexes should look like. Especially when joins and/or ordering are involved, it's the details about what the application wants to extract from the DB that are important, and I'd rather have those details at the code's call site not at the schema definition, because if/when they change or are removed it will be clearer from the context.
- zigzag312 1y ago
  What I'm proposing doesn't change that. It's just to information necessary to keep strongly typed data in sync between the database and dtos. To help with serialization and mapping. Compile time type safety. When querying db for schema you don't get enough information to be able to generate very good code in all cases. Language-first way (define schema in language and generate database schema/migrations) ties you to just one language. And usually these tools don't support using all database features.

ivanb 1y ago

One limitation of JSON is its limited set of types. For example, for decimal numbers one has to resort to stringly typing representation because DB connection libraries assume that JSON numbers are floating point. Note that JSON numbers are just sequences of digits, nothing more. There is no attached precision semantic. Another example is UUIDs. Instead of transferring 16 bytes, the libraries deal with wasteful string representation. I'm sure you can bring another examples. Nonetheless, for majority of data JSON as DB output format is alright.

jamii 1y ago
Yeah, json is annoying because of the limited types, but postgres arrays/rows are annoying because of the lack of sum/union types (if your UI has a heterogenous list of elements). The OLAP world has much nicer type systems eg https://duckdb.org/docs/stable/sql/data_types/union.html https://duckdb.org/docs/stable/sql/data_types/union.html.

phiresky 1y ago

I think relational databases are great, but this is my biggest problem with SQL, even before the ridiculous syntax. A query like select users.*, orders.* from users left join orders on orders.user_id = users.id Should always have returned a structure like: type SingleReturnRow = { users: {id: ..., }[], orders: {user_id: ..., id: ..., }[]} type Return = SingleReturnRow[] Mangling the columns together and _removing_ groupings that naturally appear is just so unnecessary. I don't think a larger change in the query language would even be needed. Even better of course would be a return value like type SingleReturnRow = { user: User, orders: Order[] } But I see how that would require a fundamental change in the language. Of course in PG now you can use select users.*, json_agg(orders.*) as orders from users left join orders on orders.user_id = users.id group by users.id but using JSON as intermediate steps just feels unnatural.

reaanb2 1y ago
See https://arxiv.org/abs/2312.00638 https://arxiv.org/abs/2312.00638 for one proposal to address this
johannes1234321 1y ago
It follows the relational algebra model. Relations (aka Tables) go in, relations (aka Tables) come out. This makes some things really nice. However I proposed a hierarchical result for such cases a long time to our database, but couldn't convince enough people. json_agg came later at there all the machinery is there, it would "just" require exposing this to the protocol and adapting all clients to understand that data format ...
- refset 1y ago
  The world deserves something better than json_agg. For XTDB we coined NEST_ONE and NEST_MANY: https://xtdb.com/blog/the-missing-sql-subqueries https://xtdb.com/blog/the-missing-sql-subqueries The output format is either raw Arrow DenseUnions (e.g. via FlightSQL) or Transit via a pgwire protocol extension type.
- andyferris 1y ago

mcdonje 1y ago

This was enlightening. I've often wondered why ORMs exist, and it turns out a bunch of devs don't understand tabular data. I was going to say they don't understand SQL, but apparently it goes deeper than that. Like, left joins have been around since SQL-92, so the query not returning a result when there is no director is a skill issue. Nowadays, you can run a JSON or XML query and have the DB return your results in a hierarchical format.

deleted 1y ago
[deleted]
kragen 1y ago
The author of this blog post writes in https://www.scattered-thoughts.net/writing/reflections-on-a-decade-of-coding/ https://www.scattered-thoughts.net/writing/reflections-on-a-...: > - In a small team (averaging ~3 people, I think?) in a little under a year, we built a SQL query planner that passed the >6 million tests in the sqlite logic test suite. > - I figured out how to decorrelate arbitrary SQL subqueries (I wasn't the first person to figure this out, but I got there independently). > - I demonstrated a mode of consistency failure in various popular streaming systems that I believe was not widely understood at the time. I think it is likely that the reason you disagree with him is not that he "don't understand tabular data" and has "a skill issue". Unless you're secretly Michael Stonebraker? You write: > Nowadays, you can run a JSON or XML query and have the DB return your results in a hierarchical format. You may not have noticed this, but the post you are commenting on explains how to run a JSON query and have the DB return your results in a hierarchical format.
- mcdonje 1y ago
  My take was charitable because if he knows better, then he's making a bad faith argument, and I didn't want to accuse him of that. I didn't read beyond the article. I only responded to what was in the post. He didn't use capabilities SQL has had for decades, made a bad query, and used that to build his argument about how bad SQL is. The ORM tables could've been written in one query that returns a tabular format of hierarchical data, which the front end could use. What does that look like? It looks like the kind of underlying tabular format JSON & XML queries require. He didn't do that with his non-JSON examples. Yes, the framework would need to turn it into an object. Yes, there is data duplication in some fields. While not ideal, it's still better than the ORM example because you're not splitting it up into multiple queries. His JSON example undercuts his main argument. The DB is fully capable of returning the representation he wants. It's also more complex than necessary with all the JSONB_AGGs in the subqueries. Then he says you can see the duct tape. So yeah, it reads like a front end person who never really learned modern SQL. Or maybe it's someone who knows better who is making a bad faith argument. Either way, it's just a bad take.

Spixel_ 1y ago

I don't get the transaction bit. At least with postgres, a transaction doesn't guarantee that all statements in it see the data at the same point in time (actually, it's not even guaranteed for subqueries). Also, often, the transactional database servers is more difficult to scale than application servers so from a technical standpoint, it makes sense to do this glue work in app code.

TheTaytay 1y ago
Doesn’t it guarantee consistency from the time the transaction started (assuming read committed isolation)? It guarantees you won’t see something “later” than when your transaction began. https://www.postgresql.org/docs/current/transaction-iso.html https://www.postgresql.org/docs/current/transaction-iso.html I’m likely misunderstanding what you mean by time.
- Spixel_ 1y ago
  Read committed (which is the default), doesn't guarantee that. See "Nonrepeatable Read" and "Phantom Read" which are both possible in your documentation page.
andyferris 1y ago
> a transaction doesn't guarantee that all statements in it see the data at the same point in time This depends on the transaction isolation level. If you use snapshot or serializable this should be the case (but you may have aborted transactions due to optimistic concurrency).
- Spixel_ 1y ago
  You are right, but note that the default isolation level is "Read committed" in postgres.
Jweb_Guru 1y ago
SSI scales fine for most workloads as long as you correctly mark your read-only transactions.

mrkeen 1y ago

The author didn't see fit to mention that you just flip the arrows. Classes/Structs know about their children. Relations know about their parents. If you want crazier m*n relationships you use an association table. Did the author just not know? Or he didn't see it worthy of dismissal? > Doing this transformation by hand is tedious and error-prone. We call this tedium "the object-relational mismatch" but it isn't really about objects or relations. It really is. > The fundamental problem is that fitting complex relationships to human vision usually requires constructing some visual hierarchy, but different tasks require different hierarchies. Yes. Different tasks require different hierarchies. One particular way of doing things should not baked into your 1970s relational model.

andyferris 1y ago

Yes - I have to agree. Codd was right in that if you want transactional semantics that are both quick and flexible, you'll need to _store_ your data in normalized relations. The system of record is unwieldly otherwise. The article is right that this idea was taken too far - queries do not need to be restricted to flat relations. In fact the application, for any given view, loves heirarchical orginization. It's my opinion that application views have more in common with analytics (OLAP) except perhaps latency requirements - they need internally consistent snapshots (and ideally the corresponding trx id) but it's the "command" in CQRS that demands the normalized OLTP database (and so long as the view can pass along the trx id as a kind of "lease" version for any causally connected user command, as in git push --force-with-lease, the two together work quite well). This issue is of course that SQL eshews hierarchical data even in ephemeral queries. It's really unfortuante that we generate jsonb aggregates to do this instead of first-class nested relations a la Dee [1] / "third manifesto" [2]. Jamie Brandon has clearly been thinking about this a long time and I generally find myself nodding along with the conclusions, but IMO the issue is that SQL poorly expresses nested relations and this has been the root cause of object-relation impedence since (AFAICT) before either of us were born. [1] https://github.com/ggaughan/dee https://github.com/ggaughan/dee [2] https://www.dcs.warwick.ac.uk/~hugh/TTM/DTATRM.pdf https://www.dcs.warwick.ac.uk/~hugh/TTM/DTATRM.pdf

reaanb2 1y ago
In my view, the O/R impedance mismatch derives from a number of shortcomings. Many developers view entities as containers of their attributes and involved only in binary relationships, rather than the subjects of n-ary facts. They map directly from a conceptual model to a physical model, bypassing logical modeling. They view OOP as a data modeling system, and reinvent network data model databases and navigational code on top of SQL.
- mcphage 1y ago
  In that case, the mismatch is between "What developers need" and "What SQL provides".
  - 1y ago

kragen 1y ago

An interesting thing about "A Relational Model of Data for Large Shared Data Banks" (the stone tablets handed down from Codd https://www.seas.upenn.edu/~zives/03f/cis550/codd.pdf https://www.seas.upenn.edu/~zives/03f/cis550/codd.pdf) is that it starts out by considering N-ary relations whose values may themselves be relations, and only then switches to considering "normalized" relations whose values are not relations: > A relation [table] whose domains [column types] are all simple [not relations] can be represented in storage by a two-dimensional column-homogeneous array of the kind discussed above. Some more complicated data structure is necessary for a relation with one or more nonsimple domains. For this reason (and others to be cited below) the possibility of eliminating nonsimple domains appears worth investigating.⁴ There is, in fact, a very simple elimination procedure, which we shall call normalization. But non-normalized relations support the kind of nested structure the eminent Dr. Brandon wants, without resorting to JSON or abandoning the strong uniform typing we have with SQL. Darwen & Date's The Third Manifesto https://www.dcs.warwick.ac.uk/~hugh/TTM/DTATRM.pdf https://www.dcs.warwick.ac.uk/~hugh/TTM/DTATRM.pdf includes group and ungroup operations which translate back and forth between normalized and non-normalized relations. I've been playing around with some of these ideas using some rather horrific atrocities perpetuated against Lua operator overloading in http://canonical.org/~kragen/sw/dev3/cripes.lua http://canonical.org/~kragen/sw/dev3/cripes.lua. I haven't added joins or ungrouping to it yet, but I think it might be a prototype of something promising for this kind of use case.

gethly 1y ago

I am using event sourcing with cqrs and that means i also utilise projections, which the db itself can replicate less efficiently as a "view" or virtual table. So one can technically create a projection/view that is tailor-made for a query that needs to display some data. Of course it is no often possible to retrieve all the data with a single select command. So joins and multiple queries are simply inherent to complexity of data we store nowadays. Anyway, years ago, i have moved to a db model where every entity is stored as a blob in a dedicated column and every additional column, beside id, is indexed. So there is no wasted space and a ton of columns that only hold data but are not used for filtering. I can run data-efficient queries that yield a list of ids of blobs to load or the blobs themselves and then i extract any necessary data out of those blobs(entities) on the application level. So the database us purely a blob store + few fast indices.

hdjrudni 1y ago
I'm sure it does work well if the database is perfectly tailored to your app. I'm skeptical that it can handle every new use case that might come up -- i.e. if you need to query for something hidden in a blob. Sounds precisely like NoSQL. It works until it doesn't.
- gethly 1y ago
  If new index is needed, you can just add it and rebuild the projection, it's no problem.
kragen 1y ago
What's your application?
- gethly 1y ago
  I use it on gethly.com and previously i used the same approach on an internal application for domain registrar that handles the EPP. So far no issues. Also using those blobs allows me to easily add encryption layer on application level.

ThinkBeat 1y ago

Would this not become a bit simpler if you populate the sql schema like this? +-------------------+ | | | | +------------------+ | Genere | | | | | | | +-------------------+ | Movies | | | | | +-------------------+ | | | | | | | Actors | +------------------+ | | | | +-------------------+ +

vbilopav 1y ago

What an awful query example! This should and is simple. Here: select jsonb_build_object( 'title', title, 'genres', genres, 'actors', json_agg( jsonb_build_object( 'name', actor_name, 'characters', actor_characters ) ), 'directors', directors, 'writers', writers ) from ( select t.primaryTitle as title, t.genres, actor_person.primaryName as actor_name, array_agg(pc.character) as actor_characters, array_agg(director_person.primaryName) as directors, array_agg(writer_person.primaryName) as writers from title t left join principal actor on t.tconst = actor.tconst and actor.category = 'actor' left join person actor_person on actor.nconst = actor_person.nconst left join principal_character pc on actor.nconst = pc.nconst and actor.tconst = pc.tconst left join principal director on t.tconst = director.tconst and director.category = 'director' left join person director_person on director.nconst = director_person.nconst left join principal writer on t.tconst = writer.tconst and writer.category = 'writer' left join person writer_person on writer.nconst = writer_person.nconst where t.tconst = 'tt3890160' group by t.tconst, actor_person.primaryName, t.primaryTitle, t.genres, actor.ordering, director_person.primaryName, writer_person.primaryName order by actor.ordering asc ) main group by title, genres, directors, writers; Here: https://i.postimg.cc/zB1Bgg1L/movies.png https://i.postimg.cc/zB1Bgg1L/movies.png