SQL Materialized Views: A Practical Tutorial

What and Why

A regular view is just a saved query. Every time you select from it, the database executes the underlying SQL. That is fine for simple lookups, but for heavy aggregations across millions of rows, it is a performance trap.

A materialized view stores the result of the query as a physical table on disk. Reads become a normal table scan or index lookup, no joins or aggregations required at query time. The tradeoff is that the data is a snapshot; it becomes stale until you refresh it. For dashboards, reporting, and expensive analytical queries that do not need sub-second freshness, this tradeoff is often a huge win.

Mental Model

Picture two boxes. The first is a regular view: a piece of paper with a query written on it. Every reader picks up the paper, runs the query from scratch, and gets a fresh answer at the cost of execution time.

The second is a materialized view: a binder with the precomputed answer printed inside. Readers flip to the page, get an instant answer, but the answer reflects whenever the binder was last printed. A refresh operation reprints the binder.

You can also index the binder, so even lookups inside the precomputed result are fast. And with REFRESH MATERIALIZED VIEW CONCURRENTLY, you can reprint a new copy while readers continue to use the old one.

Hands-on Example

CREATE MATERIALIZED VIEW daily_revenue AS
SELECT
  date_trunc('day', created_at) AS day,
  tenant_id,
  sum(amount)                   AS revenue,
  count(*)                      AS order_count
FROM orders
GROUP BY 1, 2;

-- Required for CONCURRENTLY refresh
CREATE UNIQUE INDEX ON daily_revenue (day, tenant_id);

-- Fast lookup index
CREATE INDEX ON daily_revenue (tenant_id, day DESC);

-- Refresh without blocking readers
REFRESH MATERIALIZED VIEW CONCURRENTLY daily_revenue;

 orders (10M rows)
      |
      | aggregation (slow: ~5s)
      v
+---------------------+
| daily_revenue (mv)  |  <-- physical table, indexed
|  day | tenant | rev |
+---------------------+
      ^
      | fast SELECT  (~5ms)
      |
   dashboards

 REFRESH CONCURRENTLY
 ---------------------
 build new snapshot in background -> swap atomically
 readers see consistent old data until swap completes

The dashboard never pays the aggregation cost. The refresh job pays it once per period.

Common Pitfalls

The classic mistake is forgetting that a materialized view is not magically kept up to date. Until you refresh, it returns yesterday’s data. Always schedule refreshes, and surface the last refresh time in your UI for transparency.

A second pitfall is using a plain REFRESH MATERIALIZED VIEW in production. That command takes an exclusive lock and blocks reads until it finishes. Use CONCURRENTLY, which requires a UNIQUE index but lets readers continue uninterrupted.

A third trap is over-materializing. If the underlying data changes constantly and consumers need fresh values, you may spend more on refreshes than the precomputation saves. Measure read volume against refresh cost before committing.

Finally, materialized views are not free during refresh: they recompute the entire query unless you use an incremental strategy (which Postgres does not provide natively; you must roll your own).

Practical Tips

Choose materialized views for queries that are expensive to compute, read frequently, and tolerant of some staleness. Dashboards, reporting tables, and search facets are ideal candidates.

Schedule refreshes with a job runner (cron, pg_cron, or your application scheduler). Match the refresh interval to the freshness SLA, not to “as often as possible.”

Index the materialized view for the queries that read it, just like a regular table. This is often what turns a “good” materialized view into a “great” one.

For very large or fast-changing data, consider a hybrid: keep a recent slice in a regular query and merge it with the materialized historical aggregate at read time.

Wrap-up

Materialized views trade freshness for speed by storing precomputed results on disk. Use them for expensive aggregations that tolerate staleness, refresh with CONCURRENTLY to avoid blocking readers, and index the result like any other table. Combined with a sensible refresh schedule, they turn slow analytical queries into trivial lookups.