Views and Materialized Views

Encapsulate queries as reusable virtual tables.

Views

CREATE VIEW active_users AS
SELECT id, name, email
FROM users
WHERE active;

-- Use like a table
SELECT * FROM active_users ORDER BY name;

Views are stored query definitions; selecting from a view runs the underlying query.

Updatable views

Some simple views are updatable (INSERT/UPDATE/DELETE propagate to base tables). Engines vary; complex constructs (aggregates, DISTINCT) usually block updates.

WITH CHECK OPTION

Ensure updates through a view keep rows within the view’s predicate.

CREATE VIEW us_customers AS
SELECT * FROM customers WHERE country = 'US'
WITH CHECK OPTION;

Materialized views (precomputed)

-- Postgres
CREATE MATERIALIZED VIEW top_customers AS
SELECT customer_id, SUM(total) AS spend
FROM orders
GROUP BY customer_id;

-- Query uses stored results
SELECT * FROM top_customers ORDER BY spend DESC LIMIT 10;

-- Refresh to update contents
REFRESH MATERIALIZED VIEW top_customers;

Materialized views trade freshness for speed. Consider concurrent refresh (engine-specific) to reduce blocking.

Incremental strategies

Partition base tables and refresh partitions
Use change tables or triggers to maintain a summary table
Schedule periodic REFRESH during low-traffic windows

Security and abstraction

Grant access on views to expose a curated schema without revealing base tables.

Summary

Views encapsulate logic; materialized views store results for speed
Use WITH CHECK OPTION to enforce invariants; plan refresh for freshness