Skip to content
Tutorials Center
My learning

Topics

Lesson 35 of 90 ยท Schema Design and Constraints

Views and Materialized Views

Use PostgreSQL views and materialized views well: updatable views, WITH CHECK OPTION, security_barrier, security_invoker and REFRESH CONCURRENTLY.

A view is a saved query with a name. You query it like a table, but it stores no data of its own: each time you read it, PostgreSQL runs the saved query. Views hide complicated joins, give application code a stable shape even when tables change, and let you expose only some columns to some users. A materialized view is different. It runs the query once, stores the result like a table, and serves reads from that stored copy until you refresh it.

This lesson assumes you know the basics from Views. It focuses on what PostgreSQL adds: views you can insert into, rules about what a view may change, security options, and the refresh behavior of materialized views.

What runs live here. The editors use SQLite (sql.js), which supports ordinary views. SQLite views are read-only and it has no materialized views, so updatable views, CHECK OPTION, security options and REFRESH MATERIALIZED VIEW are read-only PostgreSQL blocks. A live stand-in shows how a materialized view behaves by using a summary table that you rebuild by hand.

View or materialized view?

View Always fresh. Runs the query on every read.
Materialized view Stored result. Fast to read, stale until refreshed.

Pick by how fresh the data must be and how costly the query is.

1 / 2

Ordinary views

You create a view with CREATE VIEW name AS query. This live example builds a small shop schema and a view that joins and summarizes it. After you create the view, you use it as if it were a table.

SQL A view that hides a join

Press Run (or Ctrl + Enter) to see the result here.

Bo has 80 in one paid order, Ada has 80 over two orders, and Chen has 45 (the cancelled order is excluded). A view always reflects the current data. Add an order and the view changes at once:

SQL A view is always current

Press Run (or Ctrl + Enter) to see the result here.

First the view has 2 rows. After the changes it lists Desk and Shelf, because the view runs its query again each time.

A few facts about PostgreSQL views:

  • A view is not faster than the query inside it. Since PostgreSQL treats it like a subquery, the planner merges it into the outer query, so filters on the view can use indexes on the base tables.
  • CREATE OR REPLACE VIEW changes a view's query, but it may only add new columns at the end. You cannot rename, remove or retype existing columns. For bigger changes, drop and recreate (and anything that depends on it).
  • A view stores its column list at creation. If you write SELECT * and later add a column to the table, the view does not gain the column. List columns explicitly.
  • Views can depend on other views. DROP VIEW fails if something depends on it unless you add CASCADE.

Updatable views

In PostgreSQL, a simple view can be used with INSERT, UPDATE and DELETE directly. The change is applied to the underlying table. A view is automatically updatable when it reads from exactly one table or updatable view and has no DISTINCT, GROUP BY, HAVING, LIMIT, set operations, aggregates or window functions.

SQL
CREATE VIEW active_products AS
SELECT id, name, price FROM products WHERE active;

UPDATE active_products SET price = 45 WHERE id = 1;   -- changes products
INSERT INTO active_products (name, price) VALUES ('Shelf', 60);

Notice the second statement: the view does not show the active column, so the new row in products gets that column's default. If active had no default, the row would get NULL. A view can also accept a change that makes a row disappear from the view: update a row's price through a view that only shows cheap products, and it may no longer match the view's WHERE. To block changes like that, use WITH CHECK OPTION:

SQL
CREATE VIEW cheap_products AS
SELECT id, name, price FROM products WHERE price < 50
WITH CHECK OPTION;

INSERT INTO cheap_products (name, price) VALUES ('Mug', 12);   -- ok
UPDATE cheap_products SET price = 99 WHERE name = 'Mug';
-- ERROR:  new row violates check option for view "cheap_products"

WITH CHECK OPTION rejects any insert or update through the view that would produce a row the view cannot see. When views are built on other views, WITH LOCAL CHECK OPTION checks only that view's own condition and WITH CASCADED CHECK OPTION (the default) also checks the conditions of the views below it.

For views that are not simple, such as a join, you can still make them writable with an INSTEAD OF trigger that decides what the change means. See Triggers.

Security: security_barrier and security_invoker

Views are often used to give a user access to a slice of a table. Two options matter for that.

Who is checked. By default, a view runs with the privileges of the view's owner, not of the user who queries it. This is what lets you grant SELECT on a view without granting it on the table. PostgreSQL 15 added security_invoker:

SQL
CREATE VIEW my_orders
WITH (security_invoker = true) AS
SELECT * FROM orders;

