What is a Materialized View?

A materialized view is a precomputed query result stored as a physical table in a database, designed to deliver faster query performance by avoiding repeated calculations.

When to Use

Materialized views are ideal when queries run often on large datasets but don’t require real-time freshness—like analytics dashboards, data warehouses, or summary reports. For example, in PostgreSQL or Oracle, they’re commonly used for aggregations and joins across massive tables.

Example

An e-commerce platform might use a materialized view to store daily sales totals so reports load instantly instead of recalculating from millions of transactions.

Want to master these concepts?

Explore Grokking System Design Fundamentals, Grokking the System Design Interview, Grokking Database Fundamentals for Tech Interviews, or practice through Mock Interviews with ex-FAANG engineers.

Why Is It Important

They reduce database load and deliver near-instant answers for complex queries, which is critical in scaling systems where performance matters.

Interview Tips

In interviews, highlight the difference between a view (virtual, always up-to-date) and a materialized view (stored, requires refresh). Emphasize the trade-off between speed and freshness.

Trade-offs

They provide faster reads but consume storage and can serve stale data until refreshed. Choosing refresh strategies (manual, scheduled, incremental) is key.

Pitfalls

Common mistakes include forgetting refresh schedules, using them on highly volatile data, or assuming they’re always faster—sometimes maintaining them can outweigh their benefit.