Views

A view is a saved SQL query stored in the database under a name. You can query it exactly like a table. The database does not store the data separately; it stores the query definition. Every time you reference the view, the underlying query runs.

Views exist primarily for two reasons: they hide complexity behind a clean name, and they can enforce a consistent lens through which certain users see the data.

Creating a view

CREATE VIEW active_customers AS
SELECT id, name, email, created_at
FROM customers
WHERE status = 'active';

Once created, you query it like any table:

SELECT name, email
FROM active_customers
WHERE created_at > '2024-01-01';

The view adds its own WHERE status = 'active' condition automatically. The caller does not need to remember to include it. The filter is baked into the definition.

Why views are useful

Simplification. A join across five tables with a half-dozen conditions can be defined once as a view. Everyone who needs that data queries the view, not the raw tables. If the underlying query changes, you update the view definition once and nothing else changes.

Access control. You can grant a user SELECT access on a view while withholding access to the underlying tables. A user_summary view might show names and email addresses while excluding salary or other sensitive fields. This is a lightweight, built-in access control mechanism.

Consistency. When the same transformation appears in dozens of queries, a view ensures they all use the same logic. If the transformation is wrong, fixing the view fixes everything that uses it.

Views have no storage cost

A regular view stores only the query text. It does not duplicate data. The flip side is that every query against the view executes the underlying SQL in full. A view over a heavy aggregation across a billion-row table is no faster than running that aggregation yourself.

Replacing and dropping views

CREATE OR REPLACE VIEW active_customers AS
SELECT id, name, email, created_at, tier
FROM customers
WHERE status = 'active';

DROP VIEW active_customers;

CREATE OR REPLACE updates the definition without dropping and recreating. The new definition must return at least the same columns as the original.

Materialized views

A materialized view stores the query result as actual data, computed once and held on disk:

CREATE MATERIALIZED VIEW monthly_revenue AS
SELECT DATE_TRUNC('month', created_at) AS month, SUM(amount) AS revenue
FROM orders
GROUP BY DATE_TRUNC('month', created_at);

Querying monthly_revenue is fast because the aggregation was already done. The data does not update automatically. You refresh it manually:

REFRESH MATERIALIZED VIEW monthly_revenue;

-- Refresh without locking out readers (requires a unique index on the view)
REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_revenue;

	Regular view	Materialized view
Data stored?	No	Yes
Always current?	Yes	No (until refreshed)
Query performance	Depends on underlying query	Fast (precomputed)
Best for	Simplification, access control	Expensive recurring aggregations

Materialized views are a good fit for dashboard queries that run frequently but can tolerate data that is a few minutes or hours old. They are a poor fit for anything that must reflect the latest state.