IB Computer Science Topic A3.2.7: Evaluating the Need for Denormalizing Databases Notes

Denormalization: Trading Data Integrity for Read Speed

1. Denormalization combines tables or duplicates data on purpose, so that a common query needs fewer joins.
2. It is a step taken after a design has been normalized, once a measured performance problem has appeared.
3. The command term for A3.2.7 is Evaluate, so both sides of the trade have to be weighed and a judgement reached.

What Normalization Gives You, and What It Costs

1. Advantages of a normalized design
   1. Each fact is stored once, so it cannot be updated in one place and left stale in another.
   2. Insert, update and delete anomalies are removed, which makes writes simple and safe.
   3. Storage is smaller, and the structure adapts to new requirements without a redesign.
2. Disadvantages of a normalized design
   1. Data about one thing ends up spread across several tables, so answering a simple question needs several joins.
   2. Each join costs processing time, and the cost grows as the tables grow.
   3. The queries themselves get longer and harder to write and maintain.

Advantages of Denormalization

1. Faster data retrieval
   1. Storing related data together removes joins from the query, and a query with no joins reads one set of pages instead of several.
   2. The gain is largest where the same expensive query runs constantly, such as the page that loads on every visit.
2. Simpler queries
   1. With fewer tables to join, queries are shorter to write, easier to read and less likely to contain a join error.
3. Predictable load
   1. A pre-combined table gives the same response time whatever the query planner decides, which matters for a system with a response-time guarantee.

Disadvantages of Denormalization

1. Data redundancy
   1. Duplicate copies take more disk space, and they also take more memory and more space in every index.
2. Complex and slower updates
   1. One change now has to be written to every copy, so a single price change becomes several writes.
   2. If one of those writes fails, the copies disagree and the database is inconsistent.
3. The anomalies come back
   1. Insert, update and delete anomalies become possible again, which is the exact cost that normalization had removed.
4. Harder to change later
   1. A combined table is shaped around the queries you had when you built it, so a new requirement can mean rebuilding it.

Where Denormalization Earns Its Keep

1. Read-intensive applications
   1. An online shop's product page is read thousands of times for every time the product is edited, so storing the product name and price together with the category name removes a join from the most frequent query in the system.
2. Data warehousing
   1. A data warehouse is loaded in batches and then only queried, so the usual objection to redundancy barely applies.
   2. Because the data is append-only and historical, there are almost no updates to propagate.
3. Real-time analytics and monitoring
   1. A dashboard refreshing every second cannot afford a six-table join, so the figures it needs are kept pre-combined.
4. Values that must not change with history
   1. Copying the price onto an order line looks like redundancy, but the price paid in 2024 is a different fact from today's price and has to be stored separately.

Deciding Whether to Denormalize

1. Measure before you change anything
   1. Find the queries that are actually slow, because denormalizing a query nobody runs adds redundancy for nothing.
2. Work out the read to write ratio
   1. Reads far outnumbering writes is the condition that makes the trade pay, and the reverse means denormalizing will make the system slower.
3. Try the cheaper fixes first
   1. Adding an index to a joined column, or rewriting the query, often recovers the speed without any redundancy at all.
   2. Denormalization is the option to reach for once those have been tried and are not enough.
4. Ask how bad a stale copy would be
   1. A follower count that is a few seconds out of date is harmless, while an account balance that is out of date is not, so the same technique is acceptable in one column and unacceptable in another.

Managing the Risk Once You Have Denormalized

1. Keep the redundancy in one place and write it down, so the next developer knows which column is a copy.
2. Use database triggers or a single piece of application code to update every copy, rather than letting each program update its own.
3. Run a periodic check that compares the copies against the source table and reports any that have drifted apart.
4. Test the change in a staging environment first, and measure it, because the improvement has to be big enough to justify the redundancy you took on.