Skip to main content
Question

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

A
Anonymous
January 6, 2026

Answer

Views and materialized views are both database objects built on a SQL query. They differ in whether the results are stored.


Views

A view is a virtual table based on a SQL query. It doesn’t store results. Each time you query it, the database reads the underlying tables, so it always returns current data. Views simplify complex queries and joins, and their performance depends on the underlying SQL.

Suppose you have an employees table and want only the active employees:

CREATE VIEW active_employees AS
SELECT emp_id, first_name, salary
FROM employees
WHERE active = 1;


To retrieve the data:

SELECT * FROM active_employees;

If a new active employee is added to employees, the view reflects it the next time you query.


Materialized Views

A materialized view stores the query results physically. Instead of running an expensive query every time, the database returns the results it calculated earlier. That speeds up complex queries and aggregations, at the cost of extra storage and data that can be outdated until the view is refreshed.

Suppose a sales table holds millions of transactions and you often need total sales by product:


CREATE MATERIALIZED VIEW sales_summary AS
SELECT product_id, SUM(amount) AS total_sales
FROM sales
GROUP BY product_id;


To retrieve the results:

SELECT * FROM sales_summary;

If Product A has $1,000 in sales and a new $200 transaction is added, the materialized view may still show $1,000 until it is refreshed. In PostgreSQL, you refresh it with:

REFRESH MATERIALIZED VIEW sales_summary;

After the refresh, the result is $1,200.


View vs. Materialized View

Feature

View

Materialized view

Storage

Does not store results

Stores results physically

Data freshness

Current data

May contain outdated data

Performance

Depends on query complexity

Often faster for complex queries

Refresh

Not required

Required to update results

Storage space

Minimal

Additional storage required

Best for

Current operational data

Reporting and aggregations


Practical example in Appian

Take a financial transaction approval application that handles thousands of transactions. Executives need the latest pending transactions, approval statuses, and assigned reviewers, so a regular database view fits: the application needs current data.


Management also wants a reporting dashboard with monthly approved transactions, total transaction amounts, and average approval times. Rather than recalculating those from millions of records each time, a materialized view can store the aggregated results and speed up reporting.


Note: Materialized Views are not supported in exactly the same way by every database. SQL syntax and refresh mechanisms vary. In Appian, synchronized Record Types also have their own data refresh behavior and should not be confused with database materialized views.


Most of modern projects are using the Pppian Record and Record relationships instead of views . View are used in modern projects when complex table joins are involved

Check yourself

Question 1 of 4, on what the answer above covers.

  1. 1.
    What does a regular (non-materialized) SQL view store?
SeniorJuniorDatabase