Views and materialized views

Article.

The two share a keyword and almost nothing else. A view is a stored query that runs every time you touch it. A materialized view is a stored result that is wrong until you refresh it. Treating the second as if it were the first is how a dashboard quietly shows yesterday’s numbers, and how a refresh takes production offline for four minutes.

A view is a macro, not a table

CREATE VIEW active_subscription AS
SELECT s.id, s.customer_id, s.plan_id, s.renews_at
FROM subscription s
WHERE s.cancelled_at IS NULL
  AND s.renews_at > now();

Nothing is stored. Selecting from active_subscription substitutes the definition into your query and plans the result as one statement, so a filter you add outside pushes down inside:

SELECT * FROM active_subscription WHERE customer_id = 42;

That plans as a single query against subscription with both predicates, and uses an index on customer_id if one exists. A view costs nothing at rest and adds no indirection the planner cannot see through.

The value is interface. The definition of “active” lives in one place, so when someone decides that a subscription in its grace period still counts, that is one edit rather than fourteen. And the view is a narrower surface than the table: grant SELECT on it and a reporting role never sees the columns you left out.

Where the transparency stops

Two things break the “it is just a macro” model, and both surprise people.

Aggregation blocks pushdown. A view containing GROUP BY, DISTINCT, a window function or a LIMIT cannot have arbitrary outer predicates pushed inside — the grouped result must be computed before it can be filtered. A view that aggregates the whole table, filtered afterwards by the caller, aggregates the whole table every time.

CREATE VIEW revenue_by_day AS
SELECT date_trunc('day', paid_at) AS d, customer_id, sum(amount) AS revenue
FROM invoice GROUP BY 1, 2;

-- Aggregates the entire invoice table, then keeps one customer's rows.
SELECT * FROM revenue_by_day WHERE customer_id = 42;

Postgres can push a predicate on a grouping column into the underlying scan — customer_id is in the GROUP BY, so in this case it often does. Push a predicate on revenue, which only exists after aggregation, and it cannot. The rule to carry: predicates on grouped columns may push down; predicates on aggregates never can.

Nesting compounds. A view over a view over a view produces one enormous flattened query, and the planner’s join-order search is exponential in the number of relations. Past roughly a dozen tables it stops searching exhaustively (geqo_threshold) and starts using a genetic algorithm — which returns a plan that is usually reasonable and is not stable across runs. Three layers of convenience views can put you there without anyone writing a big query.

Writing through a view

A view is not necessarily read-only. Postgres makes a simple view automatically updatable — one base table, no aggregates, no DISTINCT, no GROUP BY, no window functions, no set operations. INSERT, UPDATE and DELETE pass straight through to the table.

Which produces a trap:

-- Inserts a row that the view itself cannot see.
INSERT INTO active_subscription (customer_id, plan_id, renews_at)
VALUES (42, 7, now() - interval '1 day');

The row goes into subscription, fails the view’s renews_at > now() predicate, and vanishes from the caller’s perspective. No error. WITH CHECK OPTION closes it:

CREATE VIEW active_subscription AS
SELECT ... FROM subscription
WHERE cancelled_at IS NULL AND renews_at > now()
WITH CHECK OPTION;              -- reject writes the view could not read back

That turns a silent disappearance into a constraint violation, and it is what makes a view usable as a security boundary: a per-tenant view with WITH CHECK OPTION cannot be used to write a row belonging to another tenant. For anything more complex than a simple view, an INSTEAD OF trigger is the mechanism — you write the insert logic yourself.

A materialized view is a cache

CREATE MATERIALIZED VIEW revenue_daily AS
SELECT date_trunc('day', paid_at) AS d, sum(amount) AS revenue, count(*) AS n
FROM invoice
WHERE status = 'paid'
GROUP BY 1;

This one has rows. It is a table populated by a query, and — the part people skip — it never updates itself. There is no trigger, no incremental maintenance, no invalidation. It holds whatever the query returned at creation or last refresh, indefinitely.

Because it is a real relation, you can index it, and usually should:

CREATE INDEX ON revenue_daily (d);

The refresh that takes production down

REFRESH MATERIALIZED VIEW revenue_daily;

That statement takes an ACCESS EXCLUSIVE lock for its whole duration. Not a write lock — the strongest lock Postgres has. Every SELECT against the view blocks until it finishes.

So the dashboard query that reads revenue_daily hangs for as long as the aggregate takes to recompute. On a large table that is minutes, during which the view appears to be down. It is a self-inflicted outage on a schedule, and it usually ships because it was fast on a development dataset.

The fix:

REFRESH MATERIALIZED VIEW CONCURRENTLY revenue_daily;

CONCURRENTLY builds the new contents alongside and swaps them, so readers are never blocked. Two conditions, both of which catch people:

• It requires a UNIQUE index on the view. Without one you get an error, and the temptation is to drop CONCURRENTLY rather than add the index.
• It is slower and does more I/O than a plain refresh, because it computes the new result and then diffs it against the old.

CREATE UNIQUE INDEX ON revenue_daily (d);   -- now CONCURRENTLY works

CONCURRENTLY also cannot populate an empty materialized view — the first refresh after creation with WITH NO DATA has to be a plain one.

Staleness is a product decision

The question nobody asks until a number is wrong: how stale may this be?

Whatever you choose, show it. A dashboard reading a materialized view should display when it was last refreshed, because a stale number with no timestamp is indistinguishable from a live one and is trusted the same way.

-- Track it yourself; Postgres does not record refresh time.
CREATE TABLE mv_refresh (view_name text PRIMARY KEY, refreshed_at timestamptz);

There is no built-in “last refreshed” for a materialized view. If you want that timestamp — and you do — record it in the same transaction as the refresh.

Choosing

The decision is not “which is faster”. It is how fresh must this be, and how expensive is the query.

• Cheap query, must be current → view. This is most cases.
• Expensive query, staleness acceptable → materialized view, refreshed CONCURRENTLY on a schedule that matches how often the data changes.
• Expensive query, must be current → neither. You need a summary table maintained incrementally, by trigger or by the application, so the cost is paid per write rather than per read. See Triggers in practice.

That third case is the one people force into a materialized view and then refresh every minute, paying full recomputation cost continuously to approximate something that was never going to be live.

The trade not taken: an ORM-shaped alternative is to keep the logic in the application and cache the result in Redis. That is often right — it is easier to reason about, easier to invalidate precisely, and does not put load on the database. Choose the materialized view when the computation genuinely belongs next to the data and the result is large enough that shipping it to a cache is itself the expensive part.

Related

• Functions and procedures - the other server-side objects
• Reading a query plan - confirming a predicate pushed into a view
• What is Redis - the alternative cache