Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

I stopped worrying and learned to love denormalized tables

glean.io

Post reply on HN
Print view View original

31–40 of 100 posts

Re: I stopped worrying and learned to love denormalized tables

#31

Post by kqr »

roenxi wrote: post #12

Well... 1) Normalisation at all costs is foolish - if the cost exceeds the value, then don't do it. That isn't complicated. Denormalised data sometimes points at design flaws, but even then all systems have design flaws and they don't automatically need to be fixed. Quality is expensive, like every other property (even doing things the cheap way is expensive, ironically - software is all about managing costs). 2) For…

> if the cost exceeds the value, then don't do it. That isn't complicated.

Could you try to elaborate on what you mean by "cost" and "value"? The complicated part is not the part you said, but specifying those two very abstract terms.

Re: I stopped worrying and learned to love denormalized tables

#32

Post by kqr »

Fire-Dragon-DoL wrote: post #28
roenxi wrote: post #12

Well... 1) Normalisation at all costs is foolish - if the cost exceeds the value, then don't do it. That isn't complicated. Denormalised data sometimes points at design flaws, but even then all systems have design flaws and they don't automatically need to be fixed. Quality is expensive, like every other property (even doing things the cheap way is expensive, ironically - software is all about managing costs). 2) For…

I kinda observe this from a different point of view: what are the use cases needed for the data and what are the queries that could satisfy those? The dynamodb book was enlightening from this point of view even for designing sql databases where we normalize data as much as possible no matter what.

Which DynamoDB book? It's very relevant to my interests.

Re: I stopped worrying and learned to love denormalized tables

#33

Post by oldelpaso66 »

Fire-Dragon-DoL wrote: post #28
roenxi wrote: post #12

Well... 1) Normalisation at all costs is foolish - if the cost exceeds the value, then don't do it. That isn't complicated. Denormalised data sometimes points at design flaws, but even then all systems have design flaws and they don't automatically need to be fixed. Quality is expensive, like every other property (even doing things the cheap way is expensive, ironically - software is all about managing costs). 2) For…

I kinda observe this from a different point of view: what are the use cases needed for the data and what are the queries that could satisfy those? The dynamodb book was enlightening from this point of view even for designing sql databases where we normalize data as much as possible no matter what.

Hi there, when you say the dynamodb book, do you mean https://www.dynamodbbook.com/?

Re: I stopped worrying and learned to love denormalized tables

#34

Post by berkle4455 »

sublinear wrote: post #24
hakunin wrote: post #6

While this talks mostly about data warehousing, oftentimes denormalization is useful for everyday web app data storage. If your web app (usually on Postgres) is mostly frequent reads and rare writes (most web apps are) — there's no excuse for your pages to load slower than a static site. Store your data as normalized as you want, add a denormalized materialized view, update it on writes, render pages based on the vie…

> there's no excuse for your pages to load slower than a static site. Indeed all web pages should be static pages. Anyone still doing server-side rendering in 2023 and defending it for any use case at all needs to turn in their badge. Same goes for people promoting frameworks like react or vue for anything but sufficiently complex web apps.

Uh, what? Let’s take this very site. How do static pages work for logged-in users?

Re: I stopped worrying and learned to love denormalized tables

#35

Post by samtho »

Given that initially defining your db schema is a one-time thing and we generally don’t change it very often after, I can’t fully get behind what the article suggests. However, the one thing I tend to do is add a text or json field called “extra” to my main tables that just stores a JSON map with fields I want to add to the record but don’t need to necessarily query by.

Re: I stopped worrying and learned to love denormalized tables

#36

Post by kqr »

noduerme wrote: post #21
MilStdJunkie wrote: post #4

For the third time this week, in relatively unrelated fields of computation science, I'm reminded of the quote: "Duplication is less expensive than the wrong abstraction". An awful lot of the time, a table schema is a terrible abstraction of the actual series it is designed to record. Sometimes it's designed under constraints that exist only to self-sustain the abstraction. Some of them have viable reasoning, some do…

> the real world doesn't have schemas This is simply incorrect. The real world has business logic, and that's ironclad (or should be, for a successful business, assuming you're not acquired at which point start the business data schema over). Where business logic has exceptions, those exceptions should be accommodated in the schema. But there should be no daylight between how the business works and how the schema rec…

I don't know what you mean by business logic but the (in my view successful) businesses I have seen operate more on the highly variable whim of upper management than any sort of ironclad logic.

You notice this quickly when you computerise existing human processes. They are riddled with (sometimes very valuable!) inconsistencies that are hard to fit into the regular computer mold some designer thought was sensible.

Re: I stopped worrying and learned to love denormalized tables

#37

Post by roenxi »

kqr wrote: post #31
roenxi wrote: post #12

Well... 1) Normalisation at all costs is foolish - if the cost exceeds the value, then don't do it. That isn't complicated. Denormalised data sometimes points at design flaws, but even then all systems have design flaws and they don't automatically need to be fixed. Quality is expensive, like every other property (even doing things the cheap way is expensive, ironically - software is all about managing costs). 2) For…

> if the cost exceeds the value, then don't do it. That isn't complicated. Could you try to elaborate on what you mean by "cost" and "value"? The complicated part is not the part you said, but specifying those two very abstract terms.

In the case of this analyst, say every couple of days he is writing SELECT * FROM events JOIN projects. Everyone is asking him for information about events and projects.

He's going to save time and make less mistakes if he uses DBT and has a denormalised tables that merges events and projects. However you can think of to define costs and benefits, it'll turn out to be a good choice. As he discovered in the article. Good move. People keep asking him to do it, there is probably value there. He is avoiding a cost which is writing the same join over and over again.

Normalisation is there to make data accessible for multiple different users (analysts, application programmers, infrastructure teams, people who want to leverage the database for a new purpose, etc). It isn't good at servicing any specific need, but it is a basic and general data layout that lets people tailor the data to their needs quickly. When there is a specific user, they should always be asking if the normalised data layout is helpful and looking for opportunities to avoid writing the same JOIN in 20 different queries. As long as the source of truth is in normal form it is reasonable practice to denormalise for specific use cases.

DBT does this really well I might add - it encourages normalising the source of truth and then denormalising the data by an analytics team to meet business needs. The ideas there are strong, flexible and encourage good practices. Analysts love big flat tables, they are easy to work with.

Re: I stopped worrying and learned to love denormalized tables

#38

Post by tlarkworthy »

You have to denormalize in very common cases even for mostly OLTP workloads in order to get sortable data into an index. Consider a cloud storage product with folders connecting with a many to one to documents. The product wants to display the most recently used folders ordered by their inner document modification date.

Because composite indexes commonly can't span tables, you have to push the last modification date up to the folder row in order to get the data in the right place to build the obvious index.

Denormalization is a normal and expected optimization to scale a relational database.

Re: I stopped worrying and learned to love denormalized tables

#40

Post by myrryr »

noduerme wrote: post #20

Okay, so someone who analyzes reads, who's never written software that needs consistent writes, is in favor of denormalized data. Let's see how this post updates in ten years with parts 4 thru 9 where they go from realizing their data is inconsistent to writing some monstrous beast to try to normalize it.

This really does remind me of that meme with the bell curve.

On the far left and right side, there is a person saying "denormalized data is great"

It is just this one in the middle which doesn't like it.

After doing pretty major projects for 40 years, let me tell you, "denormalized data is great"

It is like watching people go down the hole of "patterns for everything" and then watching them crawl back out of it again many years later.

Post reply on HN