beginnerintermediateadvanced

PostgreSQL · Section 11

Views

Level: intermediate
Read: 28 min
Concepts: 5

A view as a stored, always-current query with no data of its own, versus a materialized view that physically stores its result and trades freshness for read speed, when a view genuinely improves abstraction versus disguises an expensive query behind a simple name, how view ownership enables structural access control, and the production-critical difference between a plain REFRESH and a non-locking CONCURRENTLY one.

This section

PostgreSQL Create Views | How to Create View in PostgreSQL | Create View in PostgreSQL | DatabaseTSInfo Technologies

What is true here

A VIEW stores only its query, not data — always current, but pays the full underlying query cost on every read.
A MATERIALIZED VIEW stores its result physically — fast reads, but stale until an explicit REFRESH.
A view's calling syntax gives no visual signal of its cost — EXPLAIN reveals what the name hides.
A view runs with its owner's privileges, letting GRANT SELECT on a view expose data without exposing the underlying table.
A plain REFRESH MATERIALIZED VIEW locks all reads; CONCURRENTLY avoids that but needs a unique index and runs slower.

What you will be able to do

Choose between a plain view and a materialized view based on the freshness-vs-speed trade-off
Recognize when a view is hiding a genuinely expensive query, and check its real cost with EXPLAIN
Use a view to expose a restricted slice of data without granting broader table access
Refresh a materialized view in production without locking out concurrent readers

The fork every view decision comes back to

A query worth naming

reuse, clarity, or access control

Always current, or always fast?

the core view vs materialized view trade

VIEW

no stored data, full cost every read

MATERIALIZED VIEW

stored data, refreshed on a schedule

Two kinds of view

A stored query vs a stored result — the fundamental trade-off this section is built around.

Standard Views and Their Benefits/Limitations

corebeginner

A view is a stored, named SELECT query — querying the view re-runs the underlying query every time, always returning current data. It stores no data of its own, only the query definition, similar to a CTE that persists across statements rather than being scoped to one.

Think of it as

A view is exactly a named subquery that persists — the same relationship a CTE has to a single statement, but scoped to the whole database instead. Every time a view is queried, PostgreSQL substitutes its stored definition and executes the resulting combined query fresh, which is why a view always reflects current data (a real benefit) but also why a view built on an expensive query pays that same expense on every single read (a real limitation).

sql

CREATE VIEW active_customers AS
SELECT id, name FROM customers WHERE deleted_at IS NULL;

SELECT * FROM active_customers WHERE name = 'Ada';

What we're doing: Create a view hiding a WHERE filter, then insert a new row and confirm the view reflects it immediately with no refresh step.

view_freshness.sqlsql

CREATE TABLE customers (id SERIAL PRIMARY KEY, name TEXT, deleted_at TIMESTAMPTZ);
INSERT INTO customers (name) VALUES ('Ada');

CREATE VIEW active_customers AS
SELECT id, name FROM customers WHERE deleted_at IS NULL;

SELECT * FROM active_customers;

INSERT INTO customers (name) VALUES ('Grace');
SELECT * FROM active_customers;
-- Grace appears immediately -- no refresh needed, since the view has no stored data of its own

4–5: active_customers stores only this query — no data of its own.
8: A new row is inserted directly into the underlying table, not through the view.
9–10: Querying the view again immediately shows Grace, since the view re-runs its query fresh every time.

Output

id | name
---+------
 1 | Ada
(1 row)

id | name
---+------
 1 | Ada
 2 | Grace
(2 rows)

Why this works: active_customers has no storage of its own — PostgreSQL substitutes its stored SELECT definition into the query being run against it, so the second SELECT effectively executes SELECT id, name FROM customers WHERE deleted_at IS NULL fresh, against the customers table as it exists right now, which already includes Grace. This is the direct mechanical consequence of a view being a stored query rather than a stored result — there is no cache to go stale, and therefore nothing to refresh.

