What's the difference between a view and a table, and when do views help or hurt performance?
Quick Answer
A table stores actual data; a view is a saved, named `SELECT` statement with no storage of its own — querying a view is equivalent to running its underlying query, substituted inline wherever the view is referenced. Views help readability, security (restricting which columns/rows a user can see), and centralizing business logic, but provide no performance benefit by themselves — a view built on an expensive, unindexed join is exactly as slow as running that join directly, every time.
Detailed Answer
A view is not a cached result
CREATE VIEW expensive_report AS
SELECT c.name, SUM(o.total) AS total_spent, COUNT(*) AS order_count
FROM customers c
JOIN orders o ON o.customer_id = c.id
JOIN order_items oi ON oi.order_id = o.id
GROUP BY c.name;
SELECT * FROM expensive_report is functionally identical to pasting the entire SELECT ... GROUP BY inline — the optimizer typically inlines the view's definition into the outer query and optimizes the whole thing together (this is sometimes literally called "view merging"). There's no stored data, no caching — every read pays the full cost of the underlying joins and aggregation. This is the single most common misconception about views: creating one does not make a slow query fast.
Where views genuinely help
- Readability/abstraction — hiding a complex join behind a simple name that application code (or analysts) can query without understanding the full underlying schema.
- Security — granting access to a view that exposes only certain columns/rows, without granting direct table access:
CREATE VIEW public_employee_directory AS
SELECT name, department, work_email FROM employees; -- omits salary, ssn, home_address
GRANT SELECT ON public_employee_directory TO reporting_role;
- Centralizing business logic — if "active customer" has a specific, non-obvious definition, defining it once in a view avoids every query re-implementing (and potentially getting slightly wrong) the same filter logic.
- Backward compatibility during schema migrations — a view can present an old column/table shape backed by a newer underlying schema, buying time to migrate consumers.
Where views can hurt performance
- Nested views (a view built on top of another view, built on top of another view) can produce surprisingly complex, hard-to-optimize query plans, since each layer adds more joins/subqueries for the optimizer to reason about — sometimes defeating predicate pushdown that would otherwise let the optimizer filter early.
- A view gives a false sense that "the hard part is already solved," leading developers to add further filters/joins on top of an already-expensive view without realizing the full cost still applies underneath.
The real performance tool: materialized views
If the goal is genuinely to avoid re-computing an expensive query on every read, a materialized view (see that question) — not a regular view — is the correct tool, since it actually stores the result physically.