A materialized view is a database object that stores the results of a query as a physical table, so the query’s answer can be read directly instead of recomputed every time. It sits between a regular view — which is just a saved query, re-executed on every use — and a table you maintain by hand. You get the convenience of a view’s definition with the read speed of stored data.
The trade: speed for freshness
The case for materializing is simple: some queries are expensive, and their answers don’t change often enough to justify recomputing them on every dashboard load. A daily revenue rollup that scans a hundred million order rows takes the same work whether one person runs it or fifty. Materialize it, and everyone reads a few thousand precomputed rows instead — the same idea as a summary table, but with the defining query attached and managed by the database.
The cost is staleness. A regular view is always current because it runs against live data; a materialized view is only as fresh as its last refresh. That’s the whole design question: how stale can this answer be? For a monthly cohort analysis, hours of lag is irrelevant. For an operational queue monitor, it’s disqualifying.
Creating and refreshing one
In PostgreSQL, the syntax looks like this:
CREATE MATERIALIZED VIEW daily_revenue AS
SELECT
order_date,
SUM(amount) AS revenue
FROM orders
GROUP BY order_date;
REFRESH MATERIALIZED VIEW daily_revenue;
Refresh behavior is where databases genuinely differ. Postgres recomputes the whole view when you run REFRESH — it’s manual, so teams schedule it with cron or an orchestrator, and REFRESH ... CONCURRENTLY (which requires a unique index on the view) lets reads continue during the rebuild. ClickHouse takes the opposite approach: its materialized views update incrementally, applying the transformation to new rows as they’re inserted, so there’s no refresh step at all. Cloud warehouses like BigQuery and Snowflake maintain theirs automatically in the background, in exchange for restrictions on what the defining query may contain. Same name, meaningfully different machinery — worth checking your database’s docs before you design around one.
Materialized views and Metabase
To Metabase, a materialized view is just another table: it shows up when you sync the database, and you can query it from the query builder or the native editor like anything else. That makes materialized views a good pressure valve when a heavily-used dashboard leans on an expensive query — materialize the slow part upstream, point the questions at the result, and the dashboard loads in the time it takes to read the precomputed rows.
Metabase also has a native version of this idea: model persistence, which can write a model’s results to a dedicated schema in your database on a schedule, so questions built on that model read stored rows instead of re-running the model’s query. Different mechanism, same trade — precompute now, read fast later, accept a freshness lag.
Related terms
Further reading
Put it to work
- Data warehouse dashboard — Dashboard
- Cache hit rate — Metric
- Best practices for writing SQL queries — Tutorial
