Materialized Views

Also Known As

TL;DR

Explanation

A materialized view stores the result of a complex query (heavy aggregations, multi-table joins) as a physical table that can be indexed and queried instantly. Unlike regular views which recompute on every access, materialized views are refreshed on a schedule or on-demand. PostgreSQL supports REFRESH MATERIALIZED VIEW CONCURRENTLY (non-blocking). MySQL lacks native materialized views — they are emulated as real tables populated by event schedulers or application code. Ideal for dashboards and reporting that aggregate millions of rows but can tolerate slightly stale data (last-refreshed 5 minutes ago).

Common Misconception

Why It Matters

Common Mistakes

• Not refreshing materialized views — stale data silently serves outdated results.
• Refreshing too frequently — REFRESH MATERIALIZED VIEW locks the view briefly; concurrent refresh avoids this.
• Using materialized views for frequently changing data where staleness is unacceptable.
• Not indexing materialized views — they are stored like tables and need indexes for fast access.

Code Examples

-- Slow query run on every dashboard load:
SELECT category, COUNT(*), SUM(revenue) FROM orders
JOIN products ON orders.product_id = products.id
GROUP BY category; -- 3 seconds on 10M rows

-- Materialized view — pre-computed, instant:
CREATE MATERIALIZED VIEW order_stats AS (above query);
REFRESH MATERIALIZED VIEW CONCURRENTLY order_stats; -- Run via cron

-- PostgreSQL: pre-compute expensive aggregate
CREATE MATERIALIZED VIEW monthly_revenue AS
    SELECT
        DATE_TRUNC('month', created_at) AS month,
        SUM(total) AS revenue,
        COUNT(*) AS order_count
    FROM orders
    WHERE status = 'paid'
    GROUP BY 1;

CREATE INDEX ON monthly_revenue(month);

-- Query is now instant (reads pre-computed data)
SELECT * FROM monthly_revenue WHERE month >= '2024-01-01';

-- Refresh (schedule in cron or trigger)
REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_revenue;
-- CONCURRENTLY allows reads during refresh (PostgreSQL 9.4+)

References

• ↗ https://www.postgresql.org/docs/current/rules-materializedviews.html

Tags

Related Terms