Eric Campbell 5 min read PostgreSQL

How to Use Materialized Views in PostgreSQL

Materialized views are PostgreSQL's answer to expensive queries that you run repeatedly. Unlike regular views, which are just stored SQL queries that execute every time you reference them,...

Key Insights

  • Materialized views store query results physically on disk, turning expensive multi-table joins and aggregations into simple table scans that can be 10-100x faster for complex analytical queries
  • The CONCURRENTLY option enables non-blocking refreshes but requires a unique index, making it essential for production systems where query availability matters more than absolute real-time accuracy
  • Materialized views trade data freshness for performance—they’re ideal for dashboards and reports where 5-minute-old data is acceptable, but problematic for transactional queries requiring up-to-the-second accuracy

Introduction to Materialized Views

Materialized views are PostgreSQL’s answer to expensive queries that you run repeatedly. Unlike regular views, which are just stored SQL queries that execute every time you reference them, materialized views physically store the query results on disk. When you query a materialized view, PostgreSQL reads pre-computed data rather than executing complex joins and aggregations.

The performance difference is substantial. A regular view that joins five tables, aggregates millions of rows, and takes 30 seconds to execute becomes a simple table scan that completes in milliseconds when converted to a materialized view. You’re essentially caching query results at the database level.

Use materialized views when you have expensive analytical queries, reporting dashboards, or data aggregation needs where slight staleness is acceptable. They’re perfect for metrics that update hourly or daily—think sales dashboards, user analytics, or financial reports. Don’t use them for transactional data that must be real-time accurate or queries that are already fast.

Creating Your First Materialized View

The syntax is straightforward. Use CREATE MATERIALIZED VIEW followed by your query:

CREATE MATERIALIZED VIEW sales_summary AS
SELECT 
    p.category,
    DATE_TRUNC('day', o.order_date) AS order_day,
    COUNT(DISTINCT o.order_id) AS total_orders,
    COUNT(DISTINCT o.customer_id) AS unique_customers,
    SUM(oi.quantity * oi.unit_price) AS total_revenue,
    AVG(oi.quantity * oi.unit_price) AS avg_order_value
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
WHERE o.order_date >= CURRENT_DATE - INTERVAL '90 days'
GROUP BY p.category, DATE_TRUNC('day', o.order_date);

This creates a materialized view that aggregates sales data across orders, order items, and products. The query involves multiple joins and aggregations—exactly the type of operation that benefits from materialization. Once created, you query it like a regular table:

SELECT * FROM sales_summary 
WHERE category = 'Electronics' 
ORDER BY order_day DESC 
LIMIT 30;

This query returns instantly because the aggregation work is already done.

Refreshing Materialized Views

Materialized views don’t update automatically. You must explicitly refresh them to incorporate new data:

REFRESH MATERIALIZED VIEW sales_summary;

This command blocks all queries against the view until the refresh completes. For a view that takes 2 minutes to refresh, your dashboard is unavailable for those 2 minutes—unacceptable in production.

The solution is concurrent refresh:

REFRESH MATERIALIZED VIEW CONCURRENTLY sales_summary;

Concurrent refresh allows queries to continue reading old data while the new version builds in the background. Once complete, PostgreSQL atomically swaps the old data for new. However, concurrent refresh requires a unique index:

CREATE UNIQUE INDEX sales_summary_unique_idx 
ON sales_summary (category, order_day);

For automated refreshes, use pg_cron if you have it installed:

-- Refresh every hour at minute 0
SELECT cron.schedule('refresh-sales-summary', '0 * * * *', 
    'REFRESH MATERIALIZED VIEW CONCURRENTLY sales_summary');

Alternatively, use a simple cron job on your application server:

# Refresh every 15 minutes
*/15 * * * * psql -d yourdb -c "REFRESH MATERIALIZED VIEW CONCURRENTLY sales_summary"

Indexing Materialized Views

Materialized views are physical tables, so they benefit from indexes just like regular tables. Index the columns you filter and sort by:

-- Index for filtering by category
CREATE INDEX idx_sales_summary_category 
ON sales_summary (category);

-- Index for date range queries
CREATE INDEX idx_sales_summary_order_day 
ON sales_summary (order_day DESC);

-- Composite index for common query patterns
CREATE INDEX idx_sales_summary_category_day 
ON sales_summary (category, order_day DESC);

Remember that concurrent refresh requires at least one unique index. Design your materialized view so that some combination of columns is naturally unique. If no natural unique key exists, add a generated column:

CREATE MATERIALIZED VIEW sales_summary AS
SELECT 
    ROW_NUMBER() OVER () AS row_id,
    p.category,
    DATE_TRUNC('day', o.order_date) AS order_day,
    -- rest of query
FROM orders o
-- rest of joins
GROUP BY p.category, DATE_TRUNC('day', o.order_date);

CREATE UNIQUE INDEX sales_summary_unique_idx 
ON sales_summary (row_id);

Performance Comparison and Use Cases

Let’s compare a complex analytical query as a regular view versus a materialized view:

