Views

Learn what views are and when to use them.

Views sit on top of the tables you create in Tables and data.

A view is a convenient shortcut to a query. Creating a view doesn't involve new tables or data. When you run a view, Postgres executes the underlying query and returns its results.

Say you have the following tables from a university database:

students

courses

grades

Creating a view that consists of all three tables looks like this:

1
create view transcripts as
2
    select
3
        students.name,
4
        students.type,
5
        courses.title,
6
        courses.code,
7
        grades.result
8
    from grades
9
    left join students on grades.student_id = students.id
10
    left join courses on grades.course_id = courses.id;
11
12
grant all on table transcripts to authenticated;

Then you can access the underlying query with:

1
select * from transcripts;

View security#

By default, views are accessed with their creator's permission, known as security definer. If a privileged role creates a view, others accessing it use that role's elevated permissions. To enforce row level security policies, define the view with the security_invoker modifier.

1
-- alter a security_definer view to be security_invoker
2
alter view <view name>
3
set (security_invoker = true);
4
5
-- create a view with the security_invoker modifier
6
create view <view name> with(security_invoker=true) as (
7
  select * from <some table>
8
);

When to use views#

Views provide several benefits.

Simplicity#

As a query becomes more complex, calling it repeatedly gets tedious, especially when you run it regularly. In the example above, instead of repeatedly running:

1
select
2
  students.name,
3
  students.type,
4
  courses.title,
5
  courses.code,
6
  grades.result
7
from
8
  grades
9
  left join students on grades.student_id = students.id
10
  left join courses on grades.course_id = courses.id;

You can run this instead:

1
select * from transcripts;

A view also behaves like a typical table. You can safely use it in table joins or create new views from existing views.

Consistency#

Views reduce the likelihood of mistakes when you execute a query repeatedly. In the example above, you might decide to exclude the course Introduction to Postgres. The query becomes:

1
select
2
  students.name,
3
  students.type,
4
  courses.title,
5
  courses.code,
6
  grades.result
7
from
8
  grades
9
  left join students on grades.student_id = students.id
10
  left join courses on grades.course_id = courses.id
11
where courses.code != 'PG101';

Without a view, you need to add the new rule to every dependent query. That increases the likelihood of errors and inconsistencies, and it takes considerable effort. With views, you alter the underlying query in the transcripts view, and the change applies to every application using it.

Logical organization#

With views, you can give your query a name. This is useful for teams working with the same database. Instead of guessing what a query does, a well-named view explains it. For example, the name of the transcripts view suggests that the underlying query involves the students, courses, and grades tables.

Security#

Views can restrict the amount and type of data presented to a user. Instead of giving a user direct access to a set of tables, you give them a view. You can prevent them from reading sensitive columns by excluding those columns from the underlying query.

Materialized views#

A materialized view is a form of view that also stores its results to disk. Subsequent reads of a materialized view return results much faster than a conventional view, because the data is already available. A conventional view executes the underlying query each time you call it.

Using the example above, you can create a materialized view like this:

1
create materialized view transcripts as
2
  select
3
    students.name,
4
    students.type,
5
    courses.title,
6
    courses.code,
7
    grades.result
8
  from
9
    grades
10
    left join students on grades.student_id = students.id
11
    left join courses on grades.course_id = courses.id;

Reading from the materialized view is the same as a conventional view:

1
select * from transcripts;

Refreshing materialized views#

There's a trade-off: data in a materialized view isn't always up to date. Refresh it regularly to prevent the data from becoming too stale.

1
refresh materialized view transcripts;

How often you refresh a materialized view is up to you, and it probably differs for each view depending on its use case.

Materialized views vs conventional views#

Materialized views are useful when execution times for queries or views are too slow. This happens in views or queries that involve multiple tables and billions of rows. Use a materialized view only when you can tolerate outdated data. Internal dashboards and analytics are common use cases.

Creating a materialized view isn't a solution to inefficient queries. Always optimize a slow-running query, even when you implement a materialized view.

Resources#

• Official Docs: Create view
• Postgres Tutorial: Views