What Is a Materialized View? A Complete Guide for Data Engineers

You have a dashboard query that joins three tables, aggregates millions of rows, and takes 12 seconds to run. Every time a user refreshes the page, the database re-executes the entire query from scratch. Your CPU spikes, your users wait, and your infrastructure bill climbs.

This is the exact problem materialized views solve. Instead of re-running expensive queries on every read, a materialized view computes the result once and stores it. Reads become near-instant lookups against precomputed data.

In this guide, you will learn what a materialized view is, how it compares to a regular view, which refresh strategies exist, and how to create materialized views in practice using PostgreSQL and RisingWave. Whether you are building dashboards, caching aggregations, or accelerating analytical workloads, this guide covers the fundamentals you need.

What is a materialized view?

A materialized view is a database object that stores the precomputed result of a query, persisting it to disk so that subsequent reads return the cached result instead of re-executing the query.

Think of it as a snapshot of a query's output. When you create a materialized view, the database runs the query, takes the result set, and saves it as a physical table. Future SELECT statements against the materialized view read directly from that stored result, skipping the original computation entirely.

This contrasts with a regular (or "virtual") view, which stores only the query definition. Every time you query a regular view, the database substitutes the view's SQL into your query and executes it from scratch. A materialized view trades storage space for read performance: it occupies disk space, but it returns results orders of magnitude faster for complex queries.

Where materialized views fit in a data architecture

Materialized views sit between raw tables and application queries. They act as a precomputation layer: the database does the heavy lifting (joins, aggregations, filters) once, and your application reads the lightweight result.

Common use cases include:

• Dashboard acceleration: Precompute metrics like daily revenue, active users, or order counts so dashboards load instantly.
• Join precomputation: Flatten normalized schemas into denormalized result sets that are fast to query.
• Aggregation caching: Store GROUP BY results (sums, averages, counts) to avoid scanning large fact tables on every request.
• Search index preparation: Combine and transform data from multiple tables into a format optimized for downstream search or analytics systems.

Materialized views are supported by most major databases, including PostgreSQL, Oracle, SQL Server, Snowflake, Redshift, and streaming databases like RisingWave.

How does a materialized view differ from a regular view?

A regular view is a saved query definition. It stores no data. Every time you read from a view, the database executes the underlying SQL and returns fresh results. A materialized view stores the actual query results as physical data. Reads are fast because they hit stored rows, not live computation.

Here is a side-by-side comparison:

When to use a regular view

Use a regular view when:

• The underlying query is inexpensive (simple filters, small tables).
• You need results that always reflect the latest data with zero lag.
• Storage is constrained and you cannot afford to duplicate data.
• You want to simplify SQL by aliasing a complex query without any performance overhead.

When to use a materialized view

Use a materialized view when:

• The underlying query is expensive (multi-table joins, large aggregations).
• Your workload is read-heavy and the same query runs repeatedly.
• You can tolerate some staleness between refreshes, or you use a streaming database that refreshes automatically.
• You need to add indexes to speed up lookups against the precomputed result.

Code comparison: PostgreSQL

Here is how you create each type in PostgreSQL:

Regular view:

CREATE VIEW daily_revenue AS
SELECT
    DATE(order_time) AS order_date,
    SUM(amount) AS total_revenue,
    COUNT(*) AS order_count
FROM orders
GROUP BY DATE(order_time);

Every time you run SELECT * FROM daily_revenue, PostgreSQL re-executes the GROUP BY query against the orders table.

Materialized view:

CREATE MATERIALIZED VIEW daily_revenue_mv AS
SELECT
    DATE(order_time) AS order_date,
    SUM(amount) AS total_revenue,
    COUNT(*) AS order_count
FROM orders
GROUP BY DATE(order_time);

PostgreSQL runs the query once at creation time and stores the result. Subsequent reads hit the stored data. To get updated results, you must explicitly refresh it:

REFRESH MATERIALIZED VIEW daily_revenue_mv;

The syntax difference is small. The behavioral difference is significant.

What are the different refresh strategies for materialized views?

The core trade-off of a materialized view is freshness versus performance. The refresh strategy determines how and when the stored results get updated. There are three main approaches.