Assuming a view caches its result and needs manual refreshing

Wrong

sql

CREATE VIEW active_customers AS SELECT * FROM customers WHERE deleted_at IS NULL;
-- inserting new customers, then wondering why a "refresh" step seems necessary

Better

sql

-- a plain VIEW needs no refresh -- it always reflects current data automatically
SELECT * FROM active_customers;

What you see: A developer writes application code to periodically "refresh" a plain view, or is confused when a view built on rapidly-changing data does not need any such step, because they are thinking of it like a materialized view or an external cache.

Why: A plain VIEW (as opposed to a MATERIALIZED VIEW) stores no result at all — confusing the two is an easy mistake since they share the word "view," but only the materialized kind involves any concept of staleness or refreshing. A plain view is, mechanically, indistinguishable in freshness from running its underlying query directly by hand every time.

What actually happens when a view is queried

SELECT * FROM active_customers

a query against the view

View definition substituted

stored SELECT swapped in

Full query executed

against the real, current data

View vs the query it wraps

View vs the query it wraps
Property	A view
Stores data?	no — only the query definition
Reflects current data?	always — re-runs the query every time
Performance cost	identical to running the underlying query directly, every time

Together

sql

CREATE VIEW active_customers AS
SELECT * FROM customers WHERE deleted_at IS NULL;

SELECT * FROM active_customers;
-- equivalent to running the SELECT ... WHERE deleted_at IS NULL directly

Remember: A view stores only its defining query, not any data — it always reflects current data with no refresh needed, but pays the full cost of its underlying query on every single read.

Materialized Views and REFRESH MATERIALIZED VIEW

coreintermediate

A materialized view stores its query's result physically, like a real table — reading it is fast, but the data is a snapshot from whenever it was last refreshed, not live. REFRESH MATERIALIZED VIEW re-runs the query and replaces the stored snapshot.

Think of it as

A plain view trades nothing for freshness — always current, always pays the full query cost. A materialized view makes the opposite trade deliberately: pay the expensive query's cost once, at refresh time, then every read is cheap until the next refresh — accepting staleness as the price of read speed. Choosing between them is choosing which of "always current" or "always fast to read" matters more for a specific use case.

sql

CREATE MATERIALIZED VIEW customer_totals AS
SELECT customer_id, sum(total) AS lifetime_total FROM orders GROUP BY customer_id;

REFRESH MATERIALIZED VIEW customer_totals;

What we're doing: Create a materialized view, insert new data, and show the view stays stale until REFRESH is explicitly run.

materialized_view_staleness.sqlsql

CREATE TABLE orders (id SERIAL PRIMARY KEY, customer_id INT, total NUMERIC);
INSERT INTO orders (customer_id, total) VALUES (1, 100);

CREATE MATERIALIZED VIEW customer_totals AS
SELECT customer_id, sum(total) AS lifetime_total FROM orders GROUP BY customer_id;

SELECT * FROM customer_totals;

INSERT INTO orders (customer_id, total) VALUES (1, 50);
SELECT * FROM customer_totals;
-- still shows 100 -- stale, since REFRESH has not run

REFRESH MATERIALIZED VIEW customer_totals;
SELECT * FROM customer_totals;
-- now shows 150

4–5: The result is computed and stored physically at creation time.
8: A new order is inserted directly into the underlying orders table.
10: The materialized view still shows the old total — nothing about inserting into orders automatically updates it.
13: Only an explicit REFRESH re-runs the query and replaces the stored snapshot.

Output

customer_id | lifetime_total
-------------+----------------
           1 |            100

customer_id | lifetime_total
-------------+----------------
           1 |            100

customer_id | lifetime_total
-------------+----------------
           1 |            150

