SQL Materialized Views (PostgreSQL / Oracle)

Regular views are convenient, but they have one fundamental limitation: every time you query a view, the database executes the underlying query from scratch. When that underlying query involves heavy joins across millions of rows, complex aggregations, or expensive calculations, this live execution can be painfully slow. This is the exact problem that SQL materialized views solve.

A materialized view runs its query once, stores the result physically on disk, and serves subsequent reads directly from that stored snapshot. Instead of re-computing a ten-second aggregation on every request, you compute it once, cache it, and read it in milliseconds. The trade-off is that the data can become stale and needs to be refreshed periodically.

This guide covers what materialization is, how to create materialized views in PostgreSQL and Oracle, how to refresh them, and when they are the right performance optimization tool for your workload. Every concept is paired with practical examples and clear outputs.

What Is Materialization?

In database terminology, materialization means executing a query and physically storing the result set. A regular (virtual) view stores only the query definition. A materialized view stores the query definition and its result data.

Think of it like the difference between a live dashboard that queries the database on every page load versus a nightly report that is generated once and served as a static file. The live dashboard is always current but potentially slow. The static report is fast but may be hours old.

When Materialization Makes Sense

Materialized views are the right choice when:

• The underlying query is expensive (heavy joins, large aggregations, complex calculations)
• The data changes infrequently relative to how often it is read
• Slight staleness is acceptable for your use case
• You need to serve dashboards, reports, or analytics quickly
• You want to precompute results that would otherwise require users to wait

Materialized views are not the right choice when:

• Data must be real-time (e.g., live inventory counts, banking balances)
• The underlying data changes every few seconds and must always be current
• The underlying query is already fast enough with a regular view
• Storage space is extremely constrained

The Sample Data

We will use the following tables, simulating an e-commerce system:

customers table:

CREATE TABLE customers (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    region VARCHAR(50) NOT NULL,
    signup_date DATE NOT NULL
);
INSERT INTO customers (id, name, region, signup_date) VALUES
(101, 'Acme Corp',  'North', '2023-06-15'),
(102, 'Globex Inc', 'South', '2023-08-20'),
(103, 'Initech',    'North', '2023-11-01'),
(104, 'Umbrella',   'West',  '2024-01-10');

orders table (imagine millions of rows in production):

CREATE TABLE orders (
    id INT PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date DATE NOT NULL,
    amount DECIMAL(12,2) NOT NULL,
    status VARCHAR(20) NOT NULL,
    CONSTRAINT fk_orders_customer
        FOREIGN KEY (customer_id)
        REFERENCES customers(id)
);
INSERT INTO orders (id, customer_id, order_date, amount, status) VALUES
(1, 101, '2024-01-05', 250, 'completed'),
(2, 102, '2024-01-12', 430, 'completed'),
(3, 101, '2024-01-20', 180, 'completed'),
(4, 103, '2024-02-08', 520, 'completed'),
(5, 102, '2024-02-15', 310, 'cancelled'),
(6, 101, '2024-03-01', 275, 'completed'),
(7, 103, '2024-03-10', 190, 'completed'),
(8, 104, '2024-03-18', 640, 'completed'),
(9, 102, '2024-04-02', 380, 'pending'),
(10,104, '2024-04-10', 450, 'completed');

products table (for later examples):

CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    category VARCHAR(50) NOT NULL,
    price DECIMAL(10,2) NOT NULL
);
INSERT INTO products (id, name, category, price) VALUES
(1, 'Widget Pro',   'Electronics', 79.99),
(2, 'Gadget Max',   'Electronics', 49.99),
(3, 'Office Chair', 'Furniture',   299),
(4, 'Desk Lamp',    'Furniture',   45);

CREATE MATERIALIZED VIEW

PostgreSQL Syntax

CREATE MATERIALIZED VIEW view_name AS
SELECT ...
FROM ...
[WITH [NO] DATA];

The optional WITH DATA (the default) populates the view immediately. WITH NO DATA creates the view definition but leaves it empty until you manually refresh it.

Oracle Syntax

