Materialized view

TLDR (opens in a new tab): Save view to table

Materialized views in PostgreSQL allow persisting view data in a table-like form. This is especially beneficial for complex queries that are resource-intensive to execute repeatedly.

They are created by simply adding the MATERIALIZED keyword to the view creation statement:

CREATE MATERIALIZED VIEW mymatview AS 
SELECT * FROM mytab;
 
CREATE UNIQUE INDEX idx_mymatview
  ON mymatview (seller_no, invoice_date);

Key points:

• Physical Storage: The result set is stored on disk, enabling quick access without re-executing the query. Materialized views support creating indexes.
• Manual Refresh: Changes in underlying tables are not automatically reflected. You must use REFRESH MATERIALIZED VIEW to update the data.
• Concurrency Support: The REFRESH MATERIALIZED VIEW CONCURRENTLY command allows the view to be refreshed without locking out concurrent reads.

Caveats:

• To use REFRESH MATERIALIZED VIEW CONCURRENTLY, the materialized view must have a unique index on at least one column.
• Materialized views are not replicated automatically in the same way as regular tables in some PostgreSQL replication setups (like streaming replication).
• Materialized views are not included in the information_schema views. Relevant information can be found in pg_matviews and pg_class:

SELECT schemaname, matviewname, definition
FROM pg_matviews;
 
SELECT relname, relnamespace::regnamespace AS schemaname
FROM pg_class
WHERE relkind = 'm';