Skip to main content
Skip to main content
Edit this page

Materialized views: how they can become a double edged sword

This guide is part of a collection of findings gained from community meetups. For more real world solutions and insights you can browse by specific problem. Too many parts bogging your database down? Check out the Too Many Parts community insights guide. Learn more about Materialized Views.

The 10x storage anti-pattern​

Real production problem: "We had a materialized view. The raw log table was around 20 gig but the view from that log table exploded to 190 gig, so almost 10x the size of the raw table. This happened because we were creating one row per attribute and each log can have 10 attributes."

Rule: If your GROUP BY creates more rows than it eliminates, you're building an expensive index, not a materialized view.

Production materialized view health validation​

This query helps you predict whether a materialized view will compress or explode your data before you create it. Run it against your actual table and columns to avoid the "190GB explosion" scenario.

What it shows:

  • Low aggregation ratio (<10%) = Good MV, significant compression
  • High aggregation ratio (>70%) = Bad MV, storage explosion risk
  • Storage multiplier = How much bigger/smaller your MV will be

When materialized views become a problem​

Warning signs to monitor:

  • Insert latency increases (queries that took 10ms now take 100ms+)
  • "Too many parts" errors appearing more frequently
  • CPU spikes during insert operations
  • Insert timeouts that didn't happen before

You can compare insert performance before and after adding MVs using system.query_log to track query duration trends.

Video sources​