denormalizationnormalizationscalability
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?
It seems you are asking if denormalization makes sense.
Denormalization is the process of attempting to optimize the read performance of a database by adding redundant data or by grouping data. In some cases, denormalization helps cover up the inefficiencies inherent in relational database software. A relational normalized database imposes a heavy access load over physical storage of data even if it is well tuned for high performance.
The answer is always "it depends", so here's my rule of thumb:
If ...
then stay normalized. Yes, denormalization is faster, but it also means you have redundant data in the system -- data that has to be maintained and kept in sync. There is no longer "one source" for that data, but multiple sources that can deviate. This is risky over time, so you shouldn't do it unless you have very good reasons to do it, backed by some benchmarks.
I would only denormalize when ...
Joins are very fast on modern hardware, but they are never free.
You can try creating something like this (please excuse the crudity of the image):
Best Answer
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
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:
Extended Reading: