What is view and materialized view ? What is the difference?
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:
To retrieve the data:
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:
To retrieve the results:
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:
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.What does a regular (non-materialized) SQL view store?
- 2.You create the view active_employees
(SELECT emp_id, first_name, salary FROM employees WHERE active = 1).A new active employee is then inserted into employees. What does the next SELECT * FROM active_employees return? - 3.Which requirement is the best fit for a materialized view rather than a regular view?
- 4.Which statement is accurate about materialized views and Appian?