Views

Imagine you have a join across five tables that half your team queries every day. Without a view, everyone writes that join from scratch, copies it from someone else, and the copies slowly drift apart. A view solves this by giving the query a name. You define it once, store it in the database, and from then on everyone queries the view like a table. The underlying query still runs each time - the database stores the definition, not the data - but the complexity is hidden behind a clean name.

Creating a view

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

After that, query it like any other table:

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

Anyone querying active_customers automatically gets the WHERE status = 'active' filter. They do not need to know it exists. The condition is baked into the definition.

Why views are useful

Simplification. The join-across-five-tables case is the canonical one. Define it once, name it clearly, and every query that needs that data goes through one place. If the underlying logic changes, you fix the view and nothing else needs updating.

Access control. You can grant SELECT on a view while withholding access to the underlying tables entirely. A user_summary view might expose names and email addresses while keeping salary data invisible. No extra infrastructure required - just schema-level permissions.

Consistency. When the same transformation lives in dozens of queries, those copies will eventually diverge. A view keeps them in sync. If the logic is wrong, fixing the view fixes everything at once.

Views have no storage cost

A regular view stores only the query text. No data is duplicated. The flip side is that every query against the view runs the full underlying SQL fresh. A view over a heavy aggregation across a billion-row table gives you no speed advantage - you are hiding the complexity, not eliminating the work.

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 in place. One catch: the new definition must return at least the same columns as the original, in the same order. You can add columns to the end; you cannot remove or rename them without dropping and recreating.

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;

Materialized views are the right tool when a query is expensive, runs often, and the result does not need to be perfectly current. Think dashboard aggregations or weekly reporting rollups. For anything that must always reflect the latest data, stick to a regular view or a direct query.