Why this works: A materialized view's stored rows are set once at CREATE MATERIALIZED VIEW time and remain frozen at that value regardless of any subsequent change to the underlying orders table — there is no trigger, no automatic invalidation, nothing watching for changes. REFRESH MATERIALIZED VIEW is the only mechanism that updates the stored snapshot, which is why the second SELECT still shows 100 even after a genuine change to the underlying data, and only the third SELECT (after REFRESH) shows the current, correct 150.

Assuming a materialized view updates itself, or forgetting to schedule refreshes

Wrong

sql

CREATE MATERIALIZED VIEW customer_totals AS
SELECT customer_id, sum(total) FROM orders GROUP BY customer_id;
-- no refresh schedule set up anywhere -- the view slowly becomes more and more stale forever

Better

sql

-- scheduled externally (cron, pg_cron, application job queue) at an interval matching the use case's staleness tolerance:
REFRESH MATERIALIZED VIEW CONCURRENTLY customer_totals;

What you see: A dashboard or report backed by a materialized view shows numbers that drift further and further from reality over time, with no error or warning anywhere — the query itself always "succeeds," it just answers an increasingly outdated question.

Why: A materialized view is explicitly a one-time snapshot at whatever moment REFRESH last ran — PostgreSQL has no built-in scheduling or automatic invalidation for it, unlike some other databases' materialized view implementations. The refresh schedule is entirely the responsibility of something external (a cron job, pg_cron, application-level scheduling), and choosing that interval is itself a real design decision balancing staleness tolerance against refresh cost.

View vs materialized view — the trade being made

VIEW

+no stored data
+always current
+full query cost on every read

MATERIALIZED VIEW

−stores data physically
−current as of last REFRESH
−cheap reads, expensive refresh

Plain view vs materialized view

Plain view vs materialized view
Property	View	Materialized view
Stores data?	no	yes, physically
Always current?	yes	no — as current as its last REFRESH
Read cost	full underlying query, every time	cheap — reads the stored snapshot
Can be indexed?	no (the view itself)	yes

Together

sql

CREATE MATERIALIZED VIEW customer_totals AS
SELECT customer_id, sum(total) AS lifetime_total FROM orders GROUP BY customer_id;

Remember: A materialized view physically stores its result, trading freshness for fast reads — nothing refreshes it automatically; REFRESH MATERIALIZED VIEW must be triggered explicitly, and the refresh schedule is a real design decision.

Using views well

When abstraction is worth it, and how ownership enables access control.

When a View Improves Abstraction vs Hides Expensive Queries

standardintermediate

A view is genuinely useful abstraction when it hides SQL complexity that stays roughly the same cost every time — like a filtered subset or a straightforward join. It becomes a liability when the query it hides is expensive (heavy aggregation, many joins), because a simple SELECT * FROM some_view no longer looks like it might be slow.

Think of it as

A view's name is a promise about meaning ("this is the active customers"), but it says nothing about cost — and SQL syntax gives no visual signal that a plain-looking table reference is secretly an expensive multi-way join with aggregation underneath. This is fine when the hidden cost is trivial, and a real trap when it is not: a developer composing several views together, each individually reasonable, can accidentally build a query whose actual execution plan is far more expensive than the SQL on the page suggests.

sql

CREATE VIEW customer_lifetime_value AS
SELECT c.id, sum(o.total) AS lifetime_value
FROM customers c
JOIN orders o ON o.customer_id = c.id
JOIN order_items oi ON oi.order_id = o.id
GROUP BY c.id;

EXPLAIN SELECT * FROM customer_lifetime_value WHERE id = 42;

What we're doing: Compare EXPLAIN on a cheap view against an expensive one, to make the disguised cost concrete.

view_cost_demo.sqlsql

CREATE TABLE customers (id SERIAL PRIMARY KEY, deleted_at TIMESTAMPTZ);
CREATE TABLE orders (id SERIAL PRIMARY KEY, customer_id INT, total NUMERIC);

CREATE VIEW active_customers AS
SELECT * FROM customers WHERE deleted_at IS NULL;

