7.2.6 — Views and Materialized Views

Five different reports all need "orders that count as revenue": paid, not refunded, not from a test account, with the tax split out. The definition is fourteen lines of SQL, and it is currently copy-pasted into five places. Last month the finance team added a rule about partial refunds, and three of the five got updated.

A view fixes exactly this. A view is a stored query with a name. You define the fourteen lines once, and every report selects from revenue_orders as though it were a table. When the rule changes, it changes in one place.

CREATE VIEW revenue_orders AS
SELECT
  o.id,
  o.customer_id,
  o.placed_at,
  o.total_minor - COALESCE(r.refunded_minor, 0) AS net_minor    -- (1)
FROM orders o
LEFT JOIN refunds r ON r.order_id = o.id
WHERE o.status = 'paid'
  AND o.is_test = false;

-- Every report now says this and nothing more:
SELECT date_trunc('month', placed_at) AS month, SUM(net_minor)
FROM revenue_orders
GROUP BY 1;

(1) The partial-refund rule lives here, and only here. COALESCE turns the null from the LEFT JOIN — orders with no refund row — into zero, because subtracting null would make the whole expression null, which is the trap from Chapter 7.1 section 3.

1. A view stores no data. It stores the question.

This is the single fact that everything else on the page follows from, so it is worth being blunt about it. CREATE VIEW creates nothing on disk except the text of the query. There are no rows in a view. Selecting from a view runs the underlying query, right then, against the current contents of the real tables.

The mechanism inside PostgreSQL is worth seeing once because it explains the performance behaviour later. A view is stored as a table with no rows attached to a rewrite rule named _RETURN, and before your query is planned the rewriter substitutes the view's definition in place of the view's name. By the time the planner sees your query, the word revenue_orders has been replaced by the join and the WHERE clause. The planner then optimises the whole thing as one query — it does not run the view first and then filter afterwards.

That last sentence is the answer to "are views slow?". No, and here is the proof you can run:

EXPLAIN SELECT * FROM revenue_orders WHERE customer_id = 42;

Nested Loop Left Join  (cost=0.86..48.31 rows=6 width=28)
  ->  Index Scan using orders_customer_idx on orders o
        Index Cond: (customer_id = 42)                      ← pushed inside the view
        Filter: ((NOT is_test) AND (status = 'paid'::text))
  ->  Index Scan using refunds_order_idx on refunds r

Your WHERE customer_id = 42 ended up inside the view's own index scan. That is predicate pushdown: the filter you wrote outside was pushed down to the leaf, so the database never built the full revenue set and then discarded it. This is the same algebraic freedom from Chapter 7.1 section 7 — filter-then-join equals join-then-filter — and it is why a well-shaped view costs nothing at all compared to writing the query out by hand.

When pushdown stops working, which is the useful half of the knowledge:

• A filter on an aggregate cannot be pushed below the aggregate. If the view ends in GROUP BY customer_id and you filter WHERE order_count > 5, the grouping has to happen before that test can be evaluated, so the whole group-by runs. A filter on the grouping column can still be pushed down, and usually is.
• DISTINCT, UNION and window functions block it for the same reason: the result of the row depends on other rows, so removing rows early would change the answer.
• A LIMIT inside the view blocks it absolutely. SELECT * FROM (top 100 orders) WHERE customer_id = 42 has to take the top 100 first — that is what you asked for — and then filter.
• A volatile function inside the view blocks it. If the view calls something the engine cannot promise is repeatable, it will not risk moving work around it.

So the practical rule is: a view that is a filter, a projection and joins is free. A view that aggregates is as expensive as the aggregate, every time you select from it. That second case is what materialized views exist for, and section 5 gets there.

2. The three jobs a view actually does

Job one: name a definition once. The opening example. This is not a small thing — the copy-pasted fourteen lines are how a company ends up with two numbers for revenue and a meeting about which is right.

Job two: hide columns and rows from people who should not see them. This is the security use, and it is the one that most justifies views on their own.

CREATE VIEW support_orders AS
SELECT id, customer_id, placed_at, status, total_minor      -- (1)
FROM orders;                                                 -- (2) no card details, no internal notes

