When to Precompute vs. Optimize Ad Hoc Queries

The Fundamental Tradeoff: Query optimization exists on a spectrum between two extremes. On one end, you precompute everything into materialized views, cube tables, or pre aggregated summaries. On the other end, you optimize nothing and run fresh queries against raw data every time. Real systems live somewhere in between, and choosing the right point requires understanding specific tradeoffs. When Precomputation Wins: Materialized views shine when you have high query reuse and can tolerate data lag. Executive dashboards accessed by 200 people daily but updated only once per hour are perfect candidates. Precomputing daily revenue rollups by region and product category can take query time from 10 seconds down to under 200 milliseconds. The cost is maintaining the refresh pipeline and storing duplicate data, typically adding 50% to 200% storage overhead depending on aggregation levels. For systems with 80% or more repeated query patterns, like operational dashboards in e-commerce showing order counts and revenue, precomputation provides 50x to 100x speedup. The refresh lag of 5 to 30 minutes is acceptable because these metrics inform strategic decisions, not real time operations. When Fresh Queries Win: If you need strict real time correctness, precomputation fails. Banking systems checking account balances, inventory systems validating stock levels, or fraud detection systems cannot tolerate even 60 seconds of lag. These must query fresh data, accepting slower response times of 100 to 500 milliseconds instead of 10 to 20 milliseconds from a cache. Exploration and ad hoc analytics also demand fresh queries. Data scientists iterating on new analyses generate unique queries that have never run before. Precomputing all possible aggregations is impossible. Here you invest in fast scan optimization, good partitioning, and columnar formats rather than materialized views. The Index Tradeoff: Indexes occupy a middle ground. They speed up selective lookups without full precomputation, but they come at write cost. Each index adds 10% to 30% storage overhead, and every INSERT must update all indexes. A table with 5 indexes means 6 writes per INSERT. For read heavy workloads over 90% reads like user profiles or product catalogs, index liberally and consider materialized views. Your 10,000 reads per second benefit while 500 writes per second pay the index maintenance cost. For write heavy event logs at 80% writes, keep indexes minimal. The write throughput drop from 50,000 to 8,000 inserts per second with heavy indexing makes the tradeoff negative. Decision Framework: First, measure your read/write ratio. Second, determine freshness requirements. Real time needs (under 1 second lag) eliminate most precomputation. Tactical needs (5 to 30 minute lag acceptable) allow selective materialized views for high reuse queries. Strategic analytics (hourly or daily lag) can leverage aggressive precomputation. Third, estimate query reuse. If 90% of queries hit 10 patterns, precompute those 10. If queries are unique, optimize scan performance instead.