-- Regular view
CREATE VIEW customer_lifetime_value AS
SELECT 
    c.customer_id,
    c.email,
    COUNT(DISTINCT o.order_id) AS order_count,
    SUM(oi.quantity * oi.unit_price) AS total_spent,
    MAX(o.order_date) AS last_order_date,
    AVG(oi.quantity * oi.unit_price) AS avg_order_value
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
LEFT JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY c.customer_id, c.email;

-- Test the regular view
EXPLAIN ANALYZE SELECT * FROM customer_lifetime_value 
WHERE total_spent > 1000 
ORDER BY total_spent DESC 
LIMIT 100;

On a database with 100K customers and 1M orders, this might show:

Planning Time: 2.431 ms
Execution Time: 8,432.156 ms

Now create it as a materialized view:

CREATE MATERIALIZED VIEW customer_lifetime_value_mv AS
SELECT 
    c.customer_id,
    c.email,
    COUNT(DISTINCT o.order_id) AS order_count,
    SUM(oi.quantity * oi.unit_price) AS total_spent,
    MAX(o.order_date) AS last_order_date,
    AVG(oi.quantity * oi.unit_price) AS avg_order_value
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
LEFT JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY c.customer_id, c.email;

CREATE UNIQUE INDEX clv_mv_customer_idx 
ON customer_lifetime_value_mv (customer_id);

CREATE INDEX clv_mv_total_spent_idx 
ON customer_lifetime_value_mv (total_spent DESC);

-- Test the materialized view
EXPLAIN ANALYZE SELECT * FROM customer_lifetime_value_mv 
WHERE total_spent > 1000 
ORDER BY total_spent DESC 
LIMIT 100;

Results:

Planning Time: 0.312 ms
Execution Time: 2.847 ms

That’s a 2,960x improvement—from 8.4 seconds to 2.8 milliseconds.

Best Practices and Limitations

Storage considerations: Materialized views consume disk space proportional to their result set size. A view aggregating 10M rows into 100K summary rows is fine. A view that “aggregates” 10M rows into 9M rows wastes space and offers minimal benefit.

Staleness trade-offs: Understand your freshness requirements. Hourly refreshes work for executive dashboards. Minute-by-minute refreshes might work for operational dashboards. If you need real-time data, materialized views aren’t the answer—consider regular views with better indexes or a caching layer.

When NOT to use materialized views:

  • Queries that are already fast (sub-100ms)
  • Data that must be real-time accurate
  • Highly selective queries that only access a tiny fraction of data
  • Write-heavy tables where refresh overhead exceeds query savings

Maintenance tips:

  • Monitor view size with pg_total_relation_size('view_name')
  • Track refresh duration to ensure it fits your refresh window
  • Set up alerts when refresh fails
  • Consider partitioning the underlying tables if views become too large

Advanced Patterns

For large materialized views, full refreshes become impractical. Implement incremental refresh by tracking what’s changed:

-- Add a last_refresh tracking table
CREATE TABLE mv_refresh_log (
    view_name TEXT PRIMARY KEY,
    last_refresh_time TIMESTAMPTZ
);

-- Incremental refresh function
CREATE OR REPLACE FUNCTION refresh_sales_summary_incremental()
RETURNS void AS $$
DECLARE
    last_refresh TIMESTAMPTZ;
BEGIN
    -- Get last refresh time
    SELECT last_refresh_time INTO last_refresh
    FROM mv_refresh_log
    WHERE view_name = 'sales_summary';
    
    -- If first run, do full refresh
    IF last_refresh IS NULL THEN
        REFRESH MATERIALIZED VIEW CONCURRENTLY sales_summary;
    ELSE
        -- Delete changed rows
        DELETE FROM sales_summary
        WHERE order_day >= last_refresh - INTERVAL '1 day';
        
        -- Insert updated rows
        INSERT INTO sales_summary
        SELECT 
            p.category,
            DATE_TRUNC('day', o.order_date) AS order_day,
            COUNT(DISTINCT o.order_id) AS total_orders,
            COUNT(DISTINCT o.customer_id) AS unique_customers,
            SUM(oi.quantity * oi.unit_price) AS total_revenue,
            AVG(oi.quantity * oi.unit_price) AS avg_order_value
        FROM orders o
        JOIN order_items oi ON o.order_id = oi.order_id
        JOIN products p ON oi.product_id = p.product_id
        WHERE o.order_date >= last_refresh - INTERVAL '1 day'
        GROUP BY p.category, DATE_TRUNC('day', o.order_date);
    END IF;
    
    -- Update refresh log
    INSERT INTO mv_refresh_log VALUES ('sales_summary', NOW())
    ON CONFLICT (view_name) 
    DO UPDATE SET last_refresh_time = NOW();
END;
$$ LANGUAGE plpgsql;

This pattern only reprocesses recent data, dramatically reducing refresh time for large datasets. The one-day overlap ensures you catch late-arriving updates.

Materialized views are a powerful tool for optimizing read-heavy analytical workloads. Use them strategically where the performance gain justifies the staleness trade-off, and your dashboards will thank you.

Liked this? There's more.

Every week: one practical technique, explained simply, with code you can use immediately.