Materialized Views: Complete Reference

TL;DR

• Bottom line: Materialized views precompute and store query results as a physical table, trading storage space and data freshness for dramatically faster read performance on expensive queries (often 100-1000x speedup).
• Key tool/command: CREATE MATERIALIZED VIEW mv_name AS SELECT ...; (PostgreSQL) / CREATE VIEW ... WITH SCHEMABINDING + CREATE UNIQUE CLUSTERED INDEX (SQL Server)
• Watch out for: Forgetting to refresh — stale data is the #1 source of bugs. In PostgreSQL, non-concurrent refresh blocks all reads on the view.
• Works with: PostgreSQL 9.3+ (native), Oracle 8i+ (native with FAST refresh), SQL Server 2005+ (indexed views), MySQL (manual workaround only).

Constraints

• Materialized views store stale data by design — never use for real-time transactional reads where consistency is required
• PostgreSQL REFRESH MATERIALIZED VIEW (non-concurrent) acquires an exclusive lock — blocks all reads during refresh
• REFRESH CONCURRENTLY requires at least one UNIQUE index covering all rows (no WHERE clause, no expression indexes) [src2]
• SQL Server indexed views require SCHEMABINDING — underlying table schema cannot be altered while the view exists [src4]
• MySQL has no native materialized view support — all workarounds require manual refresh logic [src6]
• Oracle FAST refresh requires materialized view logs on all base tables — logs consume storage and add write overhead [src3]

Quick Reference

Decision Tree

START: Do you need precomputed query results?
├── Query < 50ms already?
│   ├── YES → Use a regular view or direct query (no MV needed)
│   └── NO ↓
├── Can you tolerate stale data?
│   ├── NO → Use regular views with proper indexing, or application cache with invalidation
│   └── YES ↓
├── Which database engine?
│   ├── PostgreSQL ↓
│   │   ├── Need zero-downtime refresh?
│   │   │   ├── YES → CREATE MATERIALIZED VIEW + UNIQUE INDEX + REFRESH CONCURRENTLY
│   │   │   └── NO → CREATE MATERIALIZED VIEW + REFRESH (simpler, blocks reads)
│   │   └── Refresh frequency?
│   │       ├── Daily/hourly → cron job or pg_cron
│   │       └── After each ETL load → trigger REFRESH in pipeline
│   ├── Oracle ↓
│   │   ├── Need real-time freshness?
│   │   │   ├── YES → CREATE MATERIALIZED VIEW ... REFRESH FAST ON COMMIT (+ MV logs)
│   │   │   └── NO → CREATE MATERIALIZED VIEW ... REFRESH FORCE ON DEMAND
│   │   └── Large partitioned tables?
│   │       └── YES → Use PCT (Partition Change Tracking) refresh
│   ├── SQL Server ↓
│   │   ├── Enterprise Edition?
│   │   │   ├── YES → CREATE VIEW WITH SCHEMABINDING + CREATE UNIQUE CLUSTERED INDEX
│   │   │   └── NO → Manual summary table pattern (indexed views auto-match only on Enterprise)
│   │   └── Note: Indexed views auto-update on base table changes (no manual refresh)
│   └── MySQL ↓
│       └── Create summary table + scheduled EVENT or triggers for refresh
└── DEFAULT → PostgreSQL MATERIALIZED VIEW with scheduled REFRESH is the most common pattern

Step-by-Step Guide

1. Create the materialized view (PostgreSQL)

Define the expensive query you want to precompute. The view is populated immediately on creation. [src1]

CREATE MATERIALIZED VIEW mv_monthly_sales AS
SELECT
    date_trunc('month', order_date) AS month,
    product_category,
    COUNT(*) AS order_count,
    SUM(total_amount) AS revenue,
    AVG(total_amount) AS avg_order_value
FROM orders
JOIN order_items USING (order_id)
JOIN products USING (product_id)
WHERE order_date >= '2024-01-01'
GROUP BY 1, 2
WITH DATA;

Verify: SELECT COUNT(*) FROM mv_monthly_sales; → expected: row count > 0

2. Add indexes for query performance

Materialized views are physical tables — index them like any other table. A unique index is required for REFRESH CONCURRENTLY. [src2]

-- Required for REFRESH CONCURRENTLY
CREATE UNIQUE INDEX idx_mv_monthly_sales_pk
    ON mv_monthly_sales (month, product_category);

-- Additional indexes for common query patterns
CREATE INDEX idx_mv_monthly_sales_category
    ON mv_monthly_sales (product_category);

CREATE INDEX idx_mv_monthly_sales_revenue
    ON mv_monthly_sales (revenue DESC);

Verify: \di+ mv_monthly_sales* → expected: should list all three indexes

