5 ms·

I have had pretty good results over the years following the mantra “normalize until it hurts, denormalize until it works”. Start with as little redundancy as po

I have had pretty good results over the years following the mantra “normalize until it hurts, denormalize until it works”. Start with as little redundancy as possible, then carefully consider anything you want to denormalize and plan around the complexity it introduces. Materialized views can also help a lot with this. The main issue I’ve run into following this approach has been with ORMs that don’t provide nice raw sql escape hatches–many times something you’ll jump to denormalize is easily solved with a better query, if your tools let you do so.

mjevans 3y ago
The mantra is a good reminder but poor at explaining the why. It's sort of like lego blocks or pre-fabricated wall / roof sections for homes. Fully normalized everything is broken down to the smallest possible unit of data (a value) that can be accessed by a key. One 'key' might be happen to be the same (shared) among many different kinds of values, in different tables. That's like taking something apart until you have individual lego pieces or beams of lumber. This is a good logical 'what if' step to help someone ask if data __really__ belongs together. Does the data really belong together? If a query wants (id, x) how often does it also want (id, y) or (id, z) etc. These pieces should be combined to larger tuples / object fragments if frequently accessed together. De-normalize returns the true data to a format that is efficient for the system and it's use cases by preparing what's most commonly going to be seen together in that form.
- _a_a_a_ 3y ago
  > De-normalize returns the true data to a format that is efficient for the system How many times do I have to debunk this crap. Wrong. It's not that simple. Stop repeating this garbage.
  - mjevans 3y ago
    Please explain how it's wrong __in the context__ where 'system' is the entirety of the database and consumers (applications / etc) of the data in that database producing results? Note the qualifying portion you truncated, "by preparing what's most commonly going to be seen together in that form." > Does the data really belong together? If a query wants (id, x) how often does it also want (id, y) or (id, z) etc. These pieces should be combined to larger tuples / object fragments if frequently accessed together. > De-normalize returns the true data to a format that is efficient for the system and it's use cases by preparing what's most commonly going to be seen together in that form.
    - _a_a_a_ 3y ago
      https://news.ycombinator.com/item?id=34182803 https://news.ycombinator.com/item?id=34182803
      - mjevans 3y ago
        Re: your comment (inlined as if you'd copied it verbatim in your reply rather than providing a comment ID key reference) = https://news.ycombinator.com/item?id=34182803 https://news.ycombinator.com/item?id=34182803 _a_a_a_ 5 months ago > aaand here we go again. > DB guy with 25+ years experience. Summary: it depends. >> joins are never cheap > it depends. On table size, indexes/table size vs how expensive the alternative is. Always! >> tables with billions of rows crossed with millions of rows just to find a single row with data is not something i would call cheap > indexes >> more often than not it is better to avoid joining large tables if you can live with duplicate data > 1E9 x 1E6 = 1E15 (at worst anyway). A join via an index will save you colossal amounts of IO (though as ever, it depends). > Problem here isn't this mostly clueless advice (discarding/archiving unnecessary data is the only good idea here, and it's not used as often as it should be). Problem is strong opinions put forth by someone who doesn't have the necessary experience, or understanding of what's going on under the hood. Denormalising is a useful tool that IME rarely gains you more than it loses you, but this 'advice' is just going to lead people down the wrong alley, and I'm tired of suchlike n00b advice strongly (and incorrectly and arrogantly) expressed on HN. > (edited to fix maths error) First, your target audience is humans reading this forum. It is better both for the flow of the conversation and for digital preservation to JUST COPY IT AGAIN. Even if it's an exact quote, it saves everyone the bother of loading an old comment from outside of our current work context. That doesn't advocate for the duplicate data (in a database) that you mention in your post. We (as humans) aren't accessing this like a database following a query plan, we're parsing it as a tree structured conversation. This is also exactly the sort of thing I was talking about with 'de-normalizing' the data structure back towards that's optimal for the overall system as a whole and maybe what you're missing as the forest for the trees. Maybe we're even trying to say the same thing, but the words sound too similar to someone who doesn't get it the same way you do for that to come across clearly as being the same idea, just stated differently. Your nebulous ~~ 'it depends' ... ''' examine what the different costs and alternatives are ''' always ~~ (paraphrased slightly) is very similar to what I said as "... data to a format that is efficient for the system and it's use cases by preparing what's most commonly going to be seen together in that form." Though where we differ is on the duplicate data issue. Duplicate data might be tolerable for extremely read and LOW COHERENCY pressure environments. An example that comes to mind is email, or an eventual consistency acceptable stream of changes such as a large filesystem with mostly long lived files, some temporary / growing files, and new files, along with some sort of change set that can accommodate versioned file references and update operations. A bank account with a lot of headroom and mostly small transactions might also be a good case. All three examples work much more poorly where ACID compliance might matter, such as when there isn't that headroom and where the exact sequence of operations matters for system / organizational rules. Even in systems where materialized views (an in database type of cache on a view) are usable, it still makes sense to figure out what the most frequent and maybe (if it matters, it might not if it's a report run once a day that's still fast enough!) the worst performing queries want from the database and group the data in a way that logically works best for the relevant use cases, rather than the formal and ideological fully normalized representation of the data. The more general advice of break it all down (fully normalize in theory) then built it back up (de-normalize) gives what's often a good enough first approximation to the solution. Both of us, probably the GP poster, and probably most of HN readers, agree: measure and figure out what's important!

mjevans 3y ago

The mantra is a good reminder but poor at explaining the why. It's sort of like lego blocks or pre-fabricated wall / roof sections for homes. Fully normalized everything is broken down to the smallest possible unit of data (a value) that can be accessed by a key. One 'key' might be happen to be the same (shared) among many different kinds of values, in different tables. That's like taking something apart until you have individual lego pieces or beams of lumber. This is a good logical 'what if' step to help someone ask if data __really__ belongs together. Does the data really belong together? If a query wants (id, x) how often does it also want (id, y) or (id, z) etc. These pieces should be combined to larger tuples / object fragments if frequently accessed together. De-normalize returns the true data to a format that is efficient for the system and it's use cases by preparing what's most commonly going to be seen together in that form.

_a_a_a_ 3y ago
> De-normalize returns the true data to a format that is efficient for the system How many times do I have to debunk this crap. Wrong. It's not that simple. Stop repeating this garbage.
- mjevans 3y ago
  Please explain how it's wrong __in the context__ where 'system' is the entirety of the database and consumers (applications / etc) of the data in that database producing results? Note the qualifying portion you truncated, "by preparing what's most commonly going to be seen together in that form." > Does the data really belong together? If a query wants (id, x) how often does it also want (id, y) or (id, z) etc. These pieces should be combined to larger tuples / object fragments if frequently accessed together. > De-normalize returns the true data to a format that is efficient for the system and it's use cases by preparing what's most commonly going to be seen together in that form.
  - _a_a_a_ 3y ago
    https://news.ycombinator.com/item?id=34182803 https://news.ycombinator.com/item?id=34182803
    - mjevans 3y ago
      Re: your comment (inlined as if you'd copied it verbatim in your reply rather than providing a comment ID key reference) = https://news.ycombinator.com/item?id=34182803 https://news.ycombinator.com/item?id=34182803 _a_a_a_ 5 months ago > aaand here we go again. > DB guy with 25+ years experience. Summary: it depends. >> joins are never cheap > it depends. On table size, indexes/table size vs how expensive the alternative is. Always! >> tables with billions of rows crossed with millions of rows just to find a single row with data is not something i would call cheap > indexes >> more often than not it is better to avoid joining large tables if you can live with duplicate data > 1E9 x 1E6 = 1E15 (at worst anyway). A join via an index will save you colossal amounts of IO (though as ever, it depends). > Problem here isn't this mostly clueless advice (discarding/archiving unnecessary data is the only good idea here, and it's not used as often as it should be). Problem is strong opinions put forth by someone who doesn't have the necessary experience, or understanding of what's going on under the hood. Denormalising is a useful tool that IME rarely gains you more than it loses you, but this 'advice' is just going to lead people down the wrong alley, and I'm tired of suchlike n00b advice strongly (and incorrectly and arrogantly) expressed on HN. > (edited to fix maths error) First, your target audience is humans reading this forum. It is better both for the flow of the conversation and for digital preservation to JUST COPY IT AGAIN. Even if it's an exact quote, it saves everyone the bother of loading an old comment from outside of our current work context. That doesn't advocate for the duplicate data (in a database) that you mention in your post. We (as humans) aren't accessing this like a database following a query plan, we're parsing it as a tree structured conversation. This is also exactly the sort of thing I was talking about with 'de-normalizing' the data structure back towards that's optimal for the overall system as a whole and maybe what you're missing as the forest for the trees. Maybe we're even trying to say the same thing, but the words sound too similar to someone who doesn't get it the same way you do for that to come across clearly as being the same idea, just stated differently. Your nebulous ~~ 'it depends' ... ''' examine what the different costs and alternatives are ''' always ~~ (paraphrased slightly) is very similar to what I said as "... data to a format that is efficient for the system and it's use cases by preparing what's most commonly going to be seen together in that form." Though where we differ is on the duplicate data issue. Duplicate data might be tolerable for extremely read and LOW COHERENCY pressure environments. An example that comes to mind is email, or an eventual consistency acceptable stream of changes such as a large filesystem with mostly long lived files, some temporary / growing files, and new files, along with some sort of change set that can accommodate versioned file references and update operations. A bank account with a lot of headroom and mostly small transactions might also be a good case. All three examples work much more poorly where ACID compliance might matter, such as when there isn't that headroom and where the exact sequence of operations matters for system / organizational rules. Even in systems where materialized views (an in database type of cache on a view) are usable, it still makes sense to figure out what the most frequent and maybe (if it matters, it might not if it's a report run once a day that's still fast enough!) the worst performing queries want from the database and group the data in a way that logically works best for the relevant use cases, rather than the formal and ideological fully normalized representation of the data. The more general advice of break it all down (fully normalize in theory) then built it back up (de-normalize) gives what's often a good enough first approximation to the solution. Both of us, probably the GP poster, and probably most of HN readers, agree: measure and figure out what's important!