TopolearnViews give complex joins a simple name, hide detail from consumers and let you evolve tables behind a stable interface.
A view is a stored query with a name. You query it like a table, and the database substitutes the definition each time:
CREATE VIEW customer_spend AS
SELECT c.id, c.name, COALESCE(SUM(o.total), 0) AS spent
FROM customers c LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name;A plain view stores no data and is always current. It is not inherently faster or slower than the query it contains, because the planner merges the view's definition into your query and optimises the whole thing. Views are useful for:
- giving a well-tested join or calculation one name instead of copy-pasting it,
- restricting access: grant
SELECTon a view exposing some columns, not on the table, - keeping an old interface working while the underlying tables change.
Simple views over a single table (no aggregates, DISTINCT or joins) are automatically updatable in PostgreSQL: INSERT, UPDATE and DELETE on the view go to the base table. Add WITH CHECK OPTION to stop writes that would produce rows the view cannot see.
A materialized view (PostgreSQL, Oracle; SQL Server has indexed views) runs the query once and stores the result. Reads are fast, but the data is a snapshot until you run REFRESH MATERIALIZED VIEW. A normal refresh locks out readers while it runs; REFRESH MATERIALIZED VIEW CONCURRENTLY keeps the old data readable, and requires a unique index on the materialized view. Use them for expensive aggregates where minutes-old data is acceptable, like dashboards.
CREATE OR REPLACE VIEW changes a view, but only by appending columns; to rename or remove columns, drop and recreate it.
Example
CREATE VIEW customer_spend AS
SELECT c.id, c.name, COALESCE(SUM(o.total), 0) AS spent
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id AND o.status <> 'cancelled'
GROUP BY c.id, c.name;
SELECT name, spent FROM customer_spend WHERE spent > 100 ORDER BY spent DESC;
-- Ana | 257.50
-- Ben | 247.99
CREATE MATERIALIZED VIEW monthly_revenue AS
SELECT date_trunc('month', ordered_at) AS month, SUM(total) AS revenue
FROM orders WHERE status <> 'cancelled'
GROUP BY 1;
CREATE UNIQUE INDEX ON monthly_revenue (month); -- needed for CONCURRENTLY
REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_revenue;Remember
- A view is a named query, expanded and optimised at query time
- Simple single-table views are updatable in PostgreSQL
- A materialized view stores results and must be refreshed
- REFRESH ... CONCURRENTLY needs a unique index but does not block readers
Watch out for
- Expecting a plain view to cache results and speed up an expensive query.