REVOKE ALL ON orders FROM support_role;                      -- (3)
GRANT SELECT ON support_orders TO support_role;              -- (4)

(1) Only the columns support staff need. The card token and internal risk score are simply not in the list, so there is no query they can write that reaches them. (2) No WHERE here, so all rows are visible — the next example restricts rows too. (3) The support role loses all access to the base table. This line is the one people forget, and without it the view is a convenience rather than a control. (4) They get the view instead.

Why this works at all is worth stating, because it looks like it should not. The support role cannot read orders, yet selecting from a view that reads orders succeeds. A view runs with the privileges of the user who owns the view, not the user running the query. So the permission check happens against the owner, who does have access. This is intentional and is the entire mechanism behind view-based security.

PostgreSQL 15 added the opposite behaviour for cases where you want the caller's own permissions to apply:

CREATE VIEW support_orders WITH (security_invoker = true) AS SELECT …;

Now the query runs as the caller, so the view is a convenience only. Use it when the view exists for readability and you want the underlying permissions to keep applying.

Restricting rows, not just columns, is the multi-tenant shape:

CREATE VIEW my_orders WITH (security_barrier = true) AS
SELECT * FROM orders WHERE tenant_id = current_setting('app.tenant_id')::bigint;

security_barrier closes a genuine hole. Without it, a caller can write SELECT * FROM my_orders WHERE slow_function_that_leaks(card_number), and the planner — trying to be helpful — may evaluate that cheap-looking function before the tenant filter, on rows from other tenants. The barrier tells the planner it may not move any user-supplied condition below the view's own conditions. It costs some optimisation freedom and it buys a real guarantee.

For serious row-level access control, PostgreSQL's row-level security policies are the stronger tool, and Chapter 8.4.10 covers the authorisation models behind both.

Job three: keep an old shape alive while the tables change underneath. This is the logical data independence promised in Chapter 7.1 section 4. You split a wide customers table into customers and customer_addresses, then create a view named customers_v1 that joins them back into the shape the old code expects. Old readers keep working, new code uses the real tables, and the view is deleted when the last old reader is gone. It is the read half of the expand-and-contract migration from Chapter 7.2.4.

Where you have already used a view without noticing: information_schema.tables, information_schema.columns, and most of what you query in pg_catalog are views over the database's internal tables. pg_stat_user_tables from Chapter 7.2.5 is one too. Every time you have looked up "what columns does this table have", a view answered you.

3. Writing through a view

A view is usually read-only in people's heads, and often is not in practice.

A simple view is automatically updatable. In PostgreSQL that means one base table, no DISTINCT, no GROUP BY, no aggregate, no window function, no set operation, no LIMIT, and every selected column being a plain column reference rather than an expression. Under those conditions INSERT, UPDATE and DELETE on the view are rewritten into the same statement against the base table.

CREATE VIEW pending_orders AS
SELECT id, customer_id, placed_at, status FROM orders WHERE status = 'pending';

UPDATE pending_orders SET status = 'paid' WHERE id = 1001;   -- works

And here is the surprise that has a name. Nothing stops you moving a row out of the view's own condition — that UPDATE sets status to 'paid', so the row immediately stops being visible through pending_orders. Worse, you can insert a row through the view that the view cannot see:

INSERT INTO pending_orders (customer_id, status) VALUES (42, 'cancelled');
-- Succeeds. The row is in orders. It is invisible in pending_orders.

WITH CHECK OPTION forbids that:

CREATE VIEW pending_orders AS
SELECT id, customer_id, placed_at, status FROM orders WHERE status = 'pending'
WITH CHECK OPTION;                                            -- (1)

(1) Now any row written through the view must satisfy the view's WHERE clause, so the INSERT above fails with new row violates check option for view "pending_orders". CASCADED (the default in the standard) applies the check to underlying views as well; LOCAL checks only this view's own condition. If your view exists to restrict what someone may touch, you want the check option, because otherwise the restriction only applies to reading.

