Materialized views represent a powerful middle ground between in-table denormalization and external caching. Unlike regular views (which are stored queries executed on read), materialized views persist query results as actual table data. They combine the flexibility of complex queries with the performance of pre-computed results.

Where in-table denormalization requires application code to maintain consistency, and external caches require separate infrastructure, materialized views are managed entirely within the database. The database handles storage, can maintain indexes on materialized results, and provides mechanisms for refresh.

To understand materialized views, let's contrast them with regular views:

Regular View:

CREATE VIEW active_premium_users AS
SELECT u.user_id, u.name, u.email, 
       COUNT(o.order_id) AS order_count,
       SUM(o.total) AS lifetime_value
FROM users u
JOIN orders o ON u.user_id = o.user_id
WHERE u.subscription_tier = 'premium'
  AND u.status = 'active'
GROUP BY u.user_id, u.name, u.email;

When you SELECT * FROM active_premium_users, the database executes this entire query. The view is essentially stored SQL text—a macro that expands at query time.

Materialized View:

CREATE MATERIALIZED VIEW active_premium_users AS
SELECT u.user_id, u.name, u.email, 
       COUNT(o.order_id) AS order_count,
       SUM(o.total) AS lifetime_value
FROM users u
JOIN orders o ON u.user_id = o.user_id
WHERE u.subscription_tier = 'premium'
  AND u.status = 'active'
GROUP BY u.user_id, u.name, u.email;

The database executes the query once and stores the results as a table. Subsequent reads access the stored data—no query re-execution needed.

When Materialized Views Excel:

1. Expensive Aggregations — Queries that aggregate millions of rows into summaries
2. Complex Joins — Multi-table joins that are read frequently
3. Reporting Queries — Dashboards that query the same aggregations repeatedly
4. Denormalized Search — Flattened data for simpler query patterns
5. Historical Snapshots — Point-in-time data that shouldn't change

The Trade-off:

Materialized views trade data freshness for read performance. The stored results become stale when base tables change. You must decide how and when to refresh—immediately, periodically, or on-demand.

Let's examine materialized view creation across major database systems:

How and when you refresh materialized views determines the balance between freshness and performance. The major strategies are:

1. Complete Refresh:

Re-execute the entire defining query and replace all data.

-- PostgreSQL
REFRESH MATERIALIZED VIEW mv_daily_sales;

-- Oracle
EXEC DBMS_MVIEW.REFRESH('mv_daily_sales', 'C');

Characteristics:

• Simple and always correct
• Expensive for large result sets (re-processes all data)
• Read blocking during refresh (unless concurrent mode)
• Suitable when base data changes extensively or incremental isn't supported

2. Incremental (Fast) Refresh:

Apply only the changes since the last refresh.

-- Oracle (requires materialized view log)
CREATE MATERIALIZED VIEW LOG ON orders
WITH ROWID, SEQUENCE (product_id, quantity, amount, created_at)
INCLUDING NEW VALUES;

-- Then create MV with FAST refresh
CREATE MATERIALIZED VIEW mv_daily_sales
REFRESH FAST ON DEMAND
AS SELECT ...;

-- Refresh applies only changes
EXEC DBMS_MVIEW.REFRESH('mv_daily_sales', 'F');

How It Works:

1. Base table has a materialized view log (change capture table)
2. Log records all inserts, updates, deletes with old/new values
3. Fast refresh reads the log and applies changes to the MV
4. Log entries are purged after refresh

Limitations:

• Complex queries may not support fast refresh
• Aggregates require specific structure
• Log maintenance adds write overhead to base tables

3. On-Commit Refresh:

Refresh automatically when base table transactions commit.

-- Oracle
CREATE MATERIALIZED VIEW mv_daily_sales
REFRESH FAST ON COMMIT  -- Refresh after each commit
AS SELECT ...;

Characteristics:

• Near-real-time freshness
• Adds latency to every write transaction
• Only practical for low-write-volume scenarios
• Best for critical aggregates that must always be current

