Views and Materialized Views

A view is a stored query. Postgres does not run it and keep the answer; it substitutes the definition into whatever query referenced it and plans the two together as one statement. That makes a view free at rest and free at run time, and it makes it the cheapest available cure for five teams maintaining five slightly different definitions of the same business concept.

Cartwheel has that disease in full. "Yesterday's revenue" is computed in the dashboard, in the finance export and in a weekly email, and the three disagree: one counts cancelled orders, one counts them until they are refunded, and one filters on status = 'delivered' and therefore misses everything still out with a courier. Chapter 1 promised an analytics schema holding the objects the dashboard reads. This topic builds it, starting with a plain view and moving to a materialized one only where the cost justifies the staleness.

A View Is a Rewrite, Not a Result

When a query names a view, the rewriter replaces the reference with the view's own query tree before planning starts. The planner then sees one query and optimizes across the seam, so a predicate written outside the view can end up executed inside it, against an index, on the base table.

CREATE VIEW analytics.completed_orders AS
  SELECT o.id, o.customer_id, c.city, o.placed_at, o.total
    FROM orders o
    JOIN customers c ON c.id = o.customer_id
   WHERE o.status IN ('dispatched', 'delivered');

SELECT sum(total) FROM analytics.completed_orders
 WHERE placed_at >= '2026-08-12' AND placed_at < '2026-08-13';

The date range in the outer query lands on orders.placed_at inside the view's own scan, so the index on that column is used and the join runs over one day rather than over 40 million rows. That pushdown is the whole reason a well-written view costs nothing. It also has a boundary: a predicate can be pushed below a GROUP BY only when it refers to a grouping column. Put an aggregate in the view and filter on the aggregate from outside, and the entire aggregation runs first, over every row in orders.

Updatable Views

A view is automatically updatable when the rewriter can turn a write against it back into a write against one table. The conditions are precise: exactly one entry in FROM, which must be a table or another updatable view; no WITH, DISTINCT, GROUP BY, HAVING, LIMIT or OFFSET at the top level; no UNION, INTERSECT or EXCEPT; and no aggregates, window functions or set-returning functions in the select list. A column is writable only if it is a plain reference to a writable column underneath.

CREATE VIEW public.open_orders AS
  SELECT id, customer_id, status, total
    FROM orders
   WHERE status IN ('pending', 'picking')
  WITH CHECK OPTION;

WITH CHECK OPTION is what stops the view being a one-way mirror: without it, an update through the view could set a row's status to delivered and push the row straight out of the view's own definition, which is rarely what anyone intended. Anything past those conditions — a join, an aggregate, a union — needs an INSTEAD OF trigger, and at that point the write path is code you maintain. The property the next topic's migration leans on is the other one: a view is a stable name the application holds on to while the tables behind it are being restructured.

Privileges: security_invoker and security_barrier

By default a view accesses its base tables with the privileges of the view's owner, not the caller's. That is a feature: it is how cartwheel_analytics can be granted a city-level aggregate over customers while having no rights on customers at all. It is also a hole when the elevation was not intended, because a role that can select from the view reads rows it was never granted.

CREATE VIEW analytics.city_totals
  WITH (security_invoker = true, security_barrier = true) AS
  SELECT c.city, count(*) AS orders, sum(o.total) AS revenue
    FROM orders o JOIN customers c ON c.id = o.customer_id
   GROUP BY c.city;

security_invoker, available since 15, inverts the default: the base relations are checked against the privileges of the user running the query, so the caller needs rights on the view and on everything under it. security_barrier answers a different question. Without it, a cheap function in the caller's own WHERE clause can be evaluated before the view's filter and see rows the view exists to hide, typically by raising an error that prints them. With it, the view's own conditions and any operators marked leakproof are evaluated first — Chapter 14 pairs both properties with row-level security. Neither is on by default.

Materialized Views, and the Cost of Refresh

A materialized view is a table that remembers a query's result. It occupies disk, it can be indexed and vacuumed like any table, and it is stale from the instant it is written. Cartwheel's dashboard reads one over the full orders history, which is the case that justifies the tradeoff: the query is expensive, the answer is reused by every viewer, and yesterday's number does not change.

CREATE MATERIALIZED VIEW analytics.daily_orders AS
  SELECT date_trunc('day', o.placed_at) AS day,
         c.city,
         count(*)      AS orders,
         sum(o.total)  AS revenue
    FROM orders o JOIN customers c ON c.id = o.customer_id
   GROUP BY 1, 2;

CREATE UNIQUE INDEX daily_orders_day_city
    ON analytics.daily_orders (day, city);

REFRESH MATERIALIZED VIEW CONCURRENTLY analytics.daily_orders;

The unique index is a precondition rather than an optimization. REFRESH … CONCURRENTLY is permitted only when the view already has a unique index built from plain column names covering every row — no expression index, no WHERE clause — and only when the view has already been populated once. Get it wrong and the fallback is a plain REFRESH, which takes an ACCESS EXCLUSIVE lock and blocks every reader for the length of the rebuild. The concurrent form takes an EXCLUSIVE lock instead, which still lets ordinary reads through, and it buys that by doing more work: it computes the entire new result and then applies the difference. When a refresh changes a lot of rows, the plain form uses fewer resources and finishes sooner. Only one refresh may run against a given view at a time.

Nothing Refreshes Itself

Postgres has no automatic refresh, no "fast refresh on commit", and no incremental maintenance of a materialized view. Something outside has to run the statement: pg_cron inside the database, a systemd timer, the application's own scheduler. Cartwheel refreshes analytics.daily_orders every ten minutes on pg-primary, and the result reaches the dashboard through pg-replica-a like any other change. The dashboard prints the refresh timestamp next to the number.

Printing it is the part teams skip. The number on the screen is now up to ten minutes old from the refresh, plus up to 30 seconds of replica lag on top — two independent staleness budgets that add. A figure with a timestamp beside it is a cache; the same figure without one is a claim about the present that the system cannot support. Watch the refresh duration as the data grows, too: once a refresh takes longer than the gap between refreshes, they queue, and the number on the label stops being the interval.

When It Is the Wrong Tool

A materialized view that has to be current is not a cache at all: every write implies a rebuild that reads the whole source, so the write path gets slower as the source grows. If the requirement is "always correct within a second", the answer is not a shorter refresh interval.

What is left divides two ways. A rollup that must stay fresh at Cartwheel's write rate wants a summary table maintained incrementally, either by a trigger on the source table or by a scheduled INSERT … ON CONFLICT DO UPDATE over the rows that changed since the last run — more code, and an argument about correctness you have to make yourself, in exchange for freshness the snapshot cannot give. A result that is expensive for one request and cheap for the next wants an index, not a snapshot of an answer. Oracle's incrementally-refreshable materialized views are the feature Postgres genuinely lacks here; in the Postgres ecosystem, pg_ivm and TimescaleDB's continuous aggregates are the extensions that fill the gap.