Full refresh

Full refresh recomputes the entire materialized view from scratch. The database re-runs the original query, discards the old result, and replaces it with the new output.

In PostgreSQL, this is the only built-in option:

REFRESH MATERIALIZED VIEW daily_revenue_mv;

You can also refresh without locking reads by using the CONCURRENTLY option (requires a unique index):

CREATE UNIQUE INDEX ON daily_revenue_mv (order_date);
REFRESH MATERIALIZED VIEW CONCURRENTLY daily_revenue_mv;

Pros:

• Simple to understand and implement.
• Guaranteed correctness: the result always matches the current state of the source tables.

Cons:

• Expensive for large datasets. If the source table has 100 million rows, the full query runs against all 100 million rows, even if only 10 rows changed.
• Results are stale between refreshes. If you refresh hourly, your data can be up to an hour old.
• Refresh can lock the view or consume significant resources during execution.

Incremental refresh

Incremental refresh (sometimes called "fast refresh") updates only the rows that changed since the last refresh. Instead of recomputing everything, it identifies the delta (inserts, updates, deletes) and applies those changes to the stored result.

Oracle Database supports this natively through materialized view logs. Some PostgreSQL extensions like pg_ivm add incremental refresh capabilities.

Pros:

• Much faster than full refresh for large datasets with small change volumes.
• Lower CPU and I/O cost per refresh cycle.

Cons:

• Not all query patterns support incremental refresh (complex joins, certain aggregations).
• Requires change tracking infrastructure (materialized view logs, change data capture).
• Still requires scheduled triggers: the view is stale between refresh cycles.

Continuous (streaming) refresh

Continuous refresh eliminates refresh cycles entirely. The materialized view updates automatically and incrementally as each row arrives. There is no scheduled job, no manual REFRESH command, and no staleness gap.

This is the approach used by streaming databases like RisingWave. When you create a materialized view in RisingWave, the system sets up a persistent dataflow that processes every change to the source data and propagates updates through the view in real time.

-- In RisingWave, this view updates automatically
CREATE MATERIALIZED VIEW daily_revenue_mv AS
SELECT
    DATE(order_time) AS order_date,
    SUM(amount) AS total_revenue,
    COUNT(*) AS order_count
FROM orders
GROUP BY DATE(order_time);

There is no REFRESH command because none is needed. The view is always current.

Pros:

• Results are always fresh, with sub-second latency from source change to view update.
• No operational burden of scheduling and monitoring refresh jobs.
• Efficient: processes only the changed rows, continuously.

Cons:

• Requires a streaming database or streaming infrastructure.
• Consumes ongoing compute resources to maintain the dataflow.

Comparison table: refresh strategies

For a deeper comparison of these approaches, see our guide on incremental materialized views.

How do you create and use materialized views in practice?

Let us walk through concrete examples in two systems: PostgreSQL (the most common traditional approach) and RisingWave (the streaming approach).

Example scenario

You run an e-commerce platform. You need a real-time summary of order activity by product category: total revenue, order count, and average order value. The source data comes from two tables: orders and products.

PostgreSQL: manual refresh

First, create the source tables and insert sample data:

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100),
    category VARCHAR(50),
    price NUMERIC(10,2)
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    product_id INT REFERENCES products(product_id),
    quantity INT,
    amount NUMERIC(10,2),
    order_time TIMESTAMP
);

INSERT INTO products VALUES
    (1, 'Wireless Mouse', 'Electronics', 29.99),
    (2, 'USB-C Hub', 'Electronics', 49.99),
    (3, 'Notebook', 'Office Supplies', 12.99),
    (4, 'Standing Desk', 'Furniture', 399.99);

INSERT INTO orders VALUES
    (101, 1, 2, 59.98, '2026-03-28 10:15:00'),
    (102, 2, 1, 49.99, '2026-03-28 11:30:00'),
    (103, 3, 5, 64.95, '2026-03-28 12:00:00'),
    (104, 4, 1, 399.99, '2026-03-28 14:20:00'),
    (105, 1, 3, 89.97, '2026-03-29 09:00:00');

Now create the materialized view:

