Materialized Views in PostgreSQL: The Complete Guide

What are materialized views?

A materialized view is a database object that stores the result of a query on disk. Unlike regular views, which re-execute the underlying query every time you select from them, materialized views compute the result once and serve it directly — like a pre-built cache that lives inside PostgreSQL.

The trade-off is simple: you get dramatically faster reads in exchange for slightly stale data. For most analytics, dashboards, and reporting queries, this is an excellent deal.

When should you use materialized views?

Materialized views shine when a query is expensive to compute but doesn't need real-time freshness. Common cases:

• Aggregation queries — COUNT, SUM, AVG across large tables
• Multi-table joins — queries that join 3+ tables with filtering
• Dashboard data — metrics that refresh on a schedule, not per-request
• Leaderboards and rankings — sorted results over large datasets
• Denormalized reporting — flattened views of normalized data

Here's a query that's a perfect candidate — an expensive join with aggregation:

EXPLAIN ANALYZE
SELECT c.name, COUNT(o.id) AS order_count, SUM(o.total) AS revenue
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE o.created_at > NOW() - INTERVAL '30 days'
GROUP BY c.name
ORDER BY revenue DESC;

Sort  (cost=45892.13..45892.63 rows=200 width=48) (actual time=487.231..487.298 rows=200 loops=1)
  Sort Key: revenue DESC
  ->  HashAggregate  (cost=45882.13..45884.13 rows=200 width=48) (actual time=486.891..487.012 rows=200 loops=1)
        ->  Hash Join  (cost=12.50..43382.13 rows=500000 width=20) (actual time=0.342..312.567 rows=487293 loops=1)
Planning Time: 0.284 ms
Execution Time: 487.452 ms

487ms per execution. If this runs on every page load, that's a serious bottleneck.

How do you create a materialized view?

Wrap the slow query in CREATE MATERIALIZED VIEW:

CREATE MATERIALIZED VIEW mv_customer_revenue_30d AS
SELECT c.name, COUNT(o.id) AS order_count, SUM(o.total) AS revenue
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE o.created_at > NOW() - INTERVAL '30 days'
GROUP BY c.name
ORDER BY revenue DESC;

PostgreSQL executes the query once and stores the result. Now query the view instead:

SELECT * FROM mv_customer_revenue_30d ORDER BY revenue DESC LIMIT 20;
-- Execution time: 1.2ms (was 487ms)

What are the refresh strategies?

Materialized views don't update automatically — you control when they refresh. Two options:

-- Full refresh (locks reads during refresh)
REFRESH MATERIALIZED VIEW mv_customer_revenue_30d;

-- Concurrent refresh (no read lock, requires unique index)
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_customer_revenue_30d;

• Full refresh — drops and rebuilds the entire view. Fast, but blocks reads during the rebuild.
• Concurrent refresh — computes the new result alongside the old one, then swaps. No read downtime, but requires a unique index on the view and takes longer.

Common scheduling patterns:

• Cron job — pg_cron extension, runs inside PostgreSQL
• Application-level — periodic task in your job queue (Celery, Sidekiq, etc.)
• Write-triggered — refresh after N writes to the underlying tables
• Automatic — let Gold Lapel handle it based on write frequency and staleness thresholds

How should you index materialized views?

Materialized views support indexes just like regular tables. Without indexes, PostgreSQL does a sequential scan on the view — which defeats the purpose for large result sets.

CREATE UNIQUE INDEX idx_mv_customer_revenue_name
ON mv_customer_revenue_30d (name);

CREATE INDEX idx_mv_customer_revenue_rev
ON mv_customer_revenue_30d (revenue DESC);

A unique index is required for REFRESH CONCURRENTLY. Additional indexes speed up filtered queries against the view.

How does Gold Lapel automate materialized views?

Gold Lapel sits between your application and PostgreSQL as a transparent proxy. It watches your query patterns, identifies expensive repeated queries, and automatically:

• Creates materialized views for high-impact patterns
• Adds indexes to each view
• Rewrites matching queries to read from the view
• Monitors write frequency and refreshes views when data changes
• Drops views for patterns that stop appearing

goldlapel --upstream 'postgresql://goldlapel:password@localhost:5432/mydb'

# Gold Lapel detects the pattern, creates the materialized view,
# indexes it, and rewrites matching queries automatically.

No schema changes, no application code changes, no manual refresh scheduling. The proxy handles it end-to-end.