PostgreSQL Materialized View

Materialized views allow you to store results of complex queries physically and update them periodically. So they are useful in cases where you need quick data access for reporting and business intelligence. Here’s how to create materialized view in PostgreSQL.

PostgreSQL View vs Materialized View

Materialized views are similar to PostgreSQL views which allow you to store SQL queries to call them later. However, PostgreSQL view allows you to store only the SQL query and not its result. Materialized views allow you to store the query result physically, and update them periodically. As a result, materialized views are faster than PostgreSQL views.

Bonus Read : How to Get Last Row Per Group in PostgreSQL

How to Create Materialized View in PostgreSQL

You can create materialized view in PostgreSQL using CREATE MATERIALIZED VIEW statement as shown below.

CREATE MATERIALIZED VIEW view_name
AS query 
WITH [NO] DATA;

In the above statement, you need to mention view_name as well as the query whose result you want to store in the materialized view.

Finally, you can also specify WITH DATA option if you want to load data into the view at the time of its creation. If you mention WITH NO DATA, then the view will be flagged as unreadable and you will not be able to query data from the view until you load data into it.

Bonus Read : PostgreSQL Create Function

PostgreSQL Materialized View Refresh

You can load data into materialized view using REFRESH MATERIALIZED VIEW statement as shown

REFRESH MATERIALIZED VIEW view_name

You can also use the above statement to refresh materialized view.

Please note, REFRESH MATERIALIZED VIEW statement locks the query data so you cannot run queries against it. You can avoid it using CONCURRENTLY option

REFRESH MATERIALIZED VIEW CONCURRENTLY view_name

In this case, PostgreSQL creates a temporary view, compares it with the original one and makes necessary inserts, updates and deletes. As a result, CONCURRENTLY option is available only for materialized views that have a unique index.

To auto refresh materialized view periodically, you can run REFRESH MATERIALIZED VIEW via an automated script.

Bonus Read : PostgreSQL Create Schema

Remove materialized views

You can delete materialized views using DROP MATERIALIZED VIEW statement.

DROP MATERIALIZED VIEW view_name

Materialized View Examples

Let us look at some materialized view examples.

Here’s the query to create a materialized view called sample_view

postgres=# create materialized view sample_view
           as select * from sales
           with no data;

In the above query we have mentioned WITH NO DATA option so the view will not have any data and will give an error when we try to query it.

postgres=# select * from sample_view;
ERROR: materialized view "sample_view" has not been populated
HINT: Use the REFRESH MATERIALIZED VIEW command.

Now we will refresh the view to load data into it and then run the above query against our view.

postgres=# refresh materialized view sample_view;
REFRESH MATERIALIZED VIEW

postgres=# select * from sample_view;
order_date | sale
------------+------
2020-04-01 | 210
2020-04-02 | 125
2020-04-03 | 150
2020-04-04 | 230
2020-04-05 | 200
2020-04-10 | 220
2020-04-06 | 250
2020-04-07 | 215
2020-04-08 | 300
2020-04-09 | 250

As you can see above, when we run our query again, we get the result.

Hopefully, now you can easily create materialized view for your database.

Ubiq makes it easy to visualize data in minutes, and monitor in real-time dashboards. Try it Today!