What is denormalization and when do you use it?

Detailed explanation

Denormalization is the deliberate introduction of redundant or precomputed data into a database to make selected queries faster or simpler. In a normalized design, each fact is stored in its appropriate place: orders contain order details, while order_items contain quantities and prices. A denormalized design might also store the calculated order total in orders. This avoids repeatedly aggregating item rows. Denormalization therefore trades additional storage and more complex writes for lower read-time computation; it is not simply poor schema design.

For example, ALTER TABLE orders ADD total_amount DECIMAL(10,2); adds a place to store a total, but it does not calculate or maintain that value. Existing orders need a backfill, and subsequent item inserts, updates, and deletions must update the total according to the business rules. Those rules should define whether discounts, taxes, and shipping are included. When immediate consistency is required, item changes and total updates should occur in the same transaction, with appropriate locking or atomic updates to handle concurrent writers. Otherwise, stale values or lost updates can produce incorrect totals.

Use denormalization when measurements show that frequently executed reads spend substantial time on joins or aggregations, especially in reporting, dashboards, and read-heavy workloads. Common approaches include copying frequently needed attributes, maintaining summary tables, and using materialized views where supported. These structures move work from query execution to write processing or scheduled refreshes. The benefit depends on the query pattern: an order-total column helps total-based queries but does not replace item-level data for detailed analysis. Asynchronous refreshes can reduce write-path work when some data staleness is acceptable.

Before denormalizing, inspect execution plans and consider suitable indexes, query rewrites, and whether the existing schema supports the workload. Joins are not inherently slow, and duplication is not automatically faster. Interviewers look for a justified trade-off: identify the expensive read, estimate its frequency relative to writes, define an acceptable freshness level, and explain maintenance and recovery. Keep an authoritative source of truth and a way to rebuild or reconcile derived values. Denormalization is most useful when its measured read benefits outweigh consistency risks, storage growth, and ongoing maintenance.

Key points to remember

• Denormalization deliberately duplicates facts or stores derived values to reduce read-time work.
• Typical uses include reporting, dashboards, and frequently requested aggregates.
• Stored totals need backfilling and maintenance; adding a column alone is insufficient.
• Choose transactional updates or asynchronous refreshes based on consistency requirements.
• Validate the benefit with execution plans and workload measurements before introducing redundancy.

Common mistakes candidates make

• Assuming all joins are expensive and denormalizing before checking indexes or execution plans.
• Ignoring how updates, deletions, and concurrent writes keep duplicated or derived data consistent.
• Treating a stored total as self-maintaining without defining calculation rules or a reconciliation process.

Likely follow-up questions