Materialized views in PostgreSQL differ from the regular ones as they do physically store the data in the database.
We’ve already covered how to create a regular view in PostgreSQL, so let’s talk about the key differences between materialized and regular views.
How to create a materialized view
You can create a materialized view in PostgreSQL almost like the regular one.
The only thing that you need to add is the word MATERIALIZED
CREATE MATERIALIZED VIEW view_name AS
Follow up with the SELECT query and that’s it.
Pros and cons materialized views in PostgreSQL
Materialized views do physically store data in the database.
This fact makes the access speed much faster as you don’t have to run the query every time.
On the other hand, you don’t get an instant update of the data and might end up in a situation where you’ve received an outdated set of results.
To refresh a materialized view you can run the following query:
REFRESH MATERIALIZED VIEW view_name;
Such refresh is similar to invalidating the cache and will synchronize the materialized view with the actual DB state.
Top comments (0)