With security_invoker = true the view runs with the caller's privileges. Row-level security policies on orders then apply to the person who queries the view, which is what you normally want. Without it, access is checked as the view's owner, and a table's owner normally bypasses row-level security. See Row-Level Security.

Leaking through functions. Suppose a view hides rows with WHERE tenant_id = 5. A user could write SELECT * FROM v WHERE leaky_function(secret_column), and the planner might run their cheap function against rows before it applies the view's filter. If the function raises a notice or error that contains the value, hidden data can leak. security_barrier = true tells the planner not to push user conditions below the view's own filter:

SQL
CREATE VIEW tenant_five
WITH (security_barrier = true) AS
SELECT * FROM documents WHERE tenant_id = 5;

The price is that a barrier view can plan worse, because the planner loses some freedom. Use it only for views that exist to enforce security.

Materialized views

A materialized view runs its query once and stores the result as a physical table. Reads are as fast as reading a table, and you can put indexes on it. The data is a snapshot: it does not change until you refresh it.

This suits expensive queries that you can show slightly out of date, such as a dashboard summary:

SQL
CREATE MATERIALIZED VIEW daily_revenue AS
SELECT created_at::date AS day, SUM(total) AS revenue, COUNT(*) AS orders
FROM orders
WHERE status = 'paid'
GROUP BY 1
WITH DATA;

CREATE UNIQUE INDEX daily_revenue_day_idx ON daily_revenue (day);

REFRESH MATERIALIZED VIEW daily_revenue;

A plain REFRESH MATERIALIZED VIEW rebuilds the whole result and takes an exclusive lock for the duration, which blocks reads. To keep serving old data while the new data is built, use the concurrent form:

SQL
REFRESH MATERIALIZED VIEW CONCURRENTLY daily_revenue;

CONCURRENTLY builds the new result on the side and applies only the differences, so readers are not blocked. It has two requirements and a cost:

  • The materialized view needs at least one unique index (covering all rows, no WHERE), so PostgreSQL can match old and new rows.
  • It cannot be used on a view created WITH NO DATA; the first refresh must be a normal one.
  • It is slower than a plain refresh, and it still recomputes the entire query.

PostgreSQL does not refresh materialized views on its own. Teams run the refresh from a scheduler such as pg_cron, from a cron job or from the application after a data load. A view that is never refreshed silently shows old data, so write down who refreshes it and how often. You cannot INSERT into a materialized view or edit its rows.

The next example builds a hand-made stand-in with a summary table. It shows the same behavior live: stale until refreshed.

SQL A hand-made materialized view

Press Run (or Ctrl + Enter) to see the result here.

March 1 is 80 both before and after. March 2 shows 80 in the stale copy and 100 after the refresh, because the new order only reached the summary when it was rebuilt. That gap is exactly what a materialized view risks.

Choosing between them

Question View Materialized view
Data always current? Yes No, only after a refresh
Cost per read Cost of the query Cost of reading a table
Can be indexed? No (index the base tables) Yes
Can be written to? Sometimes (simple views) No
Best for Naming and securing queries Expensive reports and dashboards

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What does an ordinary PostgreSQL view store?

    A view holds the saved query, not data. Each read runs that query against the current table contents.
  2. 2. What does WITH CHECK OPTION do?

    It enforces the view's WHERE condition on writes through the view, preventing rows from silently disappearing or being created outside the view.
  3. 3. What is needed to use REFRESH MATERIALIZED VIEW CONCURRENTLY?

    PostgreSQL needs a unique index to match old and new rows. The view must also already be populated, so the first refresh is a normal one.
  4. 4. What does security_invoker = true change for a view (PostgreSQL 15+)?

    By default a view checks permissions as its owner. security_invoker makes it use the person who queries it.
  5. 5. You create a view with SELECT * and later add a column to the table. What happens to the view?

    The column list is fixed when the view is created, even for SELECT *. Use explicit column lists and recreate or replace the view when you want new columns.

Summary

  • A view stores a query, not data, and always shows current rows. Use explicit column lists, because SELECT * is expanded once at creation.
  • Simple single-table views are updatable; WITH CHECK OPTION stops writes that the view could not show.
  • security_invoker runs the view with the caller's privileges so row-level security applies; security_barrier stops user functions from seeing rows the view filters out.
  • A materialized view stores its result, can be indexed and goes stale until refreshed.
  • REFRESH MATERIALIZED VIEW CONCURRENTLY keeps reads working but needs a unique index and a populated view.
  • The live editors ran ordinary views and a hand-built summary table on SQLite; the rest is PostgreSQL syntax shown read-only.

Next: Table Inheritance and Common Design Patterns.