Course contentsShow
← System Design
Lesson 294 of 1,9198. Database Performance OptimizationFree lesson

Aggregation Tables

Pre-aggregate metrics and summaries into dedicated tables to avoid expensive GROUP BY operations.

Aggregation Tables

What you'll learn: How to speed up analytics and reporting by storing pre-calculated summaries instead of computing them on every query.


The Core Problem

Imagine running a daily sales report that requires SELECT SUM(revenue), COUNT(*) FROM orders WHERE date = TODAY() GROUP BY category. With millions of orders, this query scans huge amounts of data and performs expensive aggregation operations every single time someone loads the dashboard.

What Are Aggregation Tables?

Aggregation tables (also called summary tables or rollup tables) store pre-computed metrics and summaries. Instead of calculating totals, averages, or counts on-demand from raw data, you maintain a separate table with those values already computed.

For example, instead of aggregating millions of order rows, you maintain a daily_sales_by_category table:

category   | date       | total_revenue | order_count
-----------|------------|---------------|-------------
Electronics| 2024-01-15 | 45000.00      | 320
Clothing   | 2024-01-15 | 23000.00      | 180

How It Works

You populate aggregation tables through:

  • Batch jobs that run periodically (hourly, daily)
  • Triggers or application logic that update summaries when new data arrives
  • Materialized views (covered in lesson 291) that handle updates automatically

The read query becomes trivial: SELECT * FROM daily_sales_by_category WHERE date = TODAY() — no GROUP BY needed.

The Tradeoff

You're trading write complexity and storage space for dramatically faster reads. Perfect for analytics, dashboards, and reporting where slightly stale data is acceptable and queries repeat the same aggregation patterns.

Key Takeaway: Aggregation tables pre-compute expensive GROUP BY operations into dedicated summary tables, trading storage and update complexity for lightning-fast analytical queries at scale.