SQL Materialized Views: Caching Query Results for Performance

Regular Views vs Materialized Views

A regular view is a stored query. Every time you SELECT from it, the database re-executes the underlying query:

CREATE VIEW monthly_revenue AS
SELECT DATE_TRUNC('month', created_at) AS month,
       SUM(total) AS revenue
FROM orders
GROUP BY 1;

-- This re-runs the aggregation every time
SELECT * FROM monthly_revenue;

A materialized view executes the query once and stores the result on disk as a table. Subsequent reads hit the cached result:

CREATE MATERIALIZED VIEW mv_monthly_revenue AS
SELECT DATE_TRUNC('month', created_at) AS month,
       SUM(total) AS revenue
FROM orders
GROUP BY 1;

-- This reads from the cached result (fast)
SELECT * FROM mv_monthly_revenue;

The trade-off: materialized views return stale data until you explicitly refresh them.

Creating Materialized Views

CREATE MATERIALIZED VIEW mv_customer_stats AS
SELECT c.id AS customer_id,
       c.name,
       COUNT(o.id) AS order_count,
       COALESCE(SUM(o.total), 0) AS lifetime_value,
       MAX(o.created_at) AS last_order_date
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name;

The view is populated immediately at creation time. To create the view structure without populating it (useful for initial setup or schema migrations):

CREATE MATERIALIZED VIEW mv_customer_stats AS
SELECT ...
WITH NO DATA;

A view created with WITH NO DATA cannot be queried until it is refreshed.

Refreshing Materialized Views

Full Refresh

REFRESH MATERIALIZED VIEW mv_customer_stats;

This re-executes the underlying query and replaces all stored data. During refresh, the view is locked and cannot be read.

Concurrent Refresh

To allow reads during refresh, use CONCURRENTLY. This requires a unique index on the materialized view:

CREATE UNIQUE INDEX idx_mv_customer_stats_id ON mv_customer_stats (customer_id);

REFRESH MATERIALIZED VIEW CONCURRENTLY mv_customer_stats;

Concurrent refresh builds the new data alongside the old, then swaps them atomically. It is slower than a regular refresh but avoids blocking readers.

Indexing Materialized Views

Materialized views are physical tables, so you can add indexes just like regular tables:

-- Index for looking up a specific customer
CREATE UNIQUE INDEX idx_mv_cust_id ON mv_customer_stats (customer_id);

-- Index for filtering by lifetime value
CREATE INDEX idx_mv_cust_ltv ON mv_customer_stats (lifetime_value DESC);

-- Index for searching by name
CREATE INDEX idx_mv_cust_name ON mv_customer_stats (name);

These indexes make querying the materialized view fast, which is the whole point. Always add indexes that match your query patterns.

Refresh Strategies

Manual / On-Demand

Call REFRESH from application code after a batch process completes:

-- After nightly ETL finishes
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_monthly_revenue;

Scheduled (Cron)

Use pg_cron or an external scheduler to refresh at regular intervals:

-- Using pg_cron (PostgreSQL extension)
SELECT cron.schedule(
  'refresh_mv_revenue',
  '0 * * * *',  -- Every hour
  'REFRESH MATERIALIZED VIEW CONCURRENTLY mv_monthly_revenue'
);

Trigger-Based (Near Real-Time)

For near-real-time freshness, use a trigger that calls refresh after relevant changes. This is usually too expensive for high-write tables, so consider batching:

-- Track when the view was last refreshed
CREATE TABLE mv_refresh_log (
  view_name TEXT PRIMARY KEY,
  last_refresh TIMESTAMPTZ,
  next_refresh TIMESTAMPTZ
);

-- Application logic: refresh only if stale
DO $$
BEGIN
  IF (SELECT next_refresh < NOW() FROM mv_refresh_log
      WHERE view_name = 'mv_monthly_revenue') THEN
    REFRESH MATERIALIZED VIEW CONCURRENTLY mv_monthly_revenue;
    UPDATE mv_refresh_log
    SET last_refresh = NOW(), next_refresh = NOW() + INTERVAL '5 minutes'
    WHERE view_name = 'mv_monthly_revenue';
  END IF;
END $$;

Practical Examples

Dashboard Summary

CREATE MATERIALIZED VIEW mv_dashboard AS
SELECT
  DATE_TRUNC('day', o.created_at) AS day,
  COUNT(DISTINCT o.customer_id) AS unique_customers,
  COUNT(o.id) AS order_count,
  SUM(o.total) AS revenue,
  AVG(o.total) AS avg_order_value
FROM orders o
WHERE o.created_at >= CURRENT_DATE - INTERVAL '90 days'
GROUP BY 1;

CREATE INDEX idx_mv_dashboard_day ON mv_dashboard (day);

This turns an expensive 90-day aggregation into an instant lookup.

Leaderboard

CREATE MATERIALIZED VIEW mv_leaderboard AS
SELECT u.id, u.username,
       COUNT(p.id) AS post_count,
       SUM(p.upvotes) AS total_upvotes,
       RANK() OVER (ORDER BY SUM(p.upvotes) DESC) AS rank
FROM users u
JOIN posts p ON p.author_id = u.id
WHERE p.created_at >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY u.id, u.username;

CREATE UNIQUE INDEX idx_mv_leaderboard_id ON mv_leaderboard (id);
CREATE INDEX idx_mv_leaderboard_rank ON mv_leaderboard (rank);

Search Denormalization

CREATE MATERIALIZED VIEW mv_product_search AS
SELECT p.id, p.name, p.price,
       c.name AS category_name,
       b.name AS brand_name,
       p.name || ' ' || c.name || ' ' || b.name AS search_text
FROM products p
JOIN categories c ON c.id = p.category_id
JOIN brands b ON b.id = p.brand_id
WHERE p.active = true;

CREATE INDEX idx_mv_product_search_text
  ON mv_product_search USING gin (to_tsvector('english', search_text));

Checking Materialized View Status

-- List all materialized views
SELECT matviewname, ispopulated
FROM pg_matviews
WHERE schemaname = 'public';

-- Check when data was last refreshed (no built-in column, track manually)
SELECT * FROM mv_refresh_log;

Dropping Materialized Views

DROP MATERIALIZED VIEW mv_customer_stats;

-- Drop with dependent objects
DROP MATERIALIZED VIEW mv_customer_stats CASCADE;

-- Drop only if it exists
DROP MATERIALIZED VIEW IF EXISTS mv_customer_stats;

Materialized Views vs Alternatives

Approach	Freshness	Complexity	Read Speed
Regular view	Real-time	Low	Slow (re-executes)
Materialized view	Periodic	Medium	Fast
Cache table (manual)	Controlled	High	Fast
Application cache (Redis)	TTL-based	High	Fastest

Materialized views hit the sweet spot for queries that are expensive to compute, tolerate slightly stale data, and need to be queried with SQL (including joins and further aggregation).

Limitations

1. No automatic refresh: You must trigger refreshes manually or via a scheduler.
2. No incremental refresh: PostgreSQL refreshes the entire view. For very large datasets, consider partitioning the source or using an incremental approach with a cache table.
3. Storage cost: The materialized view is a full copy of the result set.
4. Concurrent refresh requires a unique index: Plan for this at creation time.

Summary

Materialized views cache expensive query results as physical tables. Create them for dashboard summaries, leaderboards, search denormalization, and any frequently-read aggregation. Add indexes to match your query patterns. Use REFRESH MATERIALIZED VIEW CONCURRENTLY with a scheduled job to keep data fresh without blocking readers. Track refresh times in a log table so your application knows how stale the data is.