3. Set up concurrent refresh

Use CONCURRENTLY to avoid blocking reads during refresh. This compares old and new data, applying only deltas. [src2]

REFRESH MATERIALIZED VIEW CONCURRENTLY mv_monthly_sales;

Verify: SELECT * FROM mv_monthly_sales ORDER BY month DESC LIMIT 5; → expected: updated data

4. Automate refresh with pg_cron

Schedule periodic refresh using pg_cron (or external cron). [src1]

-- Install pg_cron extension (requires superuser)
CREATE EXTENSION IF NOT EXISTS pg_cron;

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

Verify: SELECT * FROM cron.job WHERE jobname = 'refresh-monthly-sales'; → expected: scheduled job row

5. Monitor view freshness

Check when the view was last refreshed and whether it needs updating. [src1]

SELECT schemaname, matviewname, hasindexes, ispopulated
FROM pg_matviews
WHERE matviewname = 'mv_monthly_sales';

Verify: ispopulated should be true

6. Handle refresh failures gracefully

Wrap refresh in error handling for production pipelines. [src2]

CREATE OR REPLACE FUNCTION refresh_mv_safely(view_name text)
RETURNS void AS $$
BEGIN
    EXECUTE format('REFRESH MATERIALIZED VIEW CONCURRENTLY %I', view_name);
    RAISE NOTICE 'Successfully refreshed %', view_name;
EXCEPTION WHEN OTHERS THEN
    RAISE WARNING 'Failed to refresh %: %', view_name, SQLERRM;
    BEGIN
        EXECUTE format('REFRESH MATERIALIZED VIEW %I', view_name);
        RAISE NOTICE 'Fallback refresh succeeded for %', view_name;
    EXCEPTION WHEN OTHERS THEN
        RAISE EXCEPTION 'All refresh attempts failed for %: %', view_name, SQLERRM;
    END;
END;
$$ LANGUAGE plpgsql;

Verify: SELECT refresh_mv_safely('mv_monthly_sales'); → expected: success notice

Code Examples

PostgreSQL: Materialized View with Concurrent Refresh

-- Input:  orders + products tables with millions of rows
-- Output: precomputed category-level daily aggregations

-- 1. Create the materialized view
CREATE MATERIALIZED VIEW mv_daily_category_stats AS
SELECT
    order_date::date AS day,
    p.category,
    COUNT(DISTINCT o.customer_id) AS unique_customers,
    COUNT(*) AS order_count,
    SUM(oi.quantity * oi.unit_price) AS revenue
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
GROUP BY 1, 2
WITH DATA;

-- 2. Create unique index (required for CONCURRENTLY)
CREATE UNIQUE INDEX ON mv_daily_category_stats (day, category);

-- 3. Refresh without blocking reads
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_daily_category_stats;

-- 4. Query (uses indexes, sub-ms response)
SELECT category, SUM(revenue) AS total_revenue
FROM mv_daily_category_stats
WHERE day >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY category
ORDER BY total_revenue DESC;

Oracle: FAST Refresh with Materialized View Logs

-- Input:  sales table with frequent inserts
-- Output: incrementally refreshed aggregation

-- 1. Create materialized view log on base table
CREATE MATERIALIZED VIEW LOG ON sales
WITH ROWID, SEQUENCE (product_id, customer_id, sale_date, amount)
INCLUDING NEW VALUES;

-- 2. Create MV with FAST refresh ON DEMAND
CREATE MATERIALIZED VIEW mv_product_sales
BUILD IMMEDIATE
REFRESH FAST ON DEMAND
ENABLE QUERY REWRITE
AS
SELECT product_id, COUNT(*) AS sale_count,
       SUM(amount) AS total_revenue, COUNT(*) AS cnt
FROM sales GROUP BY product_id;

-- 3. After new data loads, refresh incrementally
EXEC DBMS_MVIEW.REFRESH('mv_product_sales', 'F');

-- 4. Check staleness
SELECT mview_name, staleness, last_refresh_type, last_refresh_date
FROM user_mviews WHERE mview_name = 'MV_PRODUCT_SALES';

SQL Server: Indexed View (Materialized View Equivalent)

-- Input:  orders + order_items tables
-- Output: automatically maintained materialized aggregation

-- 1. Create the view WITH SCHEMABINDING (required)
CREATE VIEW dbo.vw_category_revenue
WITH SCHEMABINDING
AS
SELECT p.category, COUNT_BIG(*) AS order_count,
       SUM(oi.quantity * oi.unit_price) AS total_revenue
FROM dbo.order_items oi
INNER JOIN dbo.products p ON oi.product_id = p.product_id
GROUP BY p.category;
GO