CREATE VIEW customer_totals AS
SELECT customer_id, sum(total) AS lifetime_total
FROM orders GROUP BY customer_id;

EXPLAIN SELECT * FROM active_customers;
EXPLAIN SELECT * FROM customer_totals;

4–5: active_customers is a single-table filter — its EXPLAIN plan is trivially a scan.
7–9: customer_totals aggregates across all of orders — its EXPLAIN plan involves a full table scan and a grouping/hashing step, real work every time it is queried.

Output

QUERY PLAN
----------------------------------------
Seq Scan on customers  (cost=0.00..1.05)
  Filter: (deleted_at IS NULL)

QUERY PLAN
----------------------------------------------
HashAggregate  (cost=1.09..1.11)
  Group Key: customer_id
  ->  Seq Scan on orders  (cost=0.00..1.02)

Why this works: Both queries look identical in shape — SELECT * FROM some_view — but their EXPLAIN plans reveal genuinely different costs, and nothing about the SQL syntax used to query either view hints at that difference. active_customers is cheap because its underlying query is cheap; customer_totals is more expensive because a HashAggregate over the full orders table is real, non-trivial work — a cost that scales with the size of orders, invisible from the view's simple name and simple calling syntax.

Treating every view reference as equally cheap because the syntax looks the same

Wrong

sql

SELECT * FROM customer_totals WHERE customer_id = 42;
-- looks like a cheap, indexed lookup -- is actually a full aggregation over ALL of orders every time

Better

sql

EXPLAIN SELECT * FROM customer_totals WHERE customer_id = 42;
-- check the real cost before assuming, especially in a hot code path

What you see: A code path calling what looks like a simple, filtered view turns out to be one of the most expensive queries in the system, discovered only during a performance investigation, because the view's name and calling syntax gave no visual signal of the aggregation happening underneath.

Why: SQL provides no syntactic distinction between querying a plain table and querying a view wrapping an expensive multi-join aggregation — both are written identically as SELECT ... FROM name. The only reliable way to know a view's true cost is to check EXPLAIN directly, which is why views hiding genuinely expensive logic deserve extra scrutiny (or a materialized view instead) before being used casually in a hot, frequently-executed code path.

A good view use vs a risky one

A good view use vs a risky one
Use	Verdict
active_customers filtering deleted_at IS NULL	good — cheap, correctness-critical, reused everywhere
customer_lifetime_value with 4 joins and aggregation, queried per-request	risky — expensive, disguised as a plain table

Together

sql

EXPLAIN SELECT * FROM customer_lifetime_value WHERE customer_id = 42;
-- reveals the true cost hidden behind the view's simple name

Remember: A view's name and calling syntax give no visual signal of its cost — a plain-looking SELECT * FROM some_view can hide a full aggregation. Check EXPLAIN before trusting a view is cheap, especially in a hot code path.

See also: materialized views · standard views

Permissions Around Views and Ownership

standardintermediate

A view runs with the privileges of its owner, not the querying user's privileges — this means a user can be granted access to a view without being granted access to the underlying tables it reads from. It is the standard way to expose a restricted slice of data without opening up the whole table.

Think of it as

Normally, querying a table requires privileges on that table directly. A view breaks that direct link: PostgreSQL checks whether the querying user has privileges on the VIEW, and the view itself (running as its owner) is what actually reads the underlying tables. This is exactly how a view can safely expose "only the non-sensitive columns" or "only this user's own rows" to someone who has no direct access to the full table at all.

sql

CREATE VIEW public_customer_info AS
SELECT id, name FROM customers;   -- omits sensitive columns like ssn, credit_card

GRANT SELECT ON public_customer_info TO reporting_role;
REVOKE ALL ON customers FROM reporting_role;   -- reporting_role has no direct table access

What we're doing: Grant a role access to a view exposing only non-sensitive columns, while explicitly revoking that role's direct access to the underlying table.

