Course contentsShow
← Data Science
Lesson 1073 of 2,14527. Database Design and OptimizationFree lesson

Storing Computed Values and Aggregates

Explore denormalization by pre-computing and storing aggregate values to avoid expensive JOIN and GROUP BY operations.

Storing Computed Values and Aggregates

What you'll learn: How to improve query performance by pre-calculating and storing aggregate results instead of computing them on every query.

In highly normalized databases, answering questions like "What's the total sales per customer?" often requires expensive JOIN operations followed by GROUP BY aggregations. When these queries run frequently on large tables, they can slow your application to a crawl. One denormalization technique is to pre-compute these values and store them directly in your tables.

The Core Idea

Instead of calculating aggregates on-the-fly, you store them as regular columns. For example, rather than joining orders and order_items every time to sum up order totals, you could add a total_amount column to the orders table itself.

Think of it like a calculator: You could add up receipts every time someone asks "What did I spend today?" — or you could write the daily total on a sticky note once and just read it when asked.

Common Scenarios

  • Storing customer lifetime value instead of SUMming all orders repeatedly
  • Keeping a product's average rating rather than recalculating from all reviews
  • Maintaining item counts (e.g., comment_count on a post) instead of COUNTing rows

The Trade-off

You gain read performance but sacrifice:

  • Write complexity: Every time underlying data changes, you must update the aggregate
  • Data consistency risk: Aggregates can become stale or incorrect if updates fail
  • Storage space: You're duplicating information that could be derived

This approach shines in read-heavy workloads where the same aggregates are queried far more often than the underlying data changes.

Key Takeaway: Storing pre-computed aggregates trades write complexity and storage for dramatically faster read performance—ideal when queries vastly outnumber updates.