-- 2. Materialize with unique clustered index
CREATE UNIQUE CLUSTERED INDEX idx_vw_category_revenue
ON dbo.vw_category_revenue (category);
GO

-- 3. Query (Enterprise auto-uses; Standard needs NOEXPAND)
SELECT category, total_revenue
FROM dbo.vw_category_revenue WITH (NOEXPAND)
ORDER BY total_revenue DESC;

MySQL: Manual Materialized View Pattern

-- Input:  orders table (MySQL has no native MVs)
-- Output: manually maintained summary table

-- 1. Create the summary table
CREATE TABLE mv_daily_sales (
    sale_date DATE NOT NULL,
    product_id INT NOT NULL,
    total_quantity INT NOT NULL DEFAULT 0,
    total_revenue DECIMAL(12,2) NOT NULL DEFAULT 0,
    last_refreshed TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (sale_date, product_id)
) ENGINE=InnoDB;

-- 2. Full refresh procedure
DELIMITER //
CREATE PROCEDURE refresh_mv_daily_sales()
BEGIN
    TRUNCATE TABLE mv_daily_sales;
    INSERT INTO mv_daily_sales (sale_date, product_id, total_quantity, total_revenue)
    SELECT DATE(order_date), product_id,
           SUM(quantity), SUM(quantity * unit_price)
    FROM orders GROUP BY DATE(order_date), product_id;
END //
DELIMITER ;

-- 3. Automate with MySQL Event Scheduler
SET GLOBAL event_scheduler = ON;
CREATE EVENT refresh_daily_sales
ON SCHEDULE EVERY 1 HOUR
DO CALL refresh_mv_daily_sales();

Anti-Patterns

Wrong: Refreshing without CONCURRENTLY on a production view

-- BAD — blocks all SELECT queries during refresh (can take minutes on large tables)
REFRESH MATERIALIZED VIEW mv_dashboard_stats;

Correct: Use CONCURRENTLY with a unique index

-- GOOD — allows concurrent reads during refresh
CREATE UNIQUE INDEX ON mv_dashboard_stats (report_date, department_id);
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_dashboard_stats;

Wrong: No indexes on the materialized view

-- BAD — materialized view has no indexes, queries do sequential scans
CREATE MATERIALIZED VIEW mv_user_activity AS
SELECT user_id, COUNT(*) as actions, MAX(created_at) as last_active
FROM activity_log GROUP BY user_id;

-- This query seq-scans the entire MV
SELECT * FROM mv_user_activity WHERE user_id = 12345;

Correct: Index materialized views like regular tables

-- GOOD — add indexes matching your query patterns
CREATE MATERIALIZED VIEW mv_user_activity AS
SELECT user_id, COUNT(*) as actions, MAX(created_at) as last_active
FROM activity_log GROUP BY user_id;

CREATE UNIQUE INDEX ON mv_user_activity (user_id);
CREATE INDEX ON mv_user_activity (last_active DESC);

-- Now uses index scan: sub-ms response
SELECT * FROM mv_user_activity WHERE user_id = 12345;

Wrong: No refresh strategy for the materialized view

-- BAD — created the view but never refreshes it
CREATE MATERIALIZED VIEW mv_inventory_levels AS
SELECT product_id, SUM(quantity) AS stock
FROM inventory GROUP BY product_id;
-- ... weeks later, stock levels are completely wrong

Correct: Always pair MV creation with automated refresh

-- GOOD — materialized view + scheduled refresh + monitoring
CREATE MATERIALIZED VIEW mv_inventory_levels AS
SELECT product_id, SUM(quantity) AS stock
FROM inventory GROUP BY product_id;

CREATE UNIQUE INDEX ON mv_inventory_levels (product_id);

-- Refresh every 15 minutes
SELECT cron.schedule('refresh-inventory', '*/15 * * * *',
    'REFRESH MATERIALIZED VIEW CONCURRENTLY mv_inventory_levels');

Wrong: Oracle MV without logs attempting FAST refresh

-- BAD — FORCE silently falls back to COMPLETE refresh every time
CREATE MATERIALIZED VIEW mv_sales_summary
REFRESH FORCE ON DEMAND AS
SELECT product_id, SUM(amount) FROM sales GROUP BY product_id;

EXEC DBMS_MVIEW.REFRESH('mv_sales_summary', '?');

Correct: Create MV logs first for true incremental refresh

-- GOOD — MV log enables true incremental refresh
CREATE MATERIALIZED VIEW LOG ON sales
WITH ROWID, SEQUENCE (product_id, amount) INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW mv_sales_summary
REFRESH FAST ON DEMAND AS
SELECT product_id, SUM(amount) AS total, COUNT(*) AS cnt
FROM sales GROUP BY product_id;