view_permissions_demo.sqlsql

CREATE TABLE customers (id SERIAL PRIMARY KEY, name TEXT, ssn TEXT);
INSERT INTO customers (name, ssn) VALUES ('Ada', '123-45-6789');

CREATE VIEW public_customer_info AS SELECT id, name FROM customers;

CREATE ROLE reporting_role;
GRANT SELECT ON public_customer_info TO reporting_role;
REVOKE ALL ON customers FROM reporting_role;

SET ROLE reporting_role;
SELECT * FROM public_customer_info;   -- succeeds
SELECT * FROM customers;              -- fails: no privilege

4: The view deliberately omits ssn — a real access-control boundary, not just a display convenience.
6–7: reporting_role gets access to the view only, and is explicitly denied direct table access.
9–10: The view query succeeds because reporting_role has SELECT on the view — the view itself (running as its owner) is what actually reads customers.

Output

id | name
---+------
 1 | Ada
(1 row)

ERROR:  permission denied for table customers

Why this works: PostgreSQL checks the querying role's privileges against public_customer_info, which reporting_role has — it does not additionally require reporting_role to have privileges on customers, because the view executes its underlying query as its owner, not as the calling role. Attempting to query customers directly hits the ordinary privilege check with no view involved, and reporting_role genuinely has no grant there, which is exactly the boundary the view was set up to enforce: exposing name without ever exposing ssn or direct table access.

Assuming a view's access control is equivalent to filtering columns in application code

Wrong

sql

-- granting reporting_role full SELECT on customers, "trusting" application code to only ever query id, name
GRANT SELECT ON customers TO reporting_role;

Better

sql

CREATE VIEW public_customer_info AS SELECT id, name FROM customers;
GRANT SELECT ON public_customer_info TO reporting_role;
-- ssn is structurally unreachable by reporting_role, not just conventionally avoided

What you see: A role intended to only ever see non-sensitive columns can, in fact, run SELECT ssn FROM customers directly and get it — the restriction existed only in application code's query-writing habits, not as an actual database-enforced boundary.

Why: Granting SELECT directly on the full table gives that role access to every column, regardless of which columns any particular application query happens to request — the restriction to "only id, name" was never actually enforced by PostgreSQL, only assumed by whichever code was writing the queries. A view granting access to only a column subset makes the restriction structural: no query issued as reporting_role, through any tool, can reach ssn, because reporting_role has no privilege on the table that contains it.

Who needs what privilege