For anything more complicated than a simple view, you write the behaviour yourself with an INSTEAD OF trigger, which intercepts the write and decides what to do with it. That is Chapter 7.2.9's material, and the classic use is a view joining two tables where an insert must become two inserts.

4. The two ways views go wrong

Nested views are the first. A view built on a view built on a view is easy to create and hard to reason about. The rewriter expands them all, so what looks like SELECT * FROM monthly_summary can expand into a fourteen-table join — and now the join-order limits from Chapter 7.2.5 section 7 apply, because the planner is suddenly optimising fourteen tables and join_collapse_limit is eight. Query plans on deep view stacks are unstable in a way that has no visible cause in the query text. Two levels is normal, four is a smell, and when you cannot say what a view expands to, you no longer know what your query costs.

The invisible cost of a wide view is the second. SELECT * FROM revenue_orders fetches every column the view defines, including the ones you did not want, and a view that joins in three extra tables to provide columns you never select still joins them — unless the planner can prove the join cannot change the row count, which it can only do when a foreign key guarantees exactly one match. A view designed for one report and reused by ten is usually doing work for nine of them that they never asked for.

5. Materialized views: store the answer, accept that it is old

Now the case a plain view cannot help with. The finance dashboard runs this:

SELECT date_trunc('day', placed_at) AS day,
       COUNT(*) AS orders, SUM(net_minor) AS revenue
FROM revenue_orders
GROUP BY 1;

It aggregates nine million rows and takes 6 seconds. Forty people load the dashboard each morning. Nothing about the answer for last Tuesday will ever change again, and the database recomputes it forty times a day anyway.

A materialized view is a query whose result is computed once and physically stored, like a table, and recomputed only when you say so.

CREATE MATERIALIZED VIEW daily_revenue AS
SELECT date_trunc('day', placed_at) AS day,
       COUNT(*) AS orders, SUM(net_minor) AS revenue
FROM revenue_orders
GROUP BY 1
WITH DATA;                                                    -- (1)

CREATE UNIQUE INDEX ON daily_revenue (day);                   -- (2)

(1) WITH DATA runs the query now and stores the rows — this is the default. WITH NO DATA creates the definition and leaves it empty, which is useful when you want to build it during a quiet hour, but the materialized view cannot be queried at all until it has been refreshed; you get materialized view "daily_revenue" has not been populated. (2) A unique index. It makes lookups fast, and section 6 shows the second reason it is not optional.

The dashboard now reads 400 pre-aggregated rows instead of nine million raw ones, and takes 3 ms.

The arrow direction is the whole difference. A view pushes your query down to the base tables every time. A materialized view answers from its own stored rows and only looks at the base tables when a refresh is run.

6. Refreshing, and the lock that surprises people

REFRESH MATERIALIZED VIEW daily_revenue;

This re-runs the whole query and replaces all the stored rows. It takes an exclusive lock, so nobody can read the materialized view while it refreshes. Your 6-second aggregate is now a 6-second outage for the dashboard. On a small view nobody notices; on a large one this is the thing that gets you paged.

REFRESH MATERIALIZED VIEW CONCURRENTLY daily_revenue;

CONCURRENTLY lets readers keep reading the old rows while the new ones are computed, then swaps in the differences. Two conditions, and both catch people out:

1. It requires a unique index on the materialized view — that is the real reason for the CREATE UNIQUE INDEX above. Without one you get cannot refresh materialized view "daily_revenue" concurrently and no further explanation. The index is how the refresh matches old rows against new ones to work out what changed.
2. It is slower than a plain refresh, sometimes much slower, because it builds the new result into a temporary table and then computes the difference row by row. You are trading total time for availability, which is nearly always the right trade on anything user-facing.

There is no automatic refresh in PostgreSQL. Nothing keeps a materialized view current for you. You schedule it — pg_cron, an application job, a nightly pipeline — and the schedule is now part of your system's correctness, because a refresh job that silently died on Tuesday means a dashboard that has been quietly showing Tuesday's numbers ever since. Give the materialized view a column recording when it was built, and show that timestamp on the dashboard:

CREATE MATERIALIZED VIEW daily_revenue AS
SELECT …, now() AS refreshed_at FROM …;