EXEC DBMS_MVIEW.REFRESH('mv_sales_summary', 'F');

Common Pitfalls

• Stale data served to users: Materialized view data is a snapshot. If refresh fails silently, applications serve outdated results for days. Fix: Monitor refresh jobs and alert on failure; add last_refreshed column or check pg_stat_user_tables. [src1]
• REFRESH CONCURRENTLY fails with no unique index: PostgreSQL raises ERROR: cannot refresh materialized view concurrently without at least one unique index. Fix: CREATE UNIQUE INDEX ON mv_name (col1, col2); before using CONCURRENTLY. [src2]
• SQL Server COUNT vs COUNT_BIG: Indexed views require COUNT_BIG(*) instead of COUNT(*). Using COUNT causes creation to fail. Fix: Always use COUNT_BIG(*) in indexed view definitions. [src4]
• SQL Server SCHEMABINDING locks schema changes: WITH SCHEMABINDING prevents ALTER TABLE on any referenced column. Fix: Drop and recreate indexed view around migrations. [src4]
• Oracle MV log bloat: Materialized view logs grow unboundedly if refresh doesn't run. Fix: Schedule regular refreshes and monitor log sizes; set PURGE IMMEDIATE. [src3]
• PostgreSQL MV eats disk on refresh: Non-concurrent REFRESH writes an entirely new copy before swapping. A 10GB MV needs 20GB free during refresh. Fix: Monitor disk space; use CONCURRENTLY for delta-based updates. [src2]
• MySQL trigger-based MV slows writes: Implementing MVs via triggers adds latency to every write operation. Fix: Use scheduled EVENT-based refresh instead of triggers for write-heavy tables. [src6]
• Querying MV created WITH NO DATA: CREATE MATERIALIZED VIEW ... WITH NO DATA creates the structure without populating it. Querying raises ERROR: materialized view has not been populated. Fix: Run REFRESH MATERIALIZED VIEW mv_name; before first query. [src1]

Diagnostic Commands

-- PostgreSQL: List all materialized views and their state
SELECT schemaname, matviewname, hasindexes, ispopulated
FROM pg_matviews ORDER BY matviewname;

-- PostgreSQL: Check materialized view size on disk
SELECT pg_size_pretty(pg_total_relation_size('mv_monthly_sales')) AS total_size;

-- PostgreSQL: Check indexes on a materialized view
SELECT indexname, indexdef FROM pg_indexes
WHERE tablename = 'mv_monthly_sales';

-- PostgreSQL: Check if CONCURRENTLY is possible (has unique index?)
SELECT i.relname AS index_name, ix.indisunique
FROM pg_index ix
JOIN pg_class i ON i.oid = ix.indexrelid
JOIN pg_class t ON t.oid = ix.indrelid
WHERE t.relname = 'mv_monthly_sales' AND ix.indisunique = true;

-- Oracle: Check materialized view staleness and last refresh
SELECT mview_name, staleness, last_refresh_type, last_refresh_date, compile_state
FROM user_mviews ORDER BY mview_name;

-- Oracle: Check materialized view log sizes
SELECT log_owner, master, log_table, rowids, primary_key, filter_columns
FROM all_mview_logs ORDER BY master;

-- SQL Server: List indexed views
SELECT v.name AS view_name, i.name AS index_name, i.type_desc
FROM sys.views v
JOIN sys.indexes i ON v.object_id = i.object_id
WHERE i.type_desc = 'CLUSTERED' ORDER BY v.name;

-- PostgreSQL: Check pg_cron refresh schedule
SELECT jobid, schedule, command, active, jobname
FROM cron.job WHERE command LIKE '%REFRESH%';

Version History & Compatibility

When to Use / When Not to Use

Important Caveats

• PostgreSQL has no built-in auto-refresh: Unlike Oracle's ON COMMIT or SQL Server's auto-maintained indexed views, PostgreSQL materialized views require explicit refresh via pg_cron, external scheduler, or application-triggered refresh.
• REFRESH CONCURRENTLY is slower than full refresh for large changes: When most data changes, CONCURRENTLY must diff old and new result sets row-by-row. For full reloads, plain REFRESH is faster. [src2]
• SQL Server indexed views auto-update on writes: Every INSERT/UPDATE/DELETE on base tables incurs maintenance overhead on the indexed view — beneficial for reads but harmful for write-heavy workloads. [src4]
• Oracle Query Rewrite can silently redirect queries: When ENABLE QUERY REWRITE is set, Oracle may transparently rewrite base table queries to use the MV. Monitor STALENESS in USER_MVIEWS. [src3]
• Storage cost is real: A denormalized MV can be larger than the base tables. Always check pg_total_relation_size() or equivalent after creation.