Precomputed Query Results as Tables

Prerequisites(2)

SQL (DDL, DML, DCL)Query Planner & Cost Model

Builds on to(1)

A materialized view is a database object that stores the result of a query as a physical table. Unlike a regular view (which is just a saved query that re-executes every time you read from it), a materialized view computes the result once and serves subsequent reads directly from the stored data — making repeated access to expensive aggregations orders of magnitude faster.

Creating a Materialized View

CREATE MATERIALIZED VIEW monthly_revenue AS
SELECT date_trunc('month', order_date) AS month,
       SUM(amount) AS total_revenue,
       COUNT(*) AS order_count
FROM orders
GROUP BY 1;

The database executes this query immediately and stores the result set as a table. Subsequent SELECT * FROM monthly_revenue reads the precomputed result — no aggregation needed.

Regular View vs. Materialized View

A regular view is a named query:

CREATE VIEW monthly_revenue_v AS SELECT ... FROM orders GROUP BY 1;

Every time you query monthly_revenue_v, the database re-executes the full aggregation query. If orders has 100 million rows, every dashboard refresh re-scans and aggregates them. A materialized view avoids this by caching the result.

Refreshing a Materialized View

The stored data becomes stale as the underlying tables change. To update it:

REFRESH MATERIALIZED VIEW monthly_revenue;

This performs a full recomputation — re-executing the entire defining query and replacing the stored data. During refresh, the materialized view is locked for reads unless you use the CONCURRENTLY option.

Concurrent Refresh

REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_revenue;

The CONCURRENTLY option computes the new result alongside the old one, then atomically swaps them. Readers are never blocked. However, this requires a unique index on the materialized view (so the database can diff the old and new results) and is slower than a non-concurrent refresh because it computes a diff.

Use Cases

Materialized views are ideal for dashboards, reporting queries, and expensive aggregations where slightly stale data is acceptable. Common patterns include: hourly revenue summaries, user activity rollups, denormalized search indexes, and leaderboard rankings. The fundamental trade-off is freshness vs. performance — you accept stale data in exchange for instant reads.

The Cache Invalidation Problem

Materialized views are essentially a form of caching, and they inherit the classic cache invalidation problem. How often should you refresh? Too infrequently means users see outdated data. Too frequently means you waste compute on recomputation. Some applications refresh on a schedule (every 15 minutes), others trigger a refresh after known data changes.

Dashboard Performance with Materialized Views

Real-World Example

A SaaS analytics dashboard shows daily active users (DAU), monthly revenue, and top-10 features by usage. The underlying events table has 500 million rows.

Without materialized views: Each dashboard page load executes three heavy queries:

DAU: scans 500M rows, groups by date → 4 seconds
Revenue: joins events with payments, aggregates → 6 seconds
Top features: scans, groups, sorts → 3 seconds
Total: 13 seconds per page load for every user

With materialized views:

CREATE MATERIALIZED VIEW mv_dau AS
  SELECT date, COUNT(DISTINCT user_id) AS dau FROM events GROUP BY date;

CREATE MATERIALIZED VIEW mv_revenue AS
  SELECT date, SUM(amount) FROM events JOIN payments USING(event_id) GROUP BY date;

CREATE MATERIALIZED VIEW mv_top_features AS
  SELECT feature, COUNT(*) AS usage FROM events GROUP BY feature ORDER BY usage DESC LIMIT 10;

Dashboard reads from the materialized views:

DAU: reads 365 precomputed rows → 2 ms
Revenue: reads 365 precomputed rows → 2 ms
Top features: reads 10 rows → 1 ms
Total: 5 ms per page load (2600x faster)

Refresh strategy:

-- Run every 15 minutes via cron job
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_dau;
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_revenue;
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_top_features;

Users see data that is at most 15 minutes stale, which is perfectly acceptable for a dashboard. The database saves thousands of full-table scans per hour.

Regular View vs. Materialized View

Step 1 of 2