Creating a view
CREATE VIEW monthly_revenue AS
SELECT DATE_TRUNC('month', order_date) AS mth,
region,
SUM(total) AS revenue
FROM orders
GROUP BY 1, 2;
SELECT * FROM monthly_revenue WHERE region = 'East';A view stores the query, not the data. Each time you select from it, the underlying query runs.
What views are good for
- Hiding join complexity behind a simple name
- Presenting one agreed definition of a business metric, so two teams cannot compute revenue differently
- Restricting access — grant on the view rather than the base table, exposing only certain columns or rows
Where they hurt
Views nested on views become very hard to reason about, and the optimiser can struggle once several layers deep. A view that looks cheap may be running four joins underneath.
If you find yourself selecting from a view built on a view built on a view, flatten it.
Materialised views
CREATE MATERIALIZED VIEW monthly_revenue_mv AS SELECT ...;
REFRESH MATERIALIZED VIEW monthly_revenue_mv;A materialised view stores the result, so reads are fast, at the cost of being stale until refreshed. Supported in PostgreSQL and Oracle; SQL Server's equivalent is an indexed view.
The trade-off is the familiar one: a regular view is always current but recomputes; a materialised view is fast but needs refreshing.