beginnerintermediateadvanced

PostgreSQL · Views

Views

A stored query vs a stored result, when a view's abstraction hides a real cost, view-based access control, and refreshing a materialized view without locking out readers

A view stores no dataalways current, re-runs its query every read
A materialized view doesfast reads, stale until REFRESH
Same syntax, different costEXPLAIN reveals what the name hides
Plain REFRESH locks readsCONCURRENTLY needs a unique index

1.Standard Views and Their Benefits/Limitations

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.

Key features

A view stores only its defining query, not any data — every SELECT against it re-runs that query
A view always reflects the current state of its underlying tables, with no staleness
A simple view (one table, no aggregation) can often be inserted/updated/deleted through directly, subject to PostgreSQL's updatable-view rules
A view built on an expensive query (heavy joins, aggregation) pays that full cost on every read, since nothing is cached
A view can hide implementation complexity behind a simple name, but can also hide that complexity from someone estimating a query's cost

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

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

Command

sql

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

SELECT * FROM active_customers WHERE name = 'Ada';

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.

2.Materialized Views and REFRESH MATERIALIZED VIEW

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.

Key features

CREATE MATERIALIZED VIEW ... AS SELECT ... physically stores the result at creation time
REFRESH MATERIALIZED VIEW re-executes the query and replaces the stored data — the view is stale until this runs
By default, REFRESH MATERIALIZED VIEW locks the view against reads for its duration; WITH (CONCURRENTLY) avoids this but requires a unique index on the view
A materialized view can be indexed like a real table, since it genuinely stores rows
Nothing refreshes a materialized view automatically — it must be triggered explicitly, by a cron job, application logic, or a manual command

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

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

Command

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;

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.

3.When a View Improves Abstraction vs Hides Expensive Queries

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.

Key features

A view hiding a simple filter or join is a genuine readability win with negligible hidden cost
A view hiding heavy aggregation, multiple joins, or a subquery is a performance risk disguised as a simple table reference
Composing views on top of views multiplies the disguise — the true cost of the final query is not visible from its SQL at all
EXPLAIN on a query using a view reveals its actual underlying cost, regardless of how simple the view's name makes it look
A useful heuristic: reach for a view for clarity/reuse of correctness-critical logic (a security filter, a business rule); be cautious using one purely to hide complexity

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

Command

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;

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.

4.Permissions Around Views and Ownership

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.

Key features

A view executes with its owner's privileges to read the underlying tables, not the querying user's privileges
GRANT SELECT ON view_name TO some_role gives access to the view without granting access to the tables it reads
This makes views a practical way to expose a restricted column subset, or a row-filtered subset, without broader table access
If the view owner's own privileges on the underlying table are later revoked, the view breaks for everyone, not just the owner
Row-Level Security (a later section) provides an alternative, more granular mechanism for row-level restriction that complements this view-based pattern

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

Command

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

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.

5.Materialized-View Refresh Considerations for Production

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.

Key features

A plain REFRESH MATERIALIZED VIEW takes an ACCESS EXCLUSIVE lock — no reads are possible against the view until it completes
REFRESH MATERIALIZED VIEW CONCURRENTLY allows concurrent reads (against the pre-refresh data) throughout the refresh
CONCURRENTLY requires at least one UNIQUE index on the materialized view — without one, PostgreSQL rejects the concurrent refresh
CONCURRENTLY is typically slower than a plain refresh, since it computes and applies only the differences rather than replacing everything at once
For a materialized view queried by live production traffic, CONCURRENTLY is usually the right choice despite being slower, to avoid a read outage during refresh

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

Command

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;

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.

Spotted a mistake on this page? Tell us.

Read Sheet