PostgreSQL: Materialized View

In this article we will learn about Materialized View, its need, and advantage. Also, we will learn how we can manage it in PostgreSQL? Before start Materialized View, you should take a look at the article VIEW.

Materialized View in PostgreSQL

A traditional view is the group of complex queries; sometimes, it becomes expensive and slow. To resolve this problem, PostgreSQL introduced to Materialized View.

A materialized View is stored on disk that defined by the database query. Like a traditional view, the underlying query is not executed every time when you access the Materialized View. 

Syntax to create the materialized view:

CREATE MATERIALIZED VIEW view_name AS query;

The Materialized View is persisting physically into the database so we can take the advantage of performance factors like Indexing, etc. According to the requirement, we can filter the records from the underlying tables. Now, one thing comes in our mind if it looks like a table then how both different are.

You cannot insert, update or delete the records into the materialized view directly through PostgreSQL SQL statement; you have to refresh the view.

Syntax to refresh materialized view:

REFRESH MATERIALIZED VIEW view_name;

Syntax to index materialized view:

CREATE INDEX my_index_1 ON view_name (column_name);

Example of Materialized View in PostgreSQL

Suppose we have tables like below.

Employee table

CREATE TABLE public.employee (     empid serial NOT NULL,-- primary key     fname text,     lname text,     username text,     password text,     contact text,     salary numeric NOT NULL DEFAULT 0.0,     doj date NOT NULL,     managerid bigint,     age integer,     CONSTRAINT employee_primarykey PRIMARY KEY (empid),     CONSTRAINT "uk_UserName" UNIQUE (username),     CONSTRAINT chk_employee_age CHECK (age > 18) );

Employee Address table

CREATE TABLE public.address (     id serial NOT NULL,     empid integer NOT NULL,-- reference of employee     address1 text,     address2 text,     city text,     state text,     country text,     CONSTRAINT pk_address_id PRIMARY KEY (id) );

I am creating customer Materialized View as mvw_customer

CREATE MATERIALIZED VIEW mvw_customer AS SELECT   e.fname || ' ' || e.lname AS name,   e.contact,   a.city,   a.state,   a.country FROM employee e   INNER JOIN address a   ON e.empid=a.empid;

Now you can fetch the customers needed records using VIEW. 

SELECT * FROM mvw_customer

Refresh Materialized View mvw_customer

REFRESH MATERIALIZED VIEW mvw_customer;

Create an index on Materialized View

CREATE INDEX mvw_index_1 ON mvw_customer (name);

When Should You Use Materialized View

It is related to performance optimization, if you can't bear slow performance of the traditional view then you should use the materialized view that gives the better performance, especially if you add the appropriate indexes.