PostgreSQL & Materialized Views

PostgreSQL, a renowned and reliable database system, is packed with a multitude of features that often go unnoticed. In this blog, we will shine a spotlight on these lesser-known gems, revealing their true potential. Whether you're a seasoned PostgreSQL user or just starting out, join us on this enlightening journey as we unlock the secrets of one of PostgreSQL's lesser-known features - Materialized Views.

Consider the below-created tables for reference

create a table if not exists "blogs
(
? ? id serial primary key,
? ? title char(256) unique not null,
? ? author_email char(100) not null,
? ? description text,
? ? created timestamptz not null default current_timestamp
);

create a table if not exists "reviews"
(
? ? id serial primary key,
? ? user_email char(100) not null,
 ? rating_out_of_10 smallint constraint rating_range check 
(rating_out_of_10 between 0 and 10),
? ? blog_id int, constraint fk_blogs_id foreign key(blog_id) 
references blogs(id),
? ? created timestamptz not null default current_timestamp
);"        

Now, let's add some entries to the blogs and reviews table.

insert into blogs (title,author_email) values ('postgres', 
'[email protected]')
insert into blogs (title, author_email) values ('internal', 
'[email protected]');
insert into reviews (rating_out_of_10, user_email, blog_id) values 
(8, 'dummy@review', 1);
insert into reviews (rating_out_of_10, user_email, blog_id) values 
(10, 'fake@review', 2);;        

If you haven’t noticed yet, we have kept the id `serial`, which is shorthand to assign auto-increment integer sequence as the column type.

And we have a column `created` set to auto-populate current_timestamp on creation. So, after the above insert query, our data looks like this:

Enough of data entries, let’s dive into the feature now.

Materialized views allow you to create a snapshot of a query result and store it in a table (unlike normal view, which does not store the result physically). This can be useful for improving the performance of complex queries that are frequently run, as the database can simply read the data from the materialized view instead of having to re-run the query each time.

Here’s an example of creating a materialized view:

create materialized view view_name A
select user_email, rating_out_of_10, title from reviews join blogs 
on reviews.blog_id = blogs.id;        

We can perform all the operations on materialized view same as the table

Let’s add one more review for our Postgres blog and see the data using the join query

insert into reviews (rating_out_of_10, user_email, blog_id) 
values (10, 'fake@review', 1);
select user_email, rating_out_of_10, title from reviews join blogs 
on reviews.blog_id = blogs.id;        

Now let's fetch the same data using our materialized view

Wait, what! We don’t see the entry we inserted after the creation of the view.

One disadvantage of the materialized view is that you have to manually update the view on data changes in one of these tables. If we create a normal view we do not need to refresh as it executes the query on the fly.

We can refresh the materialized view using:

refresh materialized view view_name

select * from view_name;;        

However, you can use triggers to update the materialized view on insertion, updation, or deletion from the source tables. Materialized views are extremely useful for the data which comes through complex queries and the underlying tables are not that frequently updated.

Conclusion

Whether you're a seasoned veteran or a novice embarking on your PostgreSQL voyage, the insights shared in this blog serve as a testament to the richness and versatility of this remarkable database system. It's a reminder that there's always more to discover, even in tools we think we know well.

Written by: