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.