PostgreSQL Standard View vs Materialized View (with Syntax and Example)

PostgreSQL plays a very important role if you want to manage data and majorly when you want to manipulate the data according to your requirements. It provides a large variety of statements, functions and JOINs to reduce the complicated structure of the different models.

Have you ever heard of VIEW in PostgreSQL? VIEW allows users to encapsulate commonly used query logic into reusable virtual tables. In this tutorial, we will learn two prominent categories of Views, which are standard views and materialized views.

PostgreSQL Standard View

Standard view, which is also known as regular view, in PostgreSQL is a virtual representation of data that is retrieved from one or more underlying tables using a defined SQL query. Unlike physical tables, these views don’t store data on their own. It is dynamically retrieved on request.

To understand this better, we can say that VIEW is like having a customized report or dashboard that takes you through the exact data you are interested in, and it eliminates all the extra noise.

Syntax

CREATE [OR REPLACE] VIEW view_name AS
SELECT column1, column2
FROM table
[WHERE condition];
Code language: SQL (Structured Query Language) (sql)

In the above syntax,

• OR REPLACE: If a view already exists, this parameter will replace it.
• view_name: Name of view which is to be created.

Creating a Standard View

Let’s assume, you are the owner of a bookstore, and you have a record of the book sold with the table name sales_record.

Now, let’s say you want to create a view where the total_sales value is greater than 1000.

CREATE VIEW sales_over_1000 AS
SELECT customer_id, total_sales
FROM sales_record
WHERE total_sales > 1000;Code language: SQL (Structured Query Language) (sql)

You can then query the Standard view just like you would a regular table:

SELECT * FROM sales_over_1000;Code language: SQL (Structured Query Language) (sql)

As you can see, a view has been created where the total_sales is more than 1000.

Also Read: PostgreSQL Conditional Select With CASE Statements

PostgreSQL Materialized Views

Materialized views store the result set of the query physically. This pre-calculated data allows for faster access and improved query performance, especially for complex queries that involve aggregates or joins. However, this view requires periodic refreshing to match the underlying data, which makes it suitable for situations where data revision is not necessary.

Syntax

CREATE MATERIALIZED VIEW view_name AS
SELECT column1, column2
FROM table
[WHERE condition]
WITH [NO] DATA;Code language: SQL (Structured Query Language) (sql)

In the above syntax,

• view_name: Name of view which is to be created.
• WITH [NO] DATA: Determines whether the materialized view should be populated with data immediately upon creation (WITH DATA), or if it should be created without data (WITH NO DATA).

Refreshing Materialized Views:

If you use WITH NO DATA, the materialized view will be created, but it will initially be empty. So it must be refreshed to reflect changes in the underlying data. This can be done with the REFRESH command:

REFRESH MATERIALIZED VIEW  view_name;Code language: SQL (Structured Query Language) (sql)

Creating a Materialized View

Now let’s go back to the last example, yes, the one where you were the owner of the bookstore, and you need to calculate almost all the money spent by each customer.

You can create a materialized view that calculates this total for you, so you don’t have to do that every time you run a query.

CREATE MATERIALIZED VIEW order_summary_data AS
SELECT customer_id, SUM(total_sales) AS total_amount
FROM sales_record
GROUP BY customer_id
WITH DATA;Code language: SQL (Structured Query Language) (sql)

Now, let’s write a query to see the table:

SELECT * FROM order_summary_data ORDER BY customer_id;Code language: SQL (Structured Query Language) (sql)

As you can see from the above, you have a materialized view of a table which contains the total amount that each customer spent.

Standard Views vs Materialized Views

Read More: Difference Between Simple and Complex View in SQL

Conclusion

Standard and materialized views have their distinct advantages and use cases in PostgreSQL. As we know, standard views provide dynamic representations of data, on the other hand, materialized views offer improved query performance at the cost of data freshness. Understanding these both can help you during the implementation part of your model and allow you to optimize database performance. We hope you enjoyed it.

Reference

https://stackoverflow.com/questions/56226462/views-vs-materialized-views