Questions: Denormalization and Performance Trade-offs

Question 1

An e-commerce database stores orders in an `orders` table and customer names in a `customers` table. The order listing page runs 50,000 joins per second. A developer proposes adding a `customer_name` column to `orders`. What is the primary risk of this change?

• A. The query will become slower because wider rows take longer to scan
• B. The database will run out of disk space due to the duplicated names
• C. If a customer's name changes, every row in `orders` must be updated; partial failures can leave the data inconsistent
• D. SQL cannot reference a denormalized column in a SELECT statement

Answer: (C)

Denormalization trades read simplicity for write complexity. The customer name is now stored in two places, so any update to it must be applied everywhere. If the update partially fails — or if the application forgets to propagate it — the data becomes inconsistent. This is the core trade-off: reads get faster, but writes become more expensive and more dangerous. Option B is incorrect in practice; storage cost is rarely the primary concern.

Question 2

A team is considering denormalizing a heavily-joined schema. What should drive the decision?

• A. Denormalization should be applied whenever more than two tables are joined, as a general best practice
• B. Denormalization should be applied after profiling shows joins are the actual performance bottleneck, and after checking whether indexes or materialized views solve the problem first
• C. Denormalization should be avoided entirely because it always reduces data integrity
• D. Denormalization should be applied immediately to any table that is read more than written

Answer: (B)

The key word in denormalization is 'measurement.' Without profiling, teams frequently denormalize joins that weren't the bottleneck, adding write complexity for no gain. Even when joins are slow, indexes or materialized views may solve the problem without introducing redundancy. Denormalization is a last resort after less invasive options have been considered, and it should be verified with benchmarks. Options A and D are rules of thumb that substitute intuition for evidence.

Question 3

Denormalization intentionally introduces redundancy in order to improve read performance at the cost of more complex writes.

• T. True
• F. False

Answer: True

This is exactly the trade-off denormalization makes. By storing pre-joined or pre-aggregated data redundantly, reads no longer need to perform expensive joins. But every redundant copy is a potential inconsistency — writes must now maintain multiple copies in sync. This shift of complexity from reads to writes is the defining characteristic of denormalization and the reason it should be done deliberately rather than casually.

Question 4

Denormalization usually improves database performance and should be applied to any schema that has query performance issues.

• T. True
• F. False

Answer: False

Denormalization improves read performance for specific query patterns — typically those dominated by multi-table joins. It does not help (and may hurt) write-heavy workloads, random-access patterns already solved by indexes, or queries that scan aggregates not precomputed by the denormalization. A database with poor performance may have its bottleneck in missing indexes, poor query planning, insufficient memory for the buffer pool, or lock contention — none of which denormalization addresses. Applying it blindly adds consistency risk without guaranteed benefit.

Question 5

Why should the decision to denormalize be driven by measurement rather than intuition, and what should be measured?

Model answer: Intuition often overestimates join cost and underestimates write complexity. Profiling the actual query workload identifies which specific joins consume the most of the time. You should measure: query execution time before and after denormalization, write latency for the affected tables, and whether simpler alternatives (indexes, materialized views) solve the problem. This ensures denormalization is applied where it produces real gains and not used as a substitute for proper indexing or query optimization.

Engineers frequently denormalize preemptively and then discover the join was fast, the real bottleneck was elsewhere, and they've now introduced consistency obligations they must maintain indefinitely. Measurement-first discipline prevents this. Additionally, measuring after the change verifies that the expected improvement materialized — sometimes the query planner already optimized the join, and denormalization produces no benefit at all.