CREATE MATERIALIZED VIEW view_name
[BUILD IMMEDIATE | BUILD DEFERRED]
[REFRESH FAST | COMPLETE | FORCE]
[ON DEMAND | ON COMMIT]
AS
SELECT ...
FROM ...;

Oracle offers significantly more options for controlling when and how the materialized view is refreshed. We will cover these options in detail.

Your First Materialized View (PostgreSQL)

Let's create a materialized view that summarizes monthly revenue:

CREATE MATERIALIZED VIEW mv_monthly_revenue AS
SELECT
    DATE_TRUNC('month', o.order_date) AS revenue_month,
    COUNT(*) AS total_orders,
    COUNT(*) FILTER (WHERE o.status = 'completed') AS completed_orders,
    SUM(CASE WHEN o.status = 'completed' THEN o.amount ELSE 0 END) AS revenue,
    ROUND(AVG(CASE WHEN o.status = 'completed' THEN o.amount END), 2) AS avg_order_value
FROM orders o
GROUP BY DATE_TRUNC('month', o.order_date)
ORDER BY revenue_month;

At this moment, the database executes the query and stores the results on disk. Let's query it:

SELECT * FROM mv_monthly_revenue;

Output:

This query returns instantly because it reads from precomputed, stored data. No aggregation happens at query time.

Your First Materialized View (Oracle)

CREATE MATERIALIZED VIEW mv_monthly_revenue
BUILD IMMEDIATE
REFRESH COMPLETE ON DEMAND
AS
SELECT
    TRUNC(o.order_date, 'MONTH') AS revenue_month,
    COUNT(*) AS total_orders,
    SUM(CASE WHEN o.status = 'completed' THEN 1 ELSE 0 END) AS completed_orders,
    SUM(CASE WHEN o.status = 'completed' THEN o.amount ELSE 0 END) AS revenue,
    ROUND(AVG(CASE WHEN o.status = 'completed' THEN o.amount END), 2) AS avg_order_value
FROM orders o
GROUP BY TRUNC(o.order_date, 'MONTH');

The key Oracle-specific options:

• BUILD IMMEDIATE: Populate the view right now (vs. BUILD DEFERRED which creates it empty)
• REFRESH COMPLETE: Refresh by re-running the entire query (vs. FAST which applies incremental changes)
• ON DEMAND: Refresh only when explicitly requested (vs. ON COMMIT which refreshes after every transaction)

Creating Without Data (PostgreSQL)

Sometimes you want to define the view structure now but populate it later (e.g., during a maintenance window):

CREATE MATERIALIZED VIEW mv_customer_summary AS
SELECT
    c.id AS customer_id,
    c.name AS customer_name,
    c.region,
    COUNT(o.id) AS lifetime_orders,
    COALESCE(SUM(o.amount), 0) AS lifetime_spent,
    MAX(o.order_date) AS last_order_date
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id AND o.status = 'completed'
GROUP BY c.id, c.name, c.region
WITH NO DATA;

Querying this view before it is populated will throw an error:

SELECT * FROM mv_customer_summary;

Error: materialized view "mv_customer_summary" has not been populated

You must refresh it first:

REFRESH MATERIALIZED VIEW mv_customer_summary;

Now it works:

SELECT * FROM mv_customer_summary;

Output:

Refreshing Materialized Views

Since materialized views store a snapshot, they become stale as the underlying data changes. You must explicitly refresh them to update the stored data.

PostgreSQL: REFRESH MATERIALIZED VIEW

REFRESH MATERIALIZED VIEW mv_monthly_revenue;

This re-executes the entire underlying query and replaces the stored data with the fresh results. During a standard refresh, the materialized view is locked and cannot be queried.

CONCURRENTLY Refresh (Non-Blocking)

PostgreSQL offers a CONCURRENTLY option that allows queries to continue reading the old data while the refresh is in progress:

REFRESH MATERIALIZED VIEW CONCURRENTLY mv_monthly_revenue;

Once the refresh completes, queries seamlessly switch to the new data with zero downtime.

Oracle: DBMS_MVIEW.REFRESH

