Views and materialized views

conceptThe concept

CREATE VIEW stores a query. Selecting from it is like inlining the query, so filters from outside are pushed down. Simple views (one table, no aggregates) are automatically updatable; WITH CHECK OPTION rejects inserts and updates that would produce rows the view cannot see. Since 15, security_invoker makes a view check permissions as the calling user.

CREATE MATERIALIZED VIEW runs the query once and stores rows. REFRESH MATERIALIZED VIEW recomputes it under an exclusive lock that blocks reads; REFRESH ... CONCURRENTLY computes a diff and applies it while reads continue, and requires a unique index on the materialized view.

whyWhy it matters

Views give stable interfaces: you can restructure tables underneath while reports keep working. Materialized views turn a 10-second dashboard query into a 5-millisecond lookup, as long as minutes-old data is acceptable.

Use caseReal-world scenario

The executive dashboard shows revenue per country per month. A materialized view refreshed every 10 minutes serves it instantly, and the BI tool reads a view that hides internal columns.

Try itExamples

Run and edit these in the interactive lesson.

BEGIN;
CREATE VIEW customer_summary AS
SELECT c.id, c.name, c.country, c.tier,
       count(o.id) AS orders,
       coalesce(sum(o.total) FILTER (WHERE o.status <> 'cancelled'), 0) AS lifetime_value
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id;
SELECT * FROM customer_summary WHERE country = 'IN' ORDER BY lifetime_value DESC LIMIT 5;
ROLLBACK;

BEGIN;
CREATE VIEW german_customers AS
SELECT id, name, email, city, country, created_at FROM customers WHERE country = 'DE'
WITH CHECK OPTION;
UPDATE german_customers SET city = 'Hamburg' WHERE id = (SELECT min(id) FROM customers WHERE country = 'DE');
INSERT INTO german_customers (name, email, city, country, created_at) VALUES ('Eve', 'eve@example.com', 'Paris', 'FR', now());

BEGIN;
CREATE MATERIALIZED VIEW revenue_by_month AS
SELECT date_trunc('month', o.created_at)::date AS month, c.country, sum(o.total) AS revenue, count(*) AS orders
FROM orders o JOIN customers c ON c.id = o.customer_id
WHERE o.status <> 'cancelled'
GROUP BY 1, 2;
CREATE UNIQUE INDEX ON revenue_by_month (month, country);
SELECT * FROM revenue_by_month WHERE country = 'UK' ORDER BY month DESC LIMIT 3;
UPDATE orders SET total = total + 1000 WHERE id = (SELECT max(id) FROM orders);
REFRESH MATERIALIZED VIEW CONCURRENTLY revenue_by_month;
SELECT * FROM revenue_by_month WHERE month = '2025-12-01' ORDER BY revenue DESC;
ROLLBACK;

Avoid / preferDo this, not that

CREATE VIEW recent_orders AS SELECT * FROM orders WHERE created_at > now() - interval '7 days';

CREATE VIEW recent_orders AS
SELECT id, customer_id, status, total, created_at
FROM orders WHERE created_at > now() - interval '7 days';

-- view on view on view on view

-- one or two layers, each with a clear purpose

PitfallsCommon mistakes

• Stacking views on views on views. The final query becomes huge and hard to tune.
• Refreshing a large materialized view without CONCURRENTLY while users read it; they block for the whole refresh.
• Expecting a materialized view to update itself. It only changes when REFRESH runs (schedule it with cron or pg_cron).

LimitsWhen not to use it

Do not use a materialized view for data that must be current to the second; it is only as fresh as its last refresh.

SecuritySecurity notes

Views run with the owner's privileges by default, which bypasses the caller's row-level security on base tables. Since PostgreSQL 15, WITH (security_invoker = true) makes a view check the caller's privileges and policies.

ChallengeTry it yourself

BEGIN;
CREATE VIEW customer_revenue WITH (security_invoker = true) AS
SELECT c.id, c.name, count(o.id) AS orders, coalesce(sum(o.total), 0) AS revenue
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id AND o.status <> 'cancelled'
GROUP BY c.id;
SELECT name, orders, revenue FROM customer_revenue ORDER BY revenue DESC LIMIT 3;
ROLLBACK;

InterviewQuestions you should be able to answer

QuizCheck your understanding

4 questions with instant feedback in the interactive lesson; a wrong answer goes into your review deck.

Go deeperOfficial documentation

• Views
• Materialized views