Unlike regular views, materialized views store data and can be indexed. This is one of their most powerful features—you can optimize the materialized result for specific query patterns that would be impossible to index on the base tables.

Why Index Materialized Views:

1. Aggregated columns — Index on computed SUM, AVG, COUNT for fast filtering
2. Joined fields — Index on fields from multiple base tables now in one structure
3. Derived expressions — Index on computed/derived values
4. Query-specific patterns — Create indexes matching dashboard query WHERE clauses

Indexing Examples:

-- Materialized view
CREATE MATERIALIZED VIEW mv_product_stats AS
SELECT 
    p.product_id,
    p.category_id,
    p.name,
    COALESCE(SUM(oi.quantity), 0) AS total_sold,
    COALESCE(SUM(oi.quantity * oi.unit_price), 0) AS total_revenue,
    COUNT(DISTINCT o.order_id) AS order_count,
    AVG(r.rating) AS avg_rating
FROM products p
LEFT JOIN order_items oi ON p.product_id = oi.product_id
LEFT JOIN orders o ON oi.order_id = o.order_id
LEFT JOIN reviews r ON p.product_id = r.product_id
GROUP BY p.product_id, p.category_id, p.name;

-- Index for category-based queries
CREATE INDEX idx_mv_product_stats_category 
ON mv_product_stats(category_id);

-- Index for filtering by sales performance
CREATE INDEX idx_mv_product_stats_revenue 
ON mv_product_stats(total_revenue DESC);

-- Index for rating-based sorting
CREATE INDEX idx_mv_product_stats_rating 
ON mv_product_stats(avg_rating DESC NULLS LAST);

-- Composite index for dashboard query pattern
CREATE INDEX idx_mv_product_stats_dashboard 
ON mv_product_stats(category_id, total_revenue DESC, avg_rating DESC);

Index Strategy:

PostgreSQL CONCURRENTLY Refresh Requirement:

In PostgreSQL, REFRESH MATERIALIZED VIEW CONCURRENTLY (which allows reads during refresh) requires a unique index on the materialized view:

-- This fails without unique index
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_product_stats;
-- ERROR: cannot refresh concurrently without unique index

-- Solution: Add unique index
CREATE UNIQUE INDEX idx_mv_product_stats_pk 
ON mv_product_stats(product_id);

-- Now concurrent refresh works
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_product_stats;

The unique index allows PostgreSQL to identify which rows changed and perform an efficient differential update rather than a complete replacement.

Let's examine real-world scenarios where materialized views provide significant value:

Case Study: E-Commerce Product Listing:

A product listing page needs to display:

• Product name, description, price
• Category name (from categories table)
• Average rating (aggregated from reviews)
• Total sold count (aggregated from orders)
• Stock status (from inventory)

Without Materialized View:

SELECT 
    p.product_id, p.name, p.description, p.price,
    c.category_name,
    AVG(r.rating) AS avg_rating,
    COUNT(r.review_id) AS review_count,
    COALESCE(SUM(oi.quantity), 0) AS total_sold,
    i.quantity AS stock_quantity
FROM products p
JOIN categories c ON p.category_id = c.category_id
LEFT JOIN reviews r ON p.product_id = r.product_id
LEFT JOIN order_items oi ON p.product_id = oi.product_id
LEFT JOIN inventory i ON p.product_id = i.product_id
WHERE c.category_id = 15
GROUP BY p.product_id, p.name, p.description, p.price, 
         c.category_name, i.quantity
ORDER BY avg_rating DESC NULLS LAST
LIMIT 20;

This query joins 5 tables and aggregates millions of reviews/orders. Response time: 3-10 seconds.

With Materialized View:

-- Create once
CREATE MATERIALIZED VIEW mv_product_listing AS
SELECT 
    p.product_id, p.name, p.description, p.price,
    c.category_id, c.category_name,
    COALESCE(AVG(r.rating), 0) AS avg_rating,
    COUNT(r.review_id) AS review_count,
    COALESCE(SUM(oi.quantity), 0) AS total_sold,
    COALESCE(i.quantity, 0) AS stock_quantity
