Materialized Views
Practice this topic in a realistic system design interview
Many slow database queries repeat the same expensive work again and again.
A dashboard may join orders, customers, and products, then group everything by region. A reporting page may scan millions of rows to calculate daily revenue. If the result does not need to be live to the second, running that full query on every request is wasteful.
A materialized view stores the result of a query so future reads can fetch already-calculated data.
It is a database-supported form of denormalization. You keep the source tables normalized for clean writes, but keep a read-friendly copy for common expensive queries.
The tradeoff is direct. Reads get much faster, but the stored result can become stale. It also uses storage, and refreshing it costs CPU, disk work, locks, and operational work.
Treat a materialized view as a stored query result with a refresh plan. Do not treat it like a cache that magically keeps itself correct.
In this chapter, we will look at how materialized views work, when they help, and what costs they bring.