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 _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!