That one column converts a silent wrong-data incident into a visible "last updated three days ago" on the screen, and it costs nothing.

A plain refresh recomputes everything, even the 399 days that cannot have changed. PostgreSQL core has no incremental refresh, so the standard workaround for large history is to keep a real table instead and update only the recent part:

INSERT INTO daily_revenue_table
SELECT date_trunc('day', placed_at) AS day, COUNT(*), SUM(net_minor)
FROM revenue_orders
WHERE placed_at >= current_date - 3                            -- (1)
GROUP BY 1
ON CONFLICT (day) DO UPDATE                                    -- (2)
  SET orders = EXCLUDED.orders, revenue = EXCLUDED.revenue;

(1) Only the last three days get recomputed, which covers late-arriving data without touching history. (2) An upsert, so a day already present is updated in place rather than duplicated. This is a rollup table, and it is what most production systems actually run, because it refreshes in a second rather than six and it lets you decide exactly how far back "recent" reaches. What you give up is that the definition is now in a job rather than in the schema, so nothing stops the job and the table drifting apart.

7. What the other engines do

Oracle has the most complete implementation. It supports FAST REFRESH, which is a genuine incremental refresh: you create a materialized view log on each base table, the log records what changed, and the refresh applies only those changes. It also supports REFRESH ON COMMIT, which updates the materialized view as part of the transaction that changed the data — so it is never stale, at the price of making every write slower. And it can do query rewrite: the optimiser notices that a query you wrote against the base tables could be answered from a materialized view and silently uses it instead.

SQL Server calls them indexed views, and they work differently again. You add a unique clustered index to a view, and from that point the engine maintains it automatically inside every transaction that touches the base tables. Never stale, no refresh to schedule, and a real cost on every write. The restrictions are strict — WITH SCHEMABINDING, no outer joins, no subqueries, and COUNT_BIG(*) rather than COUNT(*) if the view aggregates.

MySQL has no materialized views at all. You build the rollup table from section 6 and refresh it with a scheduled event or an application job. This is worth knowing before a design meeting, because "we'll just materialize it" is not an available answer on MySQL.

The pattern across all four is one trade in different positions: when is the work done, and how stale may the answer be. Oracle's ON COMMIT and SQL Server's indexed views pay at write time and are never stale. PostgreSQL's scheduled refresh pays in a batch and is stale between runs. A plain view pays at read time and is never stale. There is no option that is cheap in all three places.

8. Choosing between the five things that look alike

A subquery, a CTE, a view, a materialized view and a table can all wrap the same SQL. The differences are exactly two questions: how long does the name live, and where does the answer live.

	Lives for	Stores rows	Stale?
Subquery	One query	No	Never
CTE	One query	Sometimes	Never
View	Forever	No	Never
Materialized view	Forever	Yes	Until refreshed
Rollup table	Forever	Yes	Until the job runs

Use a subquery when the logic is used once, inside one statement, and naming it would add nothing.

Use a CTE when one query has several steps and you want them named and read top to bottom. It disappears when the query ends. Chapter 7.2.2 covers when PostgreSQL materializes one and why that used to matter.

Use a view when several queries or several teams need the same definition, or when you are hiding columns and rows behind permissions, or when you are keeping an old shape alive during a migration. It costs nothing at read time as long as it does not aggregate.

Use a materialized view when the query is expensive, the answer changes slowly, and being a few minutes or hours behind is acceptable. Say the acceptable staleness out loud before you build it, because that number decides the refresh schedule and the refresh schedule decides whether you need CONCURRENTLY.

Use a rollup table when the materialized view is too big to recompute in full, or when you need to update only recent data, or when you are on MySQL.

And the honest fourth option is: fix the query first. A materialized view over a query that is slow because of a missing index is a refresh job you now have to operate forever, in exchange for a problem an index would have solved. Chapter 7.2.3 and 7.2.5 come before this page for a reason. Materialize when the work is genuinely irreducible — a real aggregate over a lot of rows — not when it is a plan problem in disguise.

What the interviewer will push on