CREATE MATERIALIZED VIEW category_summary_mv AS
SELECT
    p.category,
    COUNT(o.order_id) AS total_orders,
    SUM(o.amount) AS total_revenue,
    ROUND(AVG(o.amount), 2) AS avg_order_value
FROM orders o
JOIN products p ON o.product_id = p.product_id
GROUP BY p.category;

Query the materialized view:

SELECT * FROM category_summary_mv ORDER BY total_revenue DESC;

Expected output:

    category     | total_orders | total_revenue | avg_order_value
-----------------+--------------+---------------+-----------------
 Furniture       |            1 |        399.99 |          399.99
 Electronics     |            3 |        199.94 |           66.65
 Office Supplies |            1 |         64.95 |           64.95

When new orders arrive, the materialized view does not update automatically. You must refresh it:

-- Insert a new order
INSERT INTO orders VALUES (106, 2, 2, 99.98, '2026-03-29 10:30:00');

-- The materialized view still shows stale data
SELECT * FROM category_summary_mv WHERE category = 'Electronics';
-- Still shows: total_orders = 3, total_revenue = 199.94

-- Refresh to pick up the new order
REFRESH MATERIALIZED VIEW category_summary_mv;

-- Now the view reflects the new data
SELECT * FROM category_summary_mv WHERE category = 'Electronics';
-- Now shows: total_orders = 4, total_revenue = 299.92

You can add an index to speed up filtered queries:

CREATE INDEX idx_category_summary ON category_summary_mv (category);

In production, you would typically schedule the REFRESH command using pg_cron or an external scheduler (Airflow, cron job). The refresh interval becomes a business decision: refresh every minute, every hour, or every night, depending on how much staleness your users can tolerate.

RisingWave: automatic, continuous refresh

In RisingWave, the same materialized view updates automatically. There are no refresh jobs to schedule or monitor.

First, create a source. In a real deployment, this would typically be a Kafka topic or a CDC stream from your transactional database. For this example, we create tables directly:

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR,
    category VARCHAR,
    price NUMERIC
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    product_id INT,
    quantity INT,
    amount NUMERIC,
    order_time TIMESTAMP
);

Create the materialized view with the same SQL:

CREATE MATERIALIZED VIEW category_summary_mv AS
SELECT
    p.category,
    COUNT(o.order_id) AS total_orders,
    SUM(o.amount) AS total_revenue,
    ROUND(AVG(o.amount), 2) AS avg_order_value
FROM orders o
JOIN products p ON o.product_id = p.product_id
GROUP BY p.category;

The syntax is identical to PostgreSQL. The difference is what happens next. When you insert a new order, the materialized view updates within milliseconds, with no manual intervention:

INSERT INTO orders VALUES (106, 2, 2, 99.98, '2026-03-29 10:30:00');

-- The view is already updated, no REFRESH needed
SELECT * FROM category_summary_mv WHERE category = 'Electronics';
-- Shows: total_orders = 4, total_revenue = 299.92

RisingWave processes the insert, identifies which materialized views are affected, computes the incremental change, and updates the stored result. This happens automatically for every insert, update, and delete.

You can also build cascading materialized views, where one view depends on another:

-- Second-level view: top categories by revenue
CREATE MATERIALIZED VIEW top_categories AS
SELECT
    category,
    total_revenue
FROM category_summary_mv
WHERE total_revenue > 100
ORDER BY total_revenue DESC;

When the base category_summary_mv updates, top_categories updates automatically as well. Changes propagate through the entire chain. For full syntax details, see the CREATE MATERIALIZED VIEW reference in the RisingWave documentation.

Common patterns

Regardless of which database you use, these are the most common materialized view patterns:

Aggregation caching: Precompute SUM, COUNT, AVG, and other aggregate functions over large tables. This is the most straightforward and highest-impact use case.

Join precomputation: Flatten multi-table joins into a single denormalized result. Particularly valuable when your application repeatedly queries the same join across normalized tables.

Dashboard acceleration: Create one materialized view per dashboard widget. Each view computes a specific metric, and the dashboard reads all widgets in parallel from precomputed storage.

Filtering and sorting preparation: Materialize filtered subsets or pre-sorted results to avoid expensive WHERE and ORDER BY operations at query time.

