Sweaty Imposter

Materialized View

TechniqueDatabase InternalsMidSeniorUpdated
Persist the result of a query as a real table; refresh on schedule, on demand, or incrementally as base tables change.

When to Use Materialized View

Expensive aggregations or joins that are read far more often than the underlying data changes. Dashboards, leaderboards, and OLAP rollups are textbook cases.

In Practice

Postgres CREATE MATERIALIZED VIEW daily_revenue AS SELECT day, SUM(total) FROM orders GROUP BY day; then REFRESH MATERIALIZED VIEW CONCURRENTLY daily_revenue nightly. Snowflake and BigQuery offer incrementally maintained variants.

How Materialized View Comes Up in Interviews

Scenario

A leaderboard query joins three large tables and runs on every page load. Data changes a few times a minute, but the query runs thousands of times a second. Right tool?

Precomputing the join into a materialized view turns thousands of expensive joins per second into thousands of cheap reads from a flat table. Stale-by-seconds is fine for a leaderboard.

If you need fresher data, look at incremental view maintenance (Materialize, Snowflake dynamic tables, Postgres pg_ivm). Otherwise, REFRESH MATERIALIZED VIEW CONCURRENTLY avoids locking readers.

Scenario

An admin dashboard shows revenue per day for the past year. The query sums millions of order rows, takes 6 seconds, and runs every time anyone opens the page, though the numbers only need to be an hour fresh. You want the database to save the computed result and serve the page from it until the next refresh. What do you use?

A materialized view stores the query's result as a table, so the page reads 365 precomputed rows, and REFRESH MATERIALIZED VIEW recomputes them on a schedule, here hourly. That is caching: you trade freshness for speed and decide how stale the data may get. An index, covering or otherwise, helps the database find rows faster, but the query would still sum millions of them on every page load.

Every cache works the same way: a hit serves the stored copy, a miss computes the real value and stores it, and the copy goes stale as soon as the source changes. You bound staleness with an expiry (a TTL, or a scheduled refresh like this one) or by invalidating the entry when the data changes. In application code the usual shape is cache-aside: check Redis for the key, fall back to the database on a miss, and write the result back with a TTL.