Materialised Views

~12 min · extensions, views

Level 0Schema Seedling

0 XP0/86 lessons0/10 achievements

0/120 XP to next level120 XP to go0% complete

Cached query results, on demand

A regular VIEW is just a stored query — every SELECT against it re-runs the underlying query. A MATERIALIZED VIEW is a stored query plus its results — the data is computed once, stored on disk, and queried like a table. Refresh on schedule (or on demand) to get updated data.

When materialised views earn their keep

Expensive aggregations queried often (dashboards, leaderboards).
Complex joins where the underlying tables change much less than they're queried.
Reports that don't need second-by-second freshness — minute-fresh or hour-fresh is enough.

Refreshing

REFRESH MATERIALIZED VIEW rebuilds the data; while it runs, the view is locked. REFRESH MATERIALIZED VIEW CONCURRENTLY rebuilds without locking — but requires a unique index on the view and is slower. Use CONCURRENTLY in production.

Code

Materialised view for a dashboard·sql

CREATE MATERIALIZED VIEW monthly_revenue AS
SELECT DATE_TRUNC('month', placed_at) AS month,
       region,
       SUM(total)                     AS revenue,
       COUNT(*)                       AS order_count
FROM   orders
WHERE  status = 'completed'
GROUP  BY 1, 2;

-- Required for CONCURRENTLY refresh
CREATE UNIQUE INDEX monthly_revenue_uniq ON monthly_revenue (month, region);

-- Query is a plain table read — instant
SELECT * FROM monthly_revenue WHERE month >= now() - INTERVAL '12 months';

Refresh on a schedule·sql

-- Refresh in production without locking the view
REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_revenue;

-- Schedule with pg_cron (extension) or your application's job runner
-- Example pg_cron entry: every 10 minutes
-- SELECT cron.schedule('refresh-monthly-revenue', '*/10 * * * *',
--                      'REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_revenue');

External links

Exercise

Identify a slow aggregation query in your project (or invent one). Wrap it in a materialised view + unique index. Schedule a periodic CONCURRENTLY refresh. Compare query latency before and after.

Progress

Progress is local-only — sign in to sync across devices.

← PreviousFull-Text Search Without Elasticsearch Next →Generated Columns: Stored vs Virtual

Spotted a bug or have feedback on this page?Report an Issue

Comments 0

🔔 Reply notifications (sign in)

No comments yet — be the first.