What are the benefits and trade-offs of materialized views?

Benefits

Faster reads: The primary benefit. Complex queries that take seconds or minutes against raw tables return in milliseconds from a materialized view. For read-heavy workloads, this can reduce query latency by 100x or more.

Reduced compute load: Instead of running an expensive aggregation query every time a user opens a dashboard, the database runs it once (or incrementally). This frees up CPU and I/O for other operations, especially during peak traffic.

Consistent snapshots: A materialized view provides a point-in-time consistent result. All rows in the view reflect the same query execution, which avoids inconsistencies that can arise when an application issues multiple queries against rapidly changing tables.

Query simplification: Materialized views encapsulate complex SQL logic. Application code queries a simple SELECT * FROM summary_mv instead of embedding multi-join, multi-aggregate SQL. This reduces application complexity and the chance of query bugs.

Trade-offs

Storage cost: Materialized views duplicate data. The same information exists in the source tables and in the view. For large result sets or many views, storage costs grow.

Staleness (traditional databases): In PostgreSQL, Redshift, and similar systems, materialized views are stale between refreshes. A view refreshed every hour can be up to 59 minutes out of date. This is the most significant trade-off in traditional deployments.

Refresh overhead: Full refresh operations can be expensive, locking the view or consuming significant CPU. Even with CONCURRENTLY in PostgreSQL, the database must still recompute the entire query.

Maintenance complexity: In production, you need to schedule refreshes, monitor for failures, handle dependencies between cascading views, and manage the operational overhead of refresh jobs.

How streaming databases eliminate the staleness trade-off

The staleness problem is not inherent to materialized views. It is a limitation of the refresh model. Streaming databases like RisingWave solve this by replacing batch refresh with incremental view maintenance. Every change to the source data triggers an incremental update to the view, keeping it fresh without manual intervention.

This means you get the read performance benefits of materialized views without accepting stale data. The trade-off shifts from "fast reads vs. fresh data" to "fast reads vs. continuous compute cost," which is usually a much better trade-off for real-time applications.

FAQ

What is the difference between a view and a materialized view?

A view is a saved query definition that stores no data. Every time you query it, the database re-executes the underlying SQL. A materialized view stores the query result as physical data on disk. Reads are faster because they access precomputed rows, but the data can become stale unless the view is refreshed. The choice depends on whether read performance or data freshness is your priority.

Do materialized views update automatically?

In most traditional databases (PostgreSQL, Redshift, Snowflake), materialized views do not update automatically. You must run a REFRESH command manually or on a schedule. Streaming databases like RisingWave are the exception: they update materialized views continuously and automatically as source data changes, with no manual refresh required.

Can materialized views have indexes?

Yes. Because a materialized view stores data physically, you can create indexes on it just like a regular table. In PostgreSQL, you can add B-tree, GIN, GiST, and other index types to a materialized view. This is one of the key advantages over regular views, which cannot be indexed because they store no data.

When should I use a materialized view instead of a table?

Use a materialized view when the data is derived from other tables through a query (joins, aggregations, filters) and you want to avoid re-running that query on every read. Use a table when you need to insert, update, or delete individual rows directly. A materialized view is a computed cache, not a primary data store. If you find yourself manually populating a table with the output of a SELECT query on a schedule, that is a strong signal to use a materialized view instead.

Conclusion

Here are the key takeaways:

• A materialized view stores the precomputed result of a query on disk, delivering faster reads than re-executing the query each time.
• Regular views re-execute on every read and store no data. Materialized views store physical rows and can be indexed.
• Refresh strategies range from full refresh (simple but expensive) to incremental refresh (efficient but limited) to continuous streaming refresh (always fresh, fully automatic).
• PostgreSQL requires manual REFRESH MATERIALIZED VIEW commands. Streaming databases like RisingWave update materialized views automatically as data changes.
• The staleness trade-off in traditional databases is not a fundamental limitation of materialized views. Streaming refresh eliminates it.

Ready to try this yourself? Try RisingWave Cloud free, with no credit card required. Sign up here.

Join our Slack community to ask questions and connect with other stream processing developers.