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.

No comments:

Post a Comment

Please do not enter any spam link in the comment box.

NUnit : Introduction

There are several unit test tools in the market, but one of them is NUnit, the most popular tool for doing unit testing. So in this tutorial...