This article is half-done without your Comment! *** Please share your thoughts via Comment ***
What is Materialized View?
It is a one type of view which contains the result of a query.
It caches the result of complex query and you can access the result data like a normal table.
It requires physical space to store generated data. Once we put any complex query in Materialized View, we can access that query and data without disturbing a physical base table.
Only one thing you should do is: Periodically refresh your Materialized View to get newly inserted data from the base table.
In PostgreSQL, You can create a Materialized View and can refresh it.
Let me show you, full practical on this.
Create a table with sample data:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
CREATE TABLE tbl_EmployeeDetails ( EmpID INTEGER PRIMARY KEY ,EmpName VARCHAR(50) ,Gender CHAR(1) ,EmpSalary BIGINT ,DepartmentName VARCHAR(50) ); INSERT INTO tbl_EmployeeDetails VALUES (1,'Anvesh','M',80000,'Sales') ,(2,'Neevan','M',90000,'Sales') ,(3,'Jenny','F',50000,'Production') ,(4,'Roy','M',60000,'Production') ,(5,'Martin','M',30000,'Research') ,(6,'Mahi','F',85000,'Research') ,(7,'Kruti','F',45000,'Research') ,(8,'Manish','M',75000,'Research'); |
Create a materialized view to select only Male – Employee data:
|
1 2 3 4 5 6 7 8 9 |
CREATE MATERIALIZED VIEW vw_EmployeeMaleData_Materialized AS SELECT EmpID ,EmpName ,Gender ,EmpSalary ,DepartmentName FROM tbl_EmployeeDetails WHERE Gender = 'M'; |
Select a data of this materialized view:
|
1 |
SELECT *FROM vw_EmployeeMaleData_Materialized; |
Refresh a materialized view:
Once you create a materialized view, you should also refresh it otherwise newly inserted values of the table will not update in this view.
Because It acts like a physical table and once your base table update, you should refresh the data of the materialized view.
Create another non-materialized view to select only Male – Employee data:
|
1 2 3 4 5 6 7 8 9 |
CREATE VIEW vw_EmployeeMaleData_Non_Materialized AS SELECT EmpID ,EmpName ,Gender ,EmpSalary ,DepartmentName FROM tbl_EmployeeDetails WHERE Gender = 'M'; |
Check the size of both views and find size difference between both views:
Non-Materialized view size is 0 byte and Materialized view size is around 8000 bytes because It stores the generated data.
|
1 2 |
SELECT pg_size_pretty(pg_total_relation_size('vw_EmployeeMaleData_Materialized')); SELECT pg_size_pretty(pg_total_relation_size('vw_EmployeeMaleData_Non_Materialized')); |
