Materialized view

Materialized Views vs Views

Materialized View

Materialized views store the SQL query and the data returned from the SQL query.

Example

CREATE TABLE random_tab (id int, val decimal);

-- Insert random data
INSERT INTO random_tab
SELECT 1, random() FROM generate_series(1, 10000000);
INSERT INTO random_tab
SELECT 2, random() FROM generate_series(1, 10000000);

-- Check data
SELECT * FROM random_tab;

-- Create a materialized view
CREATE MATERIALIZED VIEW mv_random_tab AS
SELECT id, avg(val), count(*)
FROM random_tab
GROUP BY id;

-- Select from the materialized view
SELECT * FROM mv_random_tab;

-- Delete data from the base table
DELETE FROM random_tab WHERE id = 1;

-- Select from the materialized view (still has old data)
SELECT * FROM mv_random_tab;

-- Refresh the materialized view
REFRESH MATERIALIZED VIEW mv_random_tab;

-- Select from the materialized view (now updated)
SELECT * FROM mv_random_tab;

View

Regular views store the SQL query but do not store the data returned from the SQL query. The query is executed every time the view is queried.

Example

-- Create a view
CREATE VIEW vw_random_tab AS
SELECT id, avg(val), count(*)
FROM random_tab
GROUP BY id;

-- Select from the view
SELECT * FROM vw_random_tab;

Advanced Queries and Commands

-- List all materialized views
SELECT matviewname
FROM pg_matviews;

-- List materialized views in psql
\dm+ matview

-- Create an index on a materialized view
CREATE INDEX idx_mv_random_tab ON mv_random_tab (id);

-- Refreshing with minimal locking
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_random_tab;