Hacker News Reader

    but queries are supposed to denormalize

Well, that's how they've always worked, yes.

Might there be a better way for some use cases?

    denormalize to present information in the desired format

What if my "desired format" is a structure that maintains the relationships specified in the database?

This is an incredibly common pattern, particularly with ORMs.

    # psuedocode
    for each recipe in Recipe.joins(Ingredient).select_all() do 
       for each ingredient in recipe.ingredients do 
          print(f"{recipe.name} requires {ingredient.name}")

Depending on the ORM and the query options specified, this results in one of the following:

1. Everything getting denormalized into a single, wasteful result table and then teased back out into Recipes and Ingredients by the ORM

2. Two SQL queries. One to fetch the recipes, and then one to fetch the ingredients once we know the id's of the recipies.

3. A big ol' N+1 situation with 1 query to fetch recipes and then N additional queries to fetch the ingredients.

They all have various inefficiencies. What if we just returned the relational structures directly?

What's wrong with option 2? This seems very effective, more than enough performant for 99% of use cases, and it seems the most straightforward to me.

Two things:

1. You have given up on any sort of consistency. 2. The database implementation will never be able to optimize a lookup workload which is pretty close to pessimal. Not caring about 99% is only viable if can actually avoid the other 1%.

It's usually fine, but could it be better?

I feel like this entire discussion, and the linked article, are overflowing with answers to your question.

Don’t use ORM for performance critical DB operations and write SQL by hand might be another answer. Case in point: I recently improved performance of my email app by about 250x by replacing SwiftData with native SQLite database (and manual sql queries). The message data is under 1M rows range plus a lot of blobs/attachments (under 10MB each).

Adding ORM on top of SQL database seems so backwards. What's the point of using SQL database at that point?

Availability of standardized infrastructure and a broad labour pool that can manage it.

> Data should be stored as normalized as possible, but queries are supposed to denormalize to present information in the desired format.

But the desired format usually isn't a square, flat table. Usually the data has structure and you want to preserve that structure when you're displaying, not just having it hidden in the database. E.g. you might want to show a heading for each professor and then a list of classes taught by that professor. Getting back a flat table with n duplicate copies of the professor's information is a step backwards.

> Data should be stored as normalized as possible, but queries are supposed to denormalize to present information in the desired format.

The desired format depends on the application.

At this point a substantial portion of all SQL queries are generated by (and the results consumed by) ORMs.

For that use case having results that include Products and Categories separately (so you can instantiate Product and Category objects) is more useful than a single table.

This. If your query returns a database rather than denormalized information? Guess what, you now get to query that returned database.

Honestly, it sounds like they haven't really grasped the purpose of relational databases.

If the transfer time was really slow, I could see wanting to return a subset of data without duplication, and then allow the application to query locally among that data for all the various things it needs.

That seems so unlikely that I'd think a distributed sqlite database would be a better idea instead, and just have it sync up when it can, and always query local.

> And if you're dealing with what would be an overly-large amount of repeated values, you just run multiple queries yourself instead of one.

It is a bit annoying to do this though. It would be nice if it was done for me automatically be some db driver.

> And if round-trip latency is some kind of issue with running queries sequentially, you can always issue queries in parallel instead.

Some languages or frameworks don’t provide great parallel ization features though.

I guess at the end of the day if it’s an extension to SQL that you can optionally use, then I would have some situations where I would use it.

I think the point is if you move that denormalisation to the application (the ORM say) then it's less data on the wire.

Similarly keeping schema information gives the ORM more to work with I suppose? Helpful for typing, foreign key links where it's not necessarily the same original table structure, but in the query result professor_id is still ultimately a foreign key to professor table, etc.

Less data on the wire and a consistent snapshot at a given point in time, without the possible inconsistencies allowed by performing successive queries with intervening writes.

> Data should be stored as normalized as possible, but queries are supposed to denormalize to present information in the desired format.

Nit: data should be stored as normalized as possible, if your use-case prioritizes fast writes over fast reads. There are other benefits to normalization, but in practice they pale in comparison to the performance shift.