A materialized view is an object where the result of a previous query that has been computed is saved and can be refreshed when needed.

Unlike views which are logically saved, materialized views are physically saved. Therefore, the entire data set that is associated with a query does not need to be read all the time.

Create materialized view

CREATE MATERIALIZED VIEW [ IF NOT EXISTS ] table_name
    [ (column_name [, ...] ) ]
    [ USING method ]
    [ WITH ( storage_parameter [= value] [, ... ] ) ]
    [ TABLESPACE tablespace_name ]
    AS query
    [ WITH [ NO ] DATA ];

Refresh materialized view

REFRESH MATERIALIZED VIEW [ CONCURRENTLY ] name
    [ WITH [ NO ] DATA ]

Drop materialized view

DROP MATERIALIZED VIEW [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]

Differences between a view and materialized view

• Views are not stored on disk, so each time it is accessed it needs to get updated. On the other hand, materialized views are updated as soon as they are stored on disk.

• The row id in a view is very analagous to the row id of the ‘original’ table, whereas the row id in a materialized view is different.

• Tables that make up a view are only a view that is logical whereas in a materialized view they are a copy of the original, which is separate.

• A view gets automatically updated upon accessing it. Materialized view gets updated through a process that is automatic (e.g. a trigger) so that the materialized view has the latest data.

• The perfomance of a view is slower than a materialized view since a view is not physically stored on a disk while a materialized view is. Hence, this results in faster access for the user when using the materialized view.