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.

Aspect	Regular View	Materialized View
Stores query definition?	Yes	Yes
Stores result data?	No	Yes
Always up to date?	Yes (live execution)	No (snapshot)
Query speed	Same as running the underlying query	Fast (reads from stored data)
Uses disk space?	No	Yes
Needs refreshing?	No	Yes

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:

id	name	region	signup_date
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

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):

id	customer_id	order_date	amount	status
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

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):

id	name	category	price
1	Widget Pro	Electronics	79.99
2	Gadget Max	Electronics	49.99
3	Office Chair	Furniture	299
4	Desk Lamp	Furniture	45

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:

revenue_month	total_orders	completed_orders	revenue	avg_order_value
2024-01-01 00:00:00+00	3	3	860.00	286.67
2024-02-01 00:00:00+00	2	1	520.00	520.00
2024-03-01 00:00:00+00	3	3	1105.00	368.33
2024-04-01 00:00:00+00	2	1	450.00	450.00

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:

customer_id	customer_name	region	lifetime_orders	lifetime_spent	last_order_date
101	Acme Corp	North	3	705.00	2024-03-01
103	Initech	North	2	710.00	2024-03-10
104	Umbrella	West	2	1090.00	2024-04-10
102	Globex Inc	South	1	430.00	2024-01-12

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.

caution

CONCURRENTLY requires the materialized view to have a unique index. Without one, PostgreSQL cannot determine which rows changed and will throw an error:

-- Required for CONCURRENTLY
CREATE UNIQUE INDEX idx_mv_monthly_revenue
ON mv_monthly_revenue (revenue_month);

-- Now this works
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_monthly_revenue;

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:

Parameter	Method	Description
'C'	Complete	Truncates and re-populates entirely
'F'	Fast	Applies only incremental changes (requires materialized view logs)
'?'	Force	Tries fast first; falls back to complete if fast is not possible

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).

info

ON COMMIT refresh is an Oracle-specific feature. PostgreSQL does not support automatic refresh on commit. In PostgreSQL, you must schedule refreshes manually or use triggers/pg_cron for automation.

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)

# Crontab entry: refresh every hour
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).

How Often Should You Refresh?

The answer depends on your tolerance for stale data:

Use Case	Typical Refresh Frequency
Executive dashboards	Daily or hourly
Analytics reports	Hourly or every 15 minutes
Product catalog search	Every few minutes
Near-real-time metrics	Every minute or on commit (Oracle)

Refresh as infrequently as your business requirements allow. Each refresh consumes CPU and I/O, so there is no reason to refresh more often than necessary.

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:

revenue_month	total_orders	completed_orders	revenue	avg_order_value
2024-03-01 00:00:00+00	3	3	1105.00	368.33

info

Indexes on materialized views are automatically maintained during a refresh. After a REFRESH MATERIALIZED VIEW, the indexes are rebuilt to reflect the new data. You do not need to manually reindex.

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:

region	month	unique_customers	total_orders	revenue	avg_order_value	revenue_per_customer
North	2024-01-01 00:00:00+00	1	2	430.00	215.00	430.00
South	2024-01-01 00:00:00+00	1	1	430.00	430.00	430.00
North	2024-02-01 00:00:00+00	1	1	520.00	520.00	520.00
North	2024-03-01 00:00:00+00	2	2	465.00	232.50	232.50
West	2024-03-01 00:00:00+00	1	1	640.00	640.00	640.00
West	2024-04-01 00:00:00+00	1	1	450.00	450.00	450.00

Performance Comparison

Metric	Regular View / Direct Query	Materialized View
Query time	~12 seconds	~5 milliseconds
CPU per request	High (full aggregation)	Minimal (simple read)
Scales with users	Poorly (each user triggers full computation)	Excellently (all users read the same cached data)
Data freshness	Real-time	Up to 1 hour old (depends on refresh schedule)

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:

mview_name	last_refresh_date	last_refresh_type	staleness
MV_MONTHLY_REVENUE	2024-04-10 14:30:00	COMPLETE	NEEDS_COMPILE

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;

tip

Set up automated refreshes immediately after creating a materialized view. It is easy to forget manual refreshes, leading to dashboards showing stale data without anyone realizing it.

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:

Alternative	When to Use
Add an index to the base table	Query is slow due to missing index, not query complexity
Rewrite the query	Query is inefficient and can be optimized
Regular view	Query is fast enough already
Application-level caching	You need per-user or per-session caching
Materialized view	Query is genuinely expensive and read-heavy

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

Solution	Data Freshness	Query Speed	Persistence	Complexity
Direct query	Real-time	Depends on query	N/A	Low
Regular view	Real-time	Same as direct query	Permanent definition	Low
CTE	Real-time	Same as direct query	Single query only	Low
Temporary table	Session-level snapshot	Fast (physical data)	Single session	Medium
Materialized view	Stale (until refreshed)	Very fast (physical + indexed)	Permanent	Medium-High
Application cache	Configurable TTL	Fastest (in-memory)	Application-level	High

PostgreSQL vs Oracle Feature Comparison

Feature	PostgreSQL	Oracle
CREATE MATERIALIZED VIEW	Yes	Yes
BUILD IMMEDIATE / BUILD DEFERRED	WITH DATA / WITH NO DATA	BUILD IMMEDIATE / BUILD DEFERRED
Complete refresh	REFRESH MATERIALIZED VIEW	DBMS_MVIEW.REFRESH('name', 'C')
Non-blocking refresh	REFRESH ... CONCURRENTLY (requires unique index)	Automatic with ON DEMAND
Fast (incremental) refresh	Not natively supported	Yes (requires materialized view logs)
Automatic refresh on commit	Not supported	ON COMMIT option
Refresh scheduling	External (pg_cron, OS cron)	DBMS_SCHEDULER or DBMS_JOB
Staleness tracking	Manual	Automatic (user_mviews.staleness)
Indexes on materialized views	Yes	Yes
Query rewrite (automatic use)	Not supported	Yes (optimizer can transparently substitute)

Oracle Query Rewrite

Oracle has a unique feature called query rewrite: the optimizer can automatically detect when a user's query against base tables could be answered by an existing materialized view and transparently route the query to the materialized view instead. This means users do not even need to know the materialized view exists. PostgreSQL does not support this; users must explicitly query the materialized view by name.

-- Oracle: Enable query rewrite
CREATE MATERIALIZED VIEW mv_revenue
BUILD IMMEDIATE
REFRESH FAST ON DEMAND
ENABLE QUERY REWRITE
AS
SELECT ...;

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.

Decision Framework

Ask yourself three questions:

1. Is the query slow? If no, use a regular view.
2. Is real-time accuracy required? If yes, optimize the query or add indexes instead.
3. Is the data read far more often than it changes? If yes, a materialized view is likely the right choice.