Who needs what privilege
Actor	Needs privilege on
The view itself, to read the underlying tables	the underlying tables (as the view's owner)
A querying user, to use the view	only the view — not the underlying tables

Together

sql

CREATE VIEW public_customer_info AS SELECT id, name FROM customers;
GRANT SELECT ON public_customer_info TO reporting_role;
-- reporting_role can query the view without any privilege on customers directly

Remember: A view executes with its owner's privileges to read underlying tables — granting SELECT on a view (without granting the underlying tables) is a structural way to expose a restricted slice of data, not just a display convenience.

Operating materialized views

Refreshing without taking production reads offline.

Materialized-View Refresh Considerations for Production

standardadvanced

A plain REFRESH MATERIALIZED VIEW locks the view against reads for the entire refresh duration — any query trying to read it blocks until the refresh finishes. REFRESH MATERIALIZED VIEW CONCURRENTLY avoids that lock, letting reads continue against the old data during the refresh, but it requires a unique index on the view and is generally slower.

Think of it as

A plain refresh is conceptually "swap out the whole table" — fast to execute, but the view is fully locked and unreadable for the duration. CONCURRENTLY is conceptually "compute the new result separately, then merge in the differences" — readable throughout via the old data, but the merge computation itself is more work, and it needs a unique index to know how to match old rows to new ones. The choice is a direct trade between refresh speed and read availability during that refresh.

sql

CREATE MATERIALIZED VIEW customer_totals AS
SELECT customer_id, sum(total) AS lifetime_total FROM orders GROUP BY customer_id;

CREATE UNIQUE INDEX customer_totals_pk ON customer_totals (customer_id);

REFRESH MATERIALIZED VIEW CONCURRENTLY customer_totals;

What we're doing: Attempt a CONCURRENTLY refresh without the required unique index, then add it and succeed.

concurrent_refresh_demo.sqlsql

CREATE TABLE orders (id SERIAL PRIMARY KEY, customer_id INT, total NUMERIC);
INSERT INTO orders (customer_id, total) VALUES (1, 100);

CREATE MATERIALIZED VIEW customer_totals AS
SELECT customer_id, sum(total) AS lifetime_total FROM orders GROUP BY customer_id;

INSERT INTO orders (customer_id, total) VALUES (1, 50);

REFRESH MATERIALIZED VIEW CONCURRENTLY customer_totals;
-- ERROR: no unique index on customer_totals

CREATE UNIQUE INDEX customer_totals_pk ON customer_totals (customer_id);
REFRESH MATERIALIZED VIEW CONCURRENTLY customer_totals;
-- succeeds -- reads against customer_totals were never blocked, even during this refresh

8: No unique index exists yet on customer_totals — CONCURRENTLY needs one to identify which rows changed between old and new.
11: A unique index on customer_id gives PostgreSQL a way to match old rows to new ones for the concurrent diff.
12–13: The refresh now succeeds without ever taking the full lock a plain REFRESH would require.

Output

ERROR:  cannot refresh materialized view "customer_totals" concurrently
HINT:  Create a unique index with no WHERE clause on one or more columns of the materialized view.

REFRESH MATERIALIZED VIEW

Why this works: CONCURRENTLY works by computing the new result into a temporary location, then comparing it row-by-row against the existing materialized data to apply only the changes — that row-matching step requires a way to uniquely identify "this is the same logical row before and after," which is exactly what a unique index provides. Without one, PostgreSQL has no reliable way to perform that comparison, so it refuses the concurrent refresh outright rather than risk an incorrect merge.

Using a plain REFRESH on a materialized view queried by live production traffic

Wrong

sql

REFRESH MATERIALIZED VIEW customer_totals;
-- every query against customer_totals blocks for the full duration of this refresh

Better

sql

CREATE UNIQUE INDEX customer_totals_pk ON customer_totals (customer_id);
REFRESH MATERIALIZED VIEW CONCURRENTLY customer_totals;
-- production reads continue uninterrupted throughout

What you see: A scheduled refresh job causes a brief but real outage — every query hitting the materialized view during the refresh window hangs until it completes, which can be seconds or minutes depending on the underlying query's cost.

Why: A plain REFRESH takes an ACCESS EXCLUSIVE lock specifically because it replaces the view's entire contents in one operation, and PostgreSQL cannot let a read see a half-replaced result — so it blocks every reader until the replacement finishes. For any materialized view actually serving live read traffic, this is a real availability cost, which is exactly the trade-off CONCURRENTLY exists to avoid, at the price of requiring a unique index and generally taking longer to run.

Plain refresh vs CONCURRENTLY

Plain refresh vs CONCURRENTLY
Property	REFRESH MATERIALIZED VIEW	REFRESH ... CONCURRENTLY
Locks reads during refresh?	yes — full lock	no — reads continue against old data
Requires a unique index?	no	yes
Typical speed	faster	slower — computes and applies a diff

Together

sql

CREATE UNIQUE INDEX customer_totals_pk ON customer_totals (customer_id);
REFRESH MATERIALIZED VIEW CONCURRENTLY customer_totals;

Remember: A plain REFRESH MATERIALIZED VIEW locks all reads for its full duration; REFRESH ... CONCURRENTLY avoids that lock but requires a unique index on the view and is typically slower — for a view serving live production traffic, CONCURRENTLY is usually worth that trade.