The difference between regular views and materialized views in PostgreSQL

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.