FROM products p
JOIN categories c ON p.category_id = c.category_id
LEFT JOIN reviews r ON p.product_id = r.product_id
LEFT JOIN order_items oi ON p.product_id = oi.product_id
LEFT JOIN inventory i ON p.product_id = i.product_id
GROUP BY p.product_id, p.name, p.description, p.price, 
         c.category_id, c.category_name, i.quantity;

-- Index for category queries
CREATE INDEX idx_mv_listing_category ON mv_product_listing(category_id, avg_rating DESC);

-- Query the MV
SELECT * FROM mv_product_listing
WHERE category_id = 15
ORDER BY avg_rating DESC NULLS LAST
LIMIT 20;

Response time: 10-50 milliseconds. 100-200x improvement.

Production materialized views require monitoring, scheduling, and error handling:

Scheduling Refresh:

-- PostgreSQL: Using pg_cron extension
CREATE EXTENSION IF NOT EXISTS pg_cron;

-- Schedule refresh every 10 minutes
SELECT cron.schedule(
    'refresh_mv_product_listing',
    '*/10 * * * *',
    'REFRESH MATERIALIZED VIEW CONCURRENTLY mv_product_listing'
);

-- Check schedule
SELECT * FROM cron.job;

-- View recent runs
SELECT * FROM cron.job_run_details ORDER BY start_time DESC LIMIT 10;

Error Handling:

Refresh failures should be caught and alerted:

-- Wrapper function with error handling
CREATE OR REPLACE FUNCTION refresh_mv_with_logging(mv_name TEXT)
RETURNS VOID AS $$
DECLARE
    start_time TIMESTAMP := clock_timestamp();
BEGIN
    EXECUTE format('REFRESH MATERIALIZED VIEW CONCURRENTLY %I', mv_name);
    
    INSERT INTO mv_refresh_log (mv_name, status, duration_ms, refreshed_at)
    VALUES (
        mv_name, 
        'SUCCESS', 
        EXTRACT(MILLISECONDS FROM clock_timestamp() - start_time),
        NOW()
    );
EXCEPTION WHEN OTHERS THEN
    INSERT INTO mv_refresh_log (mv_name, status, error_message, refreshed_at)
    VALUES (mv_name, 'FAILED', SQLERRM, NOW());
    RAISE;  -- Re-raise for alert systems
END;
$$ LANGUAGE plpgsql;

Stale Data Detection:

Implement queries to detect when MVs are stale:

-- Track refresh timestamps
CREATE TABLE mv_refresh_status (
    mv_name TEXT PRIMARY KEY,
    last_refreshed TIMESTAMP,
    refresh_interval_minutes INTEGER
);

-- Alert query: Find stale MVs
SELECT mv_name, 
       last_refreshed,
       NOW() - last_refreshed AS staleness,
       refresh_interval_minutes || ' minutes' AS expected_interval
FROM mv_refresh_status
WHERE NOW() - last_refreshed > (refresh_interval_minutes * INTERVAL '1 minute' * 1.5);

When should you use materialized views versus other denormalization approaches?

Decision Framework:

1. Is the query entirely SQL-based? → Materialized view is a strong candidate
2. Is sub-millisecond latency required? → Consider Redis/cache
3. Do you need real-time consistency? → In-table denormalization or write-through cache
4. Is the database system MySQL? → Summary table (no native MVs)
5. Does the application already manage caching? → May be simpler to extend existing cache
6. Is this for analytics/reporting? → MVs or dedicated OLAP store

We've covered materialized views as a powerful database-native denormalization strategy:

What's Next:

Now that you understand the major denormalization techniques—patterns, industry practices, caching, and materialized views—we'll consolidate with best practices. The final page provides a comprehensive guide for making sound denormalization decisions in your projects.