TIL Postgres: Views & Materialized Views

What is a View?

To put it simply, a view is a convenient shortcut to a query. Creating a view does not involve any new tables or data. When ran, the underlying query is executed, returning its results to the user.

Basic Example

Say we have the following tables from a database of a university:

students

id	name	type
1	Arun	undergraduate
2	Zack	graduate
3	Joy	graduate

courses

id	title	code
1	Introduction to Postgres	PG101
2	Authentication Theories	AUTH205
3	Fundamentals of Supabase	SUP412

grades

id	student_id	course_id	result
1	1	1	B+
2	1	3	A+
3	2	2	A
4	3	1	A-
5	3	2	A
6	3	3	B-

Creating a view consisting of all the three tables will look like this:

CREATE VIEW transcripts AS
    SELECT
        students.name,
        students.type,
        courses.title,
        courses.code,
        grades.result
    FROM grades
    LEFT JOIN students where grades.student_id = students.id
    LEFT JOIN courses where grades.course_id = courses.id;

Once done, we can now easily access the underlying query with:

SELECT * FROM transcripts;

For additional parameters or options, refer here.

Why should we use it?

Views are highly useful for reading data based on the following aspects:

Simplicity

As a query become complex and begins to occupy multiple lines, it becomes a hassle to call it. It becomes even more apparent when we run it at regular intervals. In the example above, instead of repeatedly running:

SELECT
    students.name,
    students.type,
    courses.title,
    courses.code,
    grades.result
FROM grades
LEFT JOIN students where grades.student_id = students.id
LEFT JOIN courses where grades.course_id = courses.id;

We can run this instead:

SELECT * FROM transcripts;

Additionally, a view behaves like a typical table. As such, we can safely use it for any subsequent JOINs or even create a view from a query that already involves another view.

Consistency

Along with its simplicity, a view brings along consistency that ensures that the likelihood of mistakes decreases when repeatedly executing a query. With reference to the query above, it could be a part of other queries. One day, we may decide that we want to exclude the course Introduction to Postgres. The above query would become:

SELECT
    students.name,
    students.type,
    courses.title,
    courses.code,
    grades.result
FROM grades
LEFT JOIN students where grades.student_id = students.id
LEFT JOIN courses where grades.course_id = courses.id
WHERE courses.code != 'PG101';

Without a view, we would need to go into every single dependent query to add the new rule. By doing this, there will be an increase in the likelihood of errors and inconsistencies arising from typos and missing out on dependent queries. With views, we would need to just alter the underlying query in the view transcripts. The change will be applied to any other queries using this view.

Logical Organization

With views, we can give our query a name. Doing this is extremely useful in teams working on the same database. Instead of trying to guess what a query is meant to do, having it as a well-named view can easily explain it. For example, by looking at the name of the view transcripts, we can infer that the underlying query could involve the students, courses, and grades tables.

Security

Using views can also restrict the amount and type of data presented to a user. Instead of allowing a user direct access to a set of tables, we provide them a view instead. With it, we can prevent them from reading sensitive columns by not including them in the underlying query.

What is a Materialized View?

A materialized view is a form of view but with the added feature of physically storing its resultant data into storage. Given the same underlying query, in subsequent reads of a materialized view, the time taken to return its results would be much faster than that of the conventional view. And this is because the data is readily available for a materialized view while the typical view only executes the underlying query on the spot.

Basic Example

Using the same set of tables and underlying query as the above, a new materialized view will look like this:

CREATE MATERIALIZED VIEW transcripts AS
    SELECT
        students.name,
        students.type,
        courses.title,
        courses.code,
        grades.result
    FROM grades
    LEFT JOIN students where grades.student_id = students.id
    LEFT JOIN courses where grades.course_id = courses.id;

Afterward, reading the materialized view can be done as such:

SELECT * FROM transcripts;

For additional parameters or options, refer here.

Refreshing

Unfortunately, there is a trade-off - data in materialized views are not always up to date. We would need to refresh it regularly to prevent the data from becoming too stale. To do so:

REFRESH MATERIALIZED VIEW transcripts;

When should we use this over the conventional view?

Materialized views come in handy when execution times for queries or views become unbearable or exceed the service level agreements of a business. These could likely occur in views or queries involving multiple tables and hundreds of thousands of rows. When using such a view, however, there should be tolerance towards data being not up to date. We should schedule refreshes regularly to ensure that data does not become too outdated over time.

It is to note that creating a materialized view is not a solution to inefficient queries. All options to optimize a slow running query should be exhausted before implementing a materialized view.

Conclusion

Postgres views and materialized views are a great way to organize and view results from commonly used queries. Although highly similar to one another, each has its purpose. Views simplify the process of running queries. Materialized views add on to this by speeding up the process of accessing slower running queries at the trade-off of having stale or not up-to-date data.

---

TIL Postgres is an ongoing series by Supabase that aims to regularly share snippets of information about PostgreSQL and hopefully provide you with something new to learn. 😉

Any new posts or announcements of our future features and freebies will be made here on DEV first. Follow us so that you don't miss out.