In Oracle, you refresh materialized views using the DBMS_MVIEW package:

-- Complete refresh (re-runs the entire query)
BEGIN
    DBMS_MVIEW.REFRESH('mv_monthly_revenue', 'C');
END;
/

The second parameter specifies the refresh method:

Fast Refresh (Oracle)

Fast refresh is Oracle's killer feature for materialized views. Instead of re-running the entire query, it applies only the changes (inserts, updates, deletes) that happened since the last refresh. This is dramatically faster for large datasets with small incremental changes.

To enable fast refresh, you must create materialized view logs on the base tables:

-- Create materialized view logs on the base tables
CREATE MATERIALIZED VIEW LOG ON orders
    WITH ROWID, PRIMARY KEY (order_date, amount, status)
    INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON customers
    WITH ROWID, PRIMARY KEY (name, region)
    INCLUDING NEW VALUES;

-- Create the materialized view with fast refresh
CREATE MATERIALIZED VIEW mv_revenue_by_region
BUILD IMMEDIATE
REFRESH FAST ON DEMAND
AS
SELECT
    c.region,
    COUNT(*) AS order_count,
    SUM(o.amount) AS total_revenue
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.status = 'completed'
GROUP BY c.region;

Now when you refresh, Oracle only processes the rows that changed since the last refresh, not the entire dataset.

ON COMMIT Refresh (Oracle)

Oracle can automatically refresh a materialized view every time a transaction commits on the underlying tables:

CREATE MATERIALIZED VIEW mv_live_totals
BUILD IMMEDIATE
REFRESH FAST ON COMMIT
AS
SELECT
    customer_id,
    COUNT(*) AS order_count,
    SUM(amount) AS total_amount
FROM orders
GROUP BY customer_id;

This keeps the materialized view nearly real-time at the cost of slightly slower commits (since each commit triggers the incremental refresh).

Scheduling Refreshes in PostgreSQL

PostgreSQL does not have built-in scheduling for materialized view refreshes. Common approaches:

Using pg_cron (PostgreSQL Extension)

-- Install pg_cron if not already installed
CREATE EXTENSION pg_cron;

-- Refresh every hour
SELECT cron.schedule('refresh_monthly_revenue', '0 * * * *',
    'REFRESH MATERIALIZED VIEW CONCURRENTLY mv_monthly_revenue');

-- Refresh every night at 2 AM
SELECT cron.schedule('refresh_customer_summary', '0 2 * * *',
    'REFRESH MATERIALIZED VIEW CONCURRENTLY mv_customer_summary');

Using OS-Level Cron (Linux)

0 * * * * psql -d mydb -c "REFRESH MATERIALIZED VIEW CONCURRENTLY mv_monthly_revenue;"

Using Application-Level Triggers

Some applications refresh materialized views after specific events (e.g., after a batch import completes or after a nightly ETL job).

Indexing Materialized Views

One of the most powerful advantages of materialized views over regular views is that you can create indexes on them. Since the data is physically stored, indexes work exactly like they do on regular tables.

PostgreSQL

-- Index for fast lookups by month
CREATE INDEX idx_mv_revenue_month
ON mv_monthly_revenue (revenue_month);

-- Index for fast lookups by customer region
CREATE INDEX idx_mv_customer_region
ON mv_customer_summary (region);

-- Composite index
CREATE INDEX idx_mv_customer_orders
ON mv_customer_summary (lifetime_orders DESC, lifetime_spent DESC);

Oracle

-- Indexes on Oracle materialized views
CREATE INDEX idx_mv_revenue_month
ON mv_monthly_revenue (revenue_month);

Why This Matters

Without an index, even though the materialized view data is precomputed, a query like WHERE revenue_month = '2024-03-01' still requires a full scan of the materialized view. With an index, the lookup is instantaneous.

-- Fast: uses the index
SELECT * FROM mv_monthly_revenue
WHERE revenue_month = '2024-03-01';

Output:

Use Case: Performance Optimization

Let's walk through a realistic performance optimization scenario to see the full impact of materialized views.

