Views and Materialized Views

Analogy categoryAnalogies

A standard view is a stored query definition with no data of its own -- every time you SELECT from it, PostgreSQL substitutes the view's defining query into your query and plans/executes the whole thing together, meaning a view is always as fresh as the underlying tables but pays the full cost of the underlying query on every access. This makes views excellent for encapsulating complex joins or business logic behind a simple, stable interface (letting you change the underlying schema without breaking every downstream query that used the view), but a poor fit for expensive aggregations that many callers query repeatedly.

🏏

Cricket analogy: A regular view is like a live scoreboard feed that recalculates the current run rate from scratch every time someone glances at it -- always accurate to the current ball, but demanding fresh computation on every single glance.

Materialized Views: Trading Freshness for Speed

A MATERIALIZED VIEW, in contrast, executes its defining query once and physically persists the result set to disk like a regular table, so subsequent SELECTs read the stored snapshot instead of recomputing anything -- ideal for expensive aggregations or reports that many users query but that don't need up-to-the-second accuracy, like a daily sales dashboard. The data goes stale the moment underlying tables change, so you must explicitly run REFRESH MATERIALIZED VIEW to bring it up to date, and by default that refresh takes an exclusive lock that blocks concurrent reads unless you create a unique index on the view and use REFRESH MATERIALIZED VIEW CONCURRENTLY instead.

🏏

Cricket analogy: A materialized view is like a printed match program handed out at the stadium gate -- it's fast to hand over and read, but it reflects the squad list as of print time, not live substitutions, so it needs a fresh print run (REFRESH) to stay accurate.

sql

-- A regular view: always fresh, recomputed on each access
CREATE VIEW active_customers AS
SELECT c.id, c.name, COUNT(o.id) AS order_count
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE o.created_at > now() - interval '90 days'
GROUP BY c.id, c.name;

-- A materialized view: computed once, persisted, refreshed on demand
CREATE MATERIALIZED VIEW daily_sales_summary AS
SELECT date_trunc('day', created_at) AS sales_day,
       SUM(total) AS revenue,
       COUNT(*) AS order_count
FROM orders
GROUP BY 1;

-- Required for CONCURRENTLY refresh to avoid blocking readers
CREATE UNIQUE INDEX ON daily_sales_summary (sales_day);
REFRESH MATERIALIZED VIEW CONCURRENTLY daily_sales_summary;

REFRESH MATERIALIZED VIEW CONCURRENTLY requires at least one unique index on the materialized view and takes roughly twice as long as a plain refresh because it computes a diff, but it lets existing readers continue querying the old snapshot uninterrupted while the refresh runs.

A plain REFRESH MATERIALIZED VIEW (without CONCURRENTLY) takes an ACCESS EXCLUSIVE lock, meaning every query against that view -- including simple SELECTs -- blocks until the refresh completes. On a large aggregation this can cause a visible outage for any dashboard reading from it.

Updatable Views and Security

A simple view over a single base table without aggregates, DISTINCT, or GROUP BY is automatically updatable, meaning INSERT/UPDATE/DELETE against the view passes through to the underlying table, which is useful for exposing a restricted column subset to a role without granting direct table access. Views can also carry the WITH CHECK OPTION clause to reject writes that would produce rows the view itself couldn't subsequently see (preventing a user from inserting a row through a filtered view that then disappears from their own results), and combined with row-level security policies, views are a common building block for fine-grained, role-based data access.

🏏

Cricket analogy: An updatable view with CHECK OPTION is like a groundskeeper who only manages the pitch area and is physically blocked from editing the boundary rope -- their edits pass through to the real ground, but only within their permitted zone.

A regular view stores only a query definition and is recomputed on every access, always reflecting current data.
A materialized view persists query results to disk, trading freshness for much faster repeated reads.
Materialized views require an explicit REFRESH MATERIALIZED VIEW to update; they go stale as underlying data changes.
REFRESH MATERIALIZED VIEW CONCURRENTLY avoids blocking readers but needs a unique index and takes longer.
A plain (non-concurrent) refresh takes an ACCESS EXCLUSIVE lock, blocking all reads until it completes.
Simple single-table views without aggregation are automatically updatable, passing writes through to the base table.
WITH CHECK OPTION prevents writes through a view that would create rows the view itself cannot see.