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_counton 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.