The Problem: Slow Dashboard Query

Imagine your e-commerce dashboard needs to show a regional performance summary joining orders, customers, and products. The underlying tables have millions of rows:

-- This query takes 12 seconds on production data
SELECT
    c.region,
    DATE_TRUNC('month', o.order_date) AS month,
    COUNT(DISTINCT o.customer_id) AS unique_customers,
    COUNT(o.id) AS total_orders,
    SUM(o.amount) AS revenue,
    ROUND(AVG(o.amount), 2) AS avg_order_value,
    SUM(o.amount) / COUNT(DISTINCT o.customer_id) AS revenue_per_customer
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.status = 'completed'
GROUP BY c.region, DATE_TRUNC('month', o.order_date)
ORDER BY month, region;

This query runs every time anyone opens the dashboard. With 50 dashboard users refreshing throughout the day, that is potentially hundreds of expensive executions.

The Solution: Materialized View

-- Step 1: Create the materialized view
CREATE MATERIALIZED VIEW mv_regional_performance AS
SELECT
    c.region,
    DATE_TRUNC('month', o.order_date) AS month,
    COUNT(DISTINCT o.customer_id) AS unique_customers,
    COUNT(o.id) AS total_orders,
    SUM(o.amount) AS revenue,
    ROUND(AVG(o.amount), 2) AS avg_order_value,
    SUM(o.amount) / COUNT(DISTINCT o.customer_id) AS revenue_per_customer
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.status = 'completed'
GROUP BY c.region, DATE_TRUNC('month', o.order_date);

-- Step 2: Add indexes for common query patterns
CREATE UNIQUE INDEX idx_mv_regional_perf_pk
ON mv_regional_performance (region, month);

CREATE INDEX idx_mv_regional_perf_month
ON mv_regional_performance (month);

-- Step 3: Schedule hourly refresh
SELECT cron.schedule('refresh_regional_perf', '0 * * * *',
    'REFRESH MATERIALIZED VIEW CONCURRENTLY mv_regional_performance');

Now the dashboard query becomes:

-- This query returns in under 5ms
SELECT * FROM mv_regional_performance
ORDER BY month, region;

Output:

Performance Comparison

The materialized view is approximately 2400x faster. For a dashboard that is accessed frequently and does not need sub-second freshness, this is an enormous win.

Adding Dashboard-Specific Queries

Once the materialized view exists, you can run various dashboard queries against it:

-- Top region by revenue this quarter
SELECT region, SUM(revenue) AS quarterly_revenue
FROM mv_regional_performance
WHERE month >= '2024-01-01' AND month < '2024-04-01'
GROUP BY region
ORDER BY quarterly_revenue DESC
LIMIT 1;

-- Month-over-month growth by region
SELECT
    region,
    month,
    revenue,
    LAG(revenue) OVER (PARTITION BY region ORDER BY month) AS prev_revenue,
    ROUND(
        100.0 * (revenue - LAG(revenue) OVER (PARTITION BY region ORDER BY month))
              / NULLIF(LAG(revenue) OVER (PARTITION BY region ORDER BY month), 0),
        2
    ) AS growth_pct
FROM mv_regional_performance;

All of these execute in milliseconds because they query precomputed, indexed data.

Checking Materialized View Freshness

PostgreSQL

PostgreSQL does not track the last refresh time by default. You can check it using system catalog functions or by adding a refresh timestamp manually:

-- Check when a materialized view was last refreshed (PostgreSQL 10+)
SELECT
    relname AS view_name,
    last_refresh
FROM pg_stat_user_tables
WHERE relname = 'mv_monthly_revenue';

A more reliable approach is to include a refresh timestamp column:

CREATE MATERIALIZED VIEW mv_monthly_revenue AS
SELECT
    DATE_TRUNC('month', order_date) AS revenue_month,
    COUNT(*) AS total_orders,
    SUM(amount) AS revenue,
    NOW() AS refreshed_at            -- Timestamp of last refresh
FROM orders
WHERE status = 'completed'
GROUP BY DATE_TRUNC('month', order_date);

