Some basics of working with Materialized Views in PostgreSQL

matviews.sql

-- create any table to use as a test dataset
create table test_data (
	id serial not null primary key,
	data jsonb not null
);

-- insert some data!
insert into test_data (data) values ('{}');
insert into test_data (data) values ('{}');

-- query that table with an articficial delay to emulate a super large dataset
-- it should take just over 4 seconds to run; 2 seconds * 2 rows
select *, pg_sleep(2) from test_data;

-- store a view of the query
create view test_data_view as
select *, pg_sleep(2)::text from test_data;

-- note how it creates the view immediately

-- see it's performance mirroring a direct query is the same as the plain query
select * from test_data_view;

-- add more data because why not!
insert into test_data (data) values ('{}');
insert into test_data (data) values ('{}');

-- see how the view gets even slower but shows all the table data; 2 seconds * 4 rows
select * from test_data_view;

-- make a materialized view instead of a regular view
-- see how long it takes the usual query time to create
create materialized view test_data_matview as
select *, pg_sleep(2)::text from test_data;

-- see it's performance is instantaneous in contrast to a regular view
select * from test_data_matview;

-- add more data again to change the underlying data since the materialized view was created
insert into test_data (data) values ('{}');
insert into test_data (data) values ('{}');

-- see the performance is still instantaneous but also the lack of the new data
select * from test_data_matview;

-- refresh in a blocking way
-- see how long it takes to refresh is the full length to run the query
refresh materialized view test_data_matview;

-- try reading the table while refreshing in another connection
-- you'll see that you can't read from the materialized view while it is being refreshed

-- concurrent refreshing needs a unique index
create unique index id_index on test_data_matview (id);

-- now refresh concurrently
refresh materialized view concurrently test_data_matview;

-- try reading the table while refreshing in another connection
-- you'll see that you can read from the materialized view while it is being refreshed
-- but that you won't see the updated results until the refresh is fully complete
-- this is because the refresh populate a new materialized view that replaces the old upon completing the refresh

-- here is another example of only seeing the final results show up at the end of refreshing
update test_data set data = '{"x": true}';

-- try reading the table while refreshing in another connection
refresh materialized view concurrently test_data_matview;

-- conclusion: materialized views are a good solution for slow datasets that update infrequently
-- like for example an analytics materialized view that only updates once per day after midnight to produce daily stats