So I sometimes see databases purposely add redundant information for faster querying. I saw that this was mentioned here but I was wondering when would this be better than indexing, or creating a view? Or perhaps it is never better?

I know there are definitely scaling issues with adding this redundancy, but is there a time when it is more ideal to sacrifice scalability for immediate speed gains?

  • 2
    Every redundancy made for the purpose of improving the performance of one specific query will slow down all other queries, inserts and updates against the same data. Only when you have actually measured, in a production-size environment, that the performance gains of the denormalization outweigh the associated degradation should you implement the deormalization in production. In practice this can only occur when the transaction count is very low, as almost all inserts and updates are made in bulk from transaction created elsewhere. – Pieter Geerkens Aug 21 '14 at 1:05
  • This is a YMMV situation. Denormalisation is considered on a case-by-case basis, because very case is different. – Greenstone Walker Aug 21 '14 at 1:16
  • When you are designing a data warehouse – Neil McGuigan Aug 21 '14 at 2:00
  • It is appropriate whenever you need to. And by "need to", I mean only when you've rigorously tested against a copy of production, ideally under production-like load, and those tests prove the advantage. Most likely, if your system in question is an OLTP system you'll not find it particularly useful to denormalize. – Max Vernon Aug 21 '14 at 3:55
  • 2
    In some ways this is similar to the natural/surrogate key debate: dba.stackexchange.com/questions/50708/… Denormalizing will tend to decrease the row-count-per-page which can offset all the performance gains there alone. – Max Vernon Aug 21 '14 at 3:58

It seems that denormalization is done on a case-by-case basis. If you do decide to denormalize your tables for efficiency, make sure you are thoroughly testing your queries. As @Pieter Geerkens mention

Every redundancy made for for the purpose of one specific query will slow down all other queries, inserts, and updates against the same data.

That is denormalizing while may speed up some gains, there are very huge costs in not only speeds of other queries, but also in scalability. It's important to take these all into consideration along with other ways to improve performance.

In short, I'll end it with a quote from Chris Date who wrote the book Database in Depth: Relational Theory for Practitioners:

I believe firmly that anything less than a fully normalized design is strongly contraindicated ... [Y]ou should "denormalize" only as a last resort. That is, you should back off from a fully normalized design only if all other strategies for improving performance have somehow failed to meet requirements.

Extended Reading:

  1. Scaling Secret #2: Denormalizing Your Way To Speed And Profit
  2. Database War Stories #3: Flickr

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.