-- Check freshness
SELECT DISTINCT refreshed_at FROM mv_monthly_revenue;

Oracle

Oracle tracks refresh metadata automatically:

SELECT
    mview_name,
    last_refresh_date,
    last_refresh_type,
    staleness
FROM user_mviews
WHERE mview_name = 'MV_MONTHLY_REVENUE';

Output:

The staleness column tells you whether the materialized view is FRESH, STALE, or NEEDS_COMPILE.

Dropping Materialized Views

PostgreSQL

DROP MATERIALIZED VIEW mv_monthly_revenue;

-- Safe version
DROP MATERIALIZED VIEW IF EXISTS mv_monthly_revenue;

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

Oracle

DROP MATERIALIZED VIEW mv_monthly_revenue;

Dropping a materialized view removes both the view definition and the stored data. The underlying base tables are not affected.

Common Mistakes to Avoid

Mistake 1: Using CREATE VIEW Instead of CREATE MATERIALIZED VIEW

-- WRONG: This is a regular view, not materialized
CREATE VIEW mv_revenue AS
SELECT DATE_TRUNC('month', order_date) AS month, SUM(amount) AS revenue
FROM orders GROUP BY 1;

This creates a regular view that re-executes on every query. There is no caching.

Fix: Use CREATE MATERIALIZED VIEW:

CREATE MATERIALIZED VIEW mv_revenue AS
SELECT DATE_TRUNC('month', order_date) AS month, SUM(amount) AS revenue
FROM orders GROUP BY 1;

Mistake 2: Forgetting to Refresh

-- Data was inserted into orders yesterday
INSERT INTO orders VALUES (11, 105, '2024-04-15', 800, 'completed');

-- Materialized view still shows old data!
SELECT * FROM mv_monthly_revenue WHERE revenue_month = '2024-04-01';

The new order will not appear until you refresh:

REFRESH MATERIALIZED VIEW mv_monthly_revenue;

Mistake 3: Running CONCURRENTLY Without a Unique Index

-- WRONG: No unique index exists
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_monthly_revenue;

Error: cannot refresh materialized view concurrently without a unique index

Fix: Create a unique index first:

CREATE UNIQUE INDEX idx_mv_revenue_pk
ON mv_monthly_revenue (revenue_month);

-- Now this works
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_monthly_revenue;

Mistake 4: Materializing Queries That Do Not Need It

Not every slow query needs a materialized view. Consider these alternatives first:

A materialized view adds operational complexity (refresh scheduling, staleness monitoring, index maintenance). Only use it when simpler solutions are insufficient.

Mistake 5: Not Indexing the Materialized View

Creating a materialized view without indexes is like copying data into a new table and running full scans on it:

-- Materialized view exists but has no indexes
SELECT * FROM mv_customer_summary WHERE region = 'North';
-- Full scan of materialized view: still slow for large datasets

Fix: Always add indexes that match your common query patterns:

CREATE INDEX idx_mv_cust_region ON mv_customer_summary (region);

Materialized Views vs Alternatives

PostgreSQL vs Oracle Feature Comparison

Summary

SQL materialized views bridge the gap between real-time accuracy and query performance by physically storing precomputed results:

• A materialized view stores both the query definition and its result data on disk, unlike a regular view which stores only the definition.
• CREATE MATERIALIZED VIEW defines and (optionally) populates the view. Use WITH NO DATA in PostgreSQL or BUILD DEFERRED in Oracle to defer population.
• Refreshing is required to update stored data. PostgreSQL uses REFRESH MATERIALIZED VIEW (with optional CONCURRENTLY for zero-downtime refreshes). Oracle offers COMPLETE, FAST (incremental), and ON COMMIT (automatic) refresh strategies.
• Indexes can and should be created on materialized views to maximize query performance. This is one of their biggest advantages over regular views.
• The primary trade-off is data freshness. Materialized views show a snapshot that may be minutes or hours old, depending on your refresh schedule.
• Use materialized views for dashboards, reports, analytics, and any read-heavy workload where the underlying computation is expensive and slight staleness is acceptable.