Skip to main content
Skip to main content
Edit this page

Materialized Views: The 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

Community warning from real production incidents: Teams get "over enthusiastic about materialized views and we end up creating too many of them and that kind of slows down... our injection as well"

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 got 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 MV Health Validation​

The Successful MV Patterns​

From companies with excellent MV results:

"Because of how we put it into the disk we are compressing from 72 to 3 gigabytes which is 25x which is pretty good" - Example of proper MV achieving massive compression

Recovery Strategy for Over-Enthusiastic MV Usage​

From companies that fixed their MV problems:

"One thing we did was remove the Mater if you have lot of materialized views remove the selected materialized views and create the tables for them instead... we create the tables and then run periodically run a cron job to populate those tables instead of relying materialized views"

Decision Framework:

  • Keep MV: Query frequency × speed improvement > storage cost × maintenance overhead
  • Replace with cron: Aggregations that are "not very critical like if you have a 5 minute aggregation 1 hour aggregation already created... maybe it can wait"