Views in PostgreSQL: When They Help and When They Hurt

A view is a saved query you can select from as if it were a table. That simple idea is genuinely useful for hiding complexity and centralizing logic, and it's also quietly responsible for a class of performance problems where a query that looks trivial expands into a monster at runtime. Views are worth using, but only once you understand that a view is a query, not a table, and that the database inlines it every time you touch it.

This is part 28 of the PostgreSQL Masterclass, following full-text search.

What a view actually is

A view stores a query definition, not data. When you select from a view, Postgres substitutes the view's query into yours and runs the combined thing. There's no stored result, no copy of the rows.

CREATE VIEW active_customers AS
SELECT id, name, email
FROM customers
WHERE deleted_at IS NULL;

-- selecting from the view runs the underlying query
SELECT * FROM active_customers WHERE email LIKE '%@acme.com';

That last query becomes "select from customers where deleted_at is null and email like ..." at execution. The view added no runtime cost here, it just saved you from repeating the deleted_at IS NULL filter. Used this way, views are a clean readability and consistency tool.

Where views genuinely help

Two solid uses:

Encapsulating a filter or join everyone repeats. If every query has to join through three tables to get a customer's current plan, or apply the same soft-delete filter, a view captures that once. Everyone selects from the view and gets the correct logic without copying it, and if the logic changes, you change it in one place.

A stable interface over a changing schema. A view can present a consistent set of columns even as the underlying tables get refactored. You rename or split a table, update the view to preserve its output, and the application code selecting from the view doesn't change. This is a real benefit for decoupling.

Where views quietly hurt

The trap is stacking views on views, and putting expensive logic inside a view that's then used in ways it wasn't designed for.

Because a view is inlined, selecting from a view that selects from another view that joins five tables means every query pays for all of it, even if you only wanted one column. The planner is good at pruning unused parts, but not perfect. A common failure is a view with an aggregate or DISTINCT inside it: those create an optimization barrier the planner often can't see through, so filtering the view from outside doesn't push the filter down, and Postgres computes the whole aggregate before applying your WHERE.

-- a view that aggregates everything
CREATE VIEW customer_totals AS
SELECT customer_id, sum(total) AS lifetime_value
FROM orders
GROUP BY customer_id;

-- this computes totals for ALL customers, then filters to one
SELECT * FROM customer_totals WHERE customer_id = 5;

That query looks like a single-customer lookup and can execute as a full aggregation over every order in the table. The fix is often to not wrap the aggregate in a view, or to add the filter as a parameter, which is what functions are for. When a query over a view is mysteriously slow, EXPLAIN the view usage, because the plan reveals the full expanded query the view generated.

Updatable views

A simple view (selecting from one table, no aggregate, no DISTINCT, no join) is automatically updatable: you can INSERT, UPDATE, and DELETE through it and Postgres applies the change to the underlying table. Add WITH CHECK OPTION to reject writes that would fall outside the view's filter:

CREATE VIEW us_customers AS
SELECT * FROM customers WHERE country = 'US'
WITH CHECK OPTION;   -- can't insert a non-US customer through this view

For complex views that aren't automatically updatable, you can define INSTEAD OF triggers to make writes work, though at that point you're usually better off writing to the base tables directly.

Views vs materialized views

The key limitation of a plain view is that it recomputes every time, because it stores no data. If the underlying query is expensive and the data doesn't change every second, recomputing it on every read is wasteful. That's exactly the gap a materialized view fills: it stores the computed result and refreshes on demand. When a view's query is heavy and you can tolerate slightly stale data, a materialized view trades freshness for speed. That's the entire next article.

The mental model to keep: a view is a named query that gets inlined, nothing more. Use views to centralize filters and joins and to present a stable interface, and they pay off. Stack them deep or bury aggregates in them, and you get queries whose real cost is hidden behind an innocent-looking name. When in doubt, EXPLAIN the query that uses the view and read what it actually expanded into.

Next, materialized views: how to store the result of an expensive query and keep it fresh enough to be useful.