Materialized Views vs Tables: When to Use Each

Materialized Views vs. Tables: When to Use Each

Materialized views automatically maintain aggregated data but come with maintenance costs. Learn when they save money versus when standard tables are better.

How Materialized Views Work

• Store pre-computed query results
• Automatically refresh when base tables change
• Queries read from materialized results (fast, low cost)
• Maintenance happens in background (consumes compute)

When Materialized Views Win

• Base tables change infrequently (daily or less)
• Queries run frequently (100s-1000s per day)
• Aggregations are expensive to compute
• Query performance is critical

When Standard Tables Win

• Base tables change constantly (streaming, real-time)
• Queries run infrequently
• Simple aggregations
• Batch processing is acceptable

Cost Comparison Example

Scenario: Daily aggregation over 1B rows
- Query cost without MV: 5 credits × 500 queries/day = 2,500 credits/day
- MV creation: 10 credits
- MV maintenance: 8 credits/day (1 daily refresh)
- MV query cost: 0.1 credits × 500 queries/day = 50 credits/day

Savings with MV: 2,500 - 58 = 2,442 credits/day

Implementation

CREATE MATERIALIZED VIEW daily_sales_summary AS
SELECT
DATE_TRUNC('day', order_date) as sale_date,
product_category,
SUM(order_total) as total_sales,
COUNT(*) as order_count
FROM orders
GROUP BY 1, 2;

Real-World Example

A company created materialized views on 10 tables that updated every 5 minutes. Maintenance costs were 2,000 credits/day. After analysis, they found only 3 tables were queried frequently enough to justify MVs. They converted the other 7 to scheduled table refreshes (hourly), reducing maintenance costs to 400 credits/day.

Key Takeaways

• Materialized views trade storage and maintenance costs for query performance
• Best for infrequently-updated base tables queried frequently
• Consider scheduled table refreshes for frequently-updated data
• Calculate ROI before creating materialized views