"What is a view, and does it store data?" A stored query with a name; it stores no rows. Then go one level deeper than the definition: the engine rewrites the view's SQL into your query before planning, so your outer WHERE gets pushed down into the view's own index scan. That is why a simple view costs nothing, and it is the thing that separates a real answer from a textbook one.

"When is a view slow, then?" When the view aggregates, sorts, or limits — because a filter cannot be pushed below a GROUP BY, a window function or a LIMIT, so the whole thing is computed and then filtered. Also nested views, where the expansion produces more tables than the planner will reorder freely.

"View or materialized view?" Ask what the query costs and how stale the answer may be. Never stale and cheap to compute is a view. Expensive and tolerant of being minutes old is a materialized view. Then name the operational cost immediately: a materialized view needs a refresh schedule, and a dead refresh job is a silent wrong-data incident.

"What does REFRESH … CONCURRENTLY need, and why?" A unique index on the materialized view, because that is how the refresh matches new rows against old ones to apply only the differences and let readers keep reading meanwhile. Then add that it is slower in total than a plain refresh — you are trading throughput for availability.

"How would you stop support staff seeing card details?" A view exposing only the safe columns, then REVOKE on the base table and GRANT on the view. The tell is the revoke — without it the view is a convenience, not a control — plus knowing why it works: the view runs with its owner's privileges, not the caller's.

"Can you write through a view?" Yes, if it is simple: one table, no aggregate, no DISTINCT, no GROUP BY, plain column references. Then volunteer the trap: without WITH CHECK OPTION you can insert a row through the view that the view cannot see, which turns a restriction into a suggestion. Anything more complex needs an INSTEAD OF trigger.

One thing to volunteer: point out that PostgreSQL has no incremental refresh, so most production systems end up with a rollup table updated by an upsert over the last few days rather than a materialized view — it refreshes in a second instead of six and it handles late-arriving data. Mentioning that Oracle does have a true incremental refresh through materialized view logs, and SQL Server maintains indexed views inside the write transaction, shows you know it is an implementation choice rather than a law.

Recall

• A view stores the query, never rows. The engine rewrites the definition into your statement before planning, so an outer WHERE is pushed down to the leaves — a simple view costs nothing.
• Pushdown stops at a GROUP BY, a window function, DISTINCT, UNION, a LIMIT inside the view, or a volatile function. Those views cost what the operation costs, every time.
• Three real jobs: one definition instead of five copies; hiding columns and rows behind permissions; keeping an old table shape alive during a migration.
• A view runs with its owner's privileges, which is why REVOKE on the base table plus GRANT on the view works. security_invoker = true (PostgreSQL 15+) reverses it; security_barrier = true stops a caller's function being evaluated on rows it should not see.
• A simple view is automatically updatable. Without WITH CHECK OPTION you can write a row through a view that the view cannot see. Anything complex needs an INSTEAD OF trigger.
• A materialized view stores the answer. Fast to read, stale between refreshes, and nothing refreshes it for you — the schedule is part of your correctness.
• REFRESH takes an exclusive lock. REFRESH … CONCURRENTLY needs a unique index on the materialized view and is slower in total, which is the right trade for anything user-facing.
• PostgreSQL has no incremental refresh. The production answer is usually a rollup table upserted over the last few days. Oracle has true fast refresh via materialized view logs; SQL Server maintains indexed views inside the write transaction; MySQL has no materialized views at all.
• Same trade in different places: pay at write time and never be stale, pay in a batch and be stale between runs, or pay at read time. Nothing is cheap in all three.
• Fix the query before you materialize it. Materializing a plan problem buys you a refresh job to operate forever.

Self-test: Why does WHERE customer_id = 42 outside a view end up inside its index scan? · Name three things in a view that block that · Why does a view let someone read a table they have no permission on? · What exactly does REFRESH CONCURRENTLY need and why? · What can you insert through a view without WITH CHECK OPTION? · When is a rollup table the better answer than a materialized view?

Next: 7.2.7 follows the rows out of the database and into your program — what a result set physically is, why fetching ten million rows kills the application